Python Forum
Xlsxwriter: Create Multiple Sheets Based on Dataframe's Sorted Values
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Xlsxwriter: Create Multiple Sheets Based on Dataframe's Sorted Values
#1
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!
Reply
#2
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.
KMV likes this post
Almost dead, but too lazy to die: https://sourceserver.info
All humans together. We don't need politicians!
Reply
#3
Thank you, DeaD_EyE. This is exactly what I wanted to achieve.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Create dual folder on different path/drive based on the date agmoraojr 2 378 Jan-21-2024, 10:02 AM
Last Post: snippsat
  __init__() got multiple values for argument 'schema' dawid294 4 1,891 Jan-03-2024, 09:42 AM
Last Post: buran
  Transposing a dataframe without creating NaN values doug2019 2 935 Mar-18-2023, 03:14 PM
Last Post: jefsummers
  Create new dataframe from old dataframe arvin 3 929 Jan-31-2023, 01:23 PM
Last Post: jefsummers
Lightbulb Help using Google Sheets matheuspimenta 0 688 Dec-15-2022, 05:36 PM
Last Post: matheuspimenta
  create new column based on condition arvin 12 2,133 Dec-13-2022, 04:53 PM
Last Post: jefsummers
  How do you create a scatterplot of dataframe using matplotlib? asdad 2 828 Dec-07-2022, 04:53 PM
Last Post: Larz60+
  How to loop through all excel files and sheets in folder jadelola 1 4,333 Dec-01-2022, 06:12 PM
Last Post: deanhystad
  Failing to print sorted files tester_V 4 1,188 Nov-12-2022, 06:49 PM
Last Post: tester_V
  Create multiple/single csv file for each sql records mg24 6 1,323 Sep-29-2022, 08:06 AM
Last Post: buran

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020