Python Doc & Data Automation

Fix pandas merge Duplicating Rows

Enriching orders with customer details doubles the data:

>>> orders.shape
(4012, 8)
>>> enriched = orders.merge(customers, on="customer_ref", how="left")
>>> enriched.shape
(9617, 14)
>>> enriched["amount"].sum(), orders["amount"].sum()
(41822190.4, 18904221.55)

Revenue is now 2.2 times the real figure, the customer count in the report is inflated, and the sales dashboard built on the merged frame shows a record month.

Root Cause

merge produces one output row for every matching pair of input rows. When the lookup side has more than one row per key — two records for the same customer because the address changed, a contacts table with one row per contact person, a price list with one row per validity period — every order matching that key is repeated once per lookup row. pandas does not warn: a left join promises "all left rows", not "exactly one output row per left row", so duplication is normal join behaviour rather than an error. The result is doubly damaging because it looks right: the columns are correct, the rows look like real orders, and only aggregate figures reveal the problem. Keys that are almost-but-not-quite unique — a customer table with a stale duplicate for 12 of 40,000 customers — inflate totals slightly, which is harder to notice than a doubling.

Minimal Diagnostic

Check key uniqueness on both sides before merging, and predict the output size from the key multiplicities.

# pip install "pandas>=2.2"
import pandas as pd

def merge_preview(left: pd.DataFrame, right: pd.DataFrame, on: str | list[str]) -> None:
    keys = [on] if isinstance(on, str) else list(on)
    left_counts = left.groupby(keys, dropna=False).size()
    right_counts = right.groupby(keys, dropna=False).size()
    left_dupes = left_counts[left_counts > 1]
    right_dupes = right_counts[right_counts > 1]
    matched = left_counts.index.intersection(right_counts.index)
    predicted = int((left_counts.reindex(matched) * right_counts.reindex(matched)).sum())
    unmatched_left = int(left_counts.drop(matched, errors="ignore").sum())
    print(f"left rows {len(left):,}, right rows {len(right):,}")
    print(f"left keys duplicated : {len(left_dupes):,} (max {int(left_dupes.max()) if len(left_dupes) else 1})")
    print(f"right keys duplicated: {len(right_dupes):,} (max {int(right_dupes.max()) if len(right_dupes) else 1})")
    print(f"predicted inner rows : {predicted:,}; left-join rows: {predicted + unmatched_left:,}")
    if len(right_dupes):
        worst = right_dupes.sort_values(ascending=False).head(3)
        print("worst duplicated lookup keys:")
        print(right[right[keys[0]].isin(worst.index)].sort_values(keys).head(6).to_string(index=False))

if __name__ == "__main__":
    orders = pd.read_csv("in/orders.csv", dtype={"customer_ref": "string"})
    customers = pd.read_csv("in/customers.csv", dtype={"customer_ref": "string"})
    merge_preview(orders, customers, "customer_ref")

left rows 4,012, right rows 4,190
left keys duplicated : 0 (max 1)
right keys duplicated: 812 (max 4)
predicted inner rows : 9,605; left-join rows: 9,617
customer_ref  name          address_line   valid_from
C-1042        Contoso Ltd   1 Old Road     2019-04-01
C-1042        Contoso Ltd   7 New Street   2024-11-01
C-1180        Fabrikam AG   12 Hauptstr.   2021-01-01
C-1180        Fabrikam AG   14 Hauptstr.   2023-06-15

The customer table holds one row per address version. The predicted row count matches what the merge produced, which confirms the diagnosis before any code changes.

Key multiplicity decides the output size A one-to-one join produces one output row per left row and totals stay correct. A many-to-one join, with duplicates only on the left, also produces one row per left row. A one-to-many join, with duplicates on the lookup side, multiplies left rows and inflates sums. A many-to-many join multiplies both sides and inflates sums severely. Only the first two are safe for enriching a fact table. Cardinality Output rows Totals one-to-one one per left row correct many-to-one one per left row correct one-to-many left row x matches inflated many-to-many product of both badly inflated

Fix: Declare the Expected Cardinality, Then Make It True

validate makes pandas raise when the join is not the shape you expect, turning a silent inflation into an immediate error. Then reduce the lookup table to one row per key, deliberately. Changed lines carry comments.

