Python：如何在透视表中按自定义工作日顺序排序列索引

Hey there! Let's tackle the two issues you ran into with your DataFrame processing: getting your week columns in the right order (Monday first, Sunday last) and getting rid of those unwanted all-zero rows when using pd.Categorical.

Why the sort_index(key=...) Error Happens #

The key parameter for sort_index() when sorting columns (axis=1) was added in pandas 1.1.0. If you're using an older version of pandas, this parameter won't exist—hence the TypeError you saw. That's why that approach didn't work for you.

Why All-Zero Rows Appear With pd.Categorical #

When you mark day_week_name as a categorical column, pandas forces the pivot table to include every category in your list as a column, even if some [paymentType, type] combinations have no data (i.e., all zeros) across all days. These combinations get kept as rows, resulting in those all-zero entries.

Step-by-Step Solution #

Let's walk through the fix, using your original English day names (swap in the Russian ones if that's what you're using):

1. Define the Correct Week Order & Convert to Categorical #

First, we'll explicitly set the order of your week days and convert the column to a categorical type—this tells pandas exactly how to sort the columns later:

# Define your desired week order (English version)
week_order = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
# For Russian: week_order = ['Понедельник','Вторник','Среда','Четверг','Пятница','Суббота','Воскресенье']

# Convert the day_week_name column to a categorical with our custom order
week_view['day_week_name'] = pd.Categorical(
    week_view['day_week_name'],
    categories=week_order,
    ordered=True
)

2. Generate the Pivot Table #

Now when you create the pivot table, pandas will automatically use the categorical order for the columns—no need for extra sorting:

week_pivot = week_view.pivot_table(
    index=['paymentType', 'type'],
    columns=['day_week_name'],
    aggfunc={'value': 'sum'},
    fill_value=0
)

3. Filter Out All-Zero Rows #

To get rid of those rows where every value is zero, we'll check if each row has at least one non-zero entry and keep only those rows:

# Check which rows have at least one non-zero value
has_non_zero_data = (week_pivot != 0).any(axis=1)

# Keep only those rows
week_pivot = week_pivot.loc[has_non_zero_data]

Full Working Code #

Putting it all together:

import pandas as pd

# Assume your week_view DataFrame is already loaded/created
week_days = {0:'Monday', 1:'Tuesday', 2:'Wednesday', 3:'Thursday', 4:'Friday', 5:'Saturday', 6:'Sunday'}
week_view['day_week_name'] = week_view['day_week'].apply(lambda x: week_days[x])

# Set up categorical week order
week_order = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
week_view['day_week_name'] = pd.Categorical(week_view['day_week_name'], categories=week_order, ordered=True)

# Create pivot table with correct column order
week_pivot = week_view.pivot_table(
    index=['paymentType', 'type'],
    columns=['day_week_name'],
    aggfunc={'value': 'sum'},
    fill_value=0
)

# Filter out all-zero rows
has_non_zero_data = (week_pivot != 0).any(axis=1)
week_pivot = week_pivot.loc[has_non_zero_data]

print(week_pivot)

Bonus: If You Can Upgrade Pandas #

If you're able to update pandas to version 1.1.0 or newer, you can skip the categorical step entirely and use the key parameter for sort_index():

# Create pivot table without categorical conversion
week_pivot = week_view.pivot_table(
    index=['paymentType', 'type'],
    columns=['day_week_name'],
    aggfunc={'value': 'sum'},
    fill_value=0
)

# Sort columns using the custom week order
week_order = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
week_pivot = week_pivot.sort_index(
    axis=1,
    key=lambda col: col.map(lambda day: week_order.index(day))
)

# Filter all-zero rows same as before
has_non_zero_data = (week_pivot != 0).any(axis=1)
week_pivot = week_pivot.loc[has_non_zero_data]

Either approach will get you the correctly ordered columns and clean up those unwanted all-zero rows!

内容的提问来源于stack exchange，提问作者OcMaRUS