Prevent Duplicate Rows with pandas Merge Validation

A join can run without an exception while quietly multiplying your sales rows. If a customer lookup contains two rows for the same ID, every matching order can appear twice. Before joining, describe the intended relationship between the two tables and ask the tool to enforce it.

State the expected cardinality

An orders table may contain many orders for one customer. A customer lookup should contain one row per customer ID. That is a many-to-one merge, so duplicate IDs in the lookup are an error even though duplicate IDs in orders are expected.

import pandas as pd

orders = pd.DataFrame({"order_id": [1, 2, 3], "customer_id": [10, 10, 99]})
customers = pd.DataFrame({"customer_id": [10, 20], "name": ["Asha", "Ravi"]})
result = orders.merge(
    customers, on="customer_id", how="left",
    validate="many_to_one", indicator=True
)
assert len(result) == len(orders)
assert (result["_merge"] == "left_only").sum() == 1

Find unmatched records explicitly

The indicator column distinguishes matched rows from left-only records. Order 3 has customer ID 99, which has no lookup row. A left join keeps the order and leaves its customer name missing. An inner join would remove it; neither behavior should be chosen accidentally.

Test the duplicate-key failure

Add a second customer with ID 10 and run the same merge. pandas should raise a MergeError because the right-side keys are no longer unique. Investigate the duplicate rather than immediately calling drop_duplicates. Two records may disagree because the lookup is versioned, or because an upstream load duplicated data.

Watch null keys and mixed types

pandas can match null keys to each other, which differs from typical SQL null-join behavior. Decide whether missing IDs should be rejected before the join. Also inspect ID types: a numeric 10 and a string "10" may reveal inconsistent import rules rather than two different customers.

Check business totals, not just row counts

A stable row count is useful but insufficient. Compare relevant totals before and after the merge, confirm uniqueness of the order identifier, and inspect unmatched keys. If your intended relationship is genuinely many-to-many, define the resulting grain and aggregation rules before trusting a summed amount.

Practice and check your understanding

Create an additional unmatched order and a duplicate lookup key. Confirm that unmatched rows appear in the indicator report and duplicate lookup keys block the validated merge. Explain why blindly dropping duplicates is not a complete fix.

Continue learning

Explore the related Code Hacker course syllabus for structured learning. Then read Handle Missing Values in pandas Without Inventing Data to connect this exercise to the next skill.

Reference

pandas DataFrame.merge reference provides the underlying API or technical guidance. Check the documentation for the version you use; examples here are small learning exercises rather than complete production implementations.