Validate pandas joins before scoring

Three predictions became five rows before a single score was calculated. A synthetic check on 16 October 2026 reproduced that result with pandas 3.0.1 and Python 3.14.6: the reference table contained the same case twice, so the join repeated its matching predictions.

Validate pandas joins before scoring with two separate checks: lookup-key uniqueness and complete prediction coverage. My rule is that an evaluation denominator starts at the join, not at the metric. A successful merge is not evidence that the intended population survived it.

Validate pandas joins for uniqueness and coverage

The pandas merging guide explains that repeated keys can multiply the matching rows. For predictions keyed a, a, b and references keyed a, a, b, each of the two a predictions matches both a references. Four rows for a, one for b. Nothing has crashed.

Use validate="many_to_one" when several predictions may legitimately share one reference case. The merge API checks that the right-hand keys are unique. It does not establish that every prediction found a reference. For that, use a left join with indicator=True and reject anything other than both.

An inner join is the wrong diagnostic here: the missing prediction disappears before you inspect coverage. A neat output table can therefore hide the very case you needed to investigate.

A runnable pandas label-attachment check

Save this as merge_check.py and run python3 merge_check.py in an environment with pandas installed. It uses finite synthetic frames and explicit string case IDs; the tested version is 3.0.1, not a claim about every release.

import pandas as pd

def attach_labels(predictions, labels):
    keys = ["case_id"]
    for frame in (predictions, labels):
        if frame[keys].isna().any().any():
            raise ValueError("missing case identity")
    joined = predictions.merge(
        labels, on=keys, how="left",
        validate="many_to_one", indicator=True,
    )
    if not joined["_merge"].eq("both").all():
        raise ValueError("prediction without a reference label")
    return joined.drop(columns="_merge")

if __name__ == "__main__":
    predictions = pd.DataFrame({"case_id": ["a", "a", "b"]})
    labels = pd.DataFrame({"case_id": ["a", "b"], "label": [1, 0]})
    joined = attach_labels(predictions, labels)
    assert joined["label"].tolist() == [1, 1, 0]
    assert len(joined) == len(predictions)
    print("three predictions, three labelled rows")

The null check is deliberate. pandas matches null join keys to each other, unlike usual SQL equality-join behaviour. Two missing identities should not become a convincing match merely because they are missing together.

What the synthetic merge tests established

The exact helper passed, including checks for duplicate references, missing matches and null identities on either side. These three comparison cases show why the two gates need separate fixtures:

Join arrangement	Prediction rows	Output rows
Unchecked left join with duplicate reference a	3	5
Checked left join with references a, b	3	3
Inner join with only reference a	3	2

Local pandas 3.0.1 results; the last arrangement demonstrates lost coverage, not a recommended repair.

With only reference a, a left join still passed many_to_one; the helper's indicator check then rejected the missing b. Empty predictions, an unused reference and reversed reference order also passed their expected checks. These are behavioural fixtures, not a performance benchmark or a production evaluation.

My judgement for an evaluation pipeline

For a hypothetical multi-model evaluation, I would allow repeated prediction case IDs only when they represent intentional runs or models. If reference labels vary by dataset version, include that version in the join key. A unique but wrongly chosen key can attach the wrong answer perfectly consistently.

Don't repair a failed uniqueness check by blindly dropping duplicates. Conflicting reference labels need an explicit resolution policy. Likewise, this helper checks identity coverage, not whether a matched label is non-null or correct. It assumes no existing _merge column and no conflicting payload column names; validate the schema separately.

Empty input is another policy decision. The helper accepts it, but a scoring job may need to fail when no predictions arrive. Keep that assertion at the job boundary. Row conservation is useful evidence; it isn't the whole definition of a valid evaluation.

What's in it for you

• Add one duplicated reference and one missing reference to your evaluation fixtures; they should fail for different reasons.
• Check the joined population before calculating a score, while the offending identities are still available to diagnose.

Your metric cannot repair a population the join has already changed.