How do I join two tables on a key?

Line two tables up on a shared column with pd.merge, and choose which rows survive with inner, left, right and outer.

pd.merge lines two tables up on a column they share. Where the key matches, the columns of the second table are attached to the row of the first. Where it does not match, you choose what happens.

In this lesson I attach sector labels to a price table, watch a missing ticker turn into NaN, and then run all four join types on one small pair of frames so you can see which rows each one keeps.

Step 1. Prices and sector labels

Two tables, each with a Ticker column. The prices know nothing about sectors and the sector table knows nothing about prices. The numbers are simulated.

import pandas as pd

prices = pd.DataFrame({
    "Ticker": ["AAPL", "MSFT", "NVDA", "AMZN"],
    "Close":  [185.40, 410.20, 130.75, 178.30],
})

sectors = pd.DataFrame({
    "Ticker": ["AAPL", "MSFT", "AMZN", "XOM"],
    "Sector": ["Technology", "Technology", "Consumer", "Energy"],
})

print(prices)
# ->   Ticker   Close
# -> 0   AAPL  185.40
# -> 1   MSFT  410.20
# -> 2   NVDA  130.75
# -> 3   AMZN  178.30

print(sectors)
# ->   Ticker      Sector
# -> 0   AAPL  Technology
# -> 1   MSFT  Technology
# -> 2   AMZN    Consumer
# -> 3    XOM      Energy

  Ticker   Close
0   AAPL  185.40
1   MSFT  410.20
2   NVDA  130.75
3   AMZN  178.30
  Ticker      Sector
0   AAPL  Technology
1   MSFT  Technology
2   AMZN    Consumer
3    XOM      Energy

Three tickers sit in both tables. NVDA is in the prices only. XOM is in the sectors only.

on="Ticker" names the key. how="inner" keeps a row only when the key is in both tables.

inner = pd.merge(prices, sectors, on="Ticker", how="inner")

print(inner)
# ->   Ticker  Close      Sector
# -> 0   AAPL  185.4  Technology
# -> 1   MSFT  410.2  Technology
# -> 2   AMZN  178.3    Consumer

print(inner.shape)      # -> (3, 3)

  Ticker  Close      Sector
0   AAPL  185.4  Technology
1   MSFT  410.2  Technology
2   AMZN  178.3    Consumer
(3, 3)

NVDA and XOM are gone. Four price rows went in and three came out.

how="left" keeps every row of the left table, matched or not.

left = pd.merge(prices, sectors, on="Ticker", how="left")

print(left)
# ->   Ticker   Close      Sector
# -> 0   AAPL  185.40  Technology
# -> 1   MSFT  410.20  Technology
# -> 2   NVDA  130.75         NaN
# -> 3   AMZN  178.30    Consumer

print(left.shape)       # -> (4, 3)

  Ticker   Close      Sector
0   AAPL  185.40  Technology
1   MSFT  410.20  Technology
2   NVDA  130.75         NaN
3   AMZN  178.30    Consumer
(4, 3)

NVDA kept its price and got NaN for Sector, because the sector table has no NVDA row to supply. XOM is still gone: a left join never adds rows that the left table did not already have.

.isna() finds the gap.

print(left["Sector"].isna())
# -> 0    False
# -> 1    False
# -> 2     True
# -> 3    False
# -> Name: Sector, dtype: bool

0    False
1    False
2     True
3    False
Name: Sector, dtype: bool

Step 2. The other two join types

how="right" keeps every row of the right table. XOM survives now, with NaN for Close.

right = pd.merge(prices, sectors, on="Ticker", how="right")

print(right)
# ->   Ticker  Close      Sector
# -> 0   AAPL  185.4  Technology
# -> 1   MSFT  410.2  Technology
# -> 2   AMZN  178.3    Consumer
# -> 3    XOM    NaN      Energy

print(right.shape)      # -> (4, 3)

  Ticker  Close      Sector
0   AAPL  185.4  Technology
1   MSFT  410.2  Technology
2   AMZN  178.3    Consumer
3    XOM    NaN      Energy
(4, 3)

how="outer" keeps every row of both tables, with NaN on whichever side the match is missing.

outer = pd.merge(prices, sectors, on="Ticker", how="outer")

print(outer)
# ->   Ticker   Close      Sector
# -> 0   AAPL  185.40  Technology
# -> 1   AMZN  178.30    Consumer
# -> 2   MSFT  410.20  Technology
# -> 3   NVDA  130.75         NaN
# -> 4    XOM     NaN      Energy

print(outer.shape)      # -> (5, 3)

  Ticker   Close      Sector
0   AAPL  185.40  Technology
1   AMZN  178.30    Consumer
2   MSFT  410.20  Technology
3   NVDA  130.75         NaN
4    XOM     NaN      Energy
(5, 3)

Inner gave 3 rows, left 4, right 4, outer 5. The default is how="inner". Leaving how out drops the unmatched tickers.

prices lists AMZN last and the outer result puts it second: an outer join sorts the key. The left join above kept the left table’s row order.

Step 3. A column that needs both tables

