Mathew K AnalyticsLesson 14 · Pandas Full Course
Advanced Slicing in Pandas: MultiIndex, xs and IndexSlice Explained | Pandas Course #14
In this lesson you'll be able to: Slice rows and columns by label and by position, without off-by-one bugs Build a MultiIndex and slice it by country and…
- CoursePandas Full Course
- Lesson14 of 65
- FormatJupyter notebook · 44 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 14: Advanced Slicing and Cross-Section Operations#
In this lesson you'll be able to:
- Slice rows and columns by label and by position, without off-by-one bugs
- Build a MultiIndex and slice it by country and year range
- Pull cross-sections out of any level with
xsandIndexSlice - Slice wide tables with year columns, then flatten them again Data: Gapminder, 142 countries from 1952 to 2007
Warm-up: sorting from last lesson#
In lesson 13 we sorted and ranked. Who led in 2007?
import pandas as pd
gap = pd.read_csv("data/pandas/gapminder.csv")
print(gap.shape)
gap[gap["year"] == 2007].nlargest(3, "lifeExp")
Label slices versus position slices#
loc includes the end label; iloc stops before the end position.
Quick check: how many rows do
loc[0:4]andiloc[0:4]return?
print(len(gap.loc[0:4]), "rows with loc")
print(len(gap.iloc[0:4]), "rows with iloc")
gap.loc[0:2, "country":"year"]
gap.iloc[-3:, [0, 2, 4]]
first_years = gap.iloc[::12]
print(len(first_years), "rows")
first_years[["country", "year", "lifeExp"]].head(3)
recent = gap.loc[gap["year"] >= 2002, "country":"pop"]
print(recent.shape)
recent.head(3)
print(gap.at[0, "lifeExp"])
print(gap.iat[0, 4])
Common mistake: giving
iloca column name.
gap.iloc[0:3, "country"]
col = gap.columns.get_loc("country")
print(gap.iloc[0:3, col].tolist())
print(gap.loc[0:2, "country"].tolist())
Your turn: with
iloc, show the last 2 rows and the first 3 columns.
gap.iloc[-2:, :3]
Build a MultiIndex and slice it#
Two keys, country and year, become a two-level row index.
m = gap.set_index(["country", "year"]).sort_index()
print(m.index.nlevels, "levels")
m.head(3)
m.loc["Canada"].tail(3)
block = m.loc["Canada":"Chile"]
block.index.get_level_values("country").unique().tolist()
m.loc[("Canada", 2007)]
years = slice(1977, 2002)
m.loc[("Canada", years), ["lifeExp", "gdpPercap"]]
Common mistake: slicing a MultiIndex that isn't sorted.
by_year = gap.set_index(["year", "country"])
by_year.loc[(slice(1990, 2000), "Canada"), :]
by_year = by_year.sort_index()
by_year.loc[(slice(1990, 2000), "Canada"), "lifeExp"]
Your turn: get Mexico's population from 1987 to 2007.
m.loc[("Mexico", slice(1987, 2007)), "pop"]
Cross-sections with xs#
xs grabs every row that matches one value at any level.
Common mistake: calling
xswithout saying which level.
m.xs(2007)
in_2007 = m.xs(2007, level="year")
print(in_2007.shape)
in_2007.head(3)
in_2007.groupby("continent")["lifeExp"].median().round(1)
keep = m.xs(2007, level="year", drop_level=False)
keep.index[:2].tolist()
Quick check: is
m.xs("Japan", level="country")the same asm.loc["Japan"]?
via_xs = m.xs("Japan", level="country")
print(via_xs.equals(m.loc["Japan"]))
Your turn: take the 1952 cross-section. Which 3 countries had the lowest life expectancy?
in_1952 = m.xs(1952, level="year")
in_1952.nsmallest(3, "lifeExp")[["continent", "lifeExp"]]
Precise selections with IndexSlice#
pd.IndexSlice writes multi-level slices that read like a sentence.
idx = pd.IndexSlice
pair = m.loc[idx[["Canada", "Mexico"], 1997:2007], "lifeExp"]
pair.unstack("country")
every_2007 = m.loc[idx[:, 2007], ["lifeExp", "pop"]]
print(every_2007.shape)
m3 = gap.set_index(["continent", "country", "year"]).sort_index()
eu = m3.xs(("Europe", 2007), level=["continent", "year"])
print(len(eu), "European countries")
eu.nlargest(3, "gdpPercap")[["gdpPercap"]].round(0)
Quick check: how would you get every Asian country in 1952 from
m3?
asia_52 = m3.loc[idx["Asia", :, 1952], "lifeExp"]
print(len(asia_52), "countries")
print(asia_52.round(1).sort_values().tail(3))
m.query("year == 2007 and lifeExp > 81")[["lifeExp"]]
yr_first = m.swaplevel().sort_index()
yr_first.loc[2007].head(3)
Slice wide tables, then flatten#
When years become columns, slice them like any other labels.
wide = m["lifeExp"].unstack("year")
print(wide.shape)
wide.loc["Canada":"Chile", 1987:2007]
Common mistake: typing a year column as text.
wide.loc["Canada", "2007"]
print(wide.columns.dtype)
print(wide.loc["Canada", 2007])
Your turn: make a wide table of
gdpPercapand slice Japan and South Korea, 1997-2007.
gdp = m["gdpPercap"].unstack("year")
gdp.loc[["Japan", "Korea, Rep."], 1997:2007].round(0)
both = m[["lifeExp", "gdpPercap"]].unstack("year")
print(both.columns.nlevels, "column levels")
both.xs(2007, axis=1, level="year").head(3).round(1)
flat = in_2007.reset_index()
print(flat.columns.tolist())
flat.head(2)
import os
os.makedirs("output/pandas", exist_ok=True)
flat.to_csv("output/pandas/l14_gap2007.csv", index=False)
back = pd.read_csv("output/pandas/l14_gap2007.csv")
print(back.shape)
Mini-project: which continent closed the gap?#
Compare 1952 with 2007 using cross-sections. Who gained the most years of life?
start = m.xs(1952, level="year")
end = m.xs(2007, level="year")
gain = (end["lifeExp"] - start["lifeExp"]).rename("gain")
gain.sort_values(ascending=False).head(3).round(1)
by_cont = gain.groupby(end["continent"])
by_cont.agg(["mean", "min", "max"]).round(1)
backwards = gain[gain < 0].sort_values().round(1)
print(len(backwards), "countries went backwards")
backwards
south = ["Botswana", "Lesotho", "Swaziland", "Zimbabwe"]
peak = m.loc[idx[south, 1987:2007], "lifeExp"].unstack("year")
peak[[1987, 1992, 2002, 2007]].round(1)
med = pd.DataFrame({
"1952": start.groupby("continent")["lifeExp"].median(),
"2007": end.groupby("continent")["lifeExp"].median()})
med.round(1)
africa = m3.loc[idx["Africa", :, 1987:2007], "lifeExp"]
trend = africa.groupby(level="year").mean()
trend.round(1)
import matplotlib.pyplot as plt
four = ["China", "India", "Zimbabwe", "Botswana"]
paths = m.loc[idx[four, :], "lifeExp"].unstack("country")
ax = paths.plot(figsize=(8, 4), marker="o")
ax.set_title("Life expectancy, 1952-2007")
ax.set_xlabel("Year")
ax.set_ylabel("Life expectancy (years)")
plt.show()
Finding: Asia gained the most years; Africa's gains stalled after 1987 as southern Africa fell back.
Recap#
locslices include the end label;ilocslices exclude the endset_index([...]).sort_index()builds a sliceable MultiIndexxs(value, level=...)takes a cross-section at any levelpd.IndexSlicewrites clear multi-level slices with colonsunstackputs a level into columns;reset_indexflattens again
Challenges#
- With
m3, get every African country in 2007, sorted bylifeExp. - Take the 1977 cross-section and find the richest country per continent.
- Make a wide table of
popand slice India and China, 1952-1977.
What's next#
Lesson 15: Mini Project - Analysing Titanic Dataset Basics. Selecting, sorting and slicing, all in one real analysis.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.
Related lessons
20 minMaster Pandas Series: Advanced Slicing & Cross-Section Techniques for Data Analysis
57 minPandas Zero to Hero: The Complete Beginner-to-Advanced Course
36 minComprehensive Pandas Tutorial in Python: Data Analysis from Beginner to Advanced
26 min