Validate a pandas Merge to Avoid Duplicate Rows

Merging an order sheet with a customer sheet should give one row per order. Then someone adds a second row for customer C2 to the customer workbook — a changed region, a typo, a copy-paste — and the next merge quietly returns extra rows. Nothing fails. The order count in the report goes up, revenue is overstated, and the error surfaces weeks later in a reconciliation. This guide, part of Merging and Joining Excel DataFrames, shows how to make pandas refuse such merges, and which checks to put around every merge in a reporting pipeline.

Prerequisites

pip install pandas openpyxl

An order sheet and a customer sheet with the three classic problems — a duplicated customer, an order for an unknown customer and an order with no customer ID:

import pandas as pd

orders = pd.DataFrame({"order": [1, 2, 3, 4, 5],
                       "cust": ["C1", "C2", "C2", "C3", None],
                       "amount": [100, 50, 70, 20, 10]})
customers = pd.DataFrame({"cust": ["C1", "C2", "C2", "C4", None],
                          "region": ["North", "South", "South-East", "West", "Unknown"]})
with pd.ExcelWriter("sales.xlsx") as writer:
    orders.to_excel(writer, sheet_name="Orders", index=False)
    customers.to_excel(writer, sheet_name="Customers", index=False)
orders = pd.read_excel("sales.xlsx", sheet_name="Orders")
customers = pd.read_excel("sales.xlsx", sheet_name="Customers")

Step 1 — See the damage an unchecked merge does

merged = orders.merge(customers, on="cust", how="left")
print(len(orders), len(merged))                        # 5 7
print(orders["amount"].sum(), merged["amount"].sum())  # 250 370

Five orders became seven rows and 250 of revenue became 370. Orders 2 and 3 each appear twice, once per C2 row. The last order, which has no customer ID at all, picked up the region "Unknown" — because pandas, unlike SQL, treats missing keys as equal, and the customer sheet has a row with a blank ID too. Neither problem raises an error or a warning.

Step 2 — Declare the relationship with validate

Every lookup merge has an expected shape: many orders to one customer. Say so, and pandas checks it:

try:
    orders.merge(customers, on="cust", how="left", validate="many_to_one")
except pd.errors.MergeError as exc:
    print(exc)
# Merge keys are not unique in right dataset; not a many-to-one merge

The four options describe which side must have unique keys: one_to_one checks both, one_to_many the left, many_to_one the right and many_to_many neither. The check costs one pass over the key columns and turns a silent inflation into an immediate, explained failure. Make it a habit on every merge whose shape you know — which is nearly all of them in reporting code.

Know what validate does not check. It says nothing about left rows that find no partner, so an order for a customer missing from the lookup passes silently with empty columns. It does not stop blank keys from matching each other, as long as there is only one blank on the right. And it cannot tell that "C2 " with a trailing space and "C2" are meant to be the same customer; to the check they are two unique keys. The remaining steps cover those gaps.

Step 3 — Find and resolve the duplicate keys

The error tells you the right side has duplicates; list them so someone can decide which row is correct:

dupes = customers[customers.duplicated("cust", keep=False) & customers["cust"].notna()]
print(dupes)
#   cust      region
# 1   C2       South
# 2   C2  South-East

keep=False marks every copy, not just the second, so the report shows the conflict in full. Resolving it is a business decision — the most recent row, the row from the authoritative system, or a correction in the source workbook. Only after that should the code deduplicate, and it should do so explicitly, for example with drop_duplicates("cust", keep="last") when newer rows are appended below. Near-duplicates that differ by spaces or case need normalising first; see Pandas: Drop Duplicates From an Excel Column.

Step 4 — Keep missing keys out of the join

A blank key is not a key. Drop blank keys from the lookup table and leave the left side's blanks unmatched, so they show up as missing rather than joining each other:

lookup = customers.dropna(subset=["cust"]).drop_duplicates("cust", keep="last")
merged = orders.merge(lookup, on="cust", how="left", validate="many_to_one", indicator=True)
print(merged[["order", "cust", "region", "_merge"]].values.tolist())

Order 5 now has no region, which is the truth: we do not know its customer. Blank keys often hide as empty strings or spaces rather than NaN; convert those to missing values before this step.

