Crosstabs and Pivot Analysis

Analogy categoryAnalogies

pd.crosstab() computes a cross-tabulation — essentially a contingency table — showing the frequency (or another aggregated statistic) of combinations between two or more categorical variables. Given two Series, such as customer 'region' and 'plan_type', crosstab counts how many rows fall into every (region, plan_type) pair and lays them out as a matrix with regions as rows and plan types as columns. This is a specialized, more convenient interface than pivot_table for the extremely common case of counting co-occurrences between categorical fields, and it's widely used in exploratory data analysis, A/B test summaries, and survey analysis.

🏏

Cricket analogy: pd.crosstab counts how many innings fall into every combination of 'venue' and 'result', laying venues as rows and results (win/loss/draw) as columns — a quick way to spot that a team wins disproportionately at home grounds.

Basic Counting with crosstab

pd.crosstab(index, columns) is the simplest form: pass two array-likes (often DataFrame columns) and get back counts of every combination. Optional arguments extend this: normalize=True (or 'index'/'columns') converts raw counts into proportions, which is useful for comparing relative distributions across groups of different sizes; margins=True adds row and column totals ('All') for quick sanity checks; and values combined with aggfunc lets crosstab compute a statistic other than count, such as average order value per (region, plan_type) cell, effectively turning it into a specialized pivot_table call.

🏏

Cricket analogy: pd.crosstab(venue, result, normalize='index') converts raw win/loss counts into percentages per venue so a small ground with 3 wins doesn't look as impressive as a big stadium's 30; margins=True adds row/column totals, and values=runs, aggfunc='mean' turns it into average score per (venue,result) cell.

python

import pandas as pd

customers = pd.DataFrame({
    'region': ['East', 'East', 'West', 'West', 'West', 'East'],
    'plan': ['Basic', 'Pro', 'Basic', 'Pro', 'Pro', 'Basic'],
    'monthly_spend': [10, 40, 10, 45, 42, 12]
})

# raw counts
counts = pd.crosstab(customers['region'], customers['plan'])
print(counts)
# plan    Basic  Pro
# region
# East        2    1
# West        1    2

# row-normalized proportions
row_pct = pd.crosstab(customers['region'], customers['plan'], normalize='index')
print(row_pct)

# aggregate a numeric column instead of counting
avg_spend = pd.crosstab(
    customers['region'], customers['plan'],
    values=customers['monthly_spend'], aggfunc='mean'
)
print(avg_spend)

crosstab vs. pivot_table

crosstab and pivot_table overlap heavily in capability, but crosstab is optimized for the count/frequency use case (its default aggfunc is count) and accepts raw arrays or Series directly, while pivot_table always operates on DataFrame columns and defaults to computing the mean. In practice, use crosstab when the primary question is 'how many' or 'what proportion', and pivot_table when you're aggregating a continuous measure across two categorical dimensions with a specific aggfunc like sum or median as the main focus.

🏏

Cricket analogy: Counting how many matches were won at each venue is a crosstab job (default count); computing the median run rate per venue and format is a pivot_table job, since pivot_table defaults to mean and targets a continuous stat.

Crosstabs are the pandas equivalent of a two-way frequency table from introductory statistics — the same structure used to compute a chi-squared test of independence between two categorical variables, e.g. via scipy.stats.chi2_contingency(pd.crosstab(...)).

normalize='index' and normalize='columns' are easy to mix up: 'index' makes each row sum to 1 (proportions within each row category), while 'columns' makes each column sum to 1. Always double check which axis you meant to normalize against, especially before reporting percentages.

pd.crosstab() builds a frequency table (contingency table) of co-occurrences between two or more categorical variables.
Default aggregation is count; pass values and aggfunc to compute another statistic per cell instead.
normalize='index'/'columns'/True converts raw counts to proportions along the chosen axis.
margins=True adds row and column totals labeled 'All' for quick summary checks.
crosstab defaults to count and accepts raw Series; pivot_table defaults to mean and operates on DataFrame columns.
Crosstabs are the standard input for statistical tests of association like the chi-squared test.

Practice what you learned

1. What does pd.crosstab(df['region'], df['plan']) return by default?

2. What does normalize='index' do in pd.crosstab()?

3. How does the default aggregation function of pd.crosstab() differ from that of pd.pivot_table()?

4. What does passing margins=True to pd.crosstab() add to the output?

5. If you want crosstab to compute average monthly_spend per (region, plan) cell instead of counts, what must you supply?

Was this page helpful?

Transform and Filter on Groups

Topics covered

#Python#PandasNumPyStudyNotes#DataScience#CrosstabsAndPivotAnalysis#Crosstabs#Pivot#Analysis#Counting#StudyNotes#SkillVeris

Crosstabs and Pivot Analysis