Skip to main content

Pivot tables and cross-tabulation

A group-by on two keys reads best as a grid: one key down the side, the other across the top. Build it with unstack, then with pivot_table and its margins, count pairs with crosstab, see that a confusion matrix is a crosstab, and close on the mean petal length of each iris species.
P02.6.4L1 Core25 min
In review

The maths and code on this page are checked by automated tests on every change, but an independent reader has not signed it off yet. Spot a mistake or a confusing step? Tell us.

Before this lesson: P02.6.3 Transform: a group summary on every row

What you will be able to do
  • Lay a two-key group-by out as a grid with unstack and with pivot_table.
  • Explain what margins=True adds and why a margin is not the mean of the cells beside it.
  • Count pairs with crosstab, and tell its 0 for an empty pair from the NaN of a mean.
  • Build a confusion matrix as the crosstab of true labels against predicted labels.

The question

Is a clear sky warmer than a cloudy one at every station, or only on average? The seven rows of a two-key group-by hold the answer, but a list of pairs hides it. Spreadsheet users would build a pivot table: stations down the side, skies across the top, one mean in each cell. The same grid, with counts in the cells, is the confusion matrix that every classifier is judged by.

From the last lesson

1. How does the result of groupby('station')['temp'].transform('mean') differ from that of .mean()?

Show answer

.mean() gives 3 values indexed by station; transform('mean') gives 12 values, one per reading, under the readings’ index 0 to 11.

2. Why is readings['temp'] - readings.groupby('station')['temp'].mean() all NaN?

Show answer

Subtraction matches labels: 0 to 11 on one side and coast, hill, valley on the other. No label has a partner, so all 15 labels get NaN.

Intuition

Group by station and sky and you get one row per pair that occurs (lesson P02.6.2). Now move the second key, sky, from the rows to the columns. Each pair becomes a cell of a grid, found by its row and its column, and the grid is read the way a reader’s eye wants to read it: along a row to compare skies at one station, down a column to compare stations under one sky.

Left, titled long: one row per pair, a table with orange station and sky columns and a vermillion temp column: coast clear 27.67, coast rain 21, hill clear 23, hill cloudy 19, hill rain 19, valley clear 31, valley cloudy 27. A green arrow labelled unstack, sky to columns, points to the right, titled wide: station by sky: a 3 by 3 grid with orange row labels coast, hill, valley and column labels clear, cloudy, rain. Coast: 27.67, NaN, 21. Hill: 23, 19, 19. Valley: 31, 27, NaN. The two NaN cells are dashed reddish purple, with the note NaN: no readings for that pair. Underneath, the code readings.pivot_table(index='station', columns='sky', values='temp', aggfunc='mean').
P02.6.4-F1

The same seven numbers, long and wide. The grid has nine cells for seven pairs, so the two pairs that never happened show up as NaN.

Two things are easier to see in the grid. Clear is the warmest sky at every station. And the gaps are visible: the valley never saw rain in these four days and the coast never saw a cloudy noon. Wickham names spreadsheet pivot tables as one of the tools that carry out split-apply-combine.

Build it up

Step 1. Group, then unstack. unstack() moves the innermost level of the index, here sky, to the columns:

readings.groupby(['station', 'sky'])['temp'].mean().unstack()

Every (station, sky) pair finds its cell; a cell with no pair gets NaN.

Step 2. pivot_table does both. pivot_table names the row key, the column key, the value column and the summary in one call, and its summary is the mean unless told otherwise:

readings.pivot_table(index='station', columns='sky', values='temp', aggfunc='mean')

It is the pivot of lesson P02.5.3 with an aggregation added. pivot only moves values into place and fails when two rows share a (row, column) pair; pivot_table summarises them.

Step 3. Margins. margins=True adds a row and a column labelled All. The All column holds the summary of each whole row key, the All row that of each whole column key, and the corner that of the whole table.

