Mathew K Analytics

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…

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.

📓 Full notebook

Download .ipynb

Pandas 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)
(142, 6) (142, 6) (284, 6)
both.groupby("year")["lifeExp"].mean().round(1)
year
1952    49.1
2007    67.0
Name: lifeExp, dtype: float64

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)
(142, 3)
country continent pop
11 Afghanistan Asia 31889923.0
23 Albania Europe 3600523.0
35 Algeria Africa 33333216.0
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)
(81, 2)
country titles
0 United States 2883
1 India 956
2 United Kingdom 577
merged = pd.merge(countries, titles, on="country")
print(merged.shape)
merged.head(3)
(71, 4)
country continent pop titles
0 Argentina Americas 40301927.0 68
1 Australia Oceania 20434176.0 108
2 Austria Europe 8199783.0 8

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())
71

Common mistake: a key name with the wrong capital letter

pd.merge(countries, titles, on="Country")
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
~\AppData\Local\Temp\ipykernel_24096\3841883931.py in ?()
----> 1 pd.merge(countries, titles, on="Country")

~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\merge.py in ?(left, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)
    381             indicator=indicator,
    382             validate=validate,
    383         )
    384     else:
--> 385         op = _MergeOperation(
    386             left_df,
    387             right_df,
    388             how=how,

~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\merge.py in ?(self, left, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, indicator, validate)
   1014             self.right_join_keys,
   1015             self.join_names,
   1016             left_drop,
   1017             right_drop,
-> 1018         ) = self._get_merge_keys()
   1019 
   1020         if left_drop:
   1021             self.left = self.left._drop_labels_or_levels(left_drop)

~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\merge.py in ?(self)
   1616                         # Then we're either Hashable or a wrong-length arraylike,
   1617                         #  the latter of which will raise
   1618                         rk = cast(Hashable, rk)
   1619                         if rk is not None:
-> 1620                             right_keys.append(right._get_label_or_level_values(rk))
   1621                         else:
   1622                             # work-around for merge_asof(right_index=True)
   1623                             right_keys.append(right.index._values)

~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\generic.py in ?(self, key, axis)
   1760             values = self.xs(key, axis=first_other_axes)._values
   1761         elif self._is_level_reference(key, axis=axis):
   1762             values = self.axes[axis].get_level_values(key)._values
   1763         else:
-> 1764             raise KeyError(key)
   1765 
   1766         # Check for duplicates
   1767         if values.ndim > 1:

KeyError: 'Country'
print(countries.columns.tolist())
print(titles.columns.tolist())
['country', 'continent', 'pop']
['country', 'titles']

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)
inner (71, 4)
left (142, 4)
right (81, 4)
left = pd.merge(countries, titles, on="country", how="left")
left.head()
country continent pop titles
0 Afghanistan Asia 31889923.0 NaN
1 Albania Europe 3600523.0 NaN
2 Algeria Africa 33333216.0 NaN
3 Angola Africa 12420476.0 NaN
4 Argentina Americas 40301927.0 68.0
print(left["titles"].isna().sum())
left["titles"] = left["titles"].fillna(0).astype(int)
left.sort_values("titles").head(3)
71
country continent pop titles
0 Afghanistan Asia 31889923.0 0
1 Albania Europe 3600523.0 0
2 Algeria Africa 33333216.0 0
right = pd.merge(countries, titles, on="country", how="right")
right[right["pop"].isna()]
country continent pop titles
6 South Korea NaN NaN 194
16 Hong Kong NaN NaN 77
35 Russia NaN NaN 20
36 United Arab Emirates NaN NaN 19
61 Georgia NaN NaN 2
62 Ukraine NaN NaN 2
66 Soviet Union NaN NaN 1
69 West Germany NaN NaN 1
76 Belarus NaN NaN 1
77 Cyprus NaN NaN 1
flip = pd.merge(titles, countries, on="country", how="left")
print(flip.shape, right.shape)
(81, 4) (81, 4)

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()
(152, 5)
_merge
left_only     71
both          71
right_only    10
Name: count, dtype: int64

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()
country titles
123 South Korea 194.0
58 Hong Kong 77.0
111 Russia 20.0
141 United Arab Emirates 19.0
49 Georgia 2.0

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"]
671    Hong Kong, China
839    Korea, Dem. Rep.
851         Korea, Rep.
Name: country, dtype: str
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)
(73, 4)
fixed[fixed["country"].str.contains("Korea|Hong")]
country continent pop titles
22 Hong Kong, China Asia 6980412.0 77
35 Korea, Rep. Asia 49044790.0 194

