merge: SQL joins for pandas

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

1. Add a products df and 3-way merge for full order details.
2. Find users with zero orders using how="left", indicator=True.