skip to content

In pandas, what does `pd.crosstab(obs["station"], obs["condition"], normalize="index")` return, and how do margins change it?

level: middleimportance: should knowfreq 40%

answer

  1. a frequency table of two keys
  2. array-likes, not column names
  3. index, columns or all
  4. an All row from margins

basics

~20 s

pd.crosstab counts rows per station and condition pair; normalize="index" divides each count by its row total so every station's shares sum to 1. With margins=True it also adds an All row holding the overall shares.

solid answer

~40 s

`pd.crosstab` takes array-likes or Series, not column names, and by default builds a **frequency table**: one row per station, one column per weather condition, an integer count in each cell, and 0 where a pair never occurs. `normalize="index"` turns each row into shares that sum to 1, `"columns"` does it per column, and `True` or `"all"` divides by the grand total. `margins=True` adds `All` totals; with `normalize="index"` you get only an `All` row, the condition mix of the whole table, because a column of row totals would be all ones. To aggregate a third column instead of counting, pass `values` and `aggfunc` together; passing only one of them raises `ValueError`.

go deeper

for a junior

Recall that crosstab counts combinations of two Series and fills absent pairs with 0.

for a middle

Explain the three normalize axes, what sums to 1 in each, and why a row-normalized table gets only an All row.

for a senior

Choose crosstab or pivot_table for a report, and pair values with aggfunc while expecting NaN in empty aggregated cells.

for a principal

Decide which normalisation a recurring report standardises on, so readers never confuse row shares with column shares.

## What `crosstab` computes `pd.crosstab` builds a **cross-tabulation**: the values of one or more array-likes become the row labels, the values of others become the column labels, and each cell says how often that combination occurs. On a weather log with one row per observation, it answers "how many dry, rainy and snowy observations did each station record?" ```python import pandas as pd obs = pd.DataFrame({ "station": ["ST01", "ST01", "ST01", "ST02", "ST02", "ST01"], "condition": ["rain", "dry", "rain", "dry", "dry", "snow"], "reading": [4.0, 5.5, 6.0, 7.0, 6.5, 1.0], }) pd.crosstab(obs["station"], obs["condition"]) # condition dry rain snow # station # ST01 1 2 1 # ST02 2 0 0 ``` Points to notice: - The arguments are **array-likes or Series**, not column names. There is no `data=` parameter; `pd.crosstab("station", "condition", data=obs)` raises `TypeError`. - The Series names become the axis names (`station`, `condition`); `rownames` and `colnames` override them. - Pairs that never occur show **0**, and the counts are `int64`. You do not need a `fill_value`. - Passing a list of Series for `index` or `columns` builds a `MultiIndex` on that axis. ## `normalize`: counts into shares | `normalize` | Divides each cell by | What sums to 1 | |---|---|---| | `False` (default) | nothing | nothing; raw counts | | `True` or `"all"` | the grand total | the whole table | | `"index"` or `0` | its row total | each row | | `"columns"` or `1` | its column total | each column | With `normalize="index"`, `ST01`'s row becomes 0.25 dry, 0.5 rain, 0.25 snow: the condition mix *at that station*. With `normalize="columns"`, the `rain` column becomes 1.0 for `ST01` and 0.0 for `ST02`: which station the rain observations came from. Choosing the axis is choosing the question, and interviewers often ask candidates to read one of these tables aloud. ## `margins`: the `All` row and column `margins=True` adds subtotals labelled by `margins_name`, which defaults to `"All"`. 1. On raw counts you get an `All` column with each station's total and an `All` row with each condition's total, plus the grand total in the corner. 2. With `normalize="index"`, only an **`All` row** is added. It holds the condition mix of the whole table (0.5 dry, 0.333 rain, 0.167 snow here). A column of row totals would be all ones, so it is not shown. 3. With `normalize="columns"`, symmetrically, only an `All` column is added. 4. With `normalize="all"`, both margins appear and they are shares of the grand total. The docstring states it plainly: if `margins` is true, the margin values are normalized too. ## Aggregating a third column `crosstab` can do more than count. Pass `values` and `aggfunc` **together**: ```python pd.crosstab(obs["station"], obs["condition"], values=obs["reading"], aggfunc="mean") ``` - `values` without `aggfunc` raises `ValueError: values cannot be used without an aggfunc.` - `aggfunc` without `values` raises `ValueError: aggfunc cannot be used without values.` - Unlike counts, empty cells here are `NaN`, because there is nothing to average. ## How it relates to `pivot_table` `crosstab` is built on `pivot_table`. On a single DataFrame, `obs.pivot_table(index="station", columns="condition", aggfunc="size", fill_value=0)` gives the same count table. Prefer `crosstab` when the inputs are separate arrays or Series, or when you want `normalize`; prefer `pivot_table` when you are already working with column names of one frame and need several values or aggregations. ## Labels and layout - `rownames=["station"]` and `colnames=["condition"]` name the axes when the inputs are unnamed arrays, or override the Series names. - `margins_name="Total"` relabels the `All` row and column. - The result is an ordinary DataFrame, so `.round(3)`, `.mul(100)` or `.to_csv()` work on it directly; turning shares into rounded percentages is the usual last step before it goes into a report. ## Categorical inputs When an input is a `Categorical`, `dropna=False` keeps every declared category as a row or column even if no observation has it, which keeps a report's layout stable from day to day. Plain columns only ever show the values present. ## Traps - Reading a row-normalized table as if columns summed to 1. - Passing column names instead of Series. - Forgetting `aggfunc` when adding `values`. - Expecting zeros in an aggregated table; only count tables are zero-filled.

  • Why do empty cells show 0 in a count crosstab but NaN when you pass `values` and `aggfunc="mean"`?
    A count of an absent pair is genuinely zero, so the frequency table fills it. A mean of no readings is undefined, so the aggregated table leaves the cell `NaN`. Add `.fillna()` explicitly if the report needs a placeholder.
  • How would you build the same count table with `pivot_table`?
    `obs.pivot_table(index="station", columns="condition", aggfunc="size", fill_value=0)`. `"size"` counts rows per cell and `fill_value=0` replaces the missing pairs, matching `crosstab`'s default output.

  • crosstab takes column names and a data argument like pivot_table
  • normalize="index" makes each column sum to 1
  • With normalize="index" and margins, the All column shows row totals
  • Passing values alone makes crosstab average that column
  • Missing pairs in a count crosstab come out as NaN