Quick check: why not rename Russia and the Emirates too?

print("Russia" in set(gap["country"]))
print(gap["country"].str.contains("Emirates").any())
False
False

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]
(7787, 16)
['country_x', 'first_country', 'country_y']

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)
country_netflix first_country country_world
0 Brazil Brazil Brazil
1 Mexico Mexico Mexico
2 Singapore Singapore Singapore
per_title["continent"].value_counts(dropna=False)
continent
Americas    3490
Asia        1983
Europe      1414
NaN          554
Africa       223
Oceania      123
Name: count, dtype: int64

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)
type Movie TV Show
continent
Africa 0.88 0.12
Americas 0.72 0.28
Asia 0.71 0.29
Europe 0.66 0.34
Oceania 0.56 0.44

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")
---------------------------------------------------------------------------
MergeError                                Traceback (most recent call last)
Cell In[25], line 2
      1 lookup = gap[["country", "continent"]]
----> 2 pd.merge(net, lookup, left_on="first_country",
      3          right_on="country", validate="many_to_one")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\merge.py:385, in merge(left, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)
    371     return _cross_merge(
    372         left_df,
    373         right_df,
   (...)
    382         validate=validate,
    383     )
    384 else:
--> 385     op = _MergeOperation(
    386         left_df,
    387         right_df,
    388         how=how,
    389         on=on,
    390         left_on=left_on,
    391         right_on=right_on,
    392         left_index=left_index,
    393         right_index=right_index,
    394         sort=sort,
    395         suffixes=suffixes,
    396         indicator=indicator,
    397         validate=validate,
    398     )
    399     return op.get_result()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\merge.py:1037, in _MergeOperation.__init__(self, left, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, indicator, validate)
   1033 # If argument passed to validate,
   1034 # check if columns specified as unique
   1035 # are in fact unique.
   1036 if validate is not None:
-> 1037     self._validate_validate_kwd(validate)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\merge.py:2019, in _MergeOperation._validate_validate_kwd(self, validate)
   2017 elif validate in ["many_to_one", "m:1"]:
   2018     if not right_unique:
-> 2019         raise MergeError(
   2020             "Merge keys are not unique in right dataset; "
   2021             "not a many-to-one merge\n"
   2022             f"{right_error_msg(right_join_index)}"
   2023         )
   2025 elif validate in ["many_to_many", "m:m"]:
   2026     pass

MergeError: Merge keys are not unique in right dataset; not a many-to-one merge

Duplicates in right:
     country
Afghanistan
Afghanistan
Afghanistan
Afghanistan
Afghanistan ...
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)
country
Afghanistan    12
Albania        12
Algeria        12
Name: count, dtype: int64
(7787, 15)

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)
(1704, 3) (426, 3)
one = pd.merge(life, wealth, on="country")
print(one.shape)
one.head(3)
(5112, 5)
country year_x lifeExp year_y gdpPercap
0 Afghanistan 1952 28.801 1997 635.341351
1 Afghanistan 1952 28.801 2002 726.734055
2 Afghanistan 1952 28.801 2007 974.580338

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"]
(426, 4)
country year lifeExp gdpPercap
198 Japan 1997 80.690 28816.58499
199 Japan 2002 82.000 28604.59190
200 Japan 2007 82.603 31656.06806

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)
country lifeExp_1952 lifeExp_2007 gain
96 Oman 37.6 75.6 38.1
137 Vietnam 40.4 74.2 33.8
59 Indonesia 37.5 70.6 33.2
109 Saudi Arabia 39.9 72.8 32.9
75 Libya 42.7 74.0 31.2

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)
(142, 3)
continent pop titles
country
Afghanistan Asia 31889923.0 NaN
Albania Europe 3600523.0 NaN
Algeria Africa 33333216.0 NaN
inner = c_idx.join(t_idx, how="inner")
inner.sort_values("titles", ascending=False).head(3)
continent pop titles
country
United States Americas 3.011399e+08 2883
India Asia 1.110396e+09 956
United Kingdom Europe 6.077624e+07 577

Common mistake: joining tables that share a column name

