Concat and write values in new column using python

Question

import xlrd

book = xlrd.open_workbook("D:/Book.xlsx")
sh = book.sheet_by_name('Sheet4')

for rowidx in range(sh.nrows):
    row = sh.row(rowidx)
    for colidx, cell in enumerate(row):
        if cell.value == "Issue":
            print(sh.name)
            print(colidx, rowidx)
            data_colidx = colidx
            data_rowidx = rowidx
first_row = []
i = int(data_rowidx)+1
concat_val = []
for x in range(sh.nrows):
    first_row.append(sh.cell_value(i, data_colidx))
    i = i + 1
    if i >= sh.nrows:
        break
    if sh.cell(i, data_colidx).value == xlrd.empty_cell.value:
        #print(i)
        concat_val.append(sh.cell_value(i, 0) + '.' + sh.cell_value(i, 1))

Reading every row in the 'Issue' Column and if it is empty im concating the 'Dataset' and 'variable' column values.
What should do, if I need to concat both Dataset and Variable column and write in a new column.

@CDP1802 inserting a new column after 'Variable', before 'Issue' — kar_n
– kar_n, Commented Mar 16, 2020 at 17:49
xlrd only reads see stackoverflow.com/questions/41886791/…. Do you have openpyxl — CDP1802
– CDP1802, Commented Mar 16, 2020 at 18:04
@CDP1802 I thought of using xlsx writer, but I'm open to use openpyxl too — kar_n
– kar_n, Commented Mar 16, 2020 at 18:10
Why are you using xlrd ? Last I checked the library was unmaintained and openpyxl was the recommended replacement. — AMC
– AMC, Commented Mar 16, 2020 at 21:57

CDP1802 · Accepted Answer · 2020-03-17 08:14:15Z

1

from openpyxl import load_workbook

# open workbook 
excel_file = 'D:/Book.xlsx'
wb = load_workbook(excel_file)
ws = wb["Sheet4"]
r = 1
while ws.cell(r,3).value != "Issue":
    r += 1
    if r > ws.max_row:
       exit("Issue not found in Col C")

# insert new column
ws.insert_cols(3)

# start search next row    
updates = 0
r += 1

while r <= ws.max_row:
    if ws.cell(r,4).value is None:
        colA = ws.cell(r,1).value
        colB = ws.cell(r,2).value

        if colA is None:
            if colB is None: s = ""
            else: s = colB
        elif colB is None:
            s = colA
        else:
            s = colA + "." + colB 

        ws.cell(r,3).value = s
        updates += 1
    r += 1    

# save with new name
updated_file = excel_file.replace(".xlsx", "_upd.xlsx")
wb.save( updated_file )
wb.close
print(str(updates) + " rows updated. File created " + updated_file)

edited Mar 17, 2020 at 8:14

answered Mar 16, 2020 at 20:25

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

kar_n Over a year ago

this line 'if ws.cell(r,4).value is None: s = ws.cell(r,1).value + "." + ws.cell(r,2).value' TypeError: unsupported operand type(s) for +: 'NoneType' and 'str'. I made small modifications and its working fine. Thank you

kar_n Over a year ago

No, both columns are not empty. But some of the cells in the 'Issue' column is empty

kar_n Over a year ago

I'm using python 3.7. I think None in if ws.cell(r,4).value is None: is causing the error. there is no error if I remove the is None

kar_n Over a year ago

Let us continue this discussion in chat.

Collectives™ on Stack Overflow

Concat and write values in new column using python

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related