Pandas Joins and Merges

Real-World Scenario

A customer report needs orders, customer details, and product prices that live in three different tables. Getting the join type or key relationship wrong doesn’t raise an error — it quietly drops rows or double-counts revenue.

The Four Join Types

how= decides what happens to keys that exist on only one side.

import pandas as pd

customers = pd.DataFrame({"customer_id": [1, 2, 3, 5],
                          "name": ["Alice", "Bob", "Carol", "Eve"]})
orders = pd.DataFrame({"order_id": [101, 102, 103, 104],
                       "customer_id": [1, 1, 2, 6],
                       "amount": [250, 90, 180, 310]})

for how in ["inner", "left", "right", "outer"]:
    print(how, len(customers.merge(orders, on="customer_id", how=how)))

outer = customers.merge(orders, on="customer_id", how="outer", indicator=True)
print(outer)
print(outer["_merge"].value_counts())

$ python join_types.py
inner 3
left 5
right 4
outer 6
   customer_id   name  order_id  amount      _merge
0            1  Alice     101.0   250.0        both
1            1  Alice     102.0    90.0        both
2            2    Bob     103.0   180.0        both
3            3  Carol       NaN     NaN   left_only
4            5    Eve       NaN     NaN   left_only
5            6    NaN     104.0   310.0  right_only
_merge
both          3
left_only     2
right_only    1
Name: count, dtype: int64

Alice has two orders, so she appears twice in every join. Carol and Eve never ordered, and order 104 belongs to a customer (6) who doesn’t exist; inner drops all three, left keeps the customers, right keeps the orphan order, and outer keeps everything. The _merge column from indicator=True labels each row’s origin, which is the fastest way to audit a join.

Left Joins Change Integer Columns to Float

Unmatched rows get NaN, and that changes the column’s dtype.

import pandas as pd

customers = pd.DataFrame({"customer_id": [1, 2, 3], "name": ["Alice", "Bob", "Carol"]})
orders = pd.DataFrame({"customer_id": [1, 2], "order_id": [101, 102]})

left = customers.merge(orders, on="customer_id", how="left")
print(left)
print(left.dtypes)
left["order_id"] = left["order_id"].astype("Int64")
print(left)

$ python left_dtype.py
   customer_id   name  order_id
0            1  Alice     101.0
1            2    Bob     102.0
2            3  Carol       NaN
customer_id      int64
name               str
order_id       float64
dtype: object
   customer_id   name  order_id
0            1  Alice       101
1            2    Bob       102
2            3  Carol      <NA>

order_id went in as int64 and came out as float64, printing 101.0, because Carol’s row needed a NaN. The nullable Int64 dtype (capital I) stores integers alongside a real missing marker, <NA>, so the IDs print as integers again.

Keys with Different Names

left_on and right_on join columns that mean the same thing but are named differently.

import pandas as pd

users = pd.DataFrame({"user_id": [1, 2, 3], "name": ["Alice", "Bob", "Carol"]})
events = pd.DataFrame({"uid": [1, 1, 3], "event": ["login", "buy", "login"]})

result = users.merge(events, left_on="user_id", right_on="uid", how="left")
print(result)
print(result.drop(columns="uid"))

$ python different_names.py
   user_id   name  uid  event
0        1  Alice  1.0  login
1        1  Alice  1.0    buy
2        2    Bob  NaN    NaN
3        3  Carol  3.0  login
   user_id   name  event
0        1  Alice  login
1        1  Alice    buy
2        2    Bob    NaN
3        3  Carol  login

Both key columns survive the merge, and uid is NaN wherever a user had no events, so it is the one to drop. Bob has no events but is kept, because this is a left join.

Duplicate Keys Fan Out

When a key repeats on both sides, every copy pairs with every copy.

import pandas as pd

prices = pd.DataFrame({"sku": ["A", "A", "B"], "price": [10, 12, 5]})
sales = pd.DataFrame({"sku": ["A", "A", "B"], "qty": [3, 1, 4]})

joined = sales.merge(prices, on="sku")
print(joined)
print("units sold:", sales["qty"].sum(), "| units after join:", joined["qty"].sum())

try:
    sales.merge(prices, on="sku", validate="many_to_one")
except pd.errors.MergeError as e:
    print("MergeError:", e)

$ python fanout.py
  sku  qty  price
0   A    3     10
1   A    3     12
2   A    1     10
3   A    1     12
4   B    4      5
units sold: 8 | units after join: 12
MergeError: Merge keys are not unique in right dataset; not a many-to-one merge

Duplicates in right:
 sku
  A ...

The price table accidentally has two rows for SKU A, so each A sale matched twice: three sales rows became five, and the 8 units sold became 12. Nothing warned about it. Declaring validate="many_to_one" states “each sale matches at most one price” and turns the silent inflation into a loud MergeError.

