Python: populate list in dictionary from data in excel sheet using openpyxl-CodePudding

I am trying to take the below data from excel sheet and create a dictionary with a list of mac address values in this format siteinv_dict = {1741 : [XX:XX:XX:XX:XX:XX, YY:YY:YY:YY:YY:YY]} The list of mac addresses vary from col to col.

When trying to do it for just one number to try get it working i was trying something like this and failed but really i would like it to get each header number make it the key then list of mac addresses as values

wb = openpyxl.load_workbook('C:\Site_Details.xlsx', data_only=True)
sh = wb.active

siteno = "1741"
siteinv_dict = {}

for row in sh.rows:
    if row[3].value == "1741":
        for i in range(4):
            siteinv_dict ={siteno : [ ]}

CodePudding user response：

You can construct a deque by column of the workbook, then left pop everything off the deque using non-strings as keys and strings as a values list into a new dict.

import openpyxl
from collections import deque

wb = openpyxl.load_workbook('C:\Site_Details.xlsx', data_only=True)
sh = wb.active

siteinv_queue = deque([cell.value for col in sh.iter_cols() for cell in col if cell.value is not None])
siteinv_dict = {}

k = siteinv_queue.popleft()
v = []
while siteinv_queue:
    item = siteinv_queue.popleft()
    if isinstance(item, str):
        v.append(item)
    else:
        siteinv_dict.update({k:v})
        k, v = item, []             
siteinv_dict.update({k:v}) # don't forget the last dict

print(siteinv_dict)

Output

{1741: ['xx:xx:xx:f0:9b:3f', 'xx:xx:xx:f0:5b:3f', 'xx:xx:xx:f0:4b:3f', 'xx:xx:xx:f0:3b:3f'], 250: ['yy:yy:yy:f0:9b:3f', 'yy:yy:yy:f0:9b:4f'], 731: ['zz:zz:zz:zz:2a:23:12', 'zz:zz:zz:zz:2a:23:13', 'zz:zz:zz:zz:2a:23:14']}