The pivot table of mean temperature with margins. Rows coast, hill, valley and All; columns clear, cloudy, rain and All. The body is coast 27.67, NaN, 21; hill 23, 19, 19; valley 31, 27, NaN. The All column, set off by a thick vermillion rule and shaded darker, reads 26, 20, 29; the All row reads 28, 23, 20; the corner reads 25. Notes beside it: All column: the mean of each station's 4 readings (26, 20, 29); All row: the mean of each sky's readings (28, 23, 20); corner: all 12 readings, 25.
P02.6.4-F2

margins=True. The All column repeats the station means of lesson P02.6.1, the All row the sky means of lesson P02.6.2, and the corner is the overall mean, 25.

Step 4. Counting pairs. pd.crosstab(rows, columns) counts how many rows fall into each pair: a frequency table. A pair with no rows has the count 0, which is a true statement about a count, where the mean of no values does not exist and is NaN.

Left, the code pd.crosstab(station, sky) and a 3 by 3 grid of counts: coast 3, 0, 1; hill 1, 2, 1; valley 2, 2, 0, with the two zeros highlighted yellow and the note a count of no rows is 0, not NaN. Right, the code pd.crosstab(true, predicted) and a 2 by 2 grid with true down the side and predicted across the top: true 0 row: 3 and 1; true 1 row: 1 and 3. The diagonal cells, 3 and 3, are tinted green and the others reddish purple. Underneath, the lists true 1 0 1 1 0 0 1 0 and predicted 1 0 0 1 0 1 1 0, and the note diagonal: right (6 of 8); off it: wrong.
P02.6.4-F4

Left: counts of station and sky. Right: the same call on a classifier’s true and predicted labels is its confusion matrix; the diagonal counts the correct answers.

A classifier’s confusion matrix is exactly this. Put the true labels down the side and the predicted labels across the top, and each cell counts one kind of outcome; the diagonal holds the correct ones. Lesson ML01.7.1 reads accuracy, precision and recall off it.

The formal statement

NoteDefinition: pivot table and cross-tabulation

Take a table with a row key \(r\), a column key \(c\) and a value column \(x\), and a summary \(f\). The pivot table has one row per value \(a\) of \(r\) and one column per value \(b\) of \(c\). Its cell is

\[ \text{cell}(a, b) = f(\{x_i : r_i = a,\ c_i = b\}), \]

or NaN when no row has that pair. With margins, the row margin of \(a\) is \(f\) applied to all rows with \({r_i = a}\), the column margin of \(b\) is \(f\) applied to all rows with \({c_i = b}\), and the corner is \(f\) applied to every row. The cross-tabulation is the pivot table of counts, \(n_{ab}\), with 0 where no row has the pair.

A margin of means is weighted by the counts. Write \(m_{ab}\) for the mean in cell \({(a, b)}\) and \(n_{ab}\) for its count from the crosstab. The values in the cell add up to \({n_{ab}\, m_{ab}}\), and the row \(a\) holds \({n_a = \sum_b n_{ab}}\) values in all. So the row margin, the mean of all values in row \(a\), is

\[ \begin{aligned} M_a &= \frac{\text{sum of row } a}{n_a} \\ &= \frac{\sum_b n_{ab}\, m_{ab}}{\sum_b n_{ab}} , \end{aligned} \]

where an empty cell has \({n_{ab} = 0}\) and drops out. This is the weighted mean of lesson P02.6.1 again, now along one row. For the coast, the clear cell holds 3 readings with mean \({83/3}\), about 27.67, and the rain cell 1 reading of 21:

\[ \begin{aligned} M_{\text{coast}} &= \frac{3 \cdot \tfrac{83}{3} + 1 \cdot 21}{3 + 1} \\ &= \frac{83 + 21}{4} = \frac{104}{4} = 26 . \end{aligned} \]

The plain mean of the two cells, \({(27.67 + 21)/2}\), is about 24.33: it counts the single rain reading as much as the three clear ones.

Worked example

The station-by-sky grid of means, by hand. Go through the nine cells and, for each, collect the readings with that station and that sky.

