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({                          # the left table: one row per ticker
    "Ticker": ["AAA", "CCC", "DDD", "EEE"],  # the join key, shared with the sector table
    "Close":  [185.40, 410.20, 130.75, 178.30],  # simulated closing prices
})

sectors = pd.DataFrame({                         # the right table: a label per ticker
    "Ticker": ["AAA", "CCC", "EEE", "GGG"],   # same key, but no DDD and an extra GGG
    "Sector": ["Technology", "Technology", "Consumer", "Energy"],
})

print(prices)
# ->   Ticker   Close
# -> 0    AAA  185.40
# -> 1    CCC  410.20
# -> 2    DDD  130.75
# -> 3    EEE  178.30

print(sectors)
# ->   Ticker      Sector
# -> 0    AAA  Technology
# -> 1    CCC  Technology
# -> 2    EEE    Consumer
# -> 3    GGG      Energy

  Ticker   Close
0    AAA  185.40
1    CCC  410.20
2    DDD  130.75
3    EEE  178.30
  Ticker      Sector
0    AAA  Technology
1    CCC  Technology
2    EEE    Consumer
3    GGG      Energy

Three tickers sit in both tables. DDD is in the prices only. GGG 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")  # keeps only keys found in both tables

print(inner)                                                 # only the three tickers present in both
# ->   Ticker  Close      Sector
# -> 0    AAA  185.4  Technology
# -> 1    CCC  410.2  Technology
# -> 2    EEE  178.3    Consumer

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

  Ticker  Close      Sector
0    AAA  185.4  Technology
1    CCC  410.2  Technology
2    EEE  178.3    Consumer
(3, 3)

DDD and GGG 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")  # keeps all 4 price rows, matched or not

print(left)                                                # DDD stays, with no sector to fill in
# ->   Ticker   Close      Sector
# -> 0    AAA  185.40  Technology
# -> 1    CCC  410.20  Technology
# -> 2    DDD  130.75         NaN
# -> 3    EEE  178.30    Consumer

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

  Ticker   Close      Sector
0    AAA  185.40  Technology
1    CCC  410.20  Technology
2    DDD  130.75         NaN
3    EEE  178.30    Consumer
(4, 3)

DDD kept its price and got NaN for Sector, because the sector table has no DDD row to supply. GGG 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())  # True on the row the sector table missed
# -> 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. GGG survives now, with NaN for Close.

right = pd.merge(prices, sectors, on="Ticker", how="right")  # keeps all 4 sector rows instead

print(right)                                                 # GGG stays, with no price to fill in
# ->   Ticker  Close      Sector
# -> 0    AAA  185.4  Technology
# -> 1    CCC  410.2  Technology
# -> 2    EEE  178.3    Consumer
# -> 3    GGG    NaN      Energy

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

  Ticker  Close      Sector
0    AAA  185.4  Technology
1    CCC  410.2  Technology
2    EEE  178.3    Consumer
3    GGG    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")  # keeps every key from both tables

print(outer)                                                 # both leftovers kept, keys sorted
# ->   Ticker   Close      Sector
# -> 0    AAA  185.40  Technology
# -> 1    CCC  410.20  Technology
# -> 2    DDD  130.75         NaN
# -> 3    EEE  178.30    Consumer
# -> 4    GGG     NaN      Energy

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

  Ticker   Close      Sector
0    AAA  185.40  Technology
1    CCC  410.20  Technology
2    DDD  130.75         NaN
3    EEE  178.30    Consumer
4    GGG     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.

The outer result comes out sorted by key, which is why GGG lands last: an outer join sorts the key instead of following the left table’s row order. Here prices was already in key order, so nothing moved. The left join above kept the left table’s row order whether it was sorted or not.

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({                                # a third table, again keyed on Ticker
    "Ticker":    ["AAA", "CCC", "EEE", "GGG"],      # no DDD here, same as the sector table
    "SharesOut": [15.2, 7.4, 10.4, 4.0],               # shares outstanding, in billions
})

m = pd.merge(prices, shares, on="Ticker", how="left")  # left join so all 4 price rows stay
m["MktCap"] = m["Close"] * m["SharesOut"]              # price times shares, row by row

print(m)
# ->   Ticker   Close  SharesOut   MktCap
# -> 0    AAA  185.40       15.2  2818.08
# -> 1    CCC  410.20        7.4  3035.48
# -> 2    DDD  130.75        NaN      NaN
# -> 3    EEE  178.30       10.4  1854.32

  Ticker   Close  SharesOut   MktCap
0    AAA  185.40       15.2  2818.08
1    CCC  410.20        7.4  3035.48
2    DDD  130.75        NaN      NaN
3    EEE  178.30       10.4  1854.32

DDD 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": ["AAA", "CCC", "FFF"], "Close": [185.40, 410.20, 240.10]})
b = pd.DataFrame({"Ticker": ["CCC", "FFF", "HHH"], "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    CCC  410.2   0.9
# -> 1    FFF  240.1   1.8

print(pd.merge(a, b, on="Ticker", how="outer"))
# ->   Ticker  Close  Beta
# -> 0    AAA  185.4   NaN
# -> 1    CCC  410.2   0.9
# -> 2    FFF  240.1   1.8
# -> 3    HHH    NaN   1.2

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. HHH has no price, so its Close is NaN. AAA 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    AAA  185.40       15.2        both
# -> 1    CCC  410.20        7.4        both
# -> 2    DDD  130.75        NaN   left_only
# -> 3    EEE  178.30       10.4        both
# -> 4    GGG     NaN        4.0  right_only

  Ticker   Close  SharesOut      _merge
0    AAA  185.40       15.2        both
1    CCC  410.20        7.4        both
2    DDD  130.75        NaN   left_only
3    EEE  178.30       10.4        both
4    GGG     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"})  # now the key is called Symbol on this side

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

  Ticker  Close Symbol  SharesOut
0    AAA  185.4    AAA       15.2
1    CCC  410.2    CCC        7.4
2    EEE  178.3    EEE       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({                                   # a listing table, one row per venue
    "Ticker":  ["AAA", "AAA"],                        # AAA twice, the key is not unique here
    "Listing": ["MAIN", "ALT"],                     # the venue each row describes
})

print(pd.merge(prices, dupe, on="Ticker", how="left"))  # the one AAA price row comes back twice
# ->   Ticker   Close Listing
# -> 0    AAA  185.40    MAIN
# -> 1    AAA  185.40     ALT
# -> 2    CCC  410.20     NaN
# -> 3    DDD  130.75     NaN
# -> 4    EEE  178.30     NaN

  Ticker   Close Listing
0    AAA  185.40    MAIN
1    AAA  185.40     ALT
2    CCC  410.20     NaN
3    DDD  130.75     NaN
4    EEE  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.