CODEWITHSUNDEEP
python-3.xpandasdictionary
Hazem
Hazem

Reputation: 380

Python: dictionaries of different dimensions to excel

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,

enter image description here

The desired results,

enter image description here

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

Upvotes: 1

Views: 86

Answers (2)

Dishin H Goyani
Dishin H Goyani

Reputation: 7723

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))

Upvotes: 1

Henry Yik
Henry Yik

Reputation: 22503

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

Upvotes: 1

Related Questions