Overlapping Column Names

Columns that exist on both sides, other than the key, get suffixes.

import pandas as pd

budget = pd.DataFrame({"dept": ["Eng", "Ops"], "amount": [500, 200]})
actual = pd.DataFrame({"dept": ["Eng", "Ops"], "amount": [540, 180]})

print(budget.merge(actual, on="dept"))
both = budget.merge(actual, on="dept", suffixes=("_budget", "_actual"))
both["variance"] = both["amount_actual"] - both["amount_budget"]
print(both)

$ python suffixes.py
  dept  amount_x  amount_y
0  Eng       500       540
1  Ops       200       180
  dept  amount_budget  amount_actual  variance
0  Eng            500            540        40
1  Ops            200            180       -20

The default suffixes _x and _y say nothing about which table a column came from. Naming them with suffixes= makes the follow-up arithmetic readable.

Joining on the Index

join matches the other frame’s index against a column (or the index) of this one.

import pandas as pd

orders = pd.DataFrame({"order_id": [101, 102, 103], "sku": ["A", "B", "C"]})
catalog = pd.DataFrame({"name": ["Widget", "Gadget"], "price": [10, 5]},
                       index=pd.Index(["A", "B"], name="sku"))

print(orders.join(catalog, on="sku"))

$ python join_index.py
   order_id sku    name  price
0       101   A  Widget   10.0
1       102   B  Gadget    5.0
2       103   C     NaN    NaN

on="sku" looks each order’s sku up in catalog’s index. join defaults to a left join, so order 103 is kept with NaN product details — SKU C isn’t in the catalog.

Concatenating Frames

concat stacks frames; it aligns on column names, not position.

import pandas as pd

q1 = pd.DataFrame({"month": ["Jan", "Feb"], "revenue": [100, 120]})
q2 = pd.DataFrame({"month": ["Apr", "May"], "revenue": [150, 130], "refunds": [5, 0]})

print(pd.concat([q1, q2]))
print(pd.concat([q1, q2], ignore_index=True))
print(pd.concat({"Q1": q1, "Q2": q2})["revenue"].groupby(level=0).sum())

$ python concat.py
  month  revenue  refunds
0   Jan      100      NaN
1   Feb      120      NaN
0   Apr      150      5.0
1   May      130      0.0
  month  revenue  refunds
0   Jan      100      NaN
1   Feb      120      NaN
2   Apr      150      5.0
3   May      130      0.0
Q1    220
Q2    280
Name: revenue, dtype: int64

Without ignore_index, the row labels 0, 1, 0, 1 repeat, which breaks later loc lookups. refunds exists only in q2, so the q1 rows get NaN there. Passing a dict labels each block with its key, which you can then group on.

Practice

1. In join_types.py, list the customers who have never placed an order.

import pandas as pd

customers = pd.DataFrame({"customer_id": [1, 2, 3, 5],
                          "name": ["Alice", "Bob", "Carol", "Eve"]})
orders = pd.DataFrame({"customer_id": [1, 1, 2, 6], "amount": [250, 90, 180, 310]})

m = customers.merge(orders, on="customer_id", how="left", indicator=True)
print(m.loc[m["_merge"] == "left_only", ["customer_id", "name"]])

$ python practice_anti.py
   customer_id   name
3            3  Carol
4            5    Eve

2. In fanout.py, fix the price table so each SKU has one price (keep the last one), then show that the units total is correct after the join.

import pandas as pd

prices = pd.DataFrame({"sku": ["A", "A", "B"], "price": [10, 12, 5]})
sales = pd.DataFrame({"sku": ["A", "A", "B"], "qty": [3, 1, 4]})

prices = prices.drop_duplicates(subset="sku", keep="last")
joined = sales.merge(prices, on="sku", validate="many_to_one")
print(joined)
print("units after join:", joined["qty"].sum())

$ python practice_fanout.py
  sku  qty  price
0   A    3     12
1   A    1     12
2   B    4      5
units after join: 8

3. In concat.py, stack q1 and q2 so the missing refunds count as 0 rather than NaN, with integer refunds.

import pandas as pd

q1 = pd.DataFrame({"month": ["Jan", "Feb"], "revenue": [100, 120]})
q2 = pd.DataFrame({"month": ["Apr", "May"], "revenue": [150, 130], "refunds": [5, 0]})

full = pd.concat([q1, q2], ignore_index=True)
full["refunds"] = full["refunds"].fillna(0).astype(int)
print(full)

$ python practice_concat.py
  month  revenue  refunds
0   Jan      100        0
1   Feb      120        0
2   Apr      150        5
3   May      130        0