Lesson 33 · Pandas Full Course
Pandas merge Explained: Inner, Left, Right and Outer Joins (SQL-Style) | Pandas Course #33
In this lesson you'll be able to: Combine two tables on a shared key column with merge Choose inner, left, right or outer joins on purpose Find and fix keys…
- CoursePandas Full Course
- Lesson33 of 65
- FormatJupyter notebook · 40 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.
- gapminder.csv preview80.2 KB
- netflix_titles.csv preview2.9 MB
📓 Full notebook
Download .ipynbPandas 33: Merging and Joining DataFrames#
In this lesson you'll be able to:
- Combine two tables on a shared key column with merge
- Choose inner, left, right or outer joins on purpose
- Find and fix keys that fail to match
- Guard a merge with validate and tidy clashing columns
- Join on the index with join() Data: Gapminder country statistics and the Netflix catalogue
Warm-up: stacking tables with concat#
Lesson 32 stacked tables with pd.concat. Here it is once more.
import pandas as pd
gap = pd.read_csv("data/pandas/gapminder.csv")
early = gap[gap["year"] == 1952]
late = gap[gap["year"] == 2007]
both = pd.concat([early, late])
print(early.shape, late.shape, both.shape)
both.groupby("year")["lifeExp"].mean().round(1)
Combine two tables that share a key#
Problem: the facts you need live in two different tables.
countries = late[["country", "continent", "pop"]]
print(countries.shape)
countries.head(3)
net = pd.read_csv("data/pandas/netflix_titles.csv")
first = net["country"].str.split(",").str[0]
titles = first.value_counts().reset_index(name="titles")
print(titles.shape)
titles.head(3)
merged = pd.merge(countries, titles, on="country")
print(merged.shape)
merged.head(3)
Quick check: 142 countries meet 81 Netflix countries, yet only 71 rows come back. Why?
in_both = countries["country"].isin(titles["country"])
print(in_both.sum())
Common mistake: a key name with the wrong capital letter
pd.merge(countries, titles, on="Country")
print(countries.columns.tolist())
print(titles.columns.tolist())
Choose which rows survive: inner, left, right, outer#
Problem: decide what happens to rows without a partner.
- inner: only keys found in both tables (the default)
- left or right: every row of that table, gaps become NaN
- outer: every key from either table
for how in ["inner", "left", "right"]:
out = pd.merge(countries, titles, on="country", how=how)
print(how, out.shape)
left = pd.merge(countries, titles, on="country", how="left")
left.head()
print(left["titles"].isna().sum())
left["titles"] = left["titles"].fillna(0).astype(int)
left.sort_values("titles").head(3)
right = pd.merge(countries, titles, on="country", how="right")
right[right["pop"].isna()]
flip = pd.merge(titles, countries, on="country", how="left")
print(flip.shape, right.shape)
Quick check: 71 matched, 71 Gapminder-only, 10 Netflix-only. How many rows will an outer join give?
outer = pd.merge(countries, titles, on="country", how="outer",
indicator=True)
print(outer.shape)
outer["_merge"].value_counts()
Your turn#
List the Netflix countries with no match, most titles first.
lost = outer[outer["_merge"] == "right_only"]
lost = lost.sort_values("titles", ascending=False)
lost[["country", "titles"]].head()
Find and fix keys that do not match#
Problem: the same country, spelled two different ways.
pattern = "Korea|Hong Kong|Russia|Emirates"
mask = countries["country"].str.contains(pattern)
countries.loc[mask, "country"]
fixes = {"South Korea": "Korea, Rep.",
"Hong Kong": "Hong Kong, China"}
titles["country"] = titles["country"].replace(fixes)
fixed = pd.merge(countries, titles, on="country")
print(fixed.shape)
fixed[fixed["country"].str.contains("Korea|Hong")]
Quick check: why not rename Russia and the Emirates too?
print("Russia" in set(gap["country"]))
print(gap["country"].str.contains("Emirates").any())
Look up details for every row: many-to-one#
Problem: tag each of 7,787 titles with its continent.
net["first_country"] = first.replace(fixes)
per_title = pd.merge(net, countries, how="left",
left_on="first_country", right_on="country")
print(per_title.shape)
[c for c in per_title.columns if "country" in c]
Quick check: where did country_x and country_y come from?
per_title = pd.merge(net, countries, how="left",
left_on="first_country", right_on="country",
suffixes=("_netflix", "_world"))
per_title.filter(like="country").head(3)
per_title["continent"].value_counts(dropna=False)
Your turn#
For each continent, what share of its titles are TV shows?
kinds = per_title.groupby("continent")["type"]
kinds.value_counts(normalize=True).unstack().round(2)
Common mistake: a lookup table with repeated keys
lookup = gap[["country", "continent"]]
pd.merge(net, lookup, left_on="first_country",
right_on="country", validate="many_to_one")
print(lookup["country"].value_counts().head(3))
lookup = lookup.drop_duplicates()
ok = pd.merge(net, lookup, how="left", left_on="first_country",
right_on="country", validate="many_to_one")
print(ok.shape)
Merge on two keys#
Problem: a country alone is not enough when each one has many years.
life = gap[["country", "year", "lifeExp"]]
recent = gap["year"] >= 1997
wealth = gap.loc[recent, ["country", "year", "gdpPercap"]]
print(life.shape, wealth.shape)
one = pd.merge(life, wealth, on="country")
print(one.shape)
one.head(3)
Quick check: 1,704 rows went in. Why did 5,112 come out?
two = pd.merge(life, wealth, on=["country", "year"])
print(two.shape)
two[two["country"] == "Japan"]
Your turn#
Merge the 1952 and 2007 rows on country, with clear suffixes. Who gained most life expectancy?
cols = ["country", "lifeExp"]
gain = pd.merge(early[cols], late[cols], on="country",
suffixes=("_1952", "_2007"))
gain["gain"] = gain["lifeExp_2007"] - gain["lifeExp_1952"]
gain.nlargest(5, "gain").round(1)
Join on the index with join()#
Problem: both tables already use the key as their index.
c_idx = countries.set_index("country")
t_idx = titles.set_index("country")
joined = c_idx.join(t_idx)
print(joined.shape)
joined.head(3)
inner = c_idx.join(t_idx, how="inner")
inner.sort_values("titles", ascending=False).head(3)
Common mistake: joining tables that share a column name
stats = late.set_index("country")
c_idx.join(stats)
extra = stats[["lifeExp", "gdpPercap"]]
c_idx.join(extra).head(3).round(1)
c_idx.join([t_idx, extra]).head(3).round(1)
Mini-project: Netflix titles per million people#
Plan: merge, fill gaps, scale by population, compare.
world = pd.merge(countries, titles, on="country", how="left",
validate="one_to_one")
world["titles"] = world["titles"].fillna(0).astype(int)
world["pop_m"] = world["pop"] / 1e6
world["per_m"] = world["titles"] / world["pop_m"]
print(world.shape)
big = world[world["titles"] >= 20]
top = big.nlargest(5, "per_m")
top[["country", "titles", "pop_m", "per_m"]].round(2)
cont = world.groupby("continent")[["titles", "pop_m"]].sum()
cont["per_m"] = cont["titles"] / cont["pop_m"]
cont.sort_values("per_m", ascending=False).round(2)
blank = per_title["first_country"].isna()
no_match = per_title["continent"].isna() & ~blank
print(blank.sum(), no_match.sum())
import matplotlib.pyplot as plt
chart = big.nlargest(10, "per_m").set_index("country")["per_m"]
ax = chart.sort_values().plot(kind="barh", figsize=(7, 4))
ax.set_title("Netflix titles per million people (2007 pop.)")
ax.set_xlabel("Titles per million people")
ax.set_ylabel("Country")
plt.show()
best = top.iloc[0]
us = world.loc[world["country"] == "United States", "per_m"]
print(f"{best['country']}: {best['per_m']:.1f} per million")
print(f"United States: {us.iloc[0]:.1f} per million")
Recap#
- pd.merge lines up rows that share a key column
- how= picks inner, left, right or outer; indicator=True shows the source
- Unmatched keys are often spelling: check, map, merge again
- validate= catches duplicate keys; suffixes= names clashing columns
- Use a list of keys when one column is not unique; join() for indexes
Challenges#
- Find the median release year of Netflix titles per continent.
- Merge 1952 and 2007 GDP per head. Which country grew fastest?
- Find two more Netflix countries you could rename to match.
What's next#
Lesson 34: Aligning Data with Different Indexes What pandas does when labels don't line up, and how to control it.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



