Pandas joins: cardinality validation and missing lookup keys

Operation contract

The receipt rows may repeat a region, but the region directory must contain one row per region. validate="many_to_one" enforces that promise during the merge. The indicator column identifies a receipt whose region has no directory match. A duplicate directory entry is rejected instead of multiplying the receipt’s amount into several apparent records.

Failure and ownership boundary

Null join keys need special care: Pandas can match null keys to one another, unlike the usual SQL null comparison expectation. This fixture’s explicit non-null keys avoid that ambiguity. Joining by a display label is also not an identity policy. Python Unicode normalization: equality is not visual identity cannot replace stable business keys.

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

receipts = pd.DataFrame({"receipt_id": [41, 42, 43], "region": ["DEL", "DEL", "BOM"]})
directory = pd.DataFrame({"region": ["DEL"], "owner": ["north"]})
joined = receipts.merge(directory, on="region", how="left", validate="many_to_one", indicator=True)
print(joined.loc[joined["_merge"] == "left_only", "receipt_id"].tolist())
print(joined.loc[joined["_merge"] == "both", "receipt_id"].tolist())
try:
    receipts.merge(pd.DataFrame({"region": ["DEL", "DEL"]}), on="region", validate="many_to_one")
except pd.errors.MergeError:
    print("duplicate directory key rejected")

Output

[43]
[41, 42]
duplicate directory key rejected

Costs and limits

Join bookkeeping and output storage depend on key counts and implementation. Without a cardinality promise, repeated keys on both sides can create a product-sized result; input row limits alone do not cap merged output.

Common Mistakes

• Validate the promised lookup cardinality before trusting totals after a join.
• Declare null-key behavior; do not assume SQL null semantics.

Connected lessons

Python dictionaries: insertion order and duplicate-key replacement, Pandas nullable integers: missing amounts are not zero, Python Unicode normalization: equality is not visual identity.

Related Python operation checks

Django select_related: measure the foreign-key N plus one query pattern, Pandas chunked CSV aggregation: validate every chunk before returning totals.

Follow the related contract

Pandas merge with null keys: quarantine missing identifiers before matching.

Trace the related workflow

Pandas merge_asof: match an earlier event within a declared tolerance.

Trace the next boundary

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