Does drop_duplicates after the merge fix the problem?

Only if the duplicated rows are fully identical. When the right table has two different prices, the merged rows differ in that column, so drop_duplicates keeps both. Fix the input, not the output.

What does validate='one_to_many' check?

It checks that the merge keys are unique in the left DataFrame. 'many_to_one' checks the right one, and 'one_to_one' checks both. A violation raises pandas.errors.MergeError.

Why does a left merge return more rows than the left table?

Because at least one left key matched more than one right row. A left merge keeps every left row at least once, but it repeats a row for each extra match.

Can NaN keys cause extra rows?

Yes. Unlike SQL, pandas matches NaN keys to each other, so missing keys on both sides pair up. Drop or fill missing keys before merging if that isn't what you want.

Why pandas merge creates duplicate rows, and how to stop it

By the Analistable team · Updated 2026-10-10 · 5 min read

pd.merge returns one output row for every pair of matching keys, so if a key appears twice on the left and three times on the right you get six rows. Check with df.duplicated(key).sum() on each side, pass validate="many_to_one" so pandas raises an error when the right side isn't unique, then drop duplicates or aggregate the right table before merging.

Part of our guide: How to merge data in pandas, R and SQL

What is actually happening

A merge in pandas behaves like a SQL join. For each key value, every left row that has it is paired with every right row that has it. That is correct behaviour, not a bug: pandas has no way to know which of three matching price rows you meant. The row count only stays the same when the key is unique on at least one side, usually the lookup side.

The classic symptom is a sales total that jumps after you add a lookup column. You merged orders with a product table, the product table had a stale duplicate for one SKU, and every order for that SKU was counted twice. For the basics of how=, on= and suffixes, see merging DataFrames in pandas; this page is only about the extra rows.

orders (4 rows) merged with a products table where SKU A-10 appears twice
order_id	sku	qty	unit_price (from products)	line_value
1001	A-10	2	12.5	25
1001	A-10	2	13	26
1002	B-20	1	40	40
1003	A-10	3	12.5	37.5
1003	A-10	3	13	39
1004	C-30	5	4	20

Four orders became six rows, and the total line value is 187.50 instead of the 122.50 you would get using the current price of 12.50 (25 + 40 + 37.50 + 20). Both A-10 orders were doubled because the products table holds an old price and a new price for that SKU.

Diagnose before you fix

Count duplicates on each side of the key first. It takes seconds and tells you which table is to blame.

import pandas as pd

orders = pd.read_csv("orders.csv")
products = pd.read_csv("products.csv")

# How many key values repeat on each side?
print(orders["sku"].duplicated().sum())     # repeats are normal here
print(products["sku"].duplicated().sum())   # should be 0 for a lookup

# Which keys repeat in the lookup table?
dupes = products[products["sku"].duplicated(keep=False)].sort_values("sku")
print(dupes)

Next, make pandas enforce the relationship you expect. The validate argument accepts "one_to_one", "one_to_many", "many_to_one" and "many_to_many" (short forms such as "m:1" also work). If the data breaks the rule, merge raises a MergeError instead of quietly multiplying rows.

merged = orders.merge(
    products, on="sku", how="left",
    validate="many_to_one",   # each order matches at most one product
    indicator=True,           # adds a _merge column
)
print(merged["_merge"].value_counts())  # both / left_only / right_only

indicator=True adds a _merge column that says whether each row matched (both) or came from one side only. It does not catch duplicates by itself, but combined with a row-count check it shows whether the merge also lost or failed to match rows. A simple guard is assert len(merged) == len(orders) after a left merge onto a lookup table.

Fix 1: deduplicate the lookup side

If the duplicates are genuinely redundant, such as the same customer exported twice, drop them. Be explicit about which copy to keep, otherwise you keep whichever row happened to come first.

# Keep the most recent price per SKU
products_latest = (
    products.sort_values("valid_from")
            .drop_duplicates(subset="sku", keep="last")
)
merged = orders.merge(products_latest, on="sku", how="left", validate="m:1")

Watch for near-duplicates that drop_duplicates won't catch: trailing spaces, different case, or a key stored as the integer 1001 in one file and the text "1001" in another. Normalise first with .str.strip().str.upper() and .astype(str) on both sides.

Fix 2: aggregate before merging

Sometimes the many rows on the right are all real, for example several payments per invoice. Dropping them would lose money. Instead, collapse them to one row per key first, then merge the summary.

pay_per_invoice = (
    payments.groupby("invoice_id", as_index=False)
            .agg(paid=("amount", "sum"), payments=("amount", "size"))
)
invoices = invoices.merge(pay_per_invoice, on="invoice_id",
                          how="left", validate="1:1")
invoices["paid"] = invoices["paid"].fillna(0)

Three invoices after aggregating five payment rows
invoice_id	total	paid	payments	outstanding
INV-1	500	500	2	0
INV-2	300	150	1	150
INV-3	900	600	2	300

Five payments (250 + 250, 150, 400 + 200) collapse to three rows, and the invoice table keeps exactly its original three rows.

Fix 3: add the missing key column

Duplicates often mean the join key is incomplete. A price table keyed on SKU and region needs both columns in on=. Merging on SKU alone pairs each order with every regional price. Pass on=["sku", "region"] and re-run the validate check. Date-effective tables (prices valid from a date) are a special case: pd.merge_asof matches each order to the latest row on or before its date, and requires both frames to be sorted by the date column.

If you just need the answer rather than a script, Analistable loads both files into an in-browser DuckDB database and the AI writes the join for you. It still follows the same rule, so it checks key uniqueness rather than assuming it.

A checklist for every merge

Count rows on both sides and decide which side should be unique.
Normalise key types and text (strip, case, dtype) on both sides.
Merge with validate= set to the relationship you expect.
Assert the output row count, and inspect _merge for unmatched rows.
Compare a total (revenue, quantity) before and after the merge.

The same logic applies in SQL and R: see why a SQL join returns too many rows and the pandas, R and SQL merge guide.

Frequently asked questions

Does drop_duplicates after the merge fix the problem?: Only if the duplicated rows are fully identical. When the right table has two different prices, the merged rows differ in that column, so drop_duplicates keeps both. Fix the input, not the output.
What does validate='one_to_many' check?: It checks that the merge keys are unique in the left DataFrame. 'many_to_one' checks the right one, and 'one_to_one' checks both. A violation raises pandas.errors.MergeError.
Why does a left merge return more rows than the left table?: Because at least one left key matched more than one right row. A left merge keeps every left row at least once, but it repeats a row for each extra match.
Can NaN keys cause extra rows?: Yes. Unlike SQL, pandas matches NaN keys to each other, so missing keys on both sides pair up. Drop or fill missing keys before merging if that isn't what you want.