How to write Python dataframe to multiple sheets of multiple Excel files

Question

I have a Python Dataframe that I want to write to two Excel files each having three sheets. There are five columns, the first two go on all three sheets and the last three are rotated through the three sheets. The Excel files are based on the value of the first column. So each file will have the same three sheets (with different values, of course).

I have code to put the different columns onto different sheets of one Excel file. And I have code to create multiple Excel files based on a column value. I can't figure out the approach to combine these two techniques to create multiple Excel files each with multiple sheets.

Example dataframe:

df = pd.DataFrame({'School': ['School1', 'School1', 'School2', 'School2'], 
                   'Sex': ['M', 'M', 'F', 'F'],
                   'Q1' : ['Black', 'Black', 'White', 'White'],
                   'Q2' : ['Good', 'Good', 'Bad', 'Bad'],
                   'Q3' : ['Up', 'Up', 'Down', 'Down']})

This code will create a different Excel file based on the School column:

output = df[['School','Sex','Q1']].groupby('School')
output.apply(lambda x: x.to_excel('School' + str(x.name) + '.xlsx'))

This code will put different columns on different sheets of one Excel file:

writer = pd.ExcelWriter('school_tabs.xlsx', engine='xlsxwriter')
df[['School','Sex','Q1']].to_excel(writer, sheet_name='Q1')
df[['School','Sex','Q2']].to_excel(writer, sheet_name='Q2')
df[['School','Sex','Q3']].to_excel(writer, sheet_name='Q3')
writer.save()

The desired output would be:

Excel file 1

(sheet 1):
School    Sex  Q1
School 1  M    Black
School 1  M    Black

(sheet 2):
School    Sex  Q2
School 1  M    Good
School 1  M    Good

(sheet 3):
School    Sex  Q3
School 1  M    Up
School 1  M    Up

Excel file 2

(sheet 1):
School    Sex  Q1
School 2  F    White
School 2  F    White

(sheet 2):
School    Sex  Q2
School 2  F    Bad
School 2  F    Bad

(sheet 3):
School    Sex  Q3
School 2  F    Down
School 2  F    Down

rafaelc · Accepted Answer · 2019-08-05 21:27:08Z

5

IIUC, just iterate through your groupby object. That'll allow you to handle each data frame separately.

Using your own lines of code:

output = df[['School','Sex','Q1']].groupby('School')

for school, df_ in output:
    writer = pd.ExcelWriter(f'school_{school}_tabs.xlsx', engine='xlsxwriter')
    df_[['School','Sex','Q1']].to_excel(writer, sheet_name='Q1')
    df_[['School','Sex','Q2']].to_excel(writer, sheet_name='Q2')
    df_[['School','Sex','Q3']].to_excel(writer, sheet_name='Q3')
    writer.save()

answered Aug 5, 2019 at 21:27

rafaelc

59.4k15 gold badges64 silver badges87 bronze badges

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

1 Comment

Paul Over a year ago

The output line should be: output = df[['School','Sex','Q1','Q2','Q3']].groupby('School') Otherwise, it worked perfectly. Thank you!

Collectives™ on Stack Overflow

How to write Python dataframe to multiple sheets of multiple Excel files

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related