AI-Written pandas: Use the Price That Applied When the Order Arrived

At 11.58, a customer orders a print listed at £18. At noon, the shop changes its list price to £22. Later, the owner asks an AI assistant to write a pandas script that adds the list price to every order. The assistant matches each order to the closest price change. The script runs, every order still has a row, and the £18 print now appears next to a £22 price. The nearest change happened two minutes later. That is exactly why it should not have been used.

This is a problem with the question the script answers. If you want the price in force when an order was placed, the rule is to find the latest applicable price that had already taken effect. Write that sentence down before you ask for code. It gives you something concrete to test when the output looks tidy.

Choose a direction before choosing a timestamp

The pandas reference for merge_asof describes three directions. With a backward search, the function finds the last right-hand row whose key is at or before the left-hand key. A forward search looks at or after it. A nearest search looks on both sides and picks by distance. For the print shop, the orders go on the left and the dated price changes on the right. Use the backward direction when a price starts at its effective timestamp and stays in force until the next change.

Suppose the print became £18 at 09.00 and £22 at 12.00. An order at 11.58 should get £18. An order at exactly 12.00 should get £22 if the new price applies from noon, inclusive. By default, merge_asof allows exact matches. You can disable them when the rule requires a strictly earlier record. If both systems record times only to the minute and two events share a minute, find out whether you have a better timestamp or a reliable sequence number. A switch in the code cannot establish the order of events that your records did not capture.

The same reasoning applies when a workshop changes its booking capacity or a publisher revises a wholesale rate. Start with what the report is meant to reconstruct. The advertised price, the amount charged and the price a member of staff could have seen at the time are different facts. Discounts can change the amount charged; a correction entered tomorrow may amend a price that was effective yesterday. If you need to reconstruct what staff knew at the time, a history of effective dates alone cannot answer that question. You would also need records of when each version became available.

Match the right product and keep the evidence

Ask the assistant to match within the appropriate group before looking at time. In pandas, the by argument can restrict matches to rows with the same product code. If a print has distinct prices by sales channel, the product and channel may both belong in that group. Otherwise the most recent row might describe a wholesale order when you are reviewing a retail sale.

Give order time and price effective time separate names, and keep both in the result. Make their time zones compatible and sort both inputs by their respective merge times in ascending order, as pandas requires. If you want the original report order later, preserve a stable order identifier before sorting. Keep the matched price record’s identifier too if one exists. It makes a disputed row much easier to check against the source.

Decide what missing information looks like. An order before the earliest known price should stay visible with no matched price. Do not fill the gap with today’s price or treat the blank as zero. If your history contains observations that become too old to trust, pandas supports a tolerance that limits the distance between timestamps. For a price that remains valid until replaced, an arbitrary time limit could instead hide a price that really did apply. Set the rule from the meaning of the history.

Give the assistant examples it can fail

Use a small fixture with made-up orders and price changes, and write down the expected matches before running the generated code. Include more than one product and deliberately place a price change just after an order. A script that chooses the nearest value may pass an ordinary example and fail exactly where timing matters most.

• Just before a change: The 11.58 order matches the 09.00 price even though the noon price is closer.
• At the boundary: The 12.00 order matches the noon price if the new rate starts at noon.
• Before the history begins: The order remains in the output, with no matched price.
• Another product changes: A different print’s price at 11.57 cannot supply the missing value.
• Two price rows share a timestamp: Flag the duplicate or resolve it with an explicit rule grounded in the source records.

In the full export, check that the same order IDs remain and that every matched price timestamp is at or before its order timestamp under the inclusive rule. Then count unmatched orders by product or channel and review a sample around known changes. As our guide to expanded order rows in pandas explains, checking the shape of the output is useful. Here, an unchanged row count only tells you that the orders survived. It cannot tell you whether their prices belong to the right moment.

Source

• pandas, merge_asof API reference, living documentation covering direction, sorting, grouping, tolerance and exact matches.