Mathew K AnalyticsLesson 11 · Pandas Full Course
Master the Pandas Index: set_index, reset_index and sort_index | Pandas Course #11
In this lesson you'll be able to: Turn a column into row labels with setindex, and look rows up Undo it with resetindex, and repair gappy row numbers Put…
- CoursePandas Full Course
- Lesson11 of 65
- FormatJupyter notebook · 51 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 11: Setting, Resetting, and Sorting Indexes#
In this lesson you'll be able to:
- Turn a column into row labels with set_index, and look rows up
- Undo it with reset_index, and repair gappy row numbers
- Put rows in label order with sort_index
- Build a two-level index of country and year
- Let the index line up data from two tables for you Dataset: Gapminder, 142 countries from 1952 to 2007 (data/pandas/gapminder.csv)
Warm-up: query and isin from lesson 10#
Lesson 10 filtered rows with query and isin. Same tools, new data.
import pandas as pd
df = pd.read_csv("data/pandas/gapminder.csv")
print(df.shape)
df.head()
nordic = ["Norway", "Sweden", "Denmark", "Finland", "Iceland"]
recent = df.query("year == 2007")
recent[recent["country"].isin(nordic)]
Look rows up by name with set_index#
Problem: row numbers like 803 mean nothing. Country names do.
print(df.index)
Common mistake: looking up a name the index doesn't contain
df.loc["Japan"]
by_country = recent.set_index("country")
by_country.loc["Japan"]
print(by_country.index[:5].tolist())
print(by_country.shape)
pick = ["Kenya", "Ghana", "Norway"]
by_country.loc[pick, ["lifeExp", "gdpPercap"]].round(1)
by_country.loc["Kenya":"Lesotho", ["lifeExp"]]
both = recent.set_index("country", drop=False)
both[["country", "lifeExp"]].head(2)
print(df["country"].is_unique)
print(by_country.index.is_unique)
Quick check: what was Brazil's life expectancy in 2007?
print(by_country.loc["Brazil", "lifeExp"])
Quick check: after recent.set_index("country"), does recent change?
recent.set_index("country")
print(recent.columns.tolist())
Undo it with reset_index#
Problem: labels need to become a normal column again, or be renumbered.
flat = by_country.reset_index()
flat.head(3)
gone = by_country.reset_index(drop=True)
print(gone.columns.tolist())
life = recent.groupby("continent")["lifeExp"].mean().round(1)
life.reset_index()
europe = df.query("continent == 'Europe' and year == 2007")
print(europe.index[:5].tolist())
Common mistake: asking for row 0 after a filter
europe.loc[0]
europe = europe.reset_index(drop=True)
europe.loc[0, ["country", "lifeExp"]]
Your turn#
Take the 2007 African rows, number them from 0, and show the last 3.
africa = df.query("continent == 'Africa' and year == 2007")
africa = africa.reset_index(drop=True)
africa[["country", "lifeExp"]].tail(3)
Put rows in label order with sort_index#
Problem: rows end up shuffled after sorting by values.
richest = by_country.sort_values("gdpPercap", ascending=False)
richest[["gdpPercap"]].head().round(0)
alpha = richest.sort_index()
alpha[["gdpPercap"]].head(3).round(0)
print(alpha.index.is_monotonic_increasing)
print(richest.index.is_monotonic_increasing)
by_country.sort_index(axis=1).head(2)
Quick check: which country comes last alphabetically?
by_country.sort_index(ascending=False).index[0]
Your turn#
Index the 2007 Asian rows by country, sort the labels, and show the last 3.
asia = df.query("continent == 'Asia' and year == 2007")
asia = asia.set_index("country").sort_index()
asia[["lifeExp"]].tail(3)
years = df.set_index("year")
print(years.index[:3].tolist())
Common mistake: slicing a range of unsorted labels
years.loc[1952:1957]
years = years.sort_index()
print(years.loc[1952:1957].shape)
Build a two-level index of country and year#
Problem: one country has 12 years, so country alone isn't unique.
panel = df.set_index(["country", "year"]).sort_index()
print(panel.index.is_unique)
panel.head(3)
panel.loc["Kenya"].head(3)
Quick check: how many rows does panel.loc["Kenya"] return?
print(len(panel.loc["Kenya"]))
print(panel.loc[("Kenya", 2007), "lifeExp"])
ice_ire = panel.loc["Iceland":"Ireland", "lifeExp"]
ice_ire.unstack()[[1952, 2007]].round(1)
by_year = panel.sort_index(level="year")
by_year[["lifeExp"]].head(3)
newest = panel.sort_index(ascending=False)
newest[["lifeExp"]].head(3)
back = panel.reset_index(level="year")
back.head(3)
Common mistake: slicing an unsorted two-level index
messy = df.set_index(["continent", "country"])
messy.loc[("Asia", "China"):("Asia", "Japan")]
messy = messy.sort_index()
print(messy.loc[("Asia", "China"):("Asia", "Japan")].shape)
Your turn#
With panel, show India's population in 1952 and in 2007.
india = panel.loc["India", "pop"]
india.loc[[1952, 2007]].astype("int64")
Let the index line up two tables#
Problem: subtract 1952 from 2007, country by country.
y52 = df.query("year == 1952").set_index("country")["lifeExp"]
y07 = df.query("year == 2007").set_index("country")["lifeExp"]
print(y52.loc["Kenya"], y07.loc["Kenya"])
print(y52.index.equals(y07.index))
shuffled = y07.sort_values()
gain = shuffled - y52
print(round(gain.loc["Kenya"], 3))
Quick check: what if the labels don't match?
a = pd.Series([1, 2], index=["UK", "Peru"])
b = pd.Series([10, 20], index=["Peru", "Chad"])
a + b
Your turn#
Align 1952 and 2007 population. How many times bigger did Nigeria get?
p52 = df.query("year == 1952").set_index("country")["pop"]
p07 = df.query("year == 2007").set_index("country")["pop"]
growth = p07 / p52
print(round(growth.loc["Nigeria"], 1))
Mini-project: who gained the most years of life?#
Plan: align the two years, sort the gains, check by continent.
gain = gain.sort_values(ascending=False)
print(gain.head().round(1))
print(gain.tail().round(1))
print((gain > 30).sum())
print(round(gain.mean(), 1), round(gain.median(), 1))
cont = recent.set_index("country")["continent"]
table = pd.DataFrame({"gain": gain, "continent": cont})
table.groupby("continent")["gain"].median().round(1)
table.loc[["Oman", "Norway", "Zimbabwe"]].round(1)
eu = table.query("continent == 'Europe'")
eu.sort_values("gain").head(3).round(1)
import matplotlib.pyplot as plt
ends = pd.concat([gain.head(5), gain.tail(5)]).sort_values()
ax = ends.plot(kind="barh", figsize=(7, 4))
ax.set_title("Change in life expectancy, 1952 to 2007")
ax.set_xlabel("Years gained")
ax.set_ylabel("Country")
plt.show()
print((gain < 0).sum())
print(gain[gain < 0].round(1))
Recap#
- set_index turns a column into row labels for loc lookups
- reset_index moves labels back; drop=True renumbers from 0
- Filters keep old row numbers, so reset before using loc[0]
- sort_index orders by labels; sort before slicing a range
- Maths between Series lines up by index labels
Challenges#
- Index 2007 by country and show the 5 most populous countries.
- With panel, show Japan's lifeExp for 1952 to 1972 only.
- Align 1952 and 2007 gdpPercap to find the fastest growth.
What's next#
Lesson 12: Renaming Columns and Indexes Clean up messy column names and relabel rows.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.
Related lessons
26 minData Mining with Python: pandas & scikit-learn Crash Course
21 minIntroduction to Pandas for Market Research: Master Data Analysis with Python
14 minMaster Data Cleaning and Preparation with Pandas in Python for Effective Bible Study Analysis
21 min