Lesson 29 · Pandas Full Course
Pivot Tables in Pandas: Build Excel-Style Reports in One Line | Pandas Course #29
In this lesson you'll be able to: Build a summary grid in one call with pivottable Choose the statistic, or several, with aggfunc Add totals and fill gaps…
- CoursePandas Full Course
- Lesson29 of 65
- FormatJupyter notebook · 45 code cells
- Data2 datasets
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- superstore.csv preview1.7 MB
- titanic.csv preview58.9 KB
📓 Full notebook
Download .ipynbPandas 29: Pivot Tables and Cross-tabulations#
In this lesson you'll be able to:
- Build a summary grid in one call with pivot_table
- Choose the statistic, or several, with aggfunc
- Add totals and fill gaps with margins and fill_value
- Count and compare categories with crosstab
- Chart a pivot table to tell its story Data: 8,399 Superstore orders and the Titanic passenger list
Warm-up: a grid the long way#
Lesson 28 built grids with groupby and unstack. Here's one more.
import pandas as pd
orders = pd.read_csv("data/pandas/superstore.csv")
short = {"Product Category": "Category",
"Customer Segment": "Segment",
"Order Priority": "Priority", "Ship Mode": "Ship"}
orders = orders.rename(columns=short)
grp = orders.groupby(["Region", "Category"])["Sales"].sum()
grp.unstack().round(0)
Build a grid in one call with pivot_table#
Problem: say what goes down the side, across the top, and in the cells.
grid = orders.pivot_table(
index="Region",
columns="Category",
values="Sales",
aggfunc="sum",
)
grid.round(0)
print(grid.equals(grp.unstack()))
grid.idxmax(axis=1)
Quick check: what does pivot_table calculate if you leave out aggfunc?
avg = orders.pivot_table(index="Region", values="Sales")
avg.round(2).head(3)
Common mistake: using
pivoton data with repeats
orders.pivot(index="Region", columns="Category", values="Sales")
orders.pivot_table(index="Region", columns="Category",
values="Sales", aggfunc="max").round(0)
Your turn#
Total Profit by Region (rows) and Segment (columns).
orders.pivot_table(index="Region", columns="Segment",
values="Profit", aggfunc="sum").round(0)
Choose the statistic with aggfunc#
Problem: one question needs counts, another totals, another several at once.
orders.pivot_table(index="Segment", columns="Category",
values="Order ID", aggfunc="count")
orders.pivot_table(index="Segment", columns="Category",
values="Customer Name", aggfunc="nunique")
orders.pivot_table(index="Category", values=["Sales", "Profit"],
aggfunc={"Sales": "sum",
"Profit": "median"}).round(2)
stats = orders.pivot_table(index="Category", values="Profit",
aggfunc=["sum", "mean", "min"])
stats.round(0)
reg = orders.pivot_table(index="Region", values=["Sales", "Profit"],
aggfunc="sum")
reg["margin"] = reg["Profit"] / reg["Sales"]
reg.sort_values("margin").round(3).head(3)
def loss_share(profits):
return (profits < 0).mean()
orders.pivot_table(index="Region", columns="Category",
values="Profit", aggfunc=loss_share).round(2)
Your turn#
Median Sales by Segment (rows) and Category (columns).
orders.pivot_table(index="Segment", columns="Category",
values="Sales", aggfunc="median").round(2)
Add totals and fill the gaps#
Problem: reports need totals, and empty cells should read as zero.
tot = orders.pivot_table(index="Region", columns="Category",
values="Sales", aggfunc="sum",
margins=True, margins_name="Total")
tot.round(0)
tot.drop(index="Total").sort_values("Total", ascending=False).round(0)
box = orders.pivot_table(index="Product Container",
columns="Category", values="Sales",
aggfunc="sum")
box.round(0)
box = orders.pivot_table(index="Product Container",
columns="Category", values="Sales",
aggfunc="sum", fill_value=0)
box.round(0)
orders.pivot_table(index="Category", values="Sales",
aggfunc="mean", margins=True).round(2)
Common mistake: inventing a name for the statistic
orders.pivot_table(index="Region", values="Sales", aggfunc="total")
orders.pivot_table(index="Region", values="Sales",
aggfunc="sum").round(0).head(3)
Your turn#
Count orders by Product Container and Ship mode, zeros filled, with totals.
orders.pivot_table(index="Product Container", columns="Ship",
values="Order ID", aggfunc="count",
fill_value=0, margins=True)
Count combinations with crosstab#
Problem: how often does each pair of categories occur, and in what share?
df = pd.read_csv("data/pandas/titanic.csv")
names = {1: "First", 2: "Second", 3: "Third"}
df["Class"] = df["Pclass"].map(names)
pd.crosstab(df["Class"], df["Sex"])
pd.crosstab(df["Class"], df["Sex"], margins=True)
Common mistake: passing column names instead of columns
pd.crosstab("Class", "Sex")
pd.crosstab(df["Class"], df["Sex"]).loc["Third"]
pd.crosstab(df["Class"], df["Sex"], normalize="index").round(2)
Quick check: what share of all women were in third class?
pd.crosstab(df["Class"], df["Sex"], normalize="columns").round(2)
pd.crosstab(df["Class"], df["Sex"], normalize="all").round(2)
pd.crosstab(df["Class"], df["Sex"], values=df["Survived"],
aggfunc="mean").round(2)
pd.crosstab([df["Class"], df["Sex"]], df["Survived"])
pack = pd.crosstab(orders["Product Container"], orders["Category"],
normalize="columns")
pack.round(2)
Your turn#
Crosstab Class against Survived, as shares of each class.
pd.crosstab(df["Class"], df["Survived"],
normalize="index").round(2)
Multi-level pivots and charts#
Problem: a third factor, and a picture that shows the pattern.
rates = df.pivot_table(index=["Sex", "Class"], columns="Embarked",
values="Survived", aggfunc="mean")
rates.round(2)
rates.loc["male"].round(2)
Quick check: for third class women, which port had the best survival rate?
third_women = rates.loc[("female", "Third")]
print(third_women.idxmax(), round(third_women.max(), 2))
import matplotlib.pyplot as plt
ax = rates.loc["female"].plot(kind="bar", rot=0, figsize=(7, 4))
ax.set_title("Titanic: women's survival by class and port")
ax.set_xlabel("Passenger class")
ax.set_ylabel("Share who survived")
ax.legend(title="Port")
plt.show()
mix = pd.crosstab(df["Class"], df["Sex"], normalize="index")
ax = mix.plot(kind="bar", stacked=True, rot=0, figsize=(7, 4))
ax.set_title("Titanic: share of women and men in each class")
ax.set_xlabel("Passenger class")
ax.set_ylabel("Share of passengers")
plt.show()
Your turn#
Median Fare by Class (rows) and Embarked (columns), with totals.
df.pivot_table(index="Class", columns="Embarked", values="Fare",
aggfunc="median", margins=True).round(2)
Mini-project: do urgent orders ship faster?#
Plan: ship mode by priority, shipping cost, then profit by ship mode.
order = ["Critical", "High", "Medium", "Low", "Not Specified"]
ship = pd.crosstab(orders["Priority"], orders["Ship"],
normalize="index").loc[order]
ship.round(2)
cost = orders.pivot_table(index="Priority", columns="Ship",
values="Shipping Cost", aggfunc="mean")
cost.loc[order].round(2)
fmt = "%m/%d/%Y"
shipped = pd.to_datetime(orders["Ship Date"], format=fmt)
placed = pd.to_datetime(orders["Order Date"], format=fmt)
orders["days"] = (shipped - placed).dt.days
days = orders.pivot_table(index="Priority", columns="Ship",
values="days", aggfunc="mean")
days.loc[order].round(1)
wait = orders.pivot_table(index="Priority", values="days")
wait = wait["days"].loc[order]
ax = wait.plot(kind="bar", rot=0, figsize=(8, 4))
ax.set_title("Average days from order to shipping, by priority")
ax.set_xlabel("Order priority")
ax.set_ylabel("Days to ship")
plt.show()
crit = ship.loc["Critical", "Express Air"]
low = ship.loc["Low", "Express Air"]
print(f"Express Air: critical {crit:.0%}, low {low:.0%}")
print(f"Days: critical {wait['Critical']:.1f}, low {wait['Low']:.1f}")
Recap#
- pivot_table: index down the side, columns across, values in the cells
- aggfunc defaults to mean; pass sum, count, a list, a dict or a function
- pivot fails on repeats; pivot_table combines them
- margins adds totals; fill_value fills empty cells
- crosstab counts pairs; normalize turns counts into shares
Challenges#
- Superstore: total Profit by Region and Ship mode, with totals.
- Titanic: crosstab of Embarked and Class, as shares of each port.
- Superstore: loss_share by Segment and Ship mode. Which is worst?
What's next#
Lesson 30: Reshaping Data using melt, stack, and unstack Turn wide grids into long tables, and back again.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



