AI-Written pandas: Two Missing IDs Can Make a False Match

Four orders go into a report and six come out. The script runs without complaint. It even marks two orders as having found their products in the catalogue. But those orders had no product IDs, and the catalogue rows they picked up were unfinished drafts. The report has manufactured a relationship from two blanks.

This can happen when an AI-written pandas script joins two tables. The join looks routine: take the orders, look up the product ID and attach the corresponding label. The part to check is what happens when the ID is missing on both sides. A missing ID tells you why a lookup cannot proceed; it does not tell you which product belongs to an order.

A four-row example you can reason through

Imagine an independent print shop with four orders. Order A lists product P-17, a print in the catalogue. Orders B and C lack product IDs. Order D lists P-99, which has no catalogue entry in this export. The catalogue contains P-17 and two unfinished entries whose IDs are also missing. In this example the missing values are represented by Python’s None.

A left merge keeps the orders as the starting point. The pandas merge reference warns that missing join keys can match one another, unlike ordinary SQL equality joins. The pandas merging guide explains the other half of the result: repeated keys on both sides produce all pairings for that key.

In a check with pandas 2.2.3, the left merge returned six rows: A appeared once, B and C each appeared twice, and D appeared once with no match. The two missing IDs on the order side matched both missing IDs on the catalogue side. Each duplicated order picked up both draft labels.

The extra rows are visible if you count them. A false match need not be so obvious. If the catalogue had just one draft with a missing ID, this example would still return four rows, but B and C would both carry a label they had no basis for receiving. Check the relationships as well as the total.

Keep the orders; restrict the lookup

For the print shop, the proposed rule is simple: every order stays in the report, but only a populated product ID can supply a catalogue label. A missing ID and a populated ID that did not match both need follow-up. They need different follow-up, because one requires finding the original identifier and the other requires investigating a specific identifier.

Use catalogue[catalogue["product_id"].notna()] as the lookup table. This removes rows with missing IDs from the lookup, without removing any orders. The pandas missing-data guide explains why isna() and notna() are the appropriate tests for missing values. Count and retain the excluded catalogue rows separately if someone needs to repair them.

The left merge can then use on="product_id", how="left", validate="many_to_one" and indicator=True. The validation checks uniqueness of the lookup keys, so two competing records for P-17 cause a failure to investigate. The indicator records whether a key matched; check the order’s original product ID separately to distinguish missing keys from unmatched populated keys.

With that filtered lookup, the same test returned four rows. A had the print label; B, C and D remained in the report without catalogue labels. The indicator marked all three as left-only. Checking the original product ID then distinguishes B and C, which need identifiers, from D, which needs a lookup investigation.

Test the rule before using it on a real export

Give an AI assistant a small example with expected results rather than asking it to clean the data. The test should be small enough to trace by hand. Specify which columns carry identities, which duplicates are legitimate and what to do with a missing or unknown key.

• One known key: expect the matching label exactly once for each order row.
• A missing key on each side: expect the order to remain with no catalogue label. Repeat with two missing catalogue keys to expose row multiplication.
• A populated but unknown key: retain the identifier and show it as unresolved.
• A duplicated populated catalogue key: expect a validation error, then check whether the catalogue needs a correction or a more specific key.

Finally, inspect the import before assuming a blank is already missing. If a field arrives as an empty string, a space or a literal placeholder, define which values your workflow will treat as missing. Test those cases with the export you actually receive. Replacing every unknown with the same invented ID on both sides would reintroduce the very match you were trying to prevent.

Give unresolved rows a useful destination

Make the exception report actionable. Keep the original order reference, the raw product ID where supplied, and a reason such as missing ID, unmatched ID or conflicting catalogue entries. That lets someone repair the source without using a plausible label as a substitute for evidence.

MaryChuks.com’s guide to preventing deleted records from returning covers a related question about which copy of a catalogue should be trusted. Here, the immediate decision is narrower: which rows are eligible to establish a relationship in this report?

A useful review asks whether each label can be traced to a valid key and whether each order that could not be matched is still accounted for. If a script cannot answer both, a tidy-looking report is too early to hand over.

Sources

Primary references: pandas merge API for version 2.2.3 (released 20 September 2024), merging guide and missing-data guide. The print shop and order records are fictional; the two merge outcomes were reproduced in pandas 2.2.3.