How do I add row and column totals to a pivot table in pandas?

The short version

Pass margins=True to pivot_table and it adds an All row and column containing the totals. Rename it with margins_name, and pass fill_value=0 so combinations with no data show as zero rather than NaN.

A worked example

import pandas as pd

df = pd.DataFrame({
    "city": ["Leeds", "Leeds", "Bath"],
    "kind": ["online", "shop", "online"],
    "amount": [1, 2, 3],
})

table = pd.pivot_table(
    df,
    index="city",
    columns="kind",
    values="amount",
    aggfunc="sum",
    margins=True,
    fill_value=0,
)
print(table)

kind   online  shop  All
city
Bath        3     0    3
Leeds       1     2    3
All         4     2    6

The bottom row and right column are the totals, and the corner cell is the grand total.

The margin is computed from the data, not the cells

An important subtlety: the margin uses the aggregation function over the underlying rows, not over the displayed cells. For sum these agree, so the visible numbers add up. For mean they do not: the All row is the mean of all the rows in that column, which is not the mean of the cell means unless every group is the same size.

This is correct behaviour, but it confuses people who try to check the totals by adding up the cells. With a mean, a weighted average of the cells is what you would need, and the margin gives you the true overall mean instead, which is usually what you actually want.

Naming the total

All is rarely the label you want in a report.

pd.pivot_table(df, index="city", values="amount", aggfunc="sum",
               margins=True, margins_name="Total")

Why fill_value matters

Without it, a city and kind combination that never occurred shows as NaN, and a table peppered with NaN reads badly. But think before setting it: fill_value=0 asserts that no data means zero, which is true for a count of sales and false for something like an average temperature. Only fill when zero is the honest value.

Adding percentages instead

For shares rather than counts, divide by the margin after computing it, or use crosstab with normalize.

pd.crosstab(df["city"], df["kind"], normalize="index", margins=True)

This gives each row as proportions summing to one, which answers "what mix of kinds does each city have" rather than "how many".

Keep totals out of further analysis

One practical warning: once you add margins, the frame contains a row and column that are aggregates, not observations. If you then group, filter or chart that frame, the totals get treated as if they were data, double-counting everything. Add margins as the very last step, for presentation only, and never feed a table with margins into another calculation.