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

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

1
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 a new subclass in a Python extension based on an existing class voidtrance 6 1,457 Mar-25-2025, 06:37 PM
Last Post: voidtrance
  Create dual folder on different path/drive based on the date agmoraojr 2 1,387 Jan-21-2024, 10:02 AM
Last Post: snippsat
  __init__() got multiple values for argument 'schema' dawid294 4 9,850 Jan-03-2024, 09:42 AM
Last Post: buran
  Transposing a dataframe without creating NaN values doug2019 2 1,924 Mar-18-2023, 03:14 PM
Last Post: jefsummers
  Create new dataframe from old dataframe arvin 3 1,996 Jan-31-2023, 01:23 PM
Last Post: jefsummers
Lightbulb Help using Google Sheets matheuspimenta 0 1,261 Dec-15-2022, 05:36 PM
Last Post: matheuspimenta
  create new column based on condition arvin 12 4,489 Dec-13-2022, 04:53 PM
Last Post: jefsummers
  How do you create a scatterplot of dataframe using matplotlib? asdad 2 1,652 Dec-07-2022, 04:53 PM
Last Post: Larz60+
  How to loop through all excel files and sheets in folder jadelola 1 9,504 Dec-01-2022, 06:12 PM
Last Post: deanhystad
  Failing to print sorted files tester_V 4 2,651 Nov-12-2022, 06:49 PM
Last Post: tester_V

Forum Jump:

User Panel Messages

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