# pip install "pandas>=2.2"
from pathlib import Path
import pandas as pd

def current_customers(customers: pd.DataFrame, as_of: pd.Timestamp) -> pd.DataFrame:
    """One row per customer: the version in force at `as_of`."""
    valid = customers.assign(valid_from=pd.to_datetime(customers["valid_from"], errors="coerce"))
    valid = valid[valid["valid_from"] <= as_of]                            # changed: ignore future versions
    latest = (valid.sort_values(["customer_ref", "valid_from"])
                   .groupby("customer_ref", as_index=False)
                   .tail(1))                                               # changed: one row per key
    return latest

def enrich(orders: pd.DataFrame, customers: pd.DataFrame, as_of: pd.Timestamp) -> pd.DataFrame:
    lookup = current_customers(customers, as_of)
    duplicated = lookup["customer_ref"].duplicated().sum()
    if duplicated:
        raise ValueError(f"lookup still has {duplicated} duplicate key(s) after de-duplication")
    return orders.merge(
        lookup, on="customer_ref", how="left",
        validate="many_to_one",                                            # changed: raises on 1-to-many
        suffixes=("", "_customer"),
    )

if __name__ == "__main__":
    orders = pd.read_csv(Path("in/orders.csv"), dtype={"customer_ref": "string"})
    customers = pd.read_csv(Path("in/customers.csv"), dtype={"customer_ref": "string"})
    enriched = enrich(orders, customers, pd.Timestamp("2026-09-30"))
    print(enriched.shape, enriched["amount"].sum() == orders["amount"].sum())

(4012, 13) True

validate="many_to_one" states the intent: many orders may share a customer, but each customer must appear once in the lookup. If the lookup ever regains a duplicate — a data load that inserts rather than updates — the merge raises MergeError: Merge keys are not unique in right dataset; not a many-to-one merge instead of inflating the report. Row count and revenue are unchanged from the source, which is the property an enrichment step should always have.

Choosing which duplicate to keep is a business decision, not a technical one. "The version in force at the report date" suits addresses; "the most recently updated row" suits records with an updated_at; "the row with the fewest missing fields" suits merged customer databases. Write the rule explicitly, as current_customers does, rather than relying on drop_duplicates() keeping whichever row happened to be first.

Enrichment that cannot inflate Before merging, key multiplicities are counted on both sides and the output size is predicted. The lookup table is reduced to one row per key by an explicit rule, such as the version in force at the report date. The merge declares many_to_one so an unexpected duplicate raises instead of inflating. After the merge, row count and the sum of amounts are compared with the source orders. Count keys both sides Reduce lookup explicit rule Assert unique raise if not merge validate many_to_one Reconcile rows and totals

Variant Fix 1: The Duplicates Are Legitimate — Aggregate First

Sometimes the lookup genuinely has several rows per key and all of them matter: contacts per customer, tags per product. Joining them to a fact table is the wrong shape; aggregate them into one row per key first:

# pip install "pandas>=2.2"
import pandas as pd

def contacts_summary(contacts: pd.DataFrame) -> pd.DataFrame:
    return (contacts.sort_values(["customer_ref", "is_primary"], ascending=[True, False])
                    .groupby("customer_ref", as_index=False)
                    .agg(primary_contact=("name", "first"),          # the primary, by sort order
                         contact_count=("name", "size"),
                         contact_emails=("email", lambda s: "; ".join(sorted(s.dropna().unique())))))

One row per customer, with the detail collapsed into columns that answer the questions a report asks. If individual contacts are genuinely needed alongside orders, the result is a legitimately larger table — but then it is no longer an orders table, and any revenue figure computed from it must divide by the number of contacts or aggregate before summing. Keeping fact tables at one row per fact avoids that entirely.

Variant Fix 2: Nearly Unique Keys and Hidden Mismatches

A lookup that is unique after cleaning but duplicated before it is the most common real case: C-1042 and c-1042 are two rows. De-duplicate on the normalised key and keep a report of what was collapsed:

# pip install "pandas>=2.2"
import pandas as pd

def dedupe_on_normalised_key(frame: pd.DataFrame, key: str) -> tuple[pd.DataFrame, pd.DataFrame]:
    work = frame.copy()
    work["_key"] = (work[key].astype("string").str.strip().str.upper()
                             .str.replace(r"\s+", "", regex=True))
    collapsed = (work.groupby("_key")[key].nunique().loc[lambda s: s > 1])
    report = work[work["_key"].isin(collapsed.index)].sort_values("_key")
    deduped = work.drop_duplicates("_key", keep="first").drop(columns="_key")
    return deduped, report

