如何在Pandas数据透视表中按行分组添加小计？

Hey there! You’re totally right—Pandas’ built-in pivot_table with margins=True only adds a grand total at the very end, not the per-group subtotals that Excel’s pivot tables automatically insert after each grouping level. Luckily, there are a few straightforward ways to replicate that Excel-style subtotal behavior with Pandas. Let’s walk through two reliable approaches:

Approach 1: Generate Subtotals Separately and Combine #

This method creates subtotals for each grouping level first, then concatenates them with your original pivot table data and sorts the result.

import pandas as pd
import numpy as np

# Example implementation of your `f` function (adjust to match your actual logic)
def f(row):
    # Assume Financial Year starts in April
    if row['Month'] >= 4:
        return f"FY{row['Year'] + 1}"
    else:
        return f"FY{row['Year']}"

# Your original data processing
ramka = pd.DataFrame(frame)
ramka.columns = ['PPE ID', 'Volume Actual', 'Month', 'Year', 'Volume Forecast' , 'Volume difference' ]
ramka['Financial Year'] = ramka.apply(f, axis=1)

# Step 1: Create base pivot table without margins
base_pivot = pd.pivot_table(
    ramka,
    index=['Financial Year','Year','Month'],
    values=['Volume Actual', 'Volume Forecast' , 'Volume difference'],
    aggfunc=np.sum
)

# Step 2: Generate subtotals for each level
# Year-level subtotals (under each Financial Year)
year_subtotals = base_pivot.groupby(level=['Financial Year', 'Year']).sum()
year_subtotals.index = pd.MultiIndex.from_tuples(
    [(fy, yr, 'Subtotal') for fy, yr in year_subtotals.index],
    names=['Financial Year', 'Year', 'Month']
)

# Financial Year-level subtotals
fy_subtotals = base_pivot.groupby(level='Financial Year').sum()
fy_subtotals.index = pd.MultiIndex.from_tuples(
    [(fy, 'Subtotal', '') for fy in fy_subtotals.index],
    names=['Financial Year', 'Year', 'Month']
)

# Step 3: Combine all data and sort
final_pivot = pd.concat([base_pivot, year_subtotals, fy_subtotals]).sort_index()

# Optional: Add a label to distinguish detail rows from subtotals
final_pivot['Row Type'] = 'Detail'
final_pivot.loc[final_pivot.index.get_level_values('Month') == 'Subtotal', 'Row Type'] = 'Year Subtotal'
final_pivot.loc[final_pivot.index.get_level_values('Year') == 'Subtotal', 'Row Type'] = 'FY Subtotal'

print(final_pivot)

Approach 2: Use groupby.apply to Insert Subtotals Inline #

This method uses groupby.apply to dynamically insert subtotal rows directly after each group, which feels more similar to how Excel builds pivot tables.

# Reuse your base_pivot from Approach 1

# Function to add subtotals for a single Year group
def add_month_subtotals(group):
    subtotal = group.sum(numeric_only=True).to_frame().T
    # Set index to mark this as a subtotal
    subtotal.index = pd.MultiIndex.from_tuples(
        [(group.index[0][0], group.index[0][1], 'Subtotal')],
        names=['Financial Year', 'Year', 'Month']
    )
    return pd.concat([group, subtotal])

# Add month subtotals to each Year group
with_month_subtotals = base_pivot.groupby(
    level=['Financial Year', 'Year'],
    group_keys=False
).apply(add_month_subtotals)

# Function to add subtotals for a single Financial Year group
def add_fy_subtotals(group):
    subtotal = group.sum(numeric_only=True).to_frame().T
    subtotal.index = pd.MultiIndex.from_tuples(
        [(group.index[0][0], 'Subtotal', '')],
        names=['Financial Year', 'Year', 'Month']
    )
    return pd.concat([group, subtotal])

# Add Financial Year subtotals
final_pivot = with_month_subtotals.groupby(
    level='Financial Year',
    group_keys=False
).apply(add_fy_subtotals).sort_index()

print(final_pivot)

Key Notes: #

• Both methods will give you subtotals after each Year and Financial Year group, just like Excel.
• Adjust the subtotal labels (like 'Subtotal') or the grouping levels to match your exact needs.
• If you want a grand total at the end, simply append base_pivot.sum().to_frame().T with an appropriate index (e.g., ('Grand Total', '', '')).

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