Skip to main content

Joining tables on a key: merge

Attach each reading’s station details by matching rows on the key station: first by hand with a dict and one pass, then with pd.merge. Predict the rows of inner, left, right and outer joins, read the _merge indicator, and catch the repeated key that multiplies rows.
P02.5.4L1 Core25 min
In review

The maths and code on this page are checked by automated tests on every change, but an independent reader has not signed it off yet. Spot a mistake or a confusing step? Tell us.

Before this lesson: P02.5.3 Wide to long and back: melt and pivot

What you will be able to do
  • Join two tables by hand with a dict from key to row and one pass over the left table.
  • Use pd.merge(left, right, on=…, how=…) and predict the number of rows of an inner, left, right and outer join.
  • Read the _merge column that indicator=True adds to tell where each row came from.
  • Explain why a key repeated on the right multiplies rows, and catch it with validate=‘many_to_one’.

The question

The readings say which station each temperature came from; a second table says how high each station is and in which zone. To ask “are the upland stations colder?”, every reading needs its station’s details on the same row. How do you match rows across two tables, and what happens to a reading from a station the second table has never heard of?

From the last lesson

1. State the three rules of tidy data.

Show answer

Each variable forms a column, each observation forms a row, and each type of observational unit forms a table.

2. In what order does pivot put the day labels, and how do you restore the week?

Show answer

Sorted as text, mon, thu, tue, wed; .reindex(["mon", "tue", "wed", "thu"]) restores it.

Intuition

The third rule of tidy data says that each kind of thing gets its own table. Readings are one kind (one row per day and station); stations are another (one row per station). The look-up table stations has four rows: coast at 5 m in the lowland, hill at 450 m in the upland, valley at 120 m in the lowland, and lake at 300 m in the upland. Lake has no readings yet. Like every number in the course, these are invented for teaching.

A join puts the two together. The column they share, station, is the key: a reading and a station belong together when their keys are equal.

Top left, the table monday with columns day, station and temp: mon coast 21, mon hill 19, mon valley 26 and mon ridge 17, with ridge shaded reddish purple. Top right, the table stations with columns station, elevation_m and zone: coast 5 lowland, hill 450 upland, valley 120 lowland and lake 300 upland, with lake shaded reddish purple. The station columns are orange. Green lines labelled equal keys join coast to coast, hill to hill and valley to valley; between ridge and lake the words no partner for either. Below, the code pd.merge(monday, stations, on='station', how='left') and its result of 4 rows: coast 21 5.0 lowland, hill 19 450.0 upland, valley 26 120.0 lowland, and ridge 17 with NaN, NaN in reddish purple. A note says ridge keeps its row and lake does not appear.
P02.5.4-F1

A join matches rows by key, not by position: ridge and lake are both in row 3, and they do not match. In a left join every reading is kept, and a reading with no partner gets NaN.

Monday’s three readings plus one from ridge, a station that was never registered, are the left table. Three keys have a partner on each side. Ridge has none on the right, and lake has none on the left. What to do with those two is the whole difference between the four kinds of join.

This is how a training table is assembled in practice. The features of one example are spread over several tables, a reading here, the station’s elevation there, and joins bring them onto one row. It is the same operation as JOIN in SQL (P03.3).

Build it up

Step 1. A look-up. Turn the right table into a dict from key to row (P01.6.3): coast maps to (5, 'lowland'), and so on. Building it is one pass over the right table.

Step 2. One pass over the left. For each reading, look its key up. lookup.get(station) gives the matching row, or None.

Step 3. Decide what to keep. If there is a match, append the reading joined to it. If not, an inner join appends nothing, and a left join appends the reading with NaN in the columns that would have come from the right.

Three parts. First, build the look-up: lookup equals a dict from station to row, drawn as a two-column table with keys coast, hill, valley and lake and rows (5, 'lowland'), (450, 'upland'), (120, 'lowland') and (300, 'upland'). A green arrow leads to the second part, one pass over the left: match equals lookup.get(station) for coast, hill, valley and ridge gives the three rows and, for ridge, None in reddish purple. Third, what to append for each row of the left: a table whose rows are a match and None and whose columns are how='inner' and how='left'. A match appends the left row plus the match in both; None appends nothing for inner and the left row plus NaN for left, both in reddish purple. The bottom line reads inner: 3 rows, left: 4 rows (ridge with NaN).
P02.5.4-F2

A join by hand. One pass builds the look-up, a second pass looks every reading up, and the kind of join decides what to do with a miss.

Step 4. The other side. A right join is the same with the tables’ roles swapped: every station is kept, and lake gets NaN for the reading. An outer join keeps every key of either table.

