Joins & Merges
16 min
pd.merge is pandas' version of a SQL JOIN — combining two DataFrames by
matching values in one or more shared columns.
import pandas as pd
users = pd.DataFrame({"id": [1, 2, 3], "name": ["Ada", "Grace", "Alan"]})
orders = pd.DataFrame({"id": [1, 1, 3], "amount": [50, 30, 20]})
merged = pd.merge(users, orders, on="id")
print(merged)
# id name amount
# 0 1 Ada 50
# 1 1 Ada 30
# 2 3 Alan 20
Grace has no order, so the LEFT join keeps her row anyway and fills 'amount' with NaN — that's the defining behavior of a left join versus an inner join, which would drop her entirely.
The how parameter controls what happens to unmatched rows:
how="inner"(default) — keep only rows with a match in both tables.how="left"— keep every row from the left table, filling unmatched right-side columns with NaN.how="right"— the mirror image of left.how="outer"— keep every row from both tables, filling gaps with NaN on whichever side is missing.
Just like SQL, on="id" requires that column to exist (with the same
name) in both DataFrames — use left_on/right_on instead if the join
columns have different names in each table.
Write `inner_join_count(left, right, on)`: build two DataFrames from `left`/`right`, inner-merge them on column `on`, and return the number of resulting rows.
Write `left_join_fill(left, right, on, fill_col, fill_value)`: left-merge `left` and `right` on `on`, fill any missing values in `fill_col` with `fill_value`, and return the result as `.to_dict('records')`.
In a LEFT join, what happens to rows in the left table that have no match in the right table?
You can merge two DataFrames on a shared key and understand how inner/left/right/outer change which rows survive the join.