Python: dictionaries of different dimensions to excel

Question

Is there any effcient way to write different dimensions dictionaries to excel using pandas?

Example:

import pandas as pd mylist1=[7,8,'woo'] mylist2=[[1,2,3],[4,5,6],['foo','boo','doo']] d=dict(y=mylist1,x=mylist2) df=pd.DataFrame.from_dict(d, orient='index').transpose().fillna('') writer = pd.ExcelWriter('output.xls',engine = 'xlsxwriter') df.to_excel(writer) writer.save()

The current results,

The desired results,

Please note that my database is much bigger than this simple example. So a generic answer would be appreciated.

Henry Yik · Accepted Answer · 2020-05-22 11:35:16Z

You can fix your dataframe first before exporting to excel:

df=pd.DataFrame.from_dict(d, orient='index').transpose() df = pd.concat([df["y"],pd.DataFrame(df["x"].tolist(),columns=list("x"*len(df["x"])))],axis=1)

Or do it upstream:

df = pd.DataFrame([[a, *b] for a,b in zip(mylist1, mylist2)],columns=list("yxxx"))

Both yield the same result:

 y x x x 0 7 1 2 3 1 8 4 5 6 2 woo foo boo doo

Dishin Goyani · Accepted Answer · 2020-05-22 13:24:49Z

1

Get first appropriate format then save to excel.

df = df.join(df.x.apply(pd.Series)).drop('x',1) df.columns = list('yxxx') df y x x x 0 7 1 2 3 1 8 4 5 6 2 woo foo boo doo

For Dynamic columns name

df.columns = ['y'] + list('x' * (len(df.columns)-1))

edited May 22, 2020 at 13:24

answered May 22, 2020 at 11:51

Dishin Goyani

7,7533 gold badges33 silver badges42 bronze badges

1 Comment

Hazem Over a year ago

Thank you [upvoted], this worked. Is there anyway to preserve the df keys while applying this solution? I do understand that the second line of your code does this. But I am trying to acheive the task dynamically. i.e. I may not know how many column x does have.

Collectives™ on Stack Overflow

Python: dictionaries of different dimensions to excel

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related