AI-Written pandas: Match the Order ID, Not the Row Number

A small ceramics shop exports its orders and its parcel tracking numbers from two different tools. An AI assistant writes a short pandas script to add a tracking column to the order file. The script runs, every row has a tracking number and the totals agree. Then a customer clicks the link and sees a parcel travelling to the wrong town. The values were real. They were attached to the wrong orders.

This can happen when two tables have the same number of rows but use different sorting rules. It can also happen when the script relies on pandas to align rows using labels that do not actually identify the orders. A successful assignment tells you that pandas could put values in a column. To know whether the result is useful, you need to know what made each value belong to its row.

Find out what pandas is matching

A pandas DataFrame is a table with labels for its rows and columns. A Series is a single labelled sequence. The pandas indexing guide explains that assigning a Series to a DataFrame column aligns its values to the table’s row labels. A row without a corresponding label receives a missing value. When the labels match, pandas can put the values in the right places even if the Series arrives in a different order. That behaviour is useful when the labels carry the meaning you intend.

For example, suppose the order table has rows labelled A17, B24 and C39. A tracking Series with labels C39, A17 and B24 can arrive in that order and still align correctly. If both files instead have ordinary row labels 0, 1 and 2, alignment also succeeds. But if row 0 is A17 in one file and C39 in the other, the script has matched row labels while breaking the link between order and parcel. Nothing has to be blank for the result to be wrong.

Sorting the parcel file to match one sample may make the bug disappear without fixing the rule. The next export may contain an order that has not shipped, or the courier may return rows in another order. Ask what both systems agree identifies the same order. If neither file carries that identifier, stop and get it from the source. Matching names or addresses by eye is a separate reconciliation job, not a reliable substitute.

Make the order ID explicit

If the shop expects one tracking number per order, build a lookup from checked order IDs to tracking numbers. Then map the order table’s ID column through that lookup. The pandas Series.map reference documents that the result retains the original Series index, while a key absent from a plain dictionary produces a missing value. In an AI-generated script, a line such as orders["tracking"] = orders["order_id"].map(tracking_by_order) makes the intended match visible: the ID value selects the number, and the resulting Series retains the order table’s row labels.

Check for duplicate IDs in the parcel file before building that lookup. If B24 appears twice because the shop sent two boxes, the problem is not which tracking number should win. The report needs to represent two shipments, or the team must define which one to show and why. Likewise, a blank ID should be reported separately instead of treated as a usable key. A tracking value that is missing and a parcel row that never matched an order may need different follow-up.

Treat IDs as identifiers at import. If an export distinguishes 0042 from 42, converting both to numbers erases that distinction before pandas can match them. The column also has to be the one you think it is. Our guide to checking CSV headers at import covers a different way an apparently familiar field can arrive under an unexpected name.

Test identity instead of counting rows

Make a tiny fixture with three invented orders and deliberately different tracking numbers. Keep the order file in its original order and shuffle the parcel file. Check the full order-to-tracking pairs, not just the number of results. Remove one parcel row and run the test again. If the missing parcel makes every later number move up a row, you have exposed a positional assumption that the original sample could not show.

• Reorder each file independently. The same order should still receive the same tracking number.
• Remove one tracking record. Only the relevant order should lose its match, and the report should make the absence visible.
• Add an unexpected duplicate and a blank identifier. Check that the script reports them instead of quietly choosing a value.
• Check a legitimate split shipment separately. Confirm that the output preserves the number of parcels the customer actually needs to track.

There are jobs where position is the correct rule. A transformation may produce exactly one value per input row in a documented order. Even then, check that nothing has sorted or filtered the input between those steps. Turning a Series into an unlabelled array just to suppress alignment removes information that might have helped catch a mismatch.

Give the operator a short reconciliation summary: orders matched, orders still waiting for a parcel, and parcel records whose IDs are absent from the order file. A row can be missing because the exports were taken at different times. Keep the exception visible so the operator can distinguish a timing difference from a broken match.

Before you send the output anywhere, pick one order and trace its tracking number back to the parcel record. That small check expresses the promise the script needs to keep: the number belongs to this order, regardless of where either happens to appear in a file.

Sources

• pandas user guide: Series assignment and index alignment
• pandas API reference: Series.map