Step 5. A key that repeats. Many readings share one key, which is fine: four readings from coast all find the one coast row. If the right table lists a key twice, the look-up must hold a list of rows, and every reading with that key is joined to each of them.

The formal statement

NoteMerge

pd.merge(left, right, on="station", how=...) pairs every row of left with every row of right that has an equal key. The argument how decides which keys are used:

  • "inner": keys in both tables (SQL inner join); rows in the order of the left keys.
  • "left": keys of the left table (left outer join); the left’s order is kept.
  • "right": keys of the right table (right outer join); the right’s order is kept.
  • "outer": keys of either table (full outer join); the keys are sorted.

A row whose key has no partner gets NaN in the other table’s columns. indicator=True adds a column _merge that reads both, left_only or right_only. validate="many_to_one" checks that the keys are unique in the right table and raises MergeError if they are not.

A grid of key chips. Across the top, the five keys coast, hill, valley, ridge and lake, marked both, both, both, left only and right only. One row per join type. inner keeps coast, hill and valley: 3 rows, keys in both. left keeps coast, hill, valley and ridge: 4 rows, keys of the left. right keeps coast, hill, valley and lake: 4 rows, keys of the right. outer keeps all five: 5 rows, keys of either. Kept chips are filled vermillion and dropped ones are dashed grey. A note says that with a key repeated on either side, each match makes a row of its own.
P02.5.4-F5

Which keys each join keeps, for Monday’s readings plus ridge against the four stations. When no key repeats, each kept key makes one row.

Counting the rows. For a key \(k\), let \(a_k\) be the number of left rows and \(b_k\) the number of right rows with that key. Every left row with key \(k\) is paired with every right row with key \(k\), so an inner join makes \(a_k b_k\) rows for that key, and

\[ n_{\text{inner}} = \sum_k a_k\, b_k . \]

The other joins keep the rows that have no partner. A key that is missing on one side (\(b_k = 0\), say) still gives its \(a_k\) rows, once each. Writing \(\max(b_k, 1)\) for “the partners, or one row of NaN if there are none”, the outer join, which keeps every key of either table, makes

\[ n_{\text{outer}} = \sum_k \max(a_k, 1)\,\max(b_k, 1), \]

summed over the keys of both tables. A left join sums only over the keys of the left table, and a right join only over those of the right.

Worked example

Join Monday’s four readings (coast, hill, valley, ridge) to the four stations (coast, hill, valley, lake) all four ways, and count the rows.

Every key appears once on each side where it appears at all: \(a_k\) and \(b_k\) are 1 or 0.

  • Inner: coast, hill and valley have \(a_k b_k = 1\); ridge and lake have \(1 \cdot 0 = 0\). Three rows.
  • Left: the left keys coast, hill, valley and ridge each give one row; ridge’s has NaN for elevation and zone. Four rows.
  • Right: coast, hill, valley and lake; lake’s row has NaN for day and temp. Four rows.
  • Outer: all five keys, one row each, in sorted order: coast, hill, lake, ridge, valley. Five rows.
Four small tables in a two by two grid, each with the columns station, temp, elev (for elevation_m) and _merge. how='inner', 3 rows: coast 21 5, hill 19 450, valley 26 120, each both. how='left', 4 rows: the same with elev as 5.0, 450.0 and 120.0, plus ridge 17 NaN left_only. how='right', 4 rows: temp as 21.0, 19.0 and 26.0 with elev 5, 450 and 120, plus lake NaN 300 right_only. how='outer', 5 rows: coast, hill, lake with NaN temp and right_only, ridge with NaN elev and left_only, and valley. A note says outer sorts the keys: coast, hill, lake, ridge, valley.
P02.5.4-F3

The four joins with indicator=True. A column that gains a NaN turns from int64 to float64, which is why 21 becomes 21.0 and 5 becomes 5.0 in some of them.

Now the repeated key. Suppose stations lists coast twice. All 12 readings against it: coast has \(a = 4\) readings and \(b = 2\) station rows, hill and valley have \(a = 4\), \(b = 1\):

\[ \begin{aligned} n_{\text{left}} &= 4 \cdot 2 + 4 \cdot 1 + 4 \cdot 1 \\ &= 16 . \end{aligned} \]

Twelve readings went in and sixteen rows came out. Each coast reading now appears twice.

Code

The two tables of the worked example.

import numpy as np
import pandas as pd

monday = pd.DataFrame({"day": ["mon"] * 4,
                       "station": ["coast", "hill", "valley", "ridge"],
                       "temp": [21, 19, 26, 17]})
stations = pd.DataFrame({"station": ["coast", "hill", "valley", "lake"],
                         "elevation_m": [5, 450, 120, 300],
                         "zone": ["lowland", "upland", "lowland", "upland"]})

