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.
Does this left merge audit both inputs?
No. This example preserves orders and identifies orders without customer details. A customer lookup record with no order is absent from the left-merge result, so a zero count of right_only rows here does not prove that every lookup customer appears in the orders. Unused lookup customers may be perfectly valid; investigate them only when the reporting requirement calls for coverage of both inputs.
For a two-sided coverage check, use an outer merge at the intended grain or a separate key audit. Keep the production order-enrichment result separate from that audit, because adding lookup-only rows changes what each output row represents. The worked left-versus-outer merge example also shows why matched keys can have different amounts: uniqueness, coverage and value reconciliation are separate checks.
Can merge validation pass with the wrong join keys?
Yes. Validation checks the uniqueness required by the selected relationship; it does not establish that you selected the correct business keys. Keep on="customer_id" explicit in this example. When neither column keys nor index keys are specified, pandas infers the shared column names. A new status column in both inputs can therefore change the join condition without changing the merge call.
For example, an order status of paid and a customer status of active describe different facts. Requiring them to match can leave orders unmatched even when every customer ID exists. A left merge may still preserve the original row count and revenue, so inspect the indicator and the amount attached to unmatched records as well. A passed cardinality check and a reconciled total do not prove correct attribution.
See the worked example of a new shared column breaking a pandas merge: three US, UK and German orders retain their $240 total while all three profile matches fail. Explicit keys restore the intended matches and suffixes preserve both status fields.
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.