Step 5 — Count what matched with indicator

indicator=True adds a _merge column saying whether each row found a partner. Count it and act on the result:

counts = merged["_merge"].value_counts().to_dict()
print(counts)            # {'both': 3, 'left_only': 2, 'right_only': 0}
unmatched = merged.loc[merged["_merge"] == "left_only", ["order", "cust"]]
print(unmatched.values.tolist())      # [[4, 'C3'], [5, nan]]

C3 is missing from the customer sheet and order 5 has no customer. Whether that should stop the run depends on the report: a revenue total can proceed with an "Unassigned" region, while an invoicing run should not. Either way, write the unmatched rows somewhere a person will see them. Finding rows missing from one file or the other is covered in Find Rows in One Excel File Missing from Another.

Step 6 — Assert row counts and totals

validate protects against duplicate keys; it cannot know what the result should add up to. For a left lookup merge the rule is simple: the row count and every numeric total of the left table must be unchanged. Wrap that in a function you use everywhere:

def lookup_merge(left, right, on, **kwargs):
    right = right.dropna(subset=[on] if isinstance(on, str) else on)
    result = left.merge(right, on=on, how="left", validate="many_to_one", indicator=True, **kwargs)
    assert len(result) == len(left), f"row count changed: {len(left)} -> {len(result)}"
    for col in left.select_dtypes("number").columns:
        assert result[col].sum() == left[col].sum(), f"total of {col} changed"
    return result

report = lookup_merge(orders, customers.drop_duplicates("cust", keep="last"), on="cust")
print(len(report), report["amount"].sum())       # 5 250

With validate in place the assertions should never fire for a lookup merge, which is the point: they document the expectation and guard against later edits, such as someone switching to an inner join or adding a second key. For merges that are supposed to change the row count — an inner join that filters, a one-to-many expansion — assert the expected count instead.

When a check fails in a scheduled job, the message is often the only thing anyone sees. Make it specific: name the lookup file, the key column, the number of duplicate keys and the first few of them. "customers.xlsx has 1 duplicate cust key (C2)" gets fixed in minutes by whoever maintains that workbook; "AssertionError" gets forwarded to the developer. The same goes for unmatched rows — a short sheet listing them, attached to the failure alert, turns a broken run into a to-do list.

Common pitfalls

• Keys that differ only by type. "100" in one sheet and 100 in the other never match, and validate will not warn you. Convert both sides to the same type first.
• Validating after deduplicating blindly. A drop_duplicates added just to make validate pass hides the conflict. Report duplicates before choosing a row.
• Outer joins in reports. how="outer" adds right-only rows with empty amounts, which changes counts in ways that look like data. Use left joins for lookups and report right-only keys separately.
• Checking only the first merge. Pipelines often merge several lookups in a row; each one can multiply rows. Guard each merge.

Conclusion

Duplicate keys make pandas merges multiply rows without any warning, and missing keys match each other. Declare every merge's shape with validate, list and resolve duplicate keys explicitly, drop blank keys from lookup tables, use indicator=True to count and report unmatched rows, and assert that row counts and totals survive every lookup merge.

Frequently asked questions

Why does my merge have more rows than the original? At least one key appears more than once in the table you are merging in. Each matching left row is repeated once per duplicate, so row counts and sums grow. Pass validate="many_to_one" to make pandas raise an error instead.

What does validate="many_to_one" check? That the keys in the right-hand table are unique, so each left row can match at most one right row. one_to_one checks both sides, one_to_many checks the left side, and many_to_many checks nothing.

How do I see which rows did not match? Pass indicator=True. The merged frame gains a _merge column with the values both, left_only and right_only, which you can count or filter.

Do missing keys match each other in a pandas merge? Yes. Unlike SQL, pandas treats NaN keys as equal, so a left row with no customer ID matches every right row with no ID. Drop or fill missing keys before merging.

Related

• Up one level: Merging and Joining Excel DataFrames — combining data from several workbooks.
• Merge Two Excel Files on a Common Column in Python — the basic merge.
• The VLOOKUP Equivalent in pandas for Excel Files — lookups translated from Excel.
• Reconcile Excel Totals with Control Totals in Python — checking results against known figures.