Lesson 31 · Pandas Full Course
Pandas MultiIndex Explained: Hierarchical Rows and Columns Made Simple | Pandas Course #31
In this lesson you'll be able to: Build a MultiIndex and select rows with loc and xs Slice ranges safely with sortindex and IndexSlice Read groupby results…
- CoursePandas Full Course
- Lesson31 of 65
- FormatJupyter notebook · 50 code cells
- Data1 dataset
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.
- gapminder.csv preview80.2 KB
📓 Full notebook
Download .ipynbPandas 31: Working with MultiIndex and Hierarchical Columns#
In this lesson you'll be able to:
- Build a MultiIndex and select rows with loc and xs
- Slice ranges safely with sort_index and IndexSlice
- Read groupby results that have several index levels
- Select from, and flatten, two-level column headers
- Answer a real question by grouping on index levels Dataset: gapminder, 142 countries from 1952 to 2007 (gapminder.csv)
Warm-up: unstack from lesson 30#
Lesson 30 reshaped data with stack and unstack. One more time:
import pandas as pd
import matplotlib.pyplot as plt
gap = pd.read_csv("data/pandas/gapminder.csv")
print(gap.shape)
gap.head(3)
life = gap.groupby(["continent", "year"])["lifeExp"].mean()
life.unstack().iloc[:, [0, -1]].round(1)
print(type(life.index).__name__)
print(life.index.names)
Build a MultiIndex and select from it#
Problem: each row is identified by two labels, not one.
g = gap.set_index(["country", "year"])
print(g.index.nlevels, g.index.names)
g.head(3)
years = g.index.get_level_values("year")
print(years.min(), years.max())
print(g.index.get_level_values("country").nunique())
g.index[:3]
g.loc["Japan"].head(3)
row = g.loc[("Japan", 2007)]
print(row)
print(g.loc[("Japan", 2007), "lifeExp"])
Quick check: what does g.loc["Japan", "lifeExp"] return?
g.loc["Japan", "lifeExp"].head(3)
pairs = pd.MultiIndex.from_product(
[["Japan", "Kenya"], [1952, 2007]], names=["country", "year"])
g.loc[pairs, "lifeExp"]
Common mistake: selecting an inner level with plain loc
g.loc[2007]
y2007 = g.xs(2007, level="year")
print(y2007.shape)
y2007.sort_values("lifeExp").head(3)
Slice ranges with sort_index and IndexSlice#
Problem: pick several countries and a span of years in one go.
print(g.index.is_monotonic_increasing)
g.loc["Canada":"Chile", "lifeExp"].iloc[[0, -1]]
Common mistake: slicing an unsorted MultiIndex
mixed = g.sample(frac=1, random_state=42)
mixed.loc["Canada":"Chile"]
mixed = mixed.sort_index()
mixed.loc["Canada":"Chile"].shape
idx = pd.IndexSlice
pick = idx[["China", "India"], 1997:2007]
g.loc[pick, ["lifeExp", "gdpPercap"]].round(1)
in_2007 = g.loc[idx[:, 2007], "lifeExp"]
in_2007.nlargest(3)
Your turn#
Show life expectancy for France, Germany and the United Kingdom, in 1952 and 2007 only, with years as columns.
trio = ["France", "Germany", "United Kingdom"]
sel = g.loc[idx[trio, [1952, 2007]], "lifeExp"]
sel.unstack()
by_year = g.swaplevel().sort_index()
by_year.loc[2007].head(3)
Read and summarise grouped results#
Problem: groupby on two keys returns a MultiIndex. Use it.
cols = ["lifeExp", "gdpPercap"]
cont = gap.groupby(["continent", "year"])[cols].mean()
cont.round(1).head(3)
cont.loc["Europe"].tail(3).round(1)
cont.xs(2007, level="year").round(1)
world = g.groupby(level="year")["pop"].sum() / 1e9
world.iloc[[0, 5, -1]].round(2)
per_country = g.groupby(level="country")["lifeExp"].max()
per_country.nlargest(3)
year_avg = g.groupby(level="year")["lifeExp"].transform("mean")
above = (g["lifeExp"] - year_avg).round(1)
above.loc[["Japan", "Kenya"], 2007]
Your turn#
Using y2007, name the richest country in each continent.
rich = y2007.groupby("continent")["gdpPercap"].idxmax()
rich
Quick check: how many continents and years does cont cover?
print(cont.index.get_level_values("continent").nunique())
print(cont.index.levels[1].tolist())
Hierarchical columns#
Problem: several statistics for several columns give two-level headers.
recent = gap[gap["year"] == 2007]
stats = recent.groupby("continent")[cols].agg(["mean", "max"])
stats.round(1)
print(stats.columns.nlevels)
print(stats.columns.tolist()[:2])
stats["lifeExp"].round(1)
stats[("gdpPercap", "max")].round(0)
Common mistake: asking for a lower column level directly
stats["mean"]
stats.xs("mean", axis=1, level=1).round(1)
by_stat = stats.swaplevel(axis=1).sort_index(axis=1)
by_stat.round(1).head(3)
Your turn#
From stats, show only the max columns, then find the continent with the highest single life expectancy.
maxes = stats.xs("max", axis=1, level=1)
print(maxes.round(1))
print(maxes["lifeExp"].idxmax())
flat = stats.copy()
flat.columns = flat.columns.map("_".join)
flat.round(1).head(3)
named = recent.groupby("continent").agg(
life_mean=("lifeExp", "mean"),
gdp_max=("gdpPercap", "max"),
)
named.round(1).head(3)
Move levels between rows and columns#
Problem: put a level where it's easiest to compare.
wide = cont.unstack("continent")
print(wide.columns.nlevels)
wide.loc[[1952, 2007], "lifeExp"].round(1)
back = wide.stack("continent")
print(back.shape, cont.shape)
order = cont.reorder_levels(["year", "continent"]).sort_index()
order.loc[2007, "lifeExp"].round(1)
tidy = cont.reset_index()
print(tidy.columns.tolist())
tidy.round(1).head(3)
only_year = cont.droplevel("continent")
print(only_year.index.is_unique)
Your turn#
Show GDP per person for the last three years, continents as columns.
gdp = cont["gdpPercap"].unstack("continent")
gdp.tail(3).round(0)
Mini-project: biggest gains in each continent#
Question: which country added the most years of life, 1952-2007?
keys = ["continent", "country", "year"]
t = gap.set_index(keys)["lifeExp"].unstack("year")
print(t.shape)
t.iloc[:3, [0, -1]]
gain = (t[2007] - t[1952]).round(1)
top = gain.groupby(level="continent").idxmax()
gain.loc[top.values]
gain.groupby(level="continent").median()
big = gain > 30
big.groupby(level="continent").sum()
share = (gain > 20).groupby(level="continent").mean()
share.round(2).sort_values(ascending=False)
gain.nsmallest(3)
winners = gain.loc[top.values].droplevel("continent")
ax = winners.sort_values().plot(kind="barh", figsize=(7, 4))
ax.set_title("Biggest life expectancy gain per continent, 1952-2007")
ax.set_xlabel("Years of life gained")
ax.set_ylabel("Country")
plt.show()
Recap#
- set_index with a list builds a MultiIndex; each label is a tuple
- loc selects from the outer level; xs selects from any level
- Sort the index before slicing, and use IndexSlice for ranges
- Two-level columns: select with tuples, flatten with map
- groupby(level=...) summarises one level of the index
Challenges#
- Use xs to compare GDP per person in all Asian countries in 1977.
- Find each continent's most populous country in 2007 with idxmax.
- Build min, mean and max of pop per continent, then flatten the columns.
What's next#
Lesson 32: Concatenating DataFrames Vertically and Horizontally Combine several tables into one with concat.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



