How can I create a dictionary from two different row values from an Excel file?

Question

If I have two rows in excel file:

say like a,b,c,d... and another row with values 100,121,98,09,100,45,... with same number of columns in both rows; How do we make a dictionary in python, by reading from the xlsx file.... with keys as a,b,c,...with corresponding values in the other row of same column.

#to get the two rows
l1=[]
l2=[]
for x in [row1_num,row2_num]:
   cells=xl_sheet.row_slice(rowx=x,start_colx=20,end_colx=xl_sheet.ncols)
   for idx,cellobj in enumerate(cells):
       #based on the idx...should have a dictionary with values in row1 as keys
       #and row2 as values
       if(cell_obj.value is not None):
          if x==row1_num:
             l1.append(cell_obj.value)
          elif x==row2_num:
             l2.append(cell_obj.value)

dictionary=dict(zip(l1,l2))

Is this a good approach? also the list1 l1 is becoming empty when coming out of the for loop?

If `row1` and `row2` are lists (or other iterables): `dict(zip(row1, row2))`. — martineau, Nov 12 '19 at 22:44
what excel library are you using, i can probably use that to write a solution with your code — oppressionslayer, Nov 12 '19 at 23:04
using xlrd to open the workbook in memory, using file contents read and sheet_by_name to get the sheet — zoref, Nov 12 '19 at 23:18

score 1 · Answer 1 · answered Nov 12 '19 at 22:53

Here's an example with just a bit of a spin on the example from the w3schools zip () tutorial.

row1 = ("a", "b", "c")
row2 = (1, 2, 3)

x = zip(row1, row2)

print(dict(x))

This will output: {'a': 1, 'b': 2, 'c': 3}

I believe you can incorporate your additional xl_sheet logic easily enough. Happy coding!

dmmfll · Answer 2 · 2019-11-13T01:12:45.930

You have a nice start there. The hard part for me when solving this problem for myself was figuring out how to chunk the rows into pairs.

Here are two solutions to this problem. The first one might be a little easier conceptually to see what is going on. And then I discovered a very slick way to chunk things using itertools.zip_longest. See How to chunk a list in Python 3?

 import urllib.request
 import xlrd
 import itertools as it

 # Fake data stored on my Minio server.
 url = "https://minio.apps.selfip.com/mymedia/xlxs/fake_xl.xlxs"

 filepath, response = urllib.request.urlretrieve(url, "/tmp/test.xlxs")

 wb = xlrd.open_workbook(filepath)
 ws = wb.sheet_by_index(0)

 odd_rows, even_rows = it.tee(ws.get_rows()) # Get 2 iterables using itertools.tee
 row_pairs = ( # generator expression to "chunk the rows into groups of 2"
     [[cell.value for cell in row] for row in rows] # 2D list of values
     for rows in zip(
         it.islice(odd_rows, 0, ws.nrows, 2), # Use itertools.islice, odd
         it.islice(even_rows, 1, ws.nrows, 2), # even
     )
 )
 # zip is useful for "zipping" key value pairs together.
 print([dict(zip(*row_pair)) for row_pair in row_pairs])

 # Another way to chunk the rows into pairs is like this:

 print([
     dict(zip(*[[cell.value for cell in pair] for pair in pairs]))
     for pairs in it.zip_longest(*it.repeat(iter(ws.get_rows()), 2))
 ])

Ouput for both:

[{'2a5de626': 'algorithm', '86ce99a2': 'implementation', 'e6b481ba': 'adapter', 'bc85c996': 'capability', '4edfb828': 'array', '05d79ce2': 'definition', 'b9b5ae33': 'knowledgebase', 'f0da7366': 'complexity', '39a48259': 'methodology', '1ee95d9e': 'strategy'}, {'01bc389d': 'neural-net', 'd5d16b0c': 'monitoring', 'd9fb3a8d': 'installation', '8c7a049f': 'moratorium', 'f3d9aa0e': 'help-desk', 'd0e8d371': 'paradigm', '9e33f679': 'complexity', '6354affc': 'core', '606c4eb6': 'groupware', '97741196': 'strategy'}, {'76ae32df': 'algorithm', '942654da': 'task-force', '462fa31b': 'ability', '584df007': 'adapter', 'f6293960': 'attitude', 'afd8fa00': 'knowledgebase', '4c5f2c49': 'alliance', '6d76c690': 'collaboration', '3018a22b': 'solution', '034f1bb2': 'access'}]

How can I create a dictionary from two different row values from an Excel file?

2 Answers2