Multiple Statistics in a Pivot Table – Advanced pivot_table() Techniques 2026

Multiple Statistics in a Pivot Table – Advanced pivot_table() Techniques 2026

Creating pivot tables with multiple statistics (sum, mean, count, std, etc.) on the same or different columns is a very common requirement for professional reports. In 2026, Pandas pivot_table() makes this elegant and flexible using dictionaries and lists inside the aggfunc parameter.

TL;DR — How to Apply Multiple Statistics

• Use a dictionary to assign different functions to different columns
• Use a list to apply multiple functions to the same column
• Combine both approaches for rich multi-metric pivot tables
• Use margins=True to add grand totals

1. Multiple Statistics on Different Columns

import pandas as pd

# Create a small demo file so this example runs as-is
# (in your project, sales_data.csv is your real export)
import numpy as np
rng = np.random.default_rng(42)
n = 60
pd.DataFrame({
    "order_date": pd.date_range("2025-10-01", periods=n, freq="3D"),
    "customer_id": rng.integers(101, 121, n),
    "customer_name": rng.choice([" ana", "ben ", "chen", "dana"], n),
    "product_id": rng.integers(1, 9, n),
    "region": rng.choice(["North", "South", "East", "West"], n),
    "category": rng.choice(["Electronics", "Books", "Home"], n),
    "payment_method": rng.choice(["card", "cash", "wallet"], n),
    "segment": rng.choice(["consumer", "business"], n),
    "quantity": rng.integers(1, 6, n),
    "amount": rng.uniform(50, 2500, n).round(2),
}).assign(
    ship_date=lambda d: d["order_date"] + pd.Timedelta(days=2),
    status=lambda d: np.where(d["amount"] > 2000, "priority", "standard"),
).to_csv("sales_data.csv", index=False)

df = pd.read_csv("sales_data.csv", parse_dates=["order_date"])

# Helper columns: pivot_table() works best with real column names
df["month"] = df["order_date"].dt.to_period("M")
df["year"] = df["order_date"].dt.year

pivot = pd.pivot_table(
    df,
    values=["amount", "quantity"],
    index="region",
    columns="month",
    aggfunc={
        "amount": ["sum", "mean", "max"],     # multiple stats for amount
        "quantity": ["sum", "mean"]           # multiple stats for quantity
    },
    # no margins here: margins=True fails with a dict of function lists in pandas 3.1
).round(2)

print(pivot)

2. Multiple Statistics on the Same Column

sales_pivot = pd.pivot_table(
    df,
    values="amount",
    index=["region", "category"],
    columns="year",
    aggfunc=["sum", "mean", "count", "std", "min"],   # multiple functions on amount
    margins=True
).round(2)

print(sales_pivot)

3. Combined Approach – Rich Multi-Metric Pivot Table

report = pd.pivot_table(
    df,
    values=["amount", "quantity", "customer_id"],
    index="region",
    columns="category",
    aggfunc={
        "amount": ["sum", "mean", "max"],
        "quantity": "sum",
        "customer_id": "nunique"
    },
    # no margins here: margins=True fails with a dict of function lists in pandas 3.1
).round(2)

print(report)

4. Best Practices in 2026

• Use a dictionary in aggfunc when applying different statistics to different columns
• Use a list when you want several statistics on the same column
• Always round the final pivot table for clean reports
• Add margins=True to include grand totals
• Use meaningful index and column names (including date components via .dt)

Reading a Multi-Statistic Pivot

When the same column receives several functions, the result has two column levels: the statistic and the column it describes. You can pick one statistic with table["mean"] or table.xs("mean", axis=1, level=0), and you can flatten all levels into simple names. Thinking of the result as "one small pivot table per statistic, placed side by side" makes it easier to read.

Worked Example: Selecting One Statistic

This example calculates several statistics and then extracts just one layer.

import pandas as pd

df = pd.DataFrame({
    "region": ["N", "N", "S", "S"],
    "channel": ["web", "shop", "web", "web"],
    "amount": [10.0, 30.0, 20.0, 40.0],
})

t = df.pivot_table(index="region", columns="channel", values="amount",
                   aggfunc=["sum", "mean", "count"])
print(t["mean"])
# channel  shop   web
# region
# N        30.0  10.0
# S         NaN  30.0
print(t.columns.nlevels)  # 2 (statistic, channel)

The mean layer shows NaN for South in the shop channel because there were no such orders. Leave it as NaN for means; a zero would be a false average.

Common Mistakes

• Filling every gap with zero: right for sums and counts, wrong for means
• Losing track of levels: check t.columns.nlevels and t.columns.names
• Requesting too many statistics: three or four is usually enough
• Using margins with lists: unsupported in pandas 3.1 when a dictionary maps columns to lists

Quick Summary

• A list in aggfunc adds a column level per statistic
• Select a layer with t["mean"] or xs()
• Fill gaps only where zero is meaningful

Conclusion

Applying multiple statistics within a single pivot table is one of the most useful reporting techniques in Pandas. In 2026, using pivot_table() with a combination of dictionary and list in aggfunc allows you to create comprehensive, multi-dimensional reports that show totals, averages, counts, and more — all in one clean table. This approach is widely used for management dashboards and business intelligence reports.

Next steps:

• Create a pivot table that shows both total sales and average order value by region and category using multiple statistics

Related Tutorials

• Different Statistics in a Pivot Table – Advanced pivot_table() in Pandas 2026
• Filling Missing Values in Pivot Tables – Best Practices in Pandas 2026
• Multiple Grouped Summaries in Pandas – Advanced GroupBy Techniques 2026