How to convert the given Excel file to a dictionary in Python?

user_13118394 0
Viewed 216

Excel data format:

enter image description here

The file, translated to csv, reads:

{'Details': {0: 'Mickey',
  1: nan,
  2: nan,
  3: nan,
  4: 'Doland',
  5: nan,
  6: nan,
  7: nan},
 'Unnamed: 1': {0: 'Mouse',
  1: nan,
  2: nan,
  3: nan,
  4: nan,
  5: nan,
  6: nan,
  7: nan},
 'Unnamed: 2': {0: 'Address1',
  1: 'city',
  2: 'street',
  3: 'hno',
  4: 'adress1',
  5: 'city',
  6: 'street',
  7: 'hno'},
 'Unnamed: 3': {0: nan, 1: 'xxx', 2: 2, 3: 22, 4: nan, 5: 'zzz', 6: 3, 7: 33},
 'Unnamed: 4': {0: 'address2',
  1: 'city',
  2: 'street',
  3: 'hno',
  4: 'address2',
  5: 'city',
  6: 'street',
  7: 'hno'},
 'Unnamed: 5': {0: nan, 1: 'yyy', 2: 3, 3: 33, 4: nan, 5: 'www', 6: 4, 7: 44}}

How to convert the attached Excel to a dictionary in Python?

Expected dictionary:

dict1 ={'Details':[['Mickey','Mouse',{'Address1': {'city': 'xxx', 'Street': '2', 'hno': '22'}},
                    {'Address2': {'city': 'yyy', 'Street': '3', 'hno': '33'}}],
                   ['Doland','Duck',{'Address1': {'city': 'xxx', 'Street': '2', 'hno': '22'}},
                    {'Address2': {'city': 'yyy', 'Street': '3', 'hno': '33'}}]]}
pandas excel dictionary python
2 Answers

This is the worst solution I can think of:

df = pd.read_excel('samp.xlsx', header=[0], 
                   index_col=[0,1])
d = {'details': []}
for i, grp in df.groupby('Details'):
    grp = (grp.T.ffill().T)
    grp.columns = grp.iloc[0]
    grp = grp[1:].T
    name = ', '.join(grp.index.name)
    val = grp.groupby(grp.index).apply(lambda x: dict(zip(x.iloc[0], x.iloc[1]))).to_dict()
    final_val = ([name,val])
    d['details'].append(final_val)

d:

{'details': [['Donald, Duck',
   {'Address1': {'City': 'zzz', 'Street': 3, 'hno': 33},
    'Address2': {'City': 'www', 'Street': 4, 'hno': 44}}],
  ['Mickey, Mouse',
   {'Address1': {'City': 'xxx', 'Street': 2, 'hno': 22},
    'Address2': {'City': 'yyy', 'Street': 3, 'hno': 33}}]]}

I have posted the link of excel file in the comment section.

Basically, I am using z loop for each accessing each group by name (mickey mouse, and donald duck) then I am applying operations on it.

Sorry, I am in hurry will add the explanation later. Let me know if you have difficulty understanding things.

this lune:

grp.T.ffill().T

will help me to fill the nan values with Address1/Address2. That's why I transposed it and then used ffill.

enter image description here

After that I want to make the starting row of each group as column. Once this step is done. My first row will be key and second row will be my value. So I zipped them together and created a dictionary. Hope this helps you better in understanding the logic.

user_66603730 
import xlrd
from xlrd import open_workbook

fp = tempfile.NamedTemporaryFile(delete= False, suffix=filetype)
fp.write(binascii.a2b_base64(selected file))
workbook = xlrd.open_workbook(file name)
sheet = workbook.sheet_by_name(sheet name)
row = [c or '' for c in sheet.row_values(header_row)]
first_row = [] 
 for col in range(sheet.ncols):
    first_row.append(sheet.cell_value(0,col) )
archive_lines = []
   for row in range(1, sheet.nrows):
      elm = {}
      for col in range(sheet.ncols):
         elm[first_row[col]]=sheet.cell_value(row,col)
      archive_lines.append(elm)
user_154071280
Related