Python Forum

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

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

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!

Just access the Dataframe and not the Series object:

names = df.sort_values(["Sex", "Height"], ascending =[False, False])["Name"]

df["Name"] will return a Series object of the column "Name".
The columns you need ("Sex", "Height"), are not in the resulting series object.

Thank you, DeaD_EyE. This is exactly what I wanted to achieve.