Why does my merge produce more rows than either input?

The short version

A merge on a key that repeats produces a row for every matching pair. Two rows on the left matching three on the right give you six rows, not three. This is correct behaviour, and it is almost never what you wanted.

Reproduce it

import pandas as pd

people = pd.DataFrame({
    "state": ["Sokoto", "Sokoto", "Kebbi"],
    "enumerator": ["Aisha", "Bala", "Chidi"],
})

targets = pd.DataFrame({
    "state": ["Sokoto", "Sokoto", "Kebbi"],
    "target": [1200, 1350, 900],   # Sokoto appears twice. This is the bug.
})

merged = people.merge(targets, on="state")
print(len(people), len(targets), len(merged))   # 3 3 5

Sokoto has two rows on each side, so it produces four. Kebbi produces one. Five rows out of a three row frame.

Find the duplicate key before you merge

Never debug this after the fact. Check first.

dupes = targets[targets.duplicated("state", keep=False)]
print(dupes)

If that returns anything, you have a decision to make. Not a merge to run.

Let pandas enforce your assumption

The validate argument turns a silent explosion into a loud error. Use it every time. It costs nothing and it has caught more bad numbers than any code review.

merged = people.merge(targets, on="state", validate="many_to_one")
# MergeError: Merge keys are not unique in right dataset

The four values are one_to_one, one_to_many, many_to_one and many_to_many. Write down which one you believe is true, and let pandas check you.

Then fix the data, not the merge

Once you know the key repeats, you have three honest options.

Aggregate first, if the repetition is real and you want a total:

targets_by_state = targets.groupby("state", as_index=False)["target"].sum()
merged = people.merge(targets_by_state, on="state", validate="many_to_one")

Deduplicate, if the repetition is an error and one row is correct. Be explicit about which one:

targets = targets.sort_values("target").drop_duplicates("state", keep="last")

Add to the key, if the rows differ by something you forgot:

merged = people.merge(targets, on=["state", "year"], validate="one_to_one")

Why this matters more than it looks

On survey data this bug does not announce itself. It inflates a denominator, a coverage rate drops four points, and nobody can explain why. By the time somebody notices, the figure is in a report. validate is one keyword argument. Use it.

Related

Row counts changing after a merge is the single most common way a clean pipeline starts producing wrong numbers. Assert the shape you expect:

before = len(people)
merged = people.merge(targets, on="state", how="left", validate="many_to_one")
assert len(merged) == before, f"merge changed row count: {before} -> {len(merged)}"