pandas merge & join
If you know SQL joins, you know pandas merges — same four modes.
The four how values
"inner"— only rows matching on both sides."left"— all left rows, right cols become NaN if no match."right"— mirror."outer"— all rows from both sides.
Match on differently-named columns
users.merge(orders, left_on="user_id", right_on="uid")
Match on the index instead of a column
users.set_index("user_id").merge(orders.set_index("user_id"), left_index=True, right_index=True)
# or just: users.join(orders) # shortcut for index-index left join
The two features that save you from bad joins
indicator=True
Adds a _merge column with left_only / right_only / both — instantly see rows that didn't match.
validate=
Assert the cardinality:
"one_to_one"— 1:1"one_to_many"— 1:M (users → orders)"many_to_one"— M:1"many_to_many"— no check (default behavior)
Wrong assumption → merge raises immediately. Save hours of debugging.
Concatenate vs merge
pd.concat([df1, df2])— stack rows (or columns) with no key. Fast, simple..merge()— key-based. Use when relating two entities.
Try it
- Add a
productsdf and 3-way merge for full order details. - Find users with zero orders using
how="left", indicator=True.
3 graded exercises
Write the code, click Check. We’ll tell you exactly what to fix.
- Exercise 1
Merge
usersandordersonuser_idusing an INNER join. Store inmerged— expected 3 rows (Alice+2 orders + Bob's 1). Printmerged.shape. - Exercise 2
LEFT-merge users with orders on
user_id— Carol has no orders and should still appear with NaN inamount. Then fill missing amounts with 0 using.fillna(0). Print the sum of the resultingamountcolumn — expected:425.0. - Exercise 3
Merge users + orders with
how="outer"andindicator=True. Store inm. Print the count of rows whose_merge=="left_only"(users with no orders). Expected:1.