pd.crosstab builds a frequency table from two columns in one line, and the normalize argument turns those counts into shares. The catch is that there are three different percentages hiding behind normalize, and they answer three different questions. Add margins=True, missing values, empty categories or rows that belong to several groups at once, and it gets easy to publish a percentage that does not mean what the chart title says.
This guide covers pandas crosstab normalize end to end: normalize="index", "columns" and "all", what margins does to each, weighted shares with values and aggfunc, how NaN keys and unused categories quietly change the table, the right denominator for multi-label data after explode, and a quick chi-square check of whether two categories are related at all.
Tested on pandas 3.1, NumPy 2.5 and SciPy 1.18. The data is synthetic and seeded: 400 made-up tickers, each with a listing board and one theme tag. Every table below is real output.
Sample data: one row per item, two categorical columns
import numpy as np
import pandas as pd
rng = np.random.default_rng(11)
tags = ["AI chips", "Robotics", "EV battery", "Cloud", "Biotech"]
boards = ["Main", "ChiNext", "STAR"]
n = 400
df = pd.DataFrame({
"ticker": [f"T{i:03d}" for i in range(n)],
"board": rng.choice(boards, n, p=[0.55, 0.25, 0.20]),
"tag": rng.choice(tags, n, p=[0.25, 0.2, 0.2, 0.2, 0.15]),
"mentions": rng.poisson(20, n),
})
# make one cell empty on purpose
df.loc[df.board.eq("STAR") & df.tag.eq("Biotech"), "tag"] = "AI chips"
print(df.head())
print(pd.crosstab(df.tag, df.board))
ticker board tag mentions
0 T000 Main EV battery 16
1 T001 Main Cloud 16
2 T002 ChiNext Robotics 18
3 T003 Main Robotics 20
4 T004 Main EV battery 21
board ChiNext Main STAR
tag
AI chips 24 59 22
Biotech 14 38 0
Cloud 17 40 12
EV battery 23 46 21
Robotics 19 51 14
The first argument becomes the rows (index), the second the columns. Without normalize you get counts.
normalize="index": each row sums to 1
Use this when the question is "within each tag, how are items split across boards?"
print(pd.crosstab(df.tag, df.board, normalize="index").round(3))
board ChiNext Main STAR
tag
AI chips 0.229 0.562 0.210
Biotech 0.269 0.731 0.000
Cloud 0.246 0.580 0.174
EV battery 0.256 0.511 0.233
Robotics 0.226 0.607 0.167
Read across a row: 73.1% of Biotech items are on Main, and none are on STAR. Each row adds up to 1.0. normalize=1 does not mean this; integer values follow the axis convention, so 0 is "index" and 1 is "columns". Use the strings to avoid mixing them up.
normalize="columns": each column sums to 1
Use this when the question is "what is the tag mix of each board?"
print(pd.crosstab(df.tag, df.board, normalize="columns").round(3))
board ChiNext Main STAR
tag
AI chips 0.247 0.252 0.319
Biotech 0.144 0.162 0.000
Cloud 0.175 0.171 0.174
EV battery 0.237 0.197 0.304
Robotics 0.196 0.218 0.203
Now read down a column: 31.9% of STAR items are AI chips, compared with 25.2% on Main. This is the table you want for a stacked bar chart with one bar per board.
normalize="all" (or True): the whole table sums to 1
print(pd.crosstab(df.tag, df.board, normalize="all").round(3))
board ChiNext Main STAR
tag
AI chips 0.060 0.148 0.055
Biotech 0.035 0.095 0.000
Cloud 0.042 0.100 0.030
EV battery 0.058 0.115 0.052
Robotics 0.048 0.128 0.035
Each cell is a share of all 400 rows. Main × AI chips holds 14.8% of everything. This view suits a heatmap of joint frequencies but is rarely the right number for a sentence like "X% of AI chip names are on Main".
What margins=True adds with each normalize option
margins=True adds an All row and/or column, but which one you get depends on normalize:
for how in ["index", "columns", "all"]:
t = pd.crosstab(df.tag, df.board, margins=True, normalize=how)
print(f"normalize={how!r}: rows={t.index.tolist()[-1]!r}, cols={t.columns.tolist()}")
print(pd.crosstab(df.tag, df.board, margins=True, normalize="columns").round(3))
normalize='index': rows='All', cols=['ChiNext', 'Main', 'STAR']
normalize='columns': rows='Robotics', cols=['ChiNext', 'Main', 'STAR', 'All']
normalize='all': rows='All', cols=['ChiNext', 'Main', 'STAR', 'All']
board ChiNext Main STAR All
tag
AI chips 0.247 0.252 0.319 0.262
Biotech 0.144 0.162 0.000 0.130
Cloud 0.175 0.171 0.174 0.172
EV battery 0.237 0.197 0.304 0.225
Robotics 0.196 0.218 0.203 0.210
-
normalize="index"adds only anAllrow: the board split across the full sample (58.5% Main). A row-total column would be all ones, so pandas leaves it out. -
normalize="columns"adds only anAllcolumn: the tag mix across all boards. This is the baseline to compare each board against. STAR's 31.9% AI chips versus 26.2% overall is the kind of difference worth reporting. -
normalize="all"adds both, and the bottom-right corner is 1.0.
Use margins_name="Total" to rename the All label.
Weighted shares with values and aggfunc
Counting rows treats every item equally. To get shares of a numeric column (mentions, revenue, volume), pass values and aggfunc. normalize then applies to the aggregated values:
w = pd.crosstab(df.tag, df.board, values=df.mentions, aggfunc="sum",
normalize="columns")
print(w.round(3))
board ChiNext Main STAR
tag
AI chips 0.265 0.270 0.303
Biotech 0.137 0.164 0.000
Cloud 0.163 0.161 0.179
EV battery 0.228 0.190 0.313
Robotics 0.207 0.214 0.204
This is "share of mentions", not "share of tickers". The two can differ a lot when a few items dominate the numeric column. normalize with values only makes sense for additive aggregates like sum or count. Normalizing a mean produces numbers that are hard to interpret.
NaN keys are dropped silently
Rows whose row or column key is NaN do not appear anywhere in the table, and the percentages are then computed without them:
d2 = df.copy()
d2.loc[[0, 1, 2], "board"] = np.nan
print("rows in table:", pd.crosstab(d2.tag, d2.board).to_numpy().sum(), "of", len(d2))
print("after fillna:", pd.crosstab(d2.tag, d2.board.fillna("Unknown")).to_numpy().sum())
rows in table: 397 of 400
after fillna: 400
Three rows vanished without a warning. If missing values matter for your denominator, fill them with an explicit label like "Unknown" first.
Keeping empty categories (and their order)
A tag that never appears gets no row. If the column is a Categorical, dropna=False keeps unused categories, and the row order follows the category order instead of alphabetical order:
d3 = df.copy()
d3["tag"] = pd.Categorical(d3.tag, categories=tags + ["Fintech"])
print(pd.crosstab(d3.tag, d3.board).index.tolist())
print(pd.crosstab(d3.tag, d3.board, dropna=False).index.tolist())
print(pd.crosstab(df.tag, df.board).reindex(tags + ["Fintech"], fill_value=0).tail(2))
['AI chips', 'Robotics', 'EV battery', 'Cloud', 'Biotech']
['AI chips', 'Robotics', 'EV battery', 'Cloud', 'Biotech', 'Fintech']
board ChiNext Main STAR
tag
Biotech 14 38 0
Fintech 0 0 0
For plain string columns, reindex(..., fill_value=0) on the counts is the simplest way to force a fixed set of rows. Do it before normalizing. An all-zero row cannot be row-normalized (0/0 gives NaN).
Multi-label data: the denominator after explode
When an item can carry several tags, the usual approach is a list column plus explode. The table still works, but the denominator changes:
ml = df[["ticker", "board"]].copy()
ml["tags"] = [list(rng.choice(tags, size=rng.integers(1, 4), replace=False))
for _ in range(len(ml))]
ex = ml.explode("tags")
print(len(ml), "items ->", len(ex), "item-tag rows")
print(pd.crosstab(ex.tags, ex.board, normalize="columns").sum().round(3).tolist())
share = pd.crosstab(ex.tags, ex.board).div(ml.board.value_counts(), axis=1)
print(share.round(3))
400 items -> 837 item-tag rows
[1.0, 1.0, 1.0]
board ChiNext Main STAR
tags
AI chips 0.412 0.440 0.464
Biotech 0.485 0.466 0.536
Cloud 0.381 0.410 0.377
EV battery 0.433 0.376 0.333
Robotics 0.402 0.385 0.406
normalize="columns" on the exploded table still sums to 1. That gives you the share of tag assignments, not the share of items. If the question is "what fraction of STAR items carry the AI chips tag?", divide the counts by the number of items per board, as share does. Those columns add up to more than 1, which is correct for multi-label data.
Same result with groupby, and a chi-square check
crosstab is a convenience wrapper. The groupby version gives the same row shares and slots into longer chains more easily:
alt = (df.groupby("tag")["board"]
.value_counts(normalize=True)
.unstack(fill_value=0))
print(np.allclose(alt, pd.crosstab(df.tag, df.board, normalize="index")))
from scipy.stats import chi2_contingency
res = chi2_contingency(pd.crosstab(df.tag, df.board))
print(f"chi2={res.statistic:.2f} dof={res.dof} p={res.pvalue:.4f}")
True
chi2=15.05 dof=8 p=0.0582
Pass raw counts to chi2_contingency, never a normalized table, because the test depends on sample size. Here p ≈ 0.058. Even with a completely empty STAR × Biotech cell, 400 rows are not quite enough to reject independence at the 5% level. That is a useful reminder not to over-read one striking cell in a percentage table.
FAQ
How do I show crosstab percentages as 0–100 with a % sign?
Multiply by 100 and format at the end, for example (t * 100).round(1).astype(str) + "%", or use t.style.format("{:.1%}") in a notebook. Keep the numeric table for further calculations.
Why don't my row percentages add up to exactly 100?
Rounding. Each cell is rounded separately. Round only for display, or use a largest-remainder method if the displayed numbers must add up.
crosstab or pivot_table?
Same machinery. crosstab takes arrays or Series and has normalize; pivot_table takes a DataFrame and column names and has no normalize, so you divide yourself.
Conclusion
Pick the denominator before you pick the function. normalize="index" answers "how is each row group split?", "columns" answers "what is each column group made of?", and "all" gives joint shares. margins=True adds the matching baseline row or column. Fill NaN keys you want counted, reindex empty categories before normalizing, divide by item counts instead of tag counts for multi-label data, and run significance tests on raw counts.
Stock-to-theme tables are a typical multi-label case: one stock belongs to several concepts, and "share of stocks" and "share of tag assignments" give different answers. Lava's concept stock lookup shows a real version of such a mapping for A-share concept themes.
中文简介:本文用 400 条固定种子的合成数据(虚构代码、上市板块、主题标签)讲解 pandas crosstab 的 normalize 参数:normalize="index" 每行合计为 1,"columns" 每列合计为 1,"all" 全表合计为 1;margins=True 在不同 normalize 下分别只加 All 行、只加 All 列或两者都加;用 values+aggfunc="sum" 计算加权占比;键为 NaN 的行会被静默丢弃,需先 fillna;Categorical 配合 dropna=False 或 reindex 保留空类别;多标签数据 explode 后按列归一化得到的是「标签分配占比」,若要「条目占比」需除以每列条目数;最后给出 groupby 等价写法,并说明卡方检验应使用原始计数。
Top comments (0)