The report is the deliverable as much as the deduplicated frame: two customer records that differ only by whitespace are a master-data problem someone should fix at the source, and a silent merge hides it. The same normalisation applies on the fact side before joining, as covered in fix merge indicator unexpected left_only rows.

Revenue before and after the fix The naive left join produced 9,617 rows from 4,012 orders and a revenue total of 41.8 million, 2.2 times the true figure, with 812 customers matching more than one lookup row. After reducing the lookup to one row per customer and declaring many_to_one, the merge produced 4,012 rows and a revenue total of 18.9 million, identical to the source orders. Naive left join orders in : 4,012 rows out : 9,617 revenue : 41,822,190 duplicated keys: 812 totals inflated 2.2x De-duplicated + validate orders in : 4,012 rows out : 4,012 revenue : 18,904,221 duplicated keys: 0 totals unchanged

Making the Check Automatic

Every enrichment step in a pipeline benefits from the same guard, so wrap it once:

# pip install "pandas>=2.2"
import pandas as pd

def enrich_safely(fact: pd.DataFrame, lookup: pd.DataFrame, on, value_cols=("amount",)) -> pd.DataFrame:
    before_rows = len(fact)
    before_sums = {c: float(fact[c].sum()) for c in value_cols if c in fact.columns}
    merged = fact.merge(lookup, on=on, how="left", validate="many_to_one")
    assert len(merged) == before_rows, f"{len(merged)} rows after merge, {before_rows} before"
    for col, total in before_sums.items():
        assert abs(float(merged[col].sum()) - total) < 0.01, f"{col} total changed during enrichment"
    return merged

Three lines of assertion turn every join in the pipeline into a checkpoint. They cost microseconds and catch the class of bug that is otherwise found by an accountant. Log the number of unmatched rows too — a left join that suddenly matches nothing is a different failure with the same silent character.

Verification

Assert on shape and totals, and confirm no key lost or gained rows relative to the fact table.

# pip install "pandas>=2.2"
import pandas as pd

def verify_enrichment(fact: pd.DataFrame, enriched: pd.DataFrame, key: str,
                      value_col: str = "amount") -> None:
    assert len(enriched) == len(fact), f"{len(enriched)} rows, expected {len(fact)}"
    assert abs(enriched[value_col].sum() - fact[value_col].sum()) < 0.01, "value total changed"
    counts_before = fact.groupby(key, dropna=False).size().sort_index()
    counts_after = enriched.groupby(key, dropna=False).size().sort_index()
    pd.testing.assert_series_equal(counts_before, counts_after, check_names=False)
    unmatched = enriched[enriched.filter(like="_customer").isna().all(axis=1)] \
        if any(c.endswith("_customer") for c in enriched.columns) else enriched.iloc[0:0]
    share = len(unmatched) / max(len(enriched), 1)
    assert share < 0.05, f"{share:.1%} of rows found no lookup match"
    print(f"enrichment verified: {len(enriched):,} rows, totals unchanged, {len(unmatched)} unmatched")

Comparing per-key row counts before and after is stronger than comparing totals alone: it catches the case where one key gained rows and another lost them, leaving the sum coincidentally unchanged. The unmatched-share check guards the opposite failure — a join that silently matches nothing after a key format change upstream.

FAQ

What does validate="one_to_one" do? It requires uniqueness on both sides and raises otherwise. Use it for joins between two reference tables; use many_to_one for enriching facts from a lookup.

Does how="left" prevent duplication? No. The join type controls which rows are kept, not how many times each is repeated.

Should I just call drop_duplicates() after merging? Rarely. By then the duplicated rows may differ in the lookup columns, so dropping keeps an arbitrary one. Fix the lookup before the join.

Can merge_asof help for versioned lookups? Yes — for a lookup with validity dates, pd.merge_asof(orders.sort_values("order_date"), versions.sort_values("valid_from"), left_on="order_date", right_on="valid_from", by="customer_ref") picks the version in force at each order's date, one row per order.

Part of Merging Multiple Spreadsheets.