Replacing the old numbers in a list with new numbe

Question

import xlrd
fileWorkspace = '/Users/Bob/Desktop/'

wb1 = xlrd.open_workbook(fileWorkspace + 'Book1.xlsx')
sh1 = wb1.sheet_by_index(0)

colA,colB,colC,colD = [],[],[],[]
for a in range(3,sh1.nrows):
    colA.append(int(sh1.cell(a,0).value))
    colB.append(float(sh1.cell(a,1).value))
    colC.append(str(sh1.cell(a,2).value))
    colD.append(str(sh1.cell(a,3).value))
print(colA)
print(colB)   # Need to add 10 if indicated
print(colC)
print(colD)   # Adding 10 is indicated by "add10" 
print('\n')

update_colB_values = []
for i in range(3,sh1.nrows):    
    if sh1.cell(i,3).value == "add10":
        add = (sh1.cell(i,1).value) + 10
        update_colB_values.append(add)
print(update_colB_values)

I am getting all the new values for the values that has "add10" beside them. How do I create an array that replaces those values that had "add10" beside them with the new ones?

Initially pulled from the excel file ColB prints:

[32.0, 5.0, 12.0, 1.0, 87.0, 9.0]  # This is fine

After the update, I want to have a list that updates the list because 10.0 has been added to them. So, the new colB should be:

[42.0, 15.0, 12.0, 1.0, 87.0, 19.0]   # new_colB. This is what I want after the update.

The whole output as of now:

[0, 1, 2, 3, 4, 5]
[32.0, 5.0, 12.0, 1.0, 87.0, 9.0]
['', '', '', '', '', '']
['add10', 'add10', '', '', '', 'add10']


[42.0, 15.0, 19.0] 

Excel file image: Excel File

Answer 1

Right now you are only appending the values to the updated list that have been changed. Instead, you want to append all values, while updating the values that need to be added to. Here's a form using your existing columns with a list comprehension:

colB = [32.0, 5.0, 12.0, 1.0, 87.0, 9.0]
colD = ['add10', 'add10', '', '', '', 'add10']

update_colB_values = [B + 10 if D == 'add10' else B for B, D in zip(colB, colD)]
# [42.0, 15.0, 12.0, 1.0, 87.0, 19.0]

You should be able to replace your instantiation and for loop with this line.

It should be noted that the built-in zip is a very useful function that you may be able to utilize in your earlier code. For example, it's base behavior is to "tranpose" a 2D list.

matrix = [['a1', 'a2', 'a3'],
          ['b1', 'b2', 'b3'],
          ['c1', 'c2', 'c3']]

for row in zip(*matrix):
    print(list(row))

# ['a1', 'b1', 'c1']
# ['a2', 'b2', 'c2']
# ['a3', 'b3', 'c3']

The function zip takes an arbitrary number of positional arguments and matches all the ith elements together. As soon as one of the rows is exhausted, iteration ends, so zip_longest (Python 3) or izip_longest (Python 2) from itertools is useful if you want to pad.

To do this using your existing code, you can add an else statement so that you include the elements that don't need 10 added to them.

update_colB_values = []
for i in range(3,sh1.nrows):    
    if sh1.cell(i,3).value == "add10":
        add = (sh1.cell(i,1).value) + 10
        update_colB_values.append(add)
    else:                                    # add
        value = sh1.cell(i,1).value          # these
        update_colB_values.append(value)     # lines