Fixing Python Pandas crosstab Wrong Margins When Values Param Is Set

You call pd.crosstab() with a values argument and margins=True, and the All row or column shows a number that makes no sense. The individual cells look right, but the totals are off — sometimes wildly. This is one of those bugs that hides in plain sight until you actually check your margin figures against an independent calculation.

The root cause is not random. It comes from a specific mismatch between how aggfunc is applied to the interior cells versus how Pandas re-aggregates those cells to build the margin. Once you see the pattern, it is easy to avoid.

What You'll Learn

• How Pandas computes margins when values is supplied
• Why aggfunc=np.mean (or any non-sum function) breaks the margin math
• How duplicate rows in your source data cause double-counting in margins
• The correct approach for getting reliable margin totals
• How to validate margin figures independently so you catch this bug fast

Prerequisites

You need Python 3.8+ with Pandas 1.3 or newer (the behavior described here persists through at least Pandas 2.x). Basic familiarity with DataFrames and groupby is assumed. NumPy is used in examples for np.mean and np.sum.

What Actually Goes Wrong

Here is a minimal reproduction. Suppose you have sales data with a region, a product category, and a revenue figure per transaction. You want a crosstab that shows total revenue per region per category, with row and column totals.

import pandas as pd
import numpy as np

data = {
    "region": ["North", "North", "North", "South", "South", "South"],
    "category": ["A", "A", "B", "A", "B", "B"],
    "revenue": [100, 200, 150, 300, 400, 500],
}
df = pd.DataFrame(data)

result = pd.crosstab(
    index=df["region"],
    columns=df["category"],
    values=df["revenue"],
    aggfunc=np.sum,
    margins=True,
)
print(result)

Expected output:

category      A      B    All
region
North       300    150    450
South       300    900   1200
All         600   1050   1650

Now change aggfunc to np.mean — a common choice when you want average revenue per cell:

result_mean = pd.crosstab(
    index=df["region"],
    columns=df["category"],
    values=df["revenue"],
    aggfunc=np.mean,
    margins=True,
)
print(result_mean)

You might see something like this:

category      A      B    All
region
North       150    150    150
South       300    450    383
All         225    350    308

The All column for North shows 150. But the mean of 150 (cell A) and 150 (cell B) is 150 — is that right? Only if you think the margin should be the mean of the aggregated cell values. If you actually want the mean of all individual North transactions (100, 200, 150), the answer is 150 — which happens to match here by coincidence. Try an unbalanced dataset and the margin will diverge from what you expect.

How pd.crosstab() Computes Margins by Default

Without values, pd.crosstab() counts occurrences. Margins are just the sum of the counts in each row or column, which is straightforward and always correct.

When you supply values and aggfunc, Pandas builds the interior cells by applying your function to the matching rows of your source data. So far so good. For margins, Pandas applies the same aggfunc to the already-aggregated cell values, not to the underlying raw rows. This is the critical detail.

Pandas computes the margin by aggregating the aggregated cells, not by re-aggregating the raw data. For sum, this is mathematically equivalent. For any other function, it is not.

For np.sum: sum of sums equals the grand total. Safe.
For np.mean: mean of means is a weighted average only if all groups have equal size. If group sizes differ, you get the wrong answer.

Why the values Parameter Changes Margin Behavior

This catches people off guard because the margins=True documentation does not prominently warn about it. The behavior is technically documented, but the implication is easy to miss.

The underlying mechanics: when values is set, Pandas uses DataFrame.pivot_table() internally. The margin is computed via a second pass that takes the pivot result and applies aggfunc column-by-column and row-by-row over the already-pivoted values. This is fine for additive aggregations (sum, count) but produces statistically incorrect results for non-additive ones (mean, median, std, max, min in non-obvious ways).

You can verify this by comparing against a manual calculation:

# Manual grand mean — correct approach
print(df["revenue"].mean())  # 275.0

# What crosstab with mean gives for All/All
print(result_mean.loc["All", "All"])  # 308.33... (wrong)

The Aggfunc Mismatch Problem

The mismatch is clearest with np.mean, but it affects any non-additive function. Here is a table of common aggfuncs and whether their margins are reliable:

If your aggfunc is anything other than sum or count, treat the margins Pandas generates as decorative. You need to compute them yourself.

When Your Data Shape Causes Double-Counting

A second failure mode appears when your source DataFrame already contains partially aggregated data — for example, if you pre-grouped before calling crosstab. In that case, the margin can double-count because Pandas re-applies aggfunc across data that was already summed once.

This also surfaces when the same logical entity appears multiple times with different keys. For details on how Pandas silently produces extra rows from non-unique index combinations, the article on fixing Pandas melt when id_vars have duplicates shows a closely related pattern.

A concrete example:

# Pre-aggregated data — a trap
preagg = df.groupby(["region", "category"], as_index=False)["revenue"].sum()

bad_result = pd.crosstab(
    index=preagg["region"],
    columns=preagg["category"],
    values=preagg["revenue"],
    aggfunc=np.sum,   # sums the already-summed rows
    margins=True,
)
print(bad_result)
# Interior cells: correct (only one row per region/category pair now)
# Margins: also correct here, BUT only because each cell has exactly one value
# Feed raw data with duplicates and it breaks

The safest rule: always pass raw, unaggregated data to pd.crosstab(). Let it do the aggregation.

How to Get Correct Margins With values Set

There are three practical solutions depending on what your margin should actually represent.