Market capitalisation is price times shares outstanding, and neither table holds both numbers. Merge first, multiply second. Shares outstanding are in billions, and simulated like the rest.

shares = pd.DataFrame({
    "Ticker":    ["AAPL", "MSFT", "AMZN", "XOM"],
    "SharesOut": [15.2, 7.4, 10.4, 4.0],
})

m = pd.merge(prices, shares, on="Ticker", how="left")
m["MktCap"] = m["Close"] * m["SharesOut"]

print(m)
# ->   Ticker   Close  SharesOut   MktCap
# -> 0   AAPL  185.40       15.2  2818.08
# -> 1   MSFT  410.20        7.4  3035.48
# -> 2   NVDA  130.75        NaN      NaN
# -> 3   AMZN  178.30       10.4  1854.32

  Ticker   Close  SharesOut   MktCap
0   AAPL  185.40       15.2  2818.08
1   MSFT  410.20        7.4  3035.48
2   NVDA  130.75        NaN      NaN
3   AMZN  178.30       10.4  1854.32

NVDA has a price and no share count, so its market cap is NaN. Arithmetic on a missing value gives a missing value.

.sum() skips it, and .count() says how many numbers it actually added.

print(round(m["MktCap"].sum(), 2))      # -> 7707.88
print(m["MktCap"].count())              # -> 3         <- non-missing values
print(len(m))                           # -> 4         <- rows in the table

7707.88
3
4

Three of four rows carried a number.

Your turn

a = pd.DataFrame({"Ticker": ["AAPL", "MSFT", "TSLA"], "Close": [185.40, 410.20, 240.10]})
b = pd.DataFrame({"Ticker": ["MSFT", "TSLA", "META"], "Beta":  [0.9, 1.8, 1.2]})

Merge a and b on Ticker with how="inner", then with how="outer". How many rows does each one give, and which ticker gets a NaN in Close?

Show answer

print(pd.merge(a, b, on="Ticker", how="inner"))
# ->   Ticker  Close  Beta
# -> 0   MSFT  410.2   0.9
# -> 1   TSLA  240.1   1.8

print(pd.merge(a, b, on="Ticker", how="outer"))
# ->   Ticker  Close  Beta
# -> 0   AAPL  185.4   NaN
# -> 1   META    NaN   1.2
# -> 2   MSFT  410.2   0.9
# -> 3   TSLA  240.1   1.8

print(pd.merge(a, b, on="Ticker", how="inner").shape)   # -> (2, 3)
print(pd.merge(a, b, on="Ticker", how="outer").shape)   # -> (4, 3)

Inner keeps 2 rows, outer keeps 4. META has no price, so its Close is NaN. AAPL has no beta.

Which side did each row come from?

indicator=True adds a _merge column naming the source of every row.

print(pd.merge(prices, shares, on="Ticker", how="outer", indicator=True))
# ->   Ticker   Close  SharesOut      _merge
# -> 0   AAPL  185.40       15.2        both
# -> 1   AMZN  178.30       10.4        both
# -> 2   MSFT  410.20        7.4        both
# -> 3   NVDA  130.75        NaN   left_only
# -> 4    XOM     NaN        4.0  right_only

  Ticker   Close  SharesOut      _merge
0   AAPL  185.40       15.2        both
1   AMZN  178.30       10.4        both
2   MSFT  410.20        7.4        both
3   NVDA  130.75        NaN   left_only
4    XOM     NaN        4.0  right_only

Count the left_only rows and you know how many tickers your reference table is missing.

When the key columns have different names

left_on and right_on take one name each. Both columns survive in the result.

shares2 = shares.rename(columns={"Ticker": "Symbol"})

print(pd.merge(prices, shares2, left_on="Ticker", right_on="Symbol", how="inner"))
# ->   Ticker  Close Symbol  SharesOut
# -> 0   AAPL  185.4   AAPL       15.2
# -> 1   MSFT  410.2   MSFT        7.4
# -> 2   AMZN  178.3   AMZN       10.4

  Ticker  Close Symbol  SharesOut
0   AAPL  185.4   AAPL       15.2
1   MSFT  410.2   MSFT        7.4
2   AMZN  178.3   AMZN       10.4

Drop the spare with .drop(columns="Symbol").

Repeated keys multiply the rows

A key that appears twice on the right matches once for each copy, so one left row becomes two.

dupe = pd.DataFrame({
    "Ticker":  ["AAPL", "AAPL"],
    "Listing": ["NASDAQ", "XETRA"],
})

print(pd.merge(prices, dupe, on="Ticker", how="left"))
# ->   Ticker   Close Listing
# -> 0   AAPL  185.40  NASDAQ
# -> 1   AAPL  185.40   XETRA
# -> 2   MSFT  410.20     NaN
# -> 3   NVDA  130.75     NaN
# -> 4   AMZN  178.30     NaN

  Ticker   Close Listing
0   AAPL  185.40  NASDAQ
1   AAPL  185.40   XETRA
2   MSFT  410.20     NaN
3   NVDA  130.75     NaN
4   AMZN  178.30     NaN

Four rows went in and five came out. Check len() before and after a merge, or pass validate="one_to_one" and let pandas raise when the key is not unique on both sides.