A join by hand: a dict from key to a list of rows (a list, so that a key may repeat), then one pass over the left table.

def join_by_hand(left, right, key, how="inner"):
    """Inner or left join of two lists of dicts on one key."""
    lookup = {}
    for row in right:
        lookup.setdefault(row[key], []).append(row)
    others = [c for c in right[0] if c != key]
    out = []
    for row in left:
        matches = lookup.get(row[key], [])
        for match in matches:
            out.append({**row, **{c: match[c] for c in others}})
        if not matches and how == "left":
            out.append({**row, **{c: np.nan for c in others}})
    return out

left_rows = monday.to_dict("records")
right_rows = stations.to_dict("records")
for how in ["inner", "left"]:
    rows = join_by_hand(left_rows, right_rows, "station", how)
    print(how, len(rows), [r["station"] for r in rows])
inner 3 ['coast', 'hill', 'valley']
left 4 ['coast', 'hill', 'valley', 'ridge']

The same with pd.merge, all four ways, with the indicator.

for how in ["inner", "left", "right", "outer"]:
    joined = pd.merge(monday, stations, on="station", how=how, indicator=True)
    print(how, len(joined), joined["station"].tolist(), joined["_merge"].tolist())

hand = pd.DataFrame(join_by_hand(left_rows, right_rows, "station", "left"))
merged = pd.merge(monday, stations, on="station", how="left")
print(merged)
inner 3 ['coast', 'hill', 'valley'] ['both', 'both', 'both']
left 4 ['coast', 'hill', 'valley', 'ridge'] ['both', 'both', 'both', 'left_only']
right 4 ['coast', 'hill', 'valley', 'lake'] ['both', 'both', 'both', 'right_only']
outer 5 ['coast', 'hill', 'lake', 'ridge', 'valley'] ['both', 'both', 'right_only', 'left_only', 'both']
   day station  temp  elevation_m     zone
0  mon   coast    21          5.0  lowland
1  mon    hill    19        450.0   upland
2  mon  valley    26        120.0  lowland
3  mon   ridge    17          NaN      NaN

All twelve readings, joined to their stations. The joined table answers a question neither table could answer alone: the mean temperature in each zone, with a mask (P02.4.3).

temps = np.array([[21, 19, 26], [27, 23, 28], [27, 18, 30], [29, 20, 32]])
readings = pd.DataFrame({"day": np.repeat(["mon", "tue", "wed", "thu"], 3),
                         "station": ["coast", "hill", "valley"] * 4,
                         "temp": temps.ravel()})
full = pd.merge(readings, stations, on="station", how="left", validate="many_to_one")
print(full.shape)
print(full[full["zone"] == "lowland"]["temp"].mean(), full[full["zone"] == "upland"]["temp"].mean())
(12, 5)
27.5 20.0

A key listed twice: the rows multiply, and validate catches it before it reaches a model.

doubled = pd.concat([stations, stations.iloc[[0]]], ignore_index=True)
print(len(doubled), len(pd.merge(readings, doubled, on="station", how="left")))
try:
    pd.merge(readings, doubled, on="station", how="left", validate="many_to_one")
except pd.errors.MergeError:
    print("MergeError: the keys of the right table are not unique")
fixed = pd.merge(readings, doubled.drop_duplicates(), on="station", how="left", validate="many_to_one")
print(len(fixed))
5 16
MergeError: the keys of the right table are not unique
12

Explore

P02.5-X1

Join matcher

Choose a join, then press Step to watch the result grow one match at a time. Change a key on the right to coast twice and step through a left join: count how many times each coast reading appears, and compare the row count with the formula.

Two tables side by side, Monday's readings with the keys coast, hill, valley and ridge, and the stations with the keys coast, hill, valley and lake. Green lines join the equal keys; ridge and lake have no partner. Below, the left join of 4 rows, with NaN for ridge's elevation and zone.

Pitfalls

Two panels. Left, marked with a cross and titled coast listed twice: the stations table with five rows, coast appearing in rows 0 and 4, both shaded reddish purple. Below, Monday's three readings merged give 4 rows: mon coast 21 5 twice, then hill and valley; a note says the coast reading now appears twice. Bottom: 4 rows from 3 readings; all 12 readings give 16 rows. Right, marked with a tick and titled validate, then deduplicate: the stations table with four unique rows and the note that validate='many_to_one' stops a doubled key with MergeError. The merge gives 3 rows from 3 readings; all 12 readings give 12 rows.
P02.5.4-F4

Wrong beside right. A key listed twice on the right turns 12 readings into 16 rows without a warning. validate="many_to_one" stops the merge instead; removing the repeat first gives 12.

