How to write data to excel with header using Pandas?

Question

I want to write the excel file with the header is

I have data and I want to fill out the excel file. Then the expected result is

This is my code but it does not show as my expected. Could you help me to fix it? Thanks

from pandas import DataFrame
id =['1','2']
width_man=['12','18']
height_man=['20','55']
age_man=['10','12']
width_woman=['15','11']
height_woman=['40','45']
age_woman=['11','15']

df = DataFrame({'ID': id, 'WIDTH': width_man, 'HEIGHT': height_man, 'AGE': age_man, 'WIDTH': width_woman, 'HEIGHT': height_woman, 'AGE': age_woman,})
df.to_excel('table.xlsx', sheet_name='sheet1', index=False)

THIS IS OUTPUT OF MY CODE

The data you have in your example doesn't match the desired output. — MichaelD
– MichaelD, Commented Aug 12, 2019 at 22:49

bkyada · Accepted Answer · 2019-08-13 02:29:28Z

2

Use below method,

import pandas as pd

id =['1','2']
width_man=['12','18']
height_man=['20','55']
age_man=['10','12']
width_woman=['15','11']
height_woman=['40','45']
age_woman=['11','15']

df1 = pd.DataFrame({'ID': id, 'WIDTH': width_man, 'HEIGHT': height_man, 'AGE': age_man})
df2 = pd.DataFrame({'ID': id, 'WIDTH': width_woman, 'HEIGHT': height_woman, 'AGE': age_woman})
writer = pd.ExcelWriter('table.xlsx')
df1.to_excel(writer, sheet_name='Sheet1', startrow=1, index=False) 
df2.to_excel(writer, sheet_name='Sheet1',startrow=1,
             startcol=5, index=False)
worksheet = writer.sheets['Sheet1']
worksheet.write_string(0, 2, 'MAN')
worksheet.write_string(0, 6, 'WOMAN')

writer.save()

edited Aug 13, 2019 at 2:29

answered Aug 12, 2019 at 23:59

bkyada

3321 silver badge9 bronze badges

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

3 Comments

KimHee Over a year ago

Thanks. How about header man and woman in middle of the row. It looks works

KimHee Over a year ago

What does it mean 0, 2, and 0,6?

bkyada Over a year ago

@KimHee (row - 0, column -2) & (row -0, column 6). Thank you for marking it as accepted answer :)

DavidW · Accepted Answer · 2019-08-13 05:56:14Z

You should a multi-level index in Pandas to structure your data so that it matches your desired output:

dfm = pd.DataFrame({'WIDTH': width_man, 'HEIGHT': height_man, 'AGE': age_man, },index=id)
dfw = pd.DataFrame({'WIDTH': width_woman, 'HEIGHT': height_woman, 'AGE': age_woman,},index=id)
df = pd.concat([dfm,dfw],axis=1,keys=['man','woman'])
df.to_excel('table.xlsx', sheet_name='sheet1', index=True, index_label='id')

I've also changed id to the index since you're clearly using it as the index.

(The format of the excel file has some small differences to your desired output - no blank column between "man" and "woman", and "id" as the index label is on a separate line. However the point remains that by using Pandas to structure the data correctly the spreadsheet generates correctly and automatically)

Collectives™ on Stack Overflow

How to write data to excel with header using Pandas?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related