Pandas merge(validate=): reject an unintended many-to-many join

Operation contract

Each receipt should have one currency record. The first merge uses one_to_one and produces two rows. A duplicate key in the right-hand frame would multiply receipt rows; the second merge raises MergeError instead of silently inflating totals. This is a shape check, not a check that every expected key was present.

Failure and ownership boundary

Missing joins, null-key matching and field-level validity need separate policy. Pandas can match null keys to each other, unlike ordinary SQL join expectations. Pandas joins: cardinality validation and missing lookup keys, Pandas merge with null keys: quarantine missing identifiers before matching and Pandas groupby: retain missing keys and define all-null totals cover those failure paths.

Tested environment

Dependency check: this program was executed on CPython 3.14.6 with pandas==3.0.6, numpy==2.5.3. Install these versions in a separate virtual environment. The download includes the recorded environment snapshot; no third-party package is part of the website runtime.

Working program

import pandas as pd
from pandas.errors import MergeError

receipts = pd.DataFrame({"receipt_id": [41, 42], "minor": [125, 75]})
currency = pd.DataFrame({"receipt_id": [41, 42], "unit": ["INR", "INR"]})
joined = receipts.merge(currency, on="receipt_id", validate="one_to_one")
print("joined:", len(joined))
duplicates = pd.DataFrame({"receipt_id": [41, 41], "unit": ["INR", "USD"]})
try:
    receipts.merge(duplicates, on="receipt_id", validate="one_to_one")
except MergeError:
    print("duplicate key rejected")

Output

joined: 2
duplicate key rejected

Costs and limits

Hash or sort join costs depend on pandas implementation and key distribution; this fixture has two rows. A materialized many-to-many result can grow as the product of duplicate group sizes, which is why cardinality is checked before using totals.

Common Mistakes

• validate checks key multiplicity, not business completeness.
• Null keys can match in pandas merges; define their handling explicitly.

Connected lessons

Pandas joins: cardinality validation and missing lookup keys, Pandas merge with null keys: quarantine missing identifiers before matching, Pandas groupby: retain missing keys and define all-null totals.