Lesson 28 · Pandas Full Course
Advanced groupby in Pandas: Multi-level Groups and Custom Aggregations | Pandas Course #28
In this lesson you'll be able to: Group by three keys and read the multi-level result Select, roll up and rank across index levels Write your own…
- CoursePandas Full Course
- Lesson28 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.
- titanic.csv preview58.9 KB
- superstore.csv preview1.7 MB
📓 Full notebook
Download .ipynbPandas 28: Multi-level Grouping and Custom Aggregations#
In this lesson you'll be able to:
- Group by three keys and read the multi-level result
- Select, roll up and rank across index levels
- Write your own aggregation functions
- Flatten two-level column names into tidy labels
- Calculate whole-group ratios, like profit margin, correctly Data: the Titanic passenger list and 8,399 Superstore orders
Warm-up: class and sex on the Titanic#
Lesson 27 grouped by two keys. Same data, same move.
import pandas as pd
df = pd.read_csv("data/pandas/titanic.csv")
names = {1: "First", 2: "Second", 3: "Third"}
df["Class"] = df["Pclass"].map(names)
rates = df.groupby(["Class", "Sex"])["Survived"].mean()
rates.unstack().round(2)
Group by three keys and read the MultiIndex#
Problem: two keys can hide a third factor, like age.
bands = [0, 15, 80]
df["age_group"] = pd.cut(df["Age"], bins=bands,
labels=["Child", "Adult"])
df["age_group"].value_counts(dropna=False)
keys = ["Class", "Sex", "age_group"]
grp = df.groupby(keys, observed=True)
table = grp["Survived"].agg(["size", "mean"]).round(2)
table
print(table.index.nlevels)
print(table.index.names)
table["mean"].unstack("age_group")
table.loc[("Third", "male")]
table.loc[("First", "female", "Adult")]
table.sort_values("mean").head(3)
Common mistake: using loc for a label on an inner level
table.loc["Child"]
table.xs("Child", level="age_group")
Quick check: were boys in third class saved as often as boys in first?
boys = table.xs(("male", "Child"), level=["Sex", "age_group"])
boys
Your turn#
Show adult survival rates only, as a Class by Sex grid.
adults = table.xs("Adult", level="age_group")
adults["mean"].unstack()
Roll up, reshape and rank across levels#
Problem: a detailed table must be rolled up and ranked for a report.
orders = pd.read_csv("data/pandas/superstore.csv")
short = {"Product Category": "Category",
"Product Sub-Category": "Sub",
"Customer Segment": "Segment"}
orders = orders.rename(columns=short)
cols = ["Region", "Segment", "Category", "Sales", "Profit"]
print(orders.shape)
orders[cols].head(3)
sales = orders.groupby(["Region", "Category"])["Sales"].sum()
print(sales.shape)
sales.head(6).round(0)
by_region = sales.groupby(level="Region").sum()
by_region.sort_values(ascending=False).round(0)
by_cat = sales.swaplevel().sort_index()
by_cat.head(4).round(0)
region_total = sales.groupby(level="Region").transform("sum")
share = (sales / region_total).round(2)
share.unstack()
ranked = sales.sort_values(ascending=False)
ranked.groupby(level="Region").head(1).round(0)
flat = sales.reset_index(name="total_sales")
flat.head(3).round(0)
Your turn#
Which customer segment brings in the most sales in each category?
seg = orders.groupby(["Category", "Segment"])["Sales"].sum()
seg = seg.sort_values(ascending=False)
seg.groupby(level="Category").head(1).round(0)
Write your own aggregation functions#
Problem: the question needs a statistic pandas doesn't have built in.
print(orders["Profit"].median())
print(round((orders["Profit"] < 0).mean(), 3))
def loss_share(profits):
return (profits < 0).mean()
by_cat = orders.groupby("Category")["Profit"]
by_cat.agg(loss_share).round(3)
spread = orders.groupby("Category")["Discount"]
spread.agg(lambda d: d.max() - d.min())
by_cat.agg(["median", "mean", loss_share]).round(2)
Common mistake: a custom function that returns many values
by_cat.agg(lambda p: p[p < 0])
by_cat.agg(lambda p: p[p < 0].sum()).round(0)
summary = orders.groupby("Category").agg(
orders=("Order ID", "nunique"),
loss_rate=("Profit", loss_share),
biggest=("Sales", "max"),
lost=("Profit", lambda p: p[p < 0].sum()),
)
summary.round(2)
Your turn#
Write big_share: the share of orders over 1,000 dollars, per Category.
def big_share(sales):
return (sales > 1000).mean()
orders.groupby("Category")["Sales"].agg(big_share).round(3)
Quick check: what share of Technology orders in Nunavut lost money?
rk = orders.groupby(["Region", "Category"])["Profit"]
loss = rk.agg(loss_share).round(2)
print(loss.loc[("Nunavut", "Technology")])
Flatten two-level column names#
Problem: several statistics per column give awkward two-level headers.
wide = orders.groupby("Region").agg({
"Sales": ["sum", "mean"],
"Profit": ["sum", "min"],
})
print(wide.columns.tolist())
wide.sort_values(("Profit", "sum")).round(0).head(3)
Common mistake: asking for a joined name that doesn't exist
wide["Profit_sum"]
wide.columns = ["_".join(pair) for pair in wide.columns]
wide["Profit_sum"].sort_values().round(0).head(3)
named = orders.groupby("Region").agg(
sales_sum=("Sales", "sum"),
profit_min=("Profit", "min"),
)
print(named.columns.tolist())
Your turn#
Build median and max Shipping Cost per Category, flattened.
ship = orders.groupby("Category").agg(
{"Shipping Cost": ["median", "max"]})
ship.columns = ["_".join(pair) for pair in ship.columns]
ship
Whole-group ratios with apply#
Problem: a group's margin is total profit over total sales, not an average.
orders["margin"] = orders["Profit"] / orders["Sales"]
naive = orders.groupby("Category")["margin"].mean()
naive.round(3)
def margin(g):
return g["Profit"].sum() / g["Sales"].sum()
two = orders.groupby("Category")[["Sales", "Profit"]]
two.apply(margin).round(3)
Quick check: why do the two answers disagree so much?
small = orders["Sales"] < 100
print(round(orders.loc[small, "margin"].mean(), 3))
print(round(orders.loc[~small, "margin"].mean(), 3))
totals = orders.groupby(["Segment", "Category"])[["Sales", "Profit"]]
totals = totals.sum()
ratio = totals["Profit"] / totals["Sales"]
ratio.unstack().round(3)
Your turn#
Rank the 8 regions by true margin, lowest first.
reg = orders.groupby("Region")[["Sales", "Profit"]].sum()
(reg["Profit"] / reg["Sales"]).sort_values().round(3)
Mini-project: where is the store losing money?#
Plan: totals by category and sub-category, margins, then the losers.
keys = ["Category", "Sub"]
subs = orders.groupby(keys).agg(
sales=("Sales", "sum"),
profit=("Profit", "sum"),
loss_rate=("Profit", loss_share),
)
subs["margin"] = subs["profit"] / subs["sales"]
print(len(subs))
losers = subs[subs["profit"] < 0].sort_values("profit")
show = ["sales", "profit", "margin"]
losers.droplevel("Category")[show].round(3)
worst = subs["margin"].sort_values().head(8)
labels = worst.index.get_level_values("Sub")
import matplotlib.pyplot as plt
fig, ax = plt.subplots(figsize=(8, 4))
ax.barh(labels, worst.values)
ax.set_title("Superstore: lowest profit margins")
ax.set_xlabel("Profit margin (profit / sales)")
ax.set_ylabel("Sub-category")
plt.show()
tables = orders[orders["Sub"] == "Tables"]
t = tables.groupby("Region")[["Sales", "Profit"]].sum()
t["margin"] = t["Profit"] / t["Sales"]
t.sort_values("Profit").round(2).head(3)
loss = subs.loc[("Furniture", "Tables"), "profit"]
total = subs["profit"].sum()
print(f"Tables lost {-loss:,.0f}; store profit {total:,.0f}")
Recap#
- Pass a list of keys to get a MultiIndex, outermost first
- loc drills down with tuples; xs picks labels on any level
- groupby(level=...) rolls up; transform gives shares of a level
- A custom aggregation returns ONE value per group
- Flatten column pairs with join; ratios come from totals
Challenges#
- Titanic: share of missing ages per Class and Sex, with a custom function.
- Superstore: true margin by Region and Ship Mode, unstacked.
- Find the most profitable sub-category in each region.
What's next#
Lesson 29: Pivot Tables and Cross-tabulations Build these grids in one call, with totals, using pivot_table and crosstab.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



