Merges and JOIN Traps

The one idea: Combining two tables can silently drop the rows that don't match, or multiply rows when a key appears more than once — either way the totals come out wrong with no error. The row count before and after a merge is the single most reliable warning sign, so always check it.

⚠️ The trap: A plain .merge() keeps all my orders and just attaches the extra columns. That feels right and it's wrong. With no how= argument, merge defaults to an inner join and silently drops every row that has no match on the other side — pass how='left' to keep all your rows, and compare the row count before and after.

Real analysis almost always combines tables: orders plus customers, spend plus conversions, product catalog plus usage. In pandas that's pd.merge (or .merge on a DataFrame), and it is the direct sibling of the SQL JOIN — same concepts, same failure modes, same #1 tell. Everything you learned about joins silently dropping or duplicating rows transfers here verbatim. What's new is how it looks in Python, and one free safety feature almost nobody uses.

Reading a merge

merged = orders.merge(customers, on='customer_id', how='left')

Read it as: start from orders, look up each row's customer_id in customers, attach the matching customer columns. The two decisions that determine everything:

• on='customer_id' — the key both tables are matched by.
• how= — what happens when there's no match. how='left' keeps every orders row, filling unmatched customer columns with NaN (pandas' marker for a missing value). how='inner' (the default if how is omitted) keeps **only rows that match in both…