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

Answer 1

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