writing an embedded python dictionary to excel

Question

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.

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

anky · Accepted Answer · 2021-04-19 17:14:51Z

1

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:

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:

edited Apr 19, 2021 at 17:14

answered Apr 19, 2021 at 16:36

anky

75.3k11 gold badges46 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SAPNONEXPERT · Accepted Answer · 2021-04-19 16:09:31Z

0

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

answered Apr 19, 2021 at 16:09

SAPNONEXPERT

592 silver badges11 bronze badges

3 Comments

Jonathan Hay Over a year ago

This is the same thing that I have now. It becomes unreadable as the number of key-value pairs becomes large. That's why I want to show the values right next to the key.

SAPNONEXPERT Over a year ago

Which key? I dont see one

Jonathan Hay Over a year ago

Age,Sex, Income are the keys. Right under each key I want to see their values, ideally like I specified above although anky's answer below is good enough for starting.

Collectives™ on Stack Overflow

writing an embedded python dictionary to excel

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related