Pivot Tables
What You Will Learn in This Lesson
By the end of this lesson, you will be able to:
- Lay a two-key summary out as a grid.
- Name what goes down, what goes across, and what fills the cells.
- Say what
aggfuncdefaults to. - Read the gaps in the grid.
Why This Matters
A pivot table is the same computation as a two-key group-by, arranged so a person can read it. That arrangement is the whole value: a grid with subjects down and years across is scannable in a way a 40-row two-level index is not.
Step 1: Rows, columns and a value
index is what goes down the side, columns is what goes across the top, values is what fills the cells.
The same numbers, laid out
Compare it with the group-by beneath. Identical arithmetic, different shape.
import pandas as pd
df = pd.DataFrame({"subject": ["m", "m", "a"], "year": [10, 11, 10], "score": [90, 80, 70]})
print(pd.pivot_table(df, index="subject", columns="year", values="score", aggfunc="mean"))
print()
print(df.groupby(["subject", "year"])["score"].mean())aggfunc is not count
It defaults to mean, which surprises people expecting a spreadsheet pivot's count. Name it whenever you mean something else.
import pandas as pd
df = pd.DataFrame({"subject": ["m", "m"], "year": [10, 10], "score": [90, 80]})
print(pd.pivot_table(df, index="subject", columns="year", values="score"))
print(pd.pivot_table(df, index="subject", columns="year", values="score", aggfunc="size"))Mini Practice #1: Grid and group-by
Try It YourselfProduce the same summary both ways and satisfy yourself the numbers match.
Step 2: Combinations with no rows
A grid has a cell for every combination, including the ones the data never had. Those cells are NaN, and that is information.
The gaps
Unlike a group-by, which simply omits an absent pair, the grid shows it as an empty cell — which is often exactly what you wanted to see.
import pandas as pd
df = pd.DataFrame({"subject": ["m", "a"], "year": [10, 11], "score": [90, 70]})
print(pd.pivot_table(df, index="subject", columns="year", values="score", aggfunc="mean"))Filling them, if a zero is honest
fill_value replaces the gaps. Use it only when "no rows" really does mean zero — for a count it usually does, for a mean it never does.
import pandas as pd
df = pd.DataFrame({"subject": ["m", "a"], "year": [10, 11], "score": [90, 70]})
print(pd.pivot_table(df, index="subject", columns="year", values="score",
aggfunc="size", fill_value=0))Filling a missing mean with 0 claims a group scored nothing, when the truth is that nobody sat it. The gap was the honest answer.
Mini Practice #2: Count, not mean
Try It YourselfMake a grid of how many rows each subject/year pair has, with empty pairs shown as 0.
Step 3: When to Use It
A grid people will read
One key down the side, one across the top, a summary in each cell.
pd.pivot_table(df, index="team", columns="term", values="score", aggfunc="mean")Totals along the edges
margins=True adds row and column totals to the grid.
When Not to Use It
Do not leave aggfunc to its default without thinking. Duplicate combinations are averaged, which may not be what the cell should say.
End-of-Lesson Exercises
Exercise 1: The shape of the grid
Write grid_shape(df) that pivots score with subject down the rows and year across the columns, and returns the shape of the result.
index is what goes down the side, columns is what goes across the top, values is what fills the cells. The shape is distinct subjects by distinct years.
Exercise 2: How many pairs never happened
Write empty_cells(df) that pivots the same way and returns how many cells of the grid are empty — combinations of subject and year with no rows behind them.
Pivot with aggfunc="mean" and leave the gaps as NaN, then count them with .isna().sum().sum(). Passing fill_value would remove the very thing you are counting.