“A left join keeps the left table’s rows, so it has as many rows as the left table.”

Only when the keys of the right table are unique. Each left row appears once per partner, so a station listed twice doubles its readings: 12 became 16. Pass validate="many_to_one" whenever each left row should find at most one partner.

“merge lines rows up like NumPy adds arrays.”

It matches keys, not positions. Ridge and lake both sit in row 3 of their tables and do not match; coast would match coast wherever it sat. The order of the rows in either table does not change which rows are paired.

“The NaN after a left join is just a gap.”

It is a finding: a reading from a station that is not registered. Count it (_merge == "left_only") and find out why before you drop or fill it. Keys that differ only in spelling, such as "coast " with a space, also fail to match; cleaning text keys is P02.7.3.

Check yourself

Recall

1. Which keys does each join use: inner, left, right, outer?

Show answer

Inner: keys in both tables. Left: the left table’s keys. Right: the right table’s keys. Outer: the keys of either table.

2. How many rows do the four joins of Monday’s readings (with ridge) and the four stations give?

Show answer

3 (inner), 4 (left), 4 (right) and 5 (outer).

3. What are the three values of the _merge column?

Show answer

both, left_only and right_only.

4. A key appears \(a\) times on the left and \(b\) times on the right. How many rows does it give in an inner join?

Show answer

\(a \cdot b\): every left row with the key is paired with every right row with it.

5. What does validate="many_to_one" check?

Show answer

That the keys of the right table are unique. If one repeats, merge raises MergeError.

Exercises

1. ★ Join Tuesday’s three readings (coast 27, hill 23, valley 28) to stations with each of the four joins. How many rows does each give, and which row has a NaN?

Show solution

Every reading’s key is in stations, so inner and left both give 3 rows with no NaN. Right and outer give 4: the extra row is lake, with NaN for day and temp, marked right_only.

2. ★★ With coast listed twice in stations, count the rows of all four joins of the 12 readings, using \(a_k\) and \(b_k\).

Show solution

For coast \(a = 4\), \(b = 2\); for hill and valley \(a = 4\), \(b = 1\); for lake \(a = 0\), \(b = 1\). Inner: \(8 + 4 + 4 = 16\). Left (the left keys coast, hill, valley): 16 as well. Right adds lake once: \(16 + 1 = 17\). Outer, every key of either table: also 17.

3. ★★★ Show that if the keys of the right table are unique, a left join has exactly as many rows as the left table. Then say what validate="one_to_one" does with readings and stations, and why "many_to_one" is the right check here.

Show solution

Unique keys on the right mean \(b_k \le 1\) for every key, so \(\max(b_k, 1) = 1\). The left join’s count is

\[ \begin{aligned} \sum_{k \in \text{left}} a_k \max(b_k, 1) &= \sum_{k \in \text{left}} a_k \\ &= n_{\text{left}} . \end{aligned} \]

"one_to_one" also demands unique keys on the left, and readings has four rows for each station, so it raises MergeError. Many readings per station but one row per station is exactly the many-to-one shape, which "many_to_one" checks.

Summary card

A join pairs every row with every row of equal key; how decides what happens to the rest.

The anchor figure again: Monday's readings and the stations joined by lines between equal keys, ridge and lake unmatched, and the left join of 4 rows with NaN for ridge.
P02.5.4-F1

Match by key, not by position.

  • pd.merge(left, right, on="station", how="inner" | "left" | "right" | "outer").
  • Rows for key \(k\): \(a_k b_k\) when matched; unmatched rows kept once with NaN.
  • indicator=True adds _merge: both, left_only, right_only.
  • validate="many_to_one" catches a repeated key on the right before it multiplies rows.

Sources and next steps

Sources. Each was opened and checked against this lesson on 15 November 2026.

  • The pandas development team, Merge, join, concatenate and compare, pandas 3.1.1 User Guide, section merge: the table of how values with their SQL names (left outer, right outer, full outer and inner join), the _merge values left_only, right_only and both of indicator, the validate argument, and the warning that merging on duplicate keys can greatly increase the size of the result.
  • The pandas development team, pandas.merge, pandas 3.1.1 API reference: inner keeps the order of the left keys, left and right keep their table’s key order, outer sorts the keys lexicographically, and "many_to_one" checks that the merge keys are unique in the right dataset.

Next. This closes the module. Course P02 continues with P02.6, group-by and aggregation, which splits the long, joined table into groups and summarises each one; SQL’s version of today’s join is P03.3.

This lesson unlocks

P02.6 Group-by and aggregation P03.3 Joins (planned)

Your progress is saved in this browser only. No account, no tracking.
Back to top