stats = late.set_index("country")
c_idx.join(stats)
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[33], line 2
      1 stats = late.set_index("country")
----> 2 c_idx.join(stats)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py:12818, in DataFrame.join(self, other, on, how, lsuffix, rsuffix, sort, validate)
  12808     if how == "cross":
  12809         return merge(
  12810             self,
  12811             other,
   (...)
  12816             validate=validate,
  12817         )
> 12818     return merge(
  12819         self,
  12820         other,
  12821         left_on=on,
  12822         how=how,
  12823         left_index=on is None,
  12824         right_index=True,
  12825         suffixes=(lsuffix, rsuffix),
  12826         sort=sort,
  12827         validate=validate,
  12828     )
  12829 else:
  12830     if on is not None:

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\merge.py:399, in merge(left, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)
    384 else:
    385     op = _MergeOperation(
    386         left_df,
    387         right_df,
   (...)
    397         validate=validate,
    398     )
--> 399     return op.get_result()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\merge.py:1143, in _MergeOperation.get_result(self)
   1139     self.left, self.right = self._indicator_pre_merge(self.left, self.right)
   1141 join_index, left_indexer, right_indexer = self._get_join_info()
-> 1143 result = self._reindex_and_concat(join_index, left_indexer, right_indexer)
   1145 if self.indicator:
   1146     result = self._indicator_post_merge(result)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\merge.py:1094, in _MergeOperation._reindex_and_concat(self, join_index, left_indexer, right_indexer)
   1091 left = self.left[:]
   1092 right = self.right[:]
-> 1094 llabels, rlabels = _items_overlap_with_suffix(
   1095     self.left._info_axis, self.right._info_axis, self.suffixes
   1096 )
   1098 if left_indexer is not None and not is_range_indexer(left_indexer, len(left)):
   1099     # Pinning the index here (and in the right code just below) is not
   1100     #  necessary, but makes the `.take` more performant if we have e.g.
   1101     #  a MultiIndex for left.index.
   1102     lmgr = left._mgr.reindex_indexer(
   1103         join_index,
   1104         left_indexer,
   (...)
   1108         use_na_proxy=True,
   1109     )

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\merge.py:3092, in _items_overlap_with_suffix(left, right, suffixes)
   3089 lsuffix, rsuffix = suffixes
   3091 if not lsuffix and not rsuffix:
-> 3092     raise ValueError(f"columns overlap but no suffix specified: {to_rename}")
   3094 def renamer(x, suffix: str | None):
   3095     """
   3096     Rename the left and right indices.
   3097 
   (...)
   3108     x : renamed column name
   3109     """

ValueError: columns overlap but no suffix specified: Index(['continent', 'pop'], dtype='str')
extra = stats[["lifeExp", "gdpPercap"]]
c_idx.join(extra).head(3).round(1)
continent pop lifeExp gdpPercap
country
Afghanistan Asia 31889923.0 43.8 974.6
Albania Europe 3600523.0 76.4 5937.0
Algeria Africa 33333216.0 72.3 6223.4
c_idx.join([t_idx, extra]).head(3).round(1)
continent pop titles lifeExp gdpPercap
country
Afghanistan Asia 31889923.0 NaN 43.8 974.6
Albania Europe 3600523.0 NaN 76.4 5937.0
Algeria Africa 33333216.0 NaN 72.3 6223.4

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)
(142, 6)
big = world[world["titles"] >= 20]
top = big.nlargest(5, "per_m")
top[["country", "titles", "pop_m", "per_m"]].round(2)
country titles pop_m per_m
55 Hong Kong, China 77 6.98 11.03
134 United States 2883 301.14 9.57
133 United Kingdom 577 60.78 9.49
20 Canada 259 33.39 7.76
62 Ireland 26 4.11 6.33
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)
titles pop_m per_m
continent
Oceania 123 24.55 5.01
Americas 3490 898.87 3.88
Europe 1414 586.10 2.41
Asia 1983 3811.95 0.52
Africa 223 929.54 0.24
blank = per_title["first_country"].isna()
no_match = per_title["continent"].isna() & ~blank
print(blank.sum(), no_match.sum())
507 47
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()
No description has been provided for this image
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")
Hong Kong, China: 11.0 per million
United States: 9.6 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#

  1. Find the median release year of Netflix titles per continent.
  2. Merge 1952 and 2007 GDP per head. Which country grew fastest?
  3. 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.