Joins & Merges

Combining tables on shared keys is where silent row duplication and lost records happen. pandas merge and concat need explicit join types and validation.

Recipe

Quick-reference recipe card - copy-paste ready.

import pandas as pd
 
merged = left.merge(
    right,
    on="order_id",
    how="left",
    validate="many_to_one",
    indicator=True,
)
unmatched = merged.loc[merged["_merge"] == "left_only"]

When to reach for this:

• Enriching fact tables with dimension attributes
• Stitching API responses from multiple endpoints
• Unioning monthly Parquet partitions vertically
• Building training sets from features + labels tables

Working Example

import pandas as pd
 
orders = pd.DataFrame(
    {"order_id": [1, 2, 3], "customer_id": [10, 11, 10], "amount": [120, 340, 80]}
)
customers = pd.DataFrame(
    {"customer_id": [10, 11], "segment": ["SMB", "Enterprise"]}
)
refunds = pd.DataFrame({"order_id": [2], "refund": [50.0]})
 
# Dimension enrich - expect 1:1 or many:1
enriched = orders.merge(
    customers,
    on="customer_id",
    how="left",
    validate="many_to_one",
)
 
# Fact extension - refunds may be missing
with_refunds = enriched.merge(
    refunds,
    on="order_id",
    how="left",
    validate="one_to_one",
)
with_refunds["refund"] = with_refunds["refund"].fillna(0.0)
with_refunds["net"] = with_refunds["amount"] - with_refunds["refund"]
 
# Audit join coverage
audit = orders.merge(customers, on="customer_id", how="left", indicator=True)
missing_customers = audit.loc[audit["_merge"] == "left_only"]
 
print(with_refunds)
print("missing customer rows:", len(missing_customers))

What this demonstrates:

• validate to assert cardinality expectations
• Left join preserving all orders
• indicator=True for orphan detection
• Fill NA after left join for optional facts

Deep Dive

How It Works

• merge performs database-style joins on column keys (hash or sort-merge).
• how controls which keys survive: inner, left, right, outer.
• validate raises if actual cardinality violates the declared relationship.
• concat stacks along axis 0 (rows) or 1 (columns) without key alignment.

Join Types

Python Notes

import pandas as pd
 
# Different column names
pd.merge(orders, regions, left_on="region_id", right_on="id")
 
# Union monthly files with consistent columns
pd.concat([jan, feb], ignore_index=True)

Gotchas

• Many-to-many merge - duplicates explode row count multiplicatively. Fix: dedupe keys first or use validate="m:m" knowingly with row count checks.
• Key dtype mismatch - int64 vs object "1" yields empty join. Fix: astype keys on both sides before merge.
• Duplicate column names - suffixes default to _x/_y. Fix: suffixes=("", "_dim") and drop redundant cols.
• Merge on index accidentally - forgotten on= joins on index if aligned. Fix: reset_index() or explicit left_index flags.
• concat without ignore_index - preserves duplicate row labels. Fix: ignore_index=True or hierarchical index by source.

Alternatives

FAQs

assert len(merged) == len(left)  # for many-to-one

left.merge(right, on=["region", "month"])

left.merge(right, on="id", how="left", indicator=True).query("_merge == 'left_only'")

pd.merge_asof(trades.sort_values("ts"), quotes.sort_values("ts"), on="ts")

right = right.drop_duplicates("customer_id", keep="last")

Related

• Cleaning & Transforming Data - normalize keys first
• GroupBy & Aggregation - aggregate after enrich
• File Formats - Parquet partitions to concat
• Data Analysis Best Practices - reproducible joins

Stack versions: This page was written for Python 3.14.0 (stable 3.14, maintenance 3.13), FastAPI 0.115+, Django 5.2, Flask 3.1, Pydantic 2, PyTorch 2.6+, pandas 2.2+, Polars 1.x, ruff 0.9+, and uv 0.6+.