Two rows joined to two rows. Count what comes out, and note that nothing complains.
import pandas as pd
left = pd.DataFrame({'k': [1, 1], 'x': ['a', 'b']})
right = pd.DataFrame({'k': [1, 1], 'y': ['p', 'q']})
len(left.merge(right, on='k')) # -> 4
# every left row pairs with every right row that matches: 2 x 2
# The keyword that makes it an error instead of a surprise:
left.merge(right, on='k', validate='one_to_one')
# MergeError: Merge keys are not unique in either left or right dataset
# 'one_to_one' both sides unique 'one_to_many' left unique
# 'many_to_one' right unique and MergeError is a ValueError
- A join multiplies matches; it does not pair them up
Two orders for the same product against two rows for that product gives four rows, because a join emits every combination that matches. That is the correct definition and it is why a duplicated lookup row doubles a total rather than raising. The mental model to carry is that len(result) has no relationship to len(left) unless you have checked the keys.
- validate= states the relationship you believe in
A price list has one row per product — that is an assumption, and validate='many_to_one' writes it into the code so pandas checks it on every run. It costs a uniqueness check and it converts a class of silent wrong answers into an exception at the point of the mistake. Every merge in a pipeline has an intended relationship; almost none of them say so.
- MergeError is a ValueError, which is why this contract works
pandas.errors.MergeError subclasses ValueError, so a caller catching ValueError catches it and the contract can promise ValueError without naming a pandas type. Worth checking rather than assuming when you rely on a library’s exception in your own interface — the inheritance is the part that makes it yours to promise.
The arithmetic is right. Count the rows coming out of the merge for a duplicated product code, and compare that with the number of orders.
merge takes a validate argument describing the relationship you expect. Orders may repeat a product and the price list must not, which is 'many_to_one'. MergeError subclasses ValueError, so it satisfies the contract as it stands.
validate catches the duplicate side; it does not catch an order with no match, because an inner join simply drops it. Compare the row count before and after, or merge with how='left' and check for missing prices — either way the undercount needs its own check.
import pandas as pd def total_revenue(orders, products): joined = orders.merge(products, on='code', ____='many_to_one') if len(joined) != len(____): raise ____( f'{len(orders)} orders matched {len(joined)} price rows' ) return float((joined['quantity'] * joined['price']).sum())
import pandas as pd
def total_revenue(orders, products):
# many_to_one: orders may repeat a product, the price list may not.
# MergeError subclasses ValueError, so a duplicated code already
# raises what the contract promises.
joined = orders.merge(products, on="code", validate="many_to_one")
# validate catches rows appearing twice; it cannot catch rows vanishing.
# An inner join silently drops an order with no matching product.
if len(joined) != len(orders):
raise ValueError(
f"{len(orders)} orders matched {len(joined)} price rows"
)
return float((joined["quantity"] * joined["price"]).sum())
validate= catches the multiplication — rows appearing more than once — and the row-count comparison catches the disappearance, where an order has no matching product and an inner join quietly omits it. One over-counts and the other under-counts, and a report that is wrong in either direction is equally useless. Neither check subsumes the other.
A many-to-one join must produce exactly as many rows as its left side. That one comparison catches every dropped row and costs nothing, and it is worth writing after any join whose row count you believe you know. When it fires, the two numbers in the message tell you immediately which direction the problem went.
The tempting fixes — drop_duplicates on the price list, or take the first match — both produce a number, and both decide on the caller’s behalf which of two disagreeing prices is correct. A duplicated row in a lookup table is a data problem somebody has to look at; a revenue report is not the place to guess about it.
A genuine many-to-many join is sometimes exactly what you want — every order line against every applicable tax rule, say — and then the multiplication is the point and validate='many_to_many' documents that you meant it. What you should never do is leave validate off because you are fairly sure the keys are unique: that is precisely the assumption this bug is made of. In SQL the same mistake has the same shape and no such keyword, which is why a unique constraint on the lookup table is the more durable fix — check the invariant where the data lives, not in every query that reads it.
- Merge with how='left' and indicator=True instead, and report which orders found no price — more work, and a much more useful error for whoever has to fix the data.
- Add a second key so the price depends on product and region, and work out what validate should say now.
- Deduplicate the price list first and compare the revenue you get against the refusal — then decide which one you would rather explain to finance.