Why a pandas merge duplicates rows: validate the keys first

A pandas merge can legitimately produce more rows than either input. For each matching key, it creates the combinations of left and right records. Two records on one side and three on the other produce six output records for that key. Changing the join type does not remove this multiplicity.

The important question is what one row represents. If the right table is supposed to contain one customer record per key, duplicate keys violate that design. If it contains customer events, multiple rows may be expected. We will use invented orders and region records to distinguish these cases, then enforce the intended relationship with validate.

Reproduce a many-to-many expansion

Run this script with pandas. The small tables make the result inspectable without printing a large dataset.

import pandas as pd

orders = pd.DataFrame({
    "customer": ["A", "A", "B"],
    "amount": [10, 20, 30],
})
regions = pd.DataFrame({
    "customer": ["A", "A", "B"],
    "region": ["north", "south", "east"],
})

expanded = orders.merge(regions, on="customer", how="left")
print("rows:", len(expanded))
print("amount total:", int(expanded["amount"].sum()))

try:
    orders.merge(regions, on="customer", validate="many_to_one")
except pd.errors.MergeError:
    print("relationship check: failed")

# An explicit, invented business rule: the final record is authoritative.
current = regions.drop_duplicates("customer", keep="last")
checked = orders.merge(
    current, on="customer", how="left",
    validate="many_to_one", indicator=True,
)
assert len(checked) == len(orders)
assert checked["_merge"].eq("both").all()
print("checked rows:", len(checked))
print("checked amount total:", int(checked["amount"].sum()))

Expected output:

rows: 5
amount total: 90
relationship check: failed
checked rows: 3
checked amount total: 60

Customer A contributes two orders times two region records, making four joined rows. Customer B contributes one times one, making one row. Each A order amount is repeated, so summing the joined amount produces 90 instead of the original 60.

Predict the row count before merging

For an inner join, count the occurrences of each key on both sides, multiply the counts for each matching key and sum those products. For a left join, an unmatched left row still contributes one row with missing right-side fields. If the relationship really is many-to-one, the left join should preserve the left row count.

This calculation is useful because an inflated table may otherwise look reasonable. Aggregate metrics, training weights and reported transaction totals can all change after expansion. A join can also create several nearly identical records that later cross a random train/test boundary.

Enforce the intended relationship

validate="many_to_one" allows repeated left keys and requires unique right keys. one_to_one requires both sides to be unique; one_to_many allows repeated right keys. Declaring many_to_many allows duplicates on both sides and does not protect against an unexpected expansion.

The pandas merging guide documents these checks. Relationship validation and match coverage answer different questions. A unique right table can still omit customers. indicator=True exposes unmatched rows, and the assertion in the script checks coverage for this example.

Resolve duplicate keys using a real rule

The demonstration keeps the final region record because we explicitly invented that rule. In a real table, row order is not automatically a source of truth. You might need the latest timestamp, a verified current-record flag, aggregation of events, or a join on a richer composite key.

Do not use drop_duplicates merely to make a validation error disappear. Inspect conflicting values and decide what the data means. If historical region changes matter, joining a transaction to its region at transaction time is a temporal problem; selecting today's region could introduce wrong or future-derived information.

Also inspect missing keys. pandas can match null keys to each other, which differs from common SQL expectations. Reject or handle missing identifiers explicitly if that behavior would be inappropriate for the task. Check key types and whitespace too: a successful merge does not prove the identifiers were normalized correctly.

Extend and test the workflow

Add an order for customer C without adding a region. The relationship validation should still pass after deduplication, but the match-coverage assertion should fail. Decide whether the application should reject the record, label its region unknown, or investigate the source.

Then add timestamps and select the latest authoritative region using an explicit sort before deduplication. Preserve the original inputs so the decision remains auditable. Continue with pandas projects and data leakage prevention to connect table integrity to a complete analysis.