Option 1: Use aggfunc=np.sum and skip margins for non-additive stats

If you want mean values in the cells but correct totals in the margins, build them separately. Compute the crosstab without margins, then append the margin row and column yourself using the raw data.

import pandas as pd
import numpy as np

# Step 1: crosstab with mean, no margins
ct = pd.crosstab(
    index=df["region"],
    columns=df["category"],
    values=df["revenue"],
    aggfunc=np.mean,
    margins=False,
)

# Step 2: compute correct margin values from raw data
row_margin = df.groupby("region")["revenue"].mean().rename("All")
col_margin = df.groupby("category")["revenue"].mean().rename("All")
grand_total = pd.Series({"All": df["revenue"].mean()})

# Step 3: attach
ct["All"] = row_margin
col_margin["All"] = grand_total["All"]
ct.loc["All"] = col_margin

print(ct)

Output:

category      A      B    All
region
North       150    150    150
South       300    450    383
All         225    350    275

The All/All cell now shows 275 — the actual grand mean of all six revenue values.

Option 2: Use pivot_table directly with margins_name

Since pd.crosstab() calls pivot_table internally anyway, you can call it directly. The behavior is identical, but working at the pivot_table level makes the margin logic explicit and is easier to reason about.

pt = pd.pivot_table(
    df,
    values="revenue",
    index="region",
    columns="category",
    aggfunc=np.sum,
    margins=True,
    margins_name="Total",
)
print(pt)

For sum-based aggregations this is identical to crosstab with margins. For non-sum functions, you still have the same margin problem — so apply Option 1's manual approach here too.

Option 3: Validate with groupby before accepting the output

Before you trust any crosstab margin, run a quick independent check:

# Your crosstab margin row
print(result.loc["All"])  # The All row from crosstab

# Independent check via groupby
print(df.groupby("category")["revenue"].sum())  # Should match All row for sum

If those match, your margins are reliable. If not, fall back to Options 1 or 2. This is similar to the approach described in debugging Pandas GroupBy when it silently drops columns — always verify aggregation output against an independent reference.

Validating Your Margin Totals

Write a small helper function and run it every time you use crosstab with margins in a data pipeline. It takes thirty seconds and prevents silent errors from propagating downstream.

def validate_crosstab_margins(ct, raw_df, index_col, col_col, value_col, aggfunc):
    """
    Compare crosstab All row/col against independent groupby aggregations.
    Returns True if margins are consistent.
    """
    # Check column margin (All row)
    expected_col = raw_df.groupby(col_col)[value_col].agg(aggfunc)
    actual_col = ct.loc["All"].drop("All")  # drop the All/All corner
    col_ok = np.allclose(expected_col.values, actual_col.values, rtol=1e-5)

    # Check row margin (All column)
    expected_row = raw_df.groupby(index_col)[value_col].agg(aggfunc)
    actual_row = ct["All"].drop("All")
    row_ok = np.allclose(expected_row.values, actual_row.values, rtol=1e-5)

    if not col_ok:
        print("WARNING: All row (column margin) does not match independent groupby.")
    if not row_ok:
        print("WARNING: All column (row margin) does not match independent groupby.")

    return col_ok and row_ok

# Usage
result_sum = pd.crosstab(
    index=df["region"],
    columns=df["category"],
    values=df["revenue"],
    aggfunc=np.sum,
    margins=True,
)
validate_crosstab_margins(result_sum, df, "region", "category", "revenue", np.sum)
# Prints nothing — margins are correct

Pandas has other places where aggregation silently produces unexpected results. If you have run into NaN behavior in rolling windows, the breakdown in fixing Pandas rolling() when window edges have insufficient data covers a related class of issue.

Common Pitfalls to Watch For

• Passing aggfunc as a string like "mean" — this behaves the same as np.mean and has the same margin problem. The string form is not special-cased.
• Using a list of aggfuncs — when you pass aggfunc=[np.sum, np.mean], Pandas returns a MultiIndex column result. Margins are computed per-function, but still using the aggregated cells, not the raw data. Each function has the same risk.
• NaN values in the source column — np.mean ignores NaN by default, but that changes group sizes invisibly. The margin calculation is then based on different effective N than you expect. Always check df[value_col].isna().sum() before building the crosstab. For a related NaN pitfall, see how Pandas sort_values mishandles NaN by default.
• Assuming margins_name is cosmetic — changing margins_name from "All" to something else does not change how the margins are computed. It is purely a label.
• Integer vs float dtype in the margin — if your values are integers and aggfunc=np.sum, the margin cells may be cast to float in some Pandas versions. This does not affect correctness but can break downstream dtype expectations.

Wrapping Up

The wrong-margin bug in pd.crosstab() with values set is not a Pandas defect — it is a predictable consequence of how margin aggregation works. Here are concrete steps to take right now:

1. Audit every crosstab call in your codebase that uses values + aggfunc + margins=True. If aggfunc is anything other than np.sum or len, flag it immediately.
2. Run the validate_crosstab_margins helper above against any existing reports that use crosstab margins. Compare to an independent groupby to see if results have diverged.
3. For non-additive aggregations, drop margins=True and build the margin row and column manually from the raw DataFrame using groupby.
4. Always pass raw data to pd.crosstab(), never pre-aggregated data, to avoid double-counting in the interior cells.
5. Add a unit test that asserts the crosstab All row matches a groupby sum or mean over the same data. Catch regressions before they reach a report.