Pandas数据透视表中实现小计与总计的技术求助

Alright, let's tackle how to add subtotals for groups and a grand total to your Pandas pivot table. This makes your summarized data far more readable, especially when sharing with teams or stakeholders. I'll walk you through step-by-step with examples for both basic and multi-index pivot tables.

Step 1: Prepare Sample Data #

First, let's create some sample data to work with—this mirrors a typical sales dataset, but you can swap it out for your own:

import pandas as pd

# Sample sales data
data = {
    'Region': ['North', 'North', 'South', 'South', 'East', 'East', 'West', 'West'],
    'Category': ['Electronics', 'Clothing', 'Electronics', 'Clothing', 'Electronics', 'Clothing', 'Electronics', 'Clothing'],
    'Sales': [1500, 800, 1200, 600, 1800, 900, 1400, 700]
}
df = pd.DataFrame(data)

Step 2: Build a Basic Pivot Table #

Let's start with a standard pivot table to summarize sales by Region and Category:

# Basic pivot table (sum of sales per region + category)
base_pivot = pd.pivot_table(df, values='Sales', index='Region', columns='Category', aggfunc='sum')
print("Basic Pivot Table:\n", base_pivot)

Output will look like this:

Category  Clothing  Electronics
Region                         
East           900         1800
North          800         1500
South          600         1200
West           700         1400

Step 3: Add Subtotals for Each Group #

Next, let's calculate a subtotal for each region (summing across categories) and attach it as a new column:

# Calculate subtotals per region (sum across columns)
region_subtotals = base_pivot.sum(axis=1).rename('Subtotal')

# Merge subtotals with the base pivot table
pivot_with_subtotals = base_pivot.join(region_subtotals)
print("\nPivot Table with Region Subtotals:\n", pivot_with_subtotals)

Now you'll see a new Subtotal column showing total sales per region:

Category  Clothing  Electronics  Subtotal
Region                                   
East           900         1800      2700
North          800         1500      2300
South          600         1200      1800
West           700         1400      2100

Step 4: Add a Grand Total Row #

Finally, let's compute the grand total for all columns and append it as the last row of the pivot table:

# Calculate grand total (sum across all rows)
grand_total = pivot_with_subtotals.sum(axis=0).rename('Grand Total')

# Append grand total to the pivot table
final_pivot = pivot_with_subtotals.append(grand_total)
print("\nFinal Pivot Table (Subtotals + Grand Total):\n", final_pivot)

The final output will include both subtotals and a grand total at the bottom:

Category      Clothing  Electronics  Subtotal
Region                                       
East               900         1800      2700
North              800         1500      2300
South              600         1200      1800
West               700         1400      2100
Grand Total       3000         5900     8900

Bonus: Multi-Index Pivot Tables #

If you're working with a multi-index pivot table (e.g., Region → City), here's how to add subtotals for each top-level group:

# Sample multi-level data
multi_data = {
    'Region': ['North', 'North', 'North', 'South', 'South', 'South'],
    'City': ['NY', 'NY', 'Chicago', 'LA', 'LA', 'Houston'],
    'Category': ['Electronics', 'Clothing', 'Electronics', 'Clothing', 'Electronics', 'Clothing'],
    'Sales': [1500, 800, 1200, 600, 1800, 900]
}
df_multi = pd.DataFrame(multi_data)

# Create multi-index pivot table
multi_pivot = pd.pivot_table(df_multi, values='Sales', index=['Region', 'City'], columns='Category', aggfunc='sum')

# Function to add subtotals for each region
def add_group_subtotals(group):
    subtotal_row = group.sum().rename(('Subtotal', ''))
    return pd.concat([group, pd.DataFrame([subtotal_row], index=[subtotal_row.name])])

# Apply subtotals to each region group
multi_pivot_with_subtotals = multi_pivot.groupby(level='Region').apply(add_group_subtotals)

# Add grand total
multi_grand_total = multi_pivot_with_subtotals.sum(axis=0).rename(('Grand Total', ''))
final_multi_pivot = pd.concat([multi_pivot_with_subtotals, pd.DataFrame([multi_grand_total], index=[multi_grand_total.name])])

print("\nMulti-Index Pivot with Subtotals & Grand Total:\n", final_multi_pivot)

This will insert a subtotal row under each region's cities, followed by a grand total at the bottom.

Key Takeaways #

• Use sum(axis=1) to calculate row-wise subtotals, sum(axis=0) for column-wise grand totals.
• join() or concat() lets you merge these totals back into your pivot table.
• For multi-index tables, use groupby(level=...) to apply subtotals to specific groups.

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