Pandas Merge Mistakes: Prevent Duplicate Revenue

By Prakhar Shrivastava · Worked practice example

A merge can produce plausible rows and still corrupt a revenue report. This synthetic example demonstrates how a duplicate customer lookup inflates sales, how to stop the merge, and how to retain orders with missing customer details. Run the complete example with Python and Pandas; the assertions make the expected behaviour explicit.

Start with the grain of each table

The orders table has one row per order. Several orders can belong to one customer, so customer_id is deliberately not unique there. The customer lookup is intended to have one row per customer. This is a many-to-one relationship. Before choosing a join, write down that expectation in words; it becomes the rule you validate in code.

Our four orders total 430. Customer 20 has two identical lookup records, and customer 30 is absent. Those are separate quality problems: one multiplies matching rows, while the other creates an unmatched order.

Run the complete example

import pandas as pd
orders = pd.DataFrame({
    "order_id": [1, 2, 3, 4],
    "customer_id": [10, 20, 20, 30],
    "revenue": [100, 200, 50, 80]
})
customers = pd.DataFrame({
    "customer_id": [10, 20, 20],
    "segment": ["retail", "business", "business"]
})
naive = orders.merge(customers, on="customer_id", how="left")
print(len(naive), naive["revenue"].sum())  # 6, 680
try:
    orders.merge(customers, on="customer_id", how="left",
                 validate="many_to_one")
except pd.errors.MergeError:
    print("Customer lookup keys are not unique")
# In THIS fixture the duplicated records are exactly identical.
lookup = customers.drop_duplicates()
assert lookup["customer_id"].is_unique
result = orders.merge(lookup, on="customer_id", how="left",
                      validate="many_to_one", indicator=True)
assert len(result) == len(orders)
assert result["order_id"].is_unique
assert result["revenue"].sum() == orders["revenue"].sum() == 430
print(result[["order_id", "segment", "_merge"]].to_string(index=False))
print(result.groupby("segment", dropna=False)["revenue"].sum())

Understand the inflated total

The naive left merge returns six rows and revenue of 680. Both orders for customer 20 match two lookup rows, repeating revenue of 200 and 50. The error is exactly 250. A left join preserves left-side records, but it does not promise one output row per left-side record when multiple matches exist.

Adding validate=”many_to_one” makes the intended relationship executable. It raises a MergeError when customer keys on the right are duplicated. Do not catch that exception and silently continue with the unvalidated merge. In a scheduled report, stop publication and surface the conflicting keys for investigation.

Resolve duplicates using a stated rule

The fixture contains exactly identical customer records, so removing duplicate rows is justified here. Real duplicates may disagree about segment, status, or effective date. Keeping an arbitrary first row would hide that conflict. Investigate the source contract and, if the table is historical, join using a documented effective-date rule instead of pretending it is a current lookup.

After removing identical duplicates, the explicit uniqueness assertion protects the assumption. If different records still share a customer ID, the assertion fails. This is intentional: the tutorial should not manufacture an answer to a data ownership question.

Keep missing matches visible

The indicator column labels order 4 as left_only; the other orders are both. The segment summary is retail 100, business 250, and missing segment 80. Using dropna=False keeps that missing category in the grouped output. Without it, the segment totals would omit 80 even though the merged order table still contains it.

Decide whether the missing customer is a late-arriving record, a deleted account, or an invalid key. Until that is resolved, an “unknown” reporting bucket can make the gap visible, provided it is clearly labelled. Do not reassign the order to a known segment just to make a chart look complete.

Check nulls and data types before a real merge

Pandas can match null join keys to other null keys, which differs from usual SQL join behaviour. Decide whether null-key rows should be quarantined before joining. Also check that both key columns represent the same identifier: converting an identifier with leading zeros into a number may destroy meaningful information.

Row count, unique order IDs, and revenue totals are useful checks in this example because a customer lookup should only add attributes. Other joins legitimately change the grain. Define expected invariants from the business relationship rather than copying assertions into every pipeline.

Explain the result in an interview

“I expect many orders to map to one customer. I validate that relationship, inspect duplicate lookup records, retain unmatched orders, and reconcile totals after the join. If duplicates conflict, I need a business rule before I can safely publish the segment breakdown.”

Continue with the Python learning hub. Reference: Pandas merge documentation.