CODEWITHSUNDEEP
pythonexcelpandasdictionary
Jonathan Hay
Jonathan Hay

Reputation: 315

writing an embedded python dictionary to excel

Objective: I need to take a python dictionary and export it to an excel workbook.
Here is the dictionary:

graph = {'Age':{'0-30':100,'31-50':106,'51-60':117,'60+':90},
     'Sex':{'Male':80,'Female':125,'Unknown':100},
     'Income':{'<25k':160,'25-75':110,'75-175':100,'>175':80},
     'Rent':{'1':160,'0':40},
     'Pets':{'Dog':120,'Cat':110,'Duck':80,'Other':50}}

Here is what I would like it to look like. Ideally with up to 4 key-value pairs across at one time.

enter image description here Here is what I have tried:

df = pd.DataFrame(graph)
writer = pd.ExcelWriter('index_vs_total.xlsx', engine='xlsxwriter')
workbook=writer.book
df.to_excel(writer, sheet_name='index_vs_total',startrow=3, index=True)
writer.save()

enter image description here

Upvotes: 1

Views: 76

Answers (2)

anky
anky

Reputation: 75080

You can try the below:

For columns:

writer = pd.ExcelWriter('index_vs_total.xlsx', engine='xlsxwriter')
i = 0
for k,v in graph.items():
    dataframe = pd.DataFrame({k:v})
    dataframe.to_excel(writer, sheet_name='index_vs_total',startcol=i, index=True)
    i=i+ dataframe.shape[1]+2
writer.save()

Output: enter image description here

For rows:

writer = pd.ExcelWriter('index_vs_total.xlsx', engine='xlsxwriter')
i = 0 #set the startrow
for k,v in graph.items():
    dataframe = pd.DataFrame({k:v})
    dataframe.to_excel(writer, sheet_name='index_vs_total',startrow=i, index=True)
    i=i+ len(dataframe)+2
writer.save()

Output:

enter image description here

Upvotes: 1

SAPNONEXPERT
SAPNONEXPERT

Reputation: 59

I would try df.to_csv('name', index=False)

Upvotes: 0

Related Questions