Pandas ExcelWriter overwriting sheets using for loop

Question

I have a nested for loop that is taking data from a master file with multiple sheets and splits the data out by user for each sheet. I want to then write each users data to their own file with the same sheets as the master file.

Here is what I have so far:

tm_sheet_to_df_map = pd.read_excel(src_file_tm, sheet_name=None)

for key, value in sorted(tm_sheet_to_df_map.items(),reverse=True):           
    tm_group = value.groupby('TM')
    for TM, group_df in tm_group:
        attachment = attachment_path_tm / f'{TM}' / f'Q221 New Accounts - {TM}.xlsx'
        attachment1 = os.makedirs(os.path.dirname(attachment), exist_ok=True)
        writer = ExcelWriter(attachment, engine = 'xlsxwriter')
        group_df.to_excel(writer, sheet_name =f'{key}', index=False)
        writer.save()

PROBLEM - The above script creates a new file for each user, but will only write the final dataframe to each file, instead of adding all sheets from the master file. Any ideas how to write each sheet to the individual files? I've tried moving writer.save() outside the loop with no luck.

Answer 1

You need an "append" mode for ExcelWriter:

try:
    # append mode will fail if file does not exist
    writer = ExcelWriter(attachment, engine = 'openpyxl', mode="a")
except FileNotFoundError:
    writer = ExcelWriter(attachment, engine = 'openpyxl')

ExcelWriter docs