HowToCodePage

Pivot tables and crosstab

pivot_table with aggregators, crosstab with normalisation, margins, multi-level columns and flattening the result into something tableable.

pivot_table

import pandas as pd

pivot = df.pivot_table(
    index="region",
    columns="product",
    values="revenue",
    aggfunc="sum",
    fill_value=0,
    margins=True,
    margins_name="Total",
)

pivot.loc["north", "widget"]      # one cell by label
pivot / pivot.loc["Total"]        # share of total, per cell, in one line

multi = df.pivot_table(
    index="region",
    columns="product",
    values="revenue",
    aggfunc=["sum", "mean"],      # one column block per aggregator
)
multi.columns.names               # FrozenList([None, 'product'])
  • pivot_table aggregates: repeated index and column combinations are combined by aggfunc, so duplicates are harmless.
  • Plain pivot does not aggregate and raises when a combination repeats.
  • Missing combinations become NaN by default; fill_value=0 is right for counts and revenue, wrong for averages.
  • margins=True adds a Total row and column computed by the same aggfunc over the whole frame.

crosstab and normalisation

counts = pd.crosstab(df["region"], df["product"])
shares = pd.crosstab(df["region"], df["product"], normalize="index")   # row percentages
totals = pd.crosstab(
    df["region"], df["product"],
    values=df["revenue"], aggfunc="sum", margins=True,
)

pd.crosstab(df["region"], [df["product"], df["channel"]])   # cross two columns
pd.crosstab(df["region"], df["product"]).stack().to_frame("n")   # back to long
AggregatorUse it for
"sum"Revenue, quantity, anything additive
"count" / "size"Number of rows per cell
"mean"Average order value per cell
"nunique"How many distinct customers appear
lambda s: s.value_counts().index[0]The most common value in the cell
⚠️
Margins are honest only for additive aggregators. With aggfunc="mean" the Total row is the mean of the cell means, not the mean of the underlying rows - a different number whenever the cell sizes differ. Compute the overall figure separately.

Flattening for the next reader

flat = pivot.copy()
flat.columns = ["_".join(str(p) for p in c).strip("_") for c in flat.columns]
flat.columns.name = None
flat.index.name = "region"
flat = flat.reset_index()

# or, keeping the hierarchy but renaming levels
long = multi.stack(level="product", future_stack=True).rename_axis(
    columns=["stat", "product"]
).reset_index()

A MultiIndex column set is fine inside a notebook and awkward everywhere else: CSV writers flatten it into odd header lines, plotting libraries guess, and dashboards impose their own convention. Decide the flat column names explicitly before the data leaves your process.

FAQ

pivot or pivot_table?
Use pivot_table unless you have proved the key pairs are unique. It aggregates instead of failing, and it fills gaps on request, which is what almost every report actually needs.
How do I get share-of-total percentages?
normalize="index" in crosstab divides each row by its total, normalize="columns" by each column, and normalize=True by the grand total.

The index in depth: MultiIndex, set_index and reindex apply, map and writing fast pandas code

Last refreshed 2026-09-18.