Relational Merging & Joining (pd.merge)

1Concept

`pd.merge()` performs relational database-style joins (`inner`, `left`, `right`, `outer`) between DataFrames based on common key columns, handling 1-to-1, 1-to-many, and many-to-many relationships.

2Architecture Diagram

Users Table (id, name) <--- INNER JOIN on user_id ---> Orders Table (order_id, user_id, amount)

3Code Example

print("=== Pandas Relational Join Types ===")
print("Inner Join:  pd.merge(df1, df2, on='id', how='inner')  [Intersection]")
print("Left Join:   pd.merge(df1, df2, on='id', how='left')   [All left + matched right]")
print("Outer Join:  pd.merge(df1, df2, on='id', how='outer')  [Full union with NaNs]")
print("Validate:    pd.merge(..., validate='1:m')             [Validates 1-to-many relationship]")

4Expected Output

=== Pandas Relational Join Types ===
Inner Join:  pd.merge(df1, df2, on='id', how='inner')  [Intersection]
Left Join:   pd.merge(df1, df2, on='id', how='left')   [All left + matched right]
Outer Join:  pd.merge(df1, df2, on='id', how='outer')  [Full union with NaNs]
Validate:    pd.merge(..., validate='1:m')             [Validates 1-to-many relationship]

5Key Takeaways

• ✓Always specify `validate='1:m'` or `'m:1'` to catch unintended cartesian explosion joins.
• ✓Use `suffixes=('_left', '_right')` to disambiguate overlapping column names.
• ✓`pd.concat([df1, df2], axis=0)` stacks DataFrames vertically.