pivot_table(): Reshape + Aggregate
Build Excel-style summary tables with pivot_table(values, index, columns, aggfunc, fill_value, margins), and know exactly when to choose it over pivot() — video 08.
Watch the lesson
1 video on CodeWithMunnaX
Real match data almost always has repeated combinations: a team plays many matches per season, at home and away. pivot() gives up on duplicates; pivot_table() handles them by aggregating — exactly like a Pivot Table in Excel. Notebook 11 sums it up: "pivot() = data ghumana, pivot_table() = data ghumana + calculation."
python
inn = pd.DataFrame({
"season": [2023, 2023, 2023, 2023, 2023, 2024, 2024, 2024, 2024, 2024],
"team": ["MI", "MI", "CSK", "CSK", "RCB", "MI", "CSK", "CSK", "RCB", "RCB"],
"venue": ["Home", "Away", "Home", "Away", "Home", "Home", "Home", "Away", "Home", "Away"],
"score": [192, 165, 210, 178, 189, 201, 176, 220, 241, 187],
"won": [1, 0, 1, 1, 0, 1, 0, 1, 1, 0],
})
print(inn)text
season team venue score won
0 2023 MI Home 192 1
1 2023 MI Away 165 0
2 2023 CSK Home 210 1
3 2023 CSK Away 178 1
4 2023 RCB Home 189 0
5 2024 MI Home 201 1
6 2024 CSK Home 176 0
7 2024 CSK Away 220 1
8 2024 RCB Home 241 1
9 2024 RCB Away 187 0Definition
pivot_table()creates a spreadsheet-style summary table: it groups the rows by theindex(andcolumns) keys, aggregates thevaluesin each group withaggfunc(mean by default), and lays the results out as a 2D grid with one key along the rows and the other along the columns.
- Groups — internally it is a
groupbyonindex + columns. - Aggregates — duplicates are no longer a problem: all rows of a cell are combined (summed, averaged, counted…).
- 2D grid — the grouped result is then unstacked into rows × columns, like
groupby(...).agg(...).unstack().
Syntax
python
df.pivot_table(values=None, index=None, columns=None, aggfunc="mean",
fill_value=None, margins=False, margins_name="All",
dropna=True, observed=True, sort=True)
pd.pivot_table(df, ...) # same as a function| Parameter | Meaning | Default |
|---|---|---|
values | column(s) to aggregate | all numeric columns not used as keys |
index | column(s) whose values become the rows | None |
columns | column(s) whose values become the columns | None |
aggfunc | how to combine values in a cell: "mean", "sum", "count", "max", "size", a list, a dict {col: func}, or a function | "mean" |
fill_value | value used for empty cells (combinations with no rows) instead of NaN | None |
margins | add an extra row and column with totals over all rows/columns | False |
margins_name | label of the totals row/column | "All" |
dropna | drop columns whose entries are all NaN | True |
observed | for categorical keys, show only observed categories | True (pandas 3) |
sort | sort the keys | True |
Example — default aggfunc is mean
python
print(inn.pivot_table(values="score", index="team", columns="season"))text
season 2023 2024
team
CSK 194.0 198.0
MI 178.5 201.0
RCB 189.0 214.0How it works: CSK played 2 innings in 2023 (210 and 178), so the cell is their mean, 194.0. If you forget aggfunc, you get averages — a classic surprise when you expected totals.
Example — aggfunc="sum" with margins
python
print(inn.pivot_table(values="score", index="team", columns="season", aggfunc="sum", margins=True))text
season 2023 2024 All
team
CSK 388 396 784
MI 357 201 558
RCB 189 428 617
All 934 1025 1959How it works: margins=True adds an All column (each team's total over all seasons) and an All row (each season's total over all teams), plus the grand total 1959 in the corner. Margins are computed from the original rows using the same aggfunc — with aggfunc="mean", the All cell is the mean of all 10 scores (195.9), not the average of the averages:
python
print(inn.pivot_table(values="score", index="team", columns="season",
aggfunc="mean", margins=True, margins_name="Overall").round(1))text
season 2023 2024 Overall
team
CSK 194.0 198.0 196.0
MI 178.5 201.0 186.0
RCB 189.0 214.0 205.7
Overall 186.8 205.0 195.9Example — fill_value for empty cells
python
print(inn.pivot_table(values="score", index=["season", "team"], columns="venue",
aggfunc="sum", fill_value=0))text
venue Away Home
season team
2023 CSK 178 210
MI 165 192
RCB 0 189
2024 CSK 220 176
MI 0 201
RCB 187 241How it works: RCB had no away innings in 2023 — without fill_value that cell would be NaN (and the column float). A list for index creates a two-level row index (season, team).
Example — several functions, or one per column
python
print(inn.pivot_table(values="score", index="team", columns="season", aggfunc=["sum", "max"]))
print(inn.pivot_table(index="team", values=["score", "won"], aggfunc={"score": "mean", "won": "sum"}))text
sum max
season 2023 2024 2023 2024
team
CSK 388 396 210 220
MI 357 201 192 201
RCB 189 428 189 241
score won
team
CSK 196.000000 3
MI 186.000000 2
RCB 205.666667 1A list makes one block of columns per function; a dict picks a function per value column (here mean score and total wins). Without columns, pivot_table is simply a grouped summary.
Example — pivot_table fixes the duplicate problem
python
df2 = pd.DataFrame({
"Student": ["A", "A", "A", "B", "B"],
"Subject": ["Math", "Math", "Science", "Math", "Science"],
"Marks": [90, 80, 85, 70, 75],
})
print(df2.pivot_table(index="Student", columns="Subject", values="Marks", aggfunc="mean"))text
Subject Math Science
Student
A 85.0 85.0
B 70.0 75.0Student A has two Math marks (90, 80) — pivot() would fail; pivot_table averages them to 85.0 (notebook 11's example).
pivot vs pivot_table
pivot() | pivot_table() | |
|---|---|---|
| Purpose | reshape only | reshape + aggregate |
| Duplicate (index, columns) pairs | ❌ ValueError | ✅ combined with aggfunc |
aggfunc | — | "mean" by default; sum, count, max, list, dict… |
fill_value, margins | — | ✅ |
| Non-numeric values in cells | ✅ any value (it just moves them) | needs an aggfunc that works on them (e.g. "first", "count") |
| Speed | faster (no grouping) | slower (groupby inside) |
| Use when | each pair is unique and you just want to rotate | summarising raw records (most real data) |
Equivalent with groupby:
inn.pivot_table(values="score", index="team", columns="season")gives the same table asinn.groupby(["team", "season"])["score"].mean().unstack(). A pivot table is a groupby whose result is spread into a grid.
Common mistakes
- ❌ Forgetting
aggfuncand reading averages as totals. ✅ Always writeaggfunc=explicitly. - ❌ Averaging the
Allrow/column by hand. ✅margins=Truecomputes totals from the raw rows. - ❌ Using
fill_value=0when 0 is misleading (e.g. average score). ✅ Only fill with 0 when "no rows" really means zero (counts, sums). - ❌ Passing text columns as
valueswithaggfunc="mean"→TypeError. ✅ Choose numericvaluesoraggfunc="count"/"first".
Interview tip
Q: What is the difference between pivot and pivot_table?
A: pivot only reshapes and requires unique index/column pairs. pivot_table groups and aggregates duplicates with aggfunc (mean by default) and adds options like fill_value and margins. It is effectively groupby(...).agg(...).unstack().
Key points to remember
- pivot_table() groups by index/columns keys, aggregates values with aggfunc, and lays the result out as a 2D grid.
- The default aggfunc is 'mean' — pass aggfunc='sum' / 'count' / 'max' explicitly when you want something else.
- Unlike pivot(), pivot_table() handles duplicate (index, columns) pairs by aggregating them.
- fill_value replaces NaN in empty cells; margins=True adds 'All' totals computed from the original rows.
- aggfunc can be a list (one block per function) or a dict (a function per value column).
- A list for index or columns creates MultiIndex rows/columns.
- pivot_table(values, index, columns) ≈ groupby([index, columns])[values].agg(func).unstack().
Try it yourself
Season × team scores with sum, margins and fill_value
List / dict aggfunc and the groupby equivalent
Practice: city × product sales (notebook 11 style)
Revision Flashcards
Tap a card to flip it and see the answer.