A 3 by 3 grid with orange row labels coast, hill, valley and column labels clear, cloudy, rain. Each filled cell shows its arithmetic and its result in bold vermillion. Coast clear: (27 plus 27 plus 29) over 3 equals 27.67. Coast cloudy: no readings, NaN, dashed reddish purple. Coast rain: 21 over 1 equals 21. Hill clear: 23 over 1 equals 23. Hill cloudy: (18 plus 20) over 2 equals 19. Hill rain: 19 over 1 equals 19. Valley clear: (30 plus 32) over 2 equals 31. Valley cloudy: (26 plus 28) over 2 equals 27. Valley rain: no readings, NaN.
P02.6.4-F3

The grid filled by hand. Every cell is a small group-by of its own.

Coast. Clear on Tuesday, Wednesday and Thursday: \({(27 + 27 + 29)/3 = 83/3 \approx 27.67}\). Rain on Monday: 21. Never cloudy, so that cell is NaN.

Hill. Clear on Tuesday: 23. Cloudy on Wednesday and Thursday: \({(18 + 20)/2 = 19}\). Rain on Monday: 19.

Valley. Clear on Wednesday and Thursday: \({(30 + 32)/2 = 31}\). Cloudy on Monday and Tuesday: \({(26 + 28)/2 = 27}\). Never rain: NaN.

Read it. Along each row, clear is the warmest sky: 27.67 against 21 at the coast, 23 against 19 at the hill, 31 against 27 at the valley. Check the counts: \({3 + 1 + 1 + 2 + 1 + 2 + 2 = 12}\), every reading in exactly one cell.

Code

The table, then the grid from scratch with a dictionary keyed by pairs:

import numpy as np
import pandas as pd

temps = np.array([[21, 19, 26], [27, 23, 28], [27, 18, 30], [29, 20, 32]])
days, stations = ['mon', 'tue', 'wed', 'thu'], ['coast', 'hill', 'valley']
readings = pd.DataFrame({
    'day': [d for d in days for s in stations],
    'station': stations * 4,
    'temp': temps.ravel(),
    'sky': ['rain', 'rain', 'cloudy', 'clear', 'clear', 'cloudy',
            'clear', 'cloudy', 'clear', 'clear', 'cloudy', 'clear'],
})

cells = {}
for station, sky, temp in zip(readings['station'], readings['sky'], readings['temp']):
    cells.setdefault((station, sky), []).append(temp)
grid = {s: {k: (sum(cells[(s, k)]) / len(cells[(s, k)]) if (s, k) in cells else None)
            for k in ['clear', 'cloudy', 'rain']} for s in stations}
for s in stations:
    print(s, grid[s])
coast {'clear': 27.666666666666668, 'cloudy': None, 'rain': 21.0}
hill {'clear': 23.0, 'cloudy': 19.0, 'rain': 19.0}
valley {'clear': 31.0, 'cloudy': 27.0, 'rain': None}

The same grid with unstack and with pivot_table, then the margins:

stacked = readings.groupby(['station', 'sky'])['temp'].mean().unstack()
pt = readings.pivot_table(index='station', columns='sky', values='temp', aggfunc='mean')
with_margins = readings.pivot_table(index='station', columns='sky', values='temp',
                                    aggfunc='mean', margins=True)
print(pt.round(2))
print(stacked.equals(pt), int(pt.isna().sum().sum()))
print(with_margins['All'].tolist(), with_margins.loc['All'].tolist())
sky      clear  cloudy  rain
station                     
coast    27.67     NaN  21.0
hill     23.00    19.0  19.0
valley   31.00    27.0   NaN
True 2
[26.0, 20.0, 29.0, 25.0] [28.0, 23.0, 20.0, 25.0]

Counts with crosstab, and a confusion matrix built the same way:

counts = pd.crosstab(readings['station'], readings['sky'], margins=True)
print(counts)

