Multiple statistics in pivot table

Calculating multiple statistics in a pivot table is one of the most powerful features of pivot_table() in Pandas — it lets you summarize multiple columns with different aggregation functions (sum for sales, mean for profit, count for transactions, etc.) in a single, clean cross-tab view. This is ideal for financial reports, performance dashboards, cohort analysis, and any multi-metric breakdown.

In 2026, pivot tables remain essential for turning raw data into actionable, multi-dimensional summaries. Here’s a practical guide with real examples you can copy and adapt.

1. Basic Setup & Sample Data


import pandas as pd

data = {
    'Region': ['North', 'North', 'South', 'South', 'West', 'West'],
    'Salesperson': ['Alice', 'Bob', 'Charlie', 'Dave', 'Eve', 'Frank'],
    'Sales': [100, 200, 150, 50, 75, 125],
    'Profit': [20, 50, 30, 10, 15, 25]
}

df = pd.DataFrame(data)
print(df.head())

2. Multiple Statistics per Column in a Pivot Table

Pass a list of functions to aggfunc to compute several stats (sum, mean, count, etc.) for each value column.


pivot_multi_stat = pd.pivot_table(
    df,
    values=['Sales', 'Profit'],
    index='Region',
    columns='Salesperson',
    aggfunc=['sum', 'mean', 'count'],  # multiple stats for each metric
    fill_value=0,                      # replace NaN with 0
    margins=True                       # add grand totals
)

print(pivot_multi_stat)

Output (multi-level columns with sum, mean, count for each metric):


                sum                  mean               count
            Profit   Sales Profit   Sales Profit Sales
Salesperson  Alice   Bob Charlie Dave Eve Frank Alice   Bob Charlie Dave Eve Frank Alice Bob Charlie Dave Eve Frank
Region                                                                                                            
North          20.0  50.0     0.0  0.0  0.0   0.0  20.0  50.0     0.0  0.0  0.0   0.0     1   1       0    0   0     0
South           0.0   0.0  30.0 10.0  0.0   0.0   0.0   0.0  30.0 10.0  0.0   0.0     0   0       1    1   0     0
West            0.0   0.0   0.0  0.0 15.0  25.0   0.0   0.0   0.0  0.0 15.0  25.0     0   0       0    0   1     1
All            20.0  50.0  30.0 10.0 15.0  25.0  20.0  50.0  30.0 10.0 15.0  25.0     1   1       1    1   1     1

3. Different Statistics per Column (Recommended)

Use a dictionary in aggfunc for full control — different functions for different metrics.


pivot_diff_stat = pd.pivot_table(
    df,
    values=['Sales', 'Profit'],
    index='Region',
    columns='Salesperson',
    aggfunc={'Sales': 'sum', 'Profit': 'mean'},
    fill_value=0,
    margins=True
)

print(pivot_diff_stat)

Output (clean, different stats per column):


           Profit               Sales             
            mean                       sum        
Salesperson     Alice   Bob Charlie  Dave   Eve Frank   Alice   Bob Charlie  Dave   Eve Frank   All
Region                                                                                           
North        20.000000  50.0     0.0   0.0   0.0   0.0   100.0 200.0     0.0   0.0   0.0   0.0   300
South              0.0   0.0  30.0  10.0   0.0   0.0     0.0   0.0 150.0  50.0   0.0   0.0   200
West               0.0   0.0   0.0   0.0  15.0  25.0     0.0   0.0   0.0   0.0  75.0 125.0   200
All          20.000000  50.0  30.0  10.0  15.0  25.0   100.0 200.0 150.0  50.0  75.0 125.0   700

4. Real-World Example: Sales, Profit & Count by Region & Year

Add time dimension and compute different stats.


# Add 'Year' for multi-index
df['Year'] = [2025, 2025, 2026, 2026, 2025, 2026]

pivot_year = pd.pivot_table(
    df,
    values=['Sales', 'Profit'],
    index=['Region', 'Year'],
    columns='Salesperson',
    aggfunc={'Sales': 'sum', 'Profit': ['mean', 'sum']},
    fill_value=0
)

print(pivot_year)

5. Modern Alternative in 2026: Polars

For large datasets, Polars is often faster and more memory-efficient with a similar pivot API.


import polars as pl

df_pl = pl.DataFrame(data)
pivot_pl = df_pl.pivot(
    index="Region",
    columns="Salesperson",
    values=["Sales", "Profit"],
    aggregate_function={"Sales": "sum", "Profit": "mean"}
)
print(pivot_pl)

Best Practices & Common Pitfalls

Use dictionary in aggfunc for different functions per column — most flexible
Always specify fill_value=0 or handle NaN explicitly
Add margins=True for grand totals — great for reports
Reset index after pivot if you need Region/Year as regular columns
For huge data, prefer Polars or chunked processing
Visualize: pivot_diff.plot(kind='bar', stacked=True) for instant insights

Conclusion

Calculating multiple statistics in pivot tables with pivot_table() and dictionary/list aggfunc turns raw data into rich, multi-metric summaries — sum of sales, mean of profit, count of transactions, all cross-tabbed by dimensions. In 2026, use Pandas for readability and flexibility on small-to-medium data, and Polars for speed on large datasets. Master values, index, columns, aggfunc, margins, and fill_value, and you'll build powerful, multi-dimensional reports in minutes.

Next time you need different metrics across categories — reach for pivot_table with dictionary aggfunc first.