Hello,
I would appreciate your help on figuring how to create multiple excel sheets based on sorted results from a dataframe using Xlsxwriter.
In the code below, I would like to create multiple sheets in Excel for each Name sorted by Sex and Height (1st sheet for John, 2nd for Simon etc.)
An "unhashable type: 'list' " type of error is returned as lists cannot be sorted.
Is there a convenient way to avoid this error and use the sorting?
Alternatively, is there a possibility to loop through the excel file which should contain the output from df.sort_values(["Sex", "Height"], ascending =[False, False]).to_excel(writer).
Thanks in advance!
I would appreciate your help on figuring how to create multiple excel sheets based on sorted results from a dataframe using Xlsxwriter.
In the code below, I would like to create multiple sheets in Excel for each Name sorted by Sex and Height (1st sheet for John, 2nd for Simon etc.)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
import pandas as pd import xlsxwriter as xl dictionary = { "Name" : [ "Peter" , "John" , "Paula" , "Simon" , "Jess" ], "Sex" : [ "M" , "M" , "F" , "M" , "F" ], "Height" : [ 190 , 175 , 178 , 182 , 163 ]} df = pd.DataFrame(dictionary) wsname = "Test Excel Sheet.xlsx" writer = pd.ExcelWriter(wsname, engine = "xlsxwriter" ) workbook = writer.book worksheet = writer.sheets df.sort_values([ "Sex" , "Height" ], ascending = [ False , False ]).to_excel(writer) # Create sorted (by Sex and Height) sheets for each Name in the output spreadsheet names = df[ "Name" ].sort_values([ "Sex" , "Height" ], ascending = [ False , False ]) for name in names: workbook.add_worksheet(name) writer.save() |
Is there a convenient way to avoid this error and use the sorting?
Alternatively, is there a possibility to loop through the excel file which should contain the output from df.sort_values(["Sex", "Height"], ascending =[False, False]).to_excel(writer).
Thanks in advance!