y_true = pd.Series([1, 0, 1, 1, 0, 0, 1, 0], name='true')
y_pred = pd.Series([1, 0, 0, 1, 0, 1, 1, 0], name='predicted')
confusion = pd.crosstab(y_true, y_pred)
print(confusion)
print('accuracy', np.trace(confusion.to_numpy()) / len(y_true))
sky      clear  cloudy  rain  All
station                          
coast        3       0     1    4
hill         1       2     1    4
valley       2       2     0    4
All          6       4     2   12
predicted  0  1
true           
0          3  1
1          1  3
accuracy 0.75

Finally real data: the mean petal length of each species in scikit-learn’s iris.

from sklearn.datasets import load_iris

iris = load_iris(as_frame=True)
flowers = iris.frame.assign(species=iris.target_names[iris.target])
petal = flowers.groupby('species')['petal length (cm)'].mean()
print(petal.round(3))
species
setosa        1.462
versicolor    4.260
virginica     5.552
Name: petal length (cm), dtype: float64
A strip chart titled iris: 50 flowers per species. Three rows, setosa, versicolor and virginica, each with 50 blue dots spread along a horizontal axis of petal length in centimetres from 1 to 7. Setosa's dots sit between about 1 and 1.9, versicolor's between 3 and 5.1, virginica's between 4.5 and 6.9. A vermillion bar marks each group mean, labelled mean 1.462, mean 4.260 and mean 5.552.
P02.6.4-F6

The group-by on real data. Each species is one group of 50 flowers and its mean petal length is one number; no setosa petal is as long as any petal of the other two species, so this one measurement alone separates setosa from them.

Explore

The split-apply-combine machine lives in lesson P02.6.1. Run it with the key station and then with the key sky: the two results are the All column and the All row of this lesson’s pivot table.

Pitfalls

Two halves showing the coast row of the pivot table: clear 27.67, cloudy NaN, rain 21. Left, marked with a reddish-purple cross and headed mean of the cells: (83 thirds plus 21) over 2, about 24.33, which counts the one rain reading as much as three clear ones. Right, marked with a green tick and headed mean of the readings, with 3 readings under clear and 1 reading under rain: (3 times 83 thirds plus 1 times 21) over 4 equals 26, which equals (21 plus 27 plus 27 plus 29) over 4, the All value that margins=True shows.
P02.6.4-F5

Wrong beside right. A margin of means weights each cell by its number of readings.

“The All column is the mean of the cells in that row.”

It is the mean of the readings in that row. The coast’s cells, 27.67 and 21, average to about 24.33, but its four readings average to 26, because three of them are clear. The two agree only when every cell holds the same number of readings.

“An empty cell is a zero, so fill it with 0.”

In a table of counts it is: no rows, count 0, which is why crosstab writes 0. In a table of means it is not: fill_value=0 would claim that the valley’s mean in rain was 0 °C. Leave NaN where nothing was measured.

“crosstab(predicted, true) gives the same confusion matrix.”

It gives its transpose: the false positives and the false negatives swap places. Follow one convention and say it; this course puts the true label down the side, as ML01.7.1 does.

“pivot and pivot_table are the same.”

pivot only rearranges and raises an error if a (row, column) pair occurs twice, as (coast, clear) does three times here. pivot_table summarises the repeats with aggfunc.

Check yourself

Recall

1. What does unstack() do to the result of groupby(['station', 'sky'])['temp'].mean()?

Show answer

It moves the inner index level, sky, to the columns, giving a 3 by 3 grid with stations down the side and NaN for the two pairs with no readings.

2. Why does the pivot table of means have NaN for valley in rain while crosstab has 0?

Show answer

There are no such readings. The count of no readings is 0, but the mean of no readings does not exist, so it is missing.

3. What does margins=True add to the pivot table of mean temperature, and what is its corner?

Show answer

An All column with each station’s mean (26, 20, 29), an All row with each sky’s mean (28, 23, 20), and the corner, the mean of all 12 readings, 25.

4. Why is the coast’s All value 26 and not 24.33?

Show answer

It is the mean of the coast’s four readings. The clear cell stands for three of them and the rain cell for one, so they weigh 3 to 1: \({(83 + 21)/4 = 26}\).

5. How do you build a confusion matrix with pandas?

Show answer

pd.crosstab(y_true, y_pred): true labels down the side, predicted labels across, a count in each cell and the correct answers on the diagonal.

Exercises

1. ★ Build the pivot table of the highest temperature by station and sky.

Show solution

readings.pivot_table(index='station', columns='sky', values='temp', aggfunc='max') gives coast: clear 29, cloudy NaN, rain 21; hill: clear 23, cloudy 20, rain 19; valley: clear 32, cloudy 28, rain NaN. The cells come from the same collections of readings as the worked example, with the maximum in place of the mean.

2. ★★ Build the crosstab of station and sky with margins, then with normalize='index'. What share of the coast’s readings were clear?

Show solution

With margins the body is coast 3, 0, 1; hill 1, 2, 1; valley 2, 2, 0; the All column is 4, 4, 4 and the All row 6, 4, 2, with 12 in the corner. normalize='index' divides each row by its total, so the coast row becomes \({3/4 = 0.75}\), 0 and \({1/4 = 0.25}\): three quarters of the coast’s readings were clear. Every row of shares adds up to 1.

3. ★★★ Use the weighted formula with the counts from the crosstab to rebuild the hill’s All value and the clear column’s All value from the cells alone.

Show solution

Hill: the cells are clear 23 from 1 reading, cloudy 19 from 2, rain 19 from 1, so

\[ \begin{aligned} M_{\text{hill}} &= \frac{1 \cdot 23 + 2 \cdot 19 + 1 \cdot 19}{4} \\ &= \frac{80}{4} = 20 . \end{aligned} \]

The plain mean of the three cells would be \({61/3 \approx 20.33}\).

Clear, down its column: coast \({83/3}\) from 3 readings, hill 23 from 1, valley 31 from 2, so

\[ \begin{aligned} M_{\text{clear}} &= \frac{3 \cdot \tfrac{83}{3} + 23 + 2 \cdot 31}{6} \\ &= \frac{83 + 23 + 62}{6} \\ &= \frac{168}{6} = 28 , \end{aligned} \]

the clear mean of lesson P02.6.2.

Summary card

A pivot table is a two-key group-by laid out as a grid.

The anchor figure again: the seven rows of the station and sky group-by become a 3 by 3 grid of mean temperatures, with NaN in the two cells that have no readings.
P02.6.4-F1

One key down, one key across.

  • Grid: groupby([r, c])[x].mean().unstack(), or pivot_table(index=r, columns=c, values=x).
  • Margins: margins=True adds All: each key’s summary over its whole row or column.
  • A margin of means is the count-weighted mean of its cells, not their plain mean.
  • Counts: pd.crosstab(r, c), 0 for an empty pair; crosstab(y_true, y_pred) is the confusion matrix.

Sources and next steps

Sources. Each was opened and checked against this lesson on 15 November 2026.

  • pandas developers, Reshaping and pivot tables, pandas 3.1.1 User Guide: pivot_table for pivoting with aggregation; margins=True adds a row and a column labelled All with partial group aggregates; unstack pivots a level of the row index to the columns; crosstab computes a frequency table of the factors by default.
  • pandas developers, pandas.pivot_table and pandas.crosstab, pandas 3.1.1 API reference: aggfunc defaults to "mean", margins_name defaults to "All", and the crosstab examples show a count of 0 for a combination that does not occur.
  • Hadley Wickham, The Split-Apply-Combine Strategy for Data Analysis, Journal of Statistical Software 40(1), 2011. Section 1 names Excel’s pivot tables and the SQL group by operator among the tools that carry out the strategy.
  • scikit-learn developers, load_iris, scikit-learn 1.9.1: 150 samples in 3 classes of 50, and as_frame=True returns a DataFrame in frame.

Next. P02.7 Dates, text and categories uses time itself as a group key, and ML01.7.1 reads a classifier’s quality off its confusion matrix.

This lesson unlocks

P02.7 Dates, text and categories ML01.7.1 Accuracy and the confusion matrix

Your progress is saved in this browser only. No account, no tracking.
Back to top