Different Statistics in a Pivot Table – Advanced pivot_table() in Pandas 2026

Different Statistics in a Pivot Table – Advanced pivot_table() in Pandas 2026

One of the most powerful features of pivot_table() is the ability to apply different aggregation functions to different columns or even multiple functions to the same column. In 2026, this capability makes pivot_table() the go-to tool for creating rich, multi-metric summary reports.

TL;DR — How to Use Different Statistics

• Use a dictionary in aggfunc to assign different functions per column
• Use a list of functions for multiple statistics on the same column
• Combine with index and columns for multi-dimensional views

1. Different Statistics for 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"],      # two stats for amount
        "quantity": "sum"               # only sum 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"],
    margins=True
).round(2)

print(sales_pivot)

3. Advanced Example – Mixed Aggregations

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 you need different statistics per column
• Use a list of functions when you want multiple stats on the same column
• Always include margins=True for grand totals when creating reports
• Round results for clean, professional-looking output
• Combine with date components extracted via .dt for time-based pivots

Why Columns Need Different Statistics

Each column has its own meaning, so the right summary differs. Revenue should be summed, price should be averaged, and order IDs should be counted. Passing a dictionary to aggfunc maps each value column to its own function, which keeps the pivot table honest: a summed price or an averaged revenue would be technically correct and practically misleading.

When a column gets a list of functions, pandas creates MultiIndex columns such as ("amount", "sum"), which you can flatten afterwards.

Worked Example: Revenue, Average Price and Order Count

This example applies three different statistics in one pivot.

import pandas as pd

df = pd.DataFrame({
    "region": ["A", "A", "B", "B", "B"],
    "amount": [100.0, 50.0, 30.0, 70.0, 20.0],
    "price": [10.0, 5.0, 3.0, 7.0, 4.0],
    "order_id": [1, 2, 3, 4, 5],
})

table = df.pivot_table(index="region",
                       values=["amount", "price", "order_id"],
                       aggfunc={"amount": "sum", "price": "mean", "order_id": "count"})
print(table)
#         amount  order_id  price
# region
# A        150.0         2  7.500000
# B        120.0         3  4.666667

pandas sorts the value columns alphabetically. Reorder them with table[["amount", "price", "order_id"]] if a specific order matters.

Common Mistakes

• One function for all columns: sums of prices or means of IDs are meaningless
• Expecting the dictionary order to be kept: reorder columns explicitly
• Margins with function lists: in pandas 3.1, compute totals separately
• Counting with "count" on a column with gaps: missing values are skipped; use "size" to count rows

Quick Summary

• Map each column to the statistic that fits its meaning
• Lists of functions create MultiIndex columns
• Reorder and flatten for presentation

Conclusion

Applying different statistics within a single pivot table is one of the most useful capabilities in Pandas. In 2026, using pivot_table() with a dictionary in aggfunc allows you to create comprehensive, multi-metric reports in just a few lines of code. This technique is perfect for management dashboards, monthly reports, and any situation where you need to see multiple perspectives of your data at once.

Next steps:

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

Related Tutorials

• Multiple Statistics in a Pivot Table – Advanced pivot_table() Techniques 2026
• Pivot Tables in Pandas – Powerful Data Reshaping with pivot_table() in Python 2026
• Summaries on Multiple Columns in Pandas – Advanced Aggregations 2026