Cross-Tabulation and Pivot Table Depth

One question, three tools

“How many rows fall into each combination of category A and category B?” is one of the most common analytics questions — and pandas has three ways to answer it: groupby, pivot_table, and crosstab. They overlap on purpose; picking the right one is mostly about which shape of answer you want.

From the book

McKinney describes pivot_table as “a convenience interface to groupby, combined with reshape operations” — under the hood it’s doing the same split-apply-combine work you already know, just arranging the group keys as rows and columns instead of a single index. crosstab, in turn, is “a special case of a pivot table that computes group frequencies” — it’s pivot_table with aggfunc="count" built in.

Example dataset

A small survey

import pandas as pd
 
survey = pd.DataFrame({
    "nationality": ["USA", "Japan", "USA", "Japan", "Japan", "USA"],
    "handedness": ["Right", "Left", "Right", "Right", "Left", "Right"],
})
 
print(survey)

crosstab: counting combinations

pd.crosstab(rows, columns) counts how many times each combination of two (or more) categorical values occurs — no values= or aggfunc= needed, it’s frequency by default:

Basic crosstab

print(pd.crosstab(survey["nationality"], survey["handedness"]))

Adding row/column totals with margins

crosstab with margins

print(pd.crosstab(survey["nationality"], survey["handedness"], margins=True))

The "All" row and column are the totals across that dimension — 3 Japanese respondents, 3 American respondents, 6 total.

Normalizing into proportions

Raw counts don’t tell you whether a pattern is proportionally strong. Pass normalize="index" to turn each row into percentages that sum to 1:

crosstab with normalize

print(pd.crosstab(survey["nationality"], survey["handedness"], normalize="index"))

Now you can see that 100% of the American respondents in this sample are right-handed, versus about 33% of the Japanese respondents.

pivot_table: aggregating a numeric column by category

pivot_table answers a related but different question: not “how many rows,” but “what’s the sum (or mean, or any aggregate) of some numeric column,” broken down by category:

pivot_table: sum of amount by city and category

sales = pd.DataFrame({
    "city": ["pune", "pune", "delhi", "delhi", "delhi", "pune"],
    "category": ["A", "B", "A", "A", "B", "A"],
    "amount": [100, 200, 150, 120, 300, 80],
})
 
summary = sales.pivot_table(index="city", columns="category", values="amount", aggfunc="sum")
print(summary)

margins and multiple aggregations

Just like crosstab, pivot_table accepts margins=True for row/column totals. It also accepts a list of aggregation functions to compute several statistics at once:

pivot_table with margins

print(sales.pivot_table(index="city", columns="category", values="amount", aggfunc="sum", margins=True))

pivot_table with multiple aggfuncs

print(sales.pivot_table(index="city", values="amount", aggfunc=["sum", "mean"]))

crosstab vs pivot_table vs groupby

Tool	Best for	Default aggregation
groupby(...).agg(...)	Full control, any number of metrics/columns	You choose
pivot_table	A numeric column, aggregated by 1-2 categorical keys, arranged as a grid	"mean"
crosstab	Just counting how often category combinations occur	"count"

Anything crosstab can do, pivot_table(..., aggfunc="count") can also do — reach for crosstab when you specifically want frequencies, since it reads more clearly.

Common pitfalls

• pivot_table’s default aggfunc is "mean" — if you actually want totals, you must pass aggfunc="sum" explicitly, or you’ll get a quietly wrong answer.
• Empty (index, column) combinations become NaN unless you pass fill_value=0.
• margins=True’s "All" row/column is computed over the entire dataset, not just an average of the other rows/columns you can see — don’t average it in by hand.

Visualize it

diagram Same split, three different outputs mermaid

  flowchart LR
  D["Rows split by category"] --> G["groupby().agg()
your choice of metrics"]
  D --> P["pivot_table()
numeric column, grid of categories"]
  D --> C["crosstab()
frequency grid of categories"]

groupby, pivot_table, and crosstab all split rows by category, but each arranges and summarizes the result differently.

sketch A contingency grid: counts per combination p5.js Pause Restart

Each cell counts how many survey rows share that row category and that column category - the core idea behind crosstab.

🧪 Try It Yourself

Exercise 1 – Count Combinations With crosstab

pch.exerciseTag pch.exerciseTitle

Exercise 2 – Sum a Numeric Column With pivot_table

pch.exerciseTag pch.exerciseTitle

Exercise 3 – Add Grand Totals With margins

pch.exerciseTag pch.exerciseTitle

Next

That wraps up grouping and summarizing. Continue to Hierarchical Indexing (MultiIndex) to see how a two-level row index gives you similar structure without reshaping into a grid at all.