Pandas merge / join

TL;DR

df1.merge(df2, on="key") joins two DataFrames on a shared key the same way SQL does. Get the join type right (inner, left, right, outer) and the key cardinalities right, and you have a clean joined DataFrame. Get them wrong and you have either silently-dropped rows or a row-exploded DataFrame that’s larger than either input.

The two questions to answer before every merge:

1. What’s the cardinality on each side? One-to-one? One-to-many? Many-to-many? The last one is usually a bug.
2. What’s the right join type? inner (only rows with the key in both), left (keep all left rows, NaN where right is missing), etc.

Use validate="..." to assert your assumption — Pandas will raise if the data violates it. This single argument prevents most merge bugs.

joined = users.merge(orders, on="user_id", how="left", validate="one_to_many")

That validate says “I expect each user_id to appear at most once in users and many times in orders.” If it doesn’t, you get an error instead of silent corruption.

The picture in your head

A merge is a SQL join. Pick the rows from each side based on the key, glue together the matching ones, decide what to do with non-matches.

users:                    orders:
  user_id  name              user_id  amount
  1        alice             1        10
  2        bob               1        20
  3        carol             2        5
                              4        99       # no matching user

users.merge(orders, on="user_id", how="inner"):
  user_id  name   amount
  1        alice  10
  1        alice  20         # alice's row duplicated for each of her orders
  2        bob    5

users.merge(orders, on="user_id", how="left"):
  user_id  name   amount
  1        alice  10
  1        alice  20
  2        bob    5
  3        carol  NaN        # carol kept; no orders -> NaN

users.merge(orders, on="user_id", how="outer"):
  user_id  name   amount
  1        alice  10
  1        alice  20
  2        bob    5
  3        carol  NaN
  4        NaN    99         # order kept; no user -> NaN

users.merge(orders, on="user_id", how="right"):
  user_id  name   amount
  1        alice  10
  1        alice  20
  2        bob    5
  4        NaN    99         # order 4 kept, no user

The four join types

left is the most common in ML pipelines. You have a primary table (users, examples, products), and you want to attach features without dropping any primary rows. Missing-on-the-right becomes NaN, which you handle downstream.

Cardinality — the source of all merge bugs

The four cases:

Many-to-many is the dangerous one. If df1 has 10 rows with key X and df2 has 5 rows with key X, the merge produces 50 rows for that key alone. With many keys this multiplies into a DataFrame that doesn’t fit in memory.

The defensive habit: always pass validate=. If you don’t know which to use, run df1["key"].duplicated().sum() and df2["key"].duplicated().sum() first.

# Catch the bug at merge time, not three steps later
result = features.merge(
    labels,
    on="example_id",
    how="left",
    validate="many_to_one",   # one label per example_id
)

Multiple keys, different names

# Same key name on both sides
df1.merge(df2, on=["user_id", "date"])

# Different names
df1.merge(df2, left_on="customer", right_on="user_id")

# Join on the index of one side
df1.merge(df2, left_on="user_id", right_index=True)
df1.join(df2, on="user_id")            # short-hand for index-on-right joins

The df1.join(df2) shortcut joins on indices by default. Useful when you’ve already set the index but more error-prone in pipelines — explicit merge is usually clearer.

Suffixes — when both sides have the same column name

df1.columns = ["user_id", "score"]
df2.columns = ["user_id", "score"]

merged = df1.merge(df2, on="user_id", suffixes=("_train", "_test"))
# columns: user_id, score_train, score_test

Set sensible suffixes; the default ("_x", "_y") is unreadable.

concat vs merge — different operations

# Vertical concat — append rows
all_data = pd.concat([df_2024, df_2025, df_2026], ignore_index=True)

# Horizontal concat — paste columns side by side, aligned on index
combined = pd.concat([numeric_features, text_features], axis=1)

For “stack a list of DataFrames into one big one” — that’s concat. For “match rows from one to another by key” — that’s merge.

Worked ML example — feature join with leak protection

A typical ML feature pipeline: a label table, a behaviour table, an item-metadata table. Join them all together to build training rows.

import pandas as pd

labels = pd.read_parquet("labels.parquet")          # (example_id, user_id, item_id, label)
behaviour = pd.read_parquet("user_behaviour.parquet")  # (user_id, n_orders, avg_spend, ...)
items = pd.read_parquet("items.parquet")             # (item_id, category, price, ...)

# Behaviour features should be one row per user — assert it
assert behaviour["user_id"].is_unique

# Item features should be one row per item — assert it
assert items["item_id"].is_unique

# Now join with confidence
training = (
    labels
    .merge(behaviour, on="user_id", how="left", validate="many_to_one")
    .merge(items,     on="item_id", how="left", validate="many_to_one")
)

# Audit
print("rows in:", len(labels))
print("rows out:", len(training))
print("missing user features:", training["n_orders"].isna().sum())
print("missing item features:", training["category"].isna().sum())

The validate="many_to_one" on each merge guarantees we never row-explode. The audit prints catch missing-feature issues immediately — usually a sign of stale snapshots or a join-key mismatch.

A common bug — implicit row explosion

# Suspicious data: the same user_id appears multiple times in `users`
users = pd.DataFrame({"user_id": [1, 1, 2], "name": ["a", "a-dup", "b"]})
orders = pd.DataFrame({"user_id": [1, 1, 2], "amount": [10, 20, 5]})

result = users.merge(orders, on="user_id")
# 4 rows for user_id=1 (2 users × 2 orders), 1 row for user_id=2 — total 5

If users is supposed to have unique user_ids and doesn’t, the merge silently produces a row-exploded DataFrame. validate="one_to_many" would have raised. Always pass it.

Performance tips

• Sort by the key first if you’ll do many joins on the same key — Pandas can use a faster sort-merge algorithm for sorted inputs.
• Set the index to the join key for repeated joins. merge with right_index=True skips the indexing step.
• Use categorical dtypes for low-cardinality keys. Memory and hash-table performance both improve.
• Polars is much faster on large joins. If your DataFrames are > hundreds of MB, consider porting.
• DuckDB is excellent for ad-hoc joins on parquet files — duckdb.sql("select ... join ... ") against multiple parquet files with no in-memory load.

Common gotchas

• No validate=. The single most preventable Pandas bug.
• Joining on a float key. Floats compare poorly across processing steps. Use ints or strings.
• Mixed dtypes for the key column between sides — silently no matches. Cast both sides first.
• Different sentinel for missing. 0 on one side, NaN on the other; matches go missing. Normalise before merging.
• how="outer" on huge data — produces every key from both sides; can dwarf either input. Use rarely.
• Time-series joins. For “as-of” / nearest-time joins, use pd.merge_asof, not regular merge.

Where this shows up in real ML codebases

• Building training rows. Label table + features tables.
• Train/test contamination audits. set(train_ids) & set(test_ids) is one approach; train.merge(test, on="user_id", how="inner", indicator=True) is the more thorough one — shows how much overlap.
• Score-vs-truth joins. predictions.merge(ground_truth, on="example_id") before computing metrics.
• Slowly-changing-dimension joins for entity history.
• Pipeline orchestration tables — joining experiment runs to metrics to artifacts.

The defensive habit: every merge gets how=, on=, and validate= specified explicitly. Defaults silently drop or duplicate rows; explicit arguments make the contract obvious to the reader (and the linter).

Resources

• Pandas user guide — Merge, join, concatenate — pandas.pydata.org — canonical.
• pd.merge_asof — pandas.pydata.org — for time-series “as of” joins.
• DuckDB Python — duckdb.org — SQL joins on Pandas / parquet without leaving Python.
• Polars joins — pola.rs — faster alternative.