10. Merge & Join

pd.merge — SQL-style joins#

left  = pd.DataFrame({'id':[1,2,3], 'name':['A','B','C']})
right = pd.DataFrame({'id':[2,3,4], 'score':[90,80,70]})

pd.merge(left, right, on='id', how='inner')   # ids 2,3
pd.merge(left, right, on='id', how='left')    # all left, NaN score for id 1
pd.merge(left, right, on='id', how='right')   # all right
pd.merge(left, right, on='id', how='outer')   # union, NaN where missing
pd.merge(left, right, how='cross')            # cartesian product

Join type diagram#

flowchart LR
    L["Left · id 1,2,3"] --> M{"merge on id"}
    R["Right · id 2,3,4"] --> M
    M -->|inner| I["2, 3 — only matches"]
    M -->|left| LO["1, 2, 3 — all left"]
    M -->|right| RO["2, 3, 4 — all right"]
    M -->|outer| O["1, 2, 3, 4 — everything"]

merge vs join vs concat#

# merge: column keys, flexible
pd.merge(left, right, on='id')

# join: merges on INDEX by default (convenience method)
left.set_index('id').join(right.set_index('id'), how='inner')

# concat: stack along an axis (no key matching)
pd.concat([df1, df2], axis=0)   # stack rows
pd.concat([df1, df2], axis=1)   # stack columns (aligns on index)

⚠️ Common Mistake: Merging on a key with duplicates can explode row counts (many-to-many). Check df['key'].is_unique first, and use validate='one_to_many'.

💡 Tip: Pass indicator=True to merge to add a _merge column showing whether each row was left_only, right_only, or both — invaluable for debugging.

⭐ Interview Question: merge vs join vs concat? merge = key-based (columns), SQL joins; join = shortcut for index-based merge; concat = glue frames together along an axis without key matching.