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.

Example dataset

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:

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

Adding row/column totals 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:

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:

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:

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

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

crosstab vs pivot_table vs groupby

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

  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"]

🧪 Try It Yourself

Exercise 1 – Count Combinations With crosstab

Press Run to see what your code prints.

Exercise 2 – Sum a Numeric Column With pivot_table

Press Run to see what your code prints.

Exercise 3 – Add Grand Totals With margins

Press Run to see what your code prints.

Check your understanding

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.