AI-Written pandas: Expanding a List Can Inflate Your Order Totals

A print seller asks an AI assistant for a report on which designs appear in orders. One order contains a map and a poster and is worth £60. Another contains a set of postcards and is worth £20. The script creates one row per design entry so the seller can count them. That part works. Then it totals the order values from those rows and reports £140. The shop took £80 across those two orders. The map and the poster now each sit beside a copy of the same £60.

The mistake is easy to miss because the individual values are correct. The £60 really is the order total. What changed was the meaning of each row. Before the transformation, a row represented an order. Afterwards, it represented an entry in an order’s design list. A formula that made sense in the first table can answer the wrong question in the second.

What pandas changes when it expands a list

The pandas reference for DataFrame.explode explains that it expands the elements of a list into separate rows. Other fields follow those rows, and the original index labels are repeated unless you ask for a new index. In the example, orders.explode('designs') gives you two rows for the first order and one for the second. Changing the index can make the output easier to read, but it cannot make two copies of a total into two separate sales.

Check empty lists too. The documentation describes an output row with a missing value for an empty list. A count of all expanded rows can therefore include an order with no design entry, while a count restricted to actual entries should not. Do not assume an empty list means the order is invalid. It could be a custom request awaiting classification. Decide what it means in your source and preserve enough information to distinguish a known empty list from missing input if your business treats them differently.

Pick the table that can answer the question

If you want total order value, use the original table after checking that it has one row for each eligible order. If you want design entries, use the expanded table and count the entries that qualify. If you want the number of orders containing each design, count distinct order references within each design. One order can legitimately count towards two designs, so adding those design-level counts does not give you the number of orders.

Be especially careful with the phrase ‘revenue by design’. You cannot infer the value of the map from the order total alone. Use actual line amounts if the source records them. If you only have a basket total, you can either report that limitation or make an explicit allocation for analysis and label it as such. Splitting £60 equally would assign £30 to each design. That is an assumption about allocation, not evidence that each design sold for £30.

Deduplicating by amount is no remedy: two different orders can both be worth £60. Taking the first row for each order from the expanded data can also hide problems if the source has conflicting totals or if a filter removed every expanded row for some orders. Keep the order-level totals in the order-level table and use a stable order reference to connect the views when needed.

Give the two tables names that tell the next person what they contain. ‘Orders’ and ‘Order design entries’ are clearer than ‘Raw’ and ‘Clean’. If you share the expanded table for a chart, consider leaving the order total out of that export altogether. When people do need to see it for context, label it as the whole order value and calculate any grand total separately. A clear layout prevents the next person from having to rediscover the same rule.

Give the assistant a fixture that exposes the mistake

Tell the assistant which columns describe the whole order and which describe a design entry. Ask for a test using the two orders above and write down the expected results before reading the script: two orders, three design entries and £80 in order value. Add one order with no designs and another with the same design listed twice. Decide whether the repeated design means two entries or a data error. That decision may change the entry count without changing how many orders contain the design.

• Check the source. Confirm that the order reference identifies one order and that the value is at the order level. If the source is already one row per item, use those line records instead of assuming another expansion is necessary.
• Check the transformation. Retain an explicit order reference. Compare entry counts with the source lists, and review empty or missing entries separately.
• Check the final report. Verify that the total order value matches the eligible source orders. Read each label as a customer or colleague would: ‘design entries’, ‘orders containing this design’ and ‘order value’ describe different things.

This is the counterpart to a different reporting error: losing an order because a grouping label is missing. Expanding a list can make an amount appear more than once; grouping can leave an amount out. Both are reasons to reconcile a report against its source before using it.

Before approving an AI-written transformation, finish the sentence ‘one row now means…’. Then decide which fields can still be added up and which need a different source. The design list can grow without a single extra pound changing hands.

Sources

• pandas API reference: DataFrame.explode.
• pandas 0.25.0 documentation, 2019, DataFrame API reference.