CODEWITHSUNDEEP
pythonpandas
pyhugs
pyhugs

Reputation: 47

Is there a way to iterate over multiple dataframes to write them to multiple excel sheets with formatting?

I am working in pandas with 8 data frames and wish to write each of them to their own sheet in an Excel workbook. I also would like to apply formatting to each of the sheets as well. I am able to do it if I copy and paste the code 8 times, and adjust for the sheet name. However I was attempting to clean up the code by using a for loop to iterate through my data frames. However I end up with the: TypeError: 'DataFrame' objects are mutable, thus they cannot be hashed. 

with pd.ExcelWriter('Excel_Test.xlsx') as writer:
    df_list = [df_1 ,df_2 ,df_3 ,df_4 ,df_5 ,df_6 ,df_7 ,df_8]   
    for sheet in df_list:
        sheet.to_excel(writer, sheet_name=sheet, index=False)
        worksheet = writer.sheets[sheet]
        worksheet.set_zoom(80)
        worksheet.set_column('A:A', 14)
        worksheet.set_column('B:B', 50)
        worksheet.set_column('C:C', 30)
        worksheet.set_column('D:D', 30)
        worksheet.set_column('E:E', 25)
        worksheet.set_column('F:F', 20)
        money_format = writer.book.add_format({'num_format': '$#,##0'})
        integer_format = writer.book.add_format({'num_format': '#,##0'})
        worksheet.set_column('H:H', 9, integer_format)
writer.save()

The goal is to be able to have the for loop iterate through my list of dataframes and write each one to excel with the set formatting.

Upvotes: 2

Views: 105

Answers (1)

sandris
sandris

Reputation: 1383

The sheet_name parameter should be a string, not the DataFrame object. Since you use the DataFrame object as a key in the writer.sheets dictionary, Python is trying to hash that.

Upvotes: 1

Related Questions