How to store list of dict to excel using pandas

user_9236039 0
Viewed 156

I am trying to store a List of dict to excel using pandas. Somehow I am not getting how to do it.

lstofdic = [{'96': ['1/3/21', '1/3/22']}, {'97': ['1/3/20']}, {'98': []}, {'180': ['1/4/7']}, {'181': []}]

enter image description here

I tried like below

import pandas as pd
writer = pd.ExcelWriter("out.xlsx")
for l in lstofdic:
    pd.DataFrame(l.keys(),l.values()).to_excel(writer,  header=['vlan','port'], sheet_name='sheet1', index=False)
writer.save()

Kindly help to solve my issue. Thanks in advance.

pandas dataframe python-3.x list dictionary
3 Answers

you can use list comprehension for getting dataframe and then join 'port' values by ',' by using str.join():

df=pd.DataFrame([[*x.keys(),*x.values()] for x in lstofdic],columns=['vlan','port'])
df['port']=df['port'].str.join(',')

output of df:

    vlan    port
0   96      1/3/21,1/3/22
1   97      1/3/20
2   98  
3   180     1/4/7
4   181

If you want excel file then you can use to_excel() method:

df.to_excel("out.xlsx", index=False)
user_142898920

Use list comprehension with flatten dictionaries and join values by ,:

df = pd.DataFrame([(k, ','.join(v)) for x in lstofdic for k, v in x.items()], 
                  columns=['vlan','port'])
print (df)
    vlan    port
0   96      1/3/21,1/3/22
1   97      1/3/20
2   98  
3   180     1/4/7
4   181

For write to excel:

writer = pd.ExcelWriter("out.xlsx")
df.to_excel(writer,  sheet_name='sheet1', index=False)
writer.save()

Or:

df.to_excel("out.xlsx", sheet_name='sheet1', index=False)
user_29010020

Try this code with bfill:

print(pd.DataFrame(lstofdic).T.bfill(axis=1).iloc[:, 0].reset_index().rename({'index': 'vlan', 0: 'port'}, axis=1))

Output:

  vlan              port
0   96  [1/3/21, 1/3/22]
1   97          [1/3/20]
2   98                []
3  180           [1/4/7]
4  181                []
user_87083640
Related