DEV Community

dustin liang
dustin liang

Posted on

pandas crosstab normalize: Row, Column and Total Percentages (Plus the margins, NaN and Multi-Label Traps)

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))
Enter fullscreen mode Exit fullscreen mode
  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
Enter fullscreen mode Exit fullscreen mode

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))
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode

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))
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode

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))
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode

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))
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode
  • normalize="index" adds only an All row: 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 an All column: 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))
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode

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())
Enter fullscreen mode Exit fullscreen mode
rows in table: 397 of 400
after fillna: 400
Enter fullscreen mode Exit fullscreen mode

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))
Enter fullscreen mode Exit fullscreen mode
['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
Enter fullscreen mode Exit fullscreen mode

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))
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode

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}")
Enter fullscreen mode Exit fullscreen mode
True
chi2=15.05  dof=8  p=0.0582
Enter fullscreen mode Exit fullscreen mode

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)