Find matching strings and populate data using openpyxl

Admin 1
Viewed 401

I have a dictionary and data in Excel. I would like to iterate through all the rows in one column in the excel file and see if it matches the string from my 'key' in my dictionary.

Once matched I would like then map the value to a different column but same row.

For example - if the key matches at A2 then populate C2 with the dictionary value or if it matches at A8 then populate C8.

Dictionary example:

my_dict = {'data1':'A','data2':'B','data3':'C'}

Excel spreadsheet column:

    A   | B  |  C |
1  col1 |col2|col3|
2  data1|    |    |
3  apple|    |    |
4  pear||    |    |
5  data2|    |    |
6  data3|    |    |

Desired output in excel:

    A   | B  |  C |
1  col1 |col2|col3|
2  data1|    |A   |
3  apple|    |    |
4  pear||    |    |
5  data2|    |B   |
6  data3|    |C   |

What I have tried so far:

from openpyxl import load_workbook
wb = load_workbook('input.xlsx')
ws = wb['sheet1']

#iterate through column
for rownum in range(1, sheet.max_row + 1):
    cell = ws.cell(row=rownum, column=1).value
    #iterate through dictionary
    for k,v in my_dict.items():
        if cell == k:
            #find location
            print(cell.value,cell.row,cell.column,cell)
            print(cell +'match')
            #append value to correct cell

Please could someone point me in the right direction?

openpyxl excel dictionary python
1 Answers

Openpyxl has a built-in function for iterating through rows, iter_rows(). Docs for it can be found here. Using this, the solution is quite easy. Iterating through the rows, test to see if the value of your first column matches a key in your dictionary. If it does, write the corresponding value into the third column. The code below does this.

import openpyxl as op

wb = op.load_workbook("test.xlsx")
ws = wb["Sheet"]

my_dict = {'data1':'A', 'data2':'B', 'data3':'C'}

for row in ws.iter_rows(min_row=2, max_col=3):
    if row[0].value in my_dict:
        row[2].value = my_dict[row[0].value]

wb.save("text.xlsx")

Note that there is no need for you to iterate through each item in your dictionary (which is your second for loop). Using if X in my_dict directly loops over the keys in the dictionary.

user_126640400
Related