ml-002 · As-of price joins — Python

Problem statement

Open this practice in GitHub Codespaces runs in your browser; open this folder under practices/ after it starts

A retailer records every order in an orders table (order_id, product_id, order_datetime) and keeps prices in a price_history table (product_id, price, effective_from) with one row per price change. Reconstruct the price each order was actually billed at: the most recent effective_from at or before order_datetime for that product. First demonstrate why a naive join on product_id is wrong (it duplicates orders and can attach prices that only took effect after the order), then produce the correct one-row-per-order result with pandas.merge_asof, verify it against an independent filter + groupby implementation, and report how many orders have no price in effect yet. All data is generated in-script with numpy/pandas (fixed seed) — about 400 orders, 60 products, 1–5 price changes each.

Learning objectives

After completing this practice you should be able to:

  • explain why an equality join cannot express “price in effect at time t” and what two distinct errors it causes (row duplication, future-price leakage)
  • use pd.merge_asof with by=, direction="backward", and its sort requirements to perform a grouped temporal join
  • reproduce an as-of join from primitives (merge, filter, groupby(...).tail(1)) and use the agreement of two implementations as a correctness check
  • handle orders placed before a product’s first price without silently dropping them

Concepts and definitions

See concepts for the shared background. Python-specific notes: pd.merge_asof requires both frames sorted by the time key (it raises ValueError: left keys must be sorted otherwise), matches with <= when direction="backward" (the boundary case is included), and leaves NaN in the joined columns when no eligible right-hand row exists.

Input / output description

Item Description
Input orders: 400 rows — order_id (str), product_id (str), order_datetime (datetime64). price_history: ~175 rows — product_id (str), price (float), effective_from (datetime64), 1–5 rows per product. Both built in-script with numpy.random.default_rng(42).
Output One row per order with the effective price and its effective_from (NaN/NaT when no price was in effect yet), plus printed diagnostics: naive-join row count, count of future-price rows, no-price-yet order count, and correct vs shortcut revenue.

Before-coding checklist

My attempt

Work here. Run cells interactively; nothing below overwrites this section.

# Milestone 1: generate the synthetic tables (seeded rng, ~400 orders,
# 60 products, 1-5 price rows each; make some products first priced mid-year)
# Milestone 2: do the naive merge on product_id; count the extra rows and
# the rows whose effective_from is AFTER order_datetime
# Milestone 3: correct as-of join with pd.merge_asof
# (sort both frames by the time key, by="product_id", direction="backward")
# Milestone 4: independent check - merge, filter effective_from <= order_datetime,
# groupby(order_id).tail(1), re-join to orders; compare with milestone 3
# Milestone 5: report orders with no price yet, and correct revenue vs the
# "latest price per product" shortcut

Hint 1

Think about what a single order for a product with four price rows becomes after orders.merge(price_history, on="product_id") — four rows, only one of which can be right, and some of which may not even have existed yet at order time. You need a join whose matching rule is an inequality on time within each product, not equality on the key alone.

Hint 2

pd.merge_asof(left, right, left_on="order_datetime", right_on="effective_from", by="product_id", direction="backward") — but both frames must first be sorted by their time columns, and afterwards you will want to re-sort by order_id. For the cross-check version: after filtering candidates to effective_from <= order_datetime, sort by ["order_id", "effective_from"] and keep groupby("order_id").tail(1); then merge that back onto the full orders table with how="left" so no-price-yet orders are kept as NaN rather than dropped.

Complete solution

The official, tested script lives at solutions/solution.py. Run it with:

python practices/ml-002-asof-price-join/solutions/solution.py

Core of the solution (data generation and diagnostics omitted here):

def asof_price_join(orders, price_history):
    """Most recent price at or before each order's timestamp."""
    left = orders.sort_values("order_datetime", kind="mergesort")
    right = price_history.sort_values("effective_from", kind="mergesort")
    merged = pd.merge_asof(
        left, right,
        left_on="order_datetime", right_on="effective_from",
        by="product_id", direction="backward",
    )
    return merged.sort_values("order_id").reset_index(drop=True)


def filter_groupby_price_join(orders, price_history):
    """Same result without merge_asof - used as a cross-check."""
    candidates = orders.merge(price_history, on="product_id", how="inner")
    candidates = candidates[candidates["effective_from"] <= candidates["order_datetime"]]
    candidates = candidates.sort_values(["order_id", "effective_from"], kind="mergesort")
    latest = candidates.groupby("order_id", as_index=False).tail(1)
    out = orders.merge(latest[["order_id", "price", "effective_from"]],
                       on="order_id", how="left")
    return out.sort_values("order_id").reset_index(drop=True)

With seed 42 the script reports: naive join 1146 rows (x2.87 duplication) with 363 future-price rows; as-of join 400 rows with 22 no-price-yet orders; correct revenue 24803.74 vs 27153.39 for the latest-price shortcut.

Line-by-line explanation

  • sort_values("order_datetime") / sort_values("effective_from"): merge_asof walks both frames in time order with two pointers, so both sides must be sorted by their time keys or pandas raises. kind="mergesort" keeps the sort stable, which makes results deterministic when timestamps tie.
  • by="product_id": restricts the temporal match to rows of the same product — without it, an order could pick up another product’s price change.
  • direction="backward": selects the last right-hand row with effective_from <= order_datetime. The <= matters: a price effective at the exact order timestamp applies to that order.
  • merged.sort_values("order_id").reset_index(drop=True): restores a stable, comparison-friendly row order after the time-sorted merge.
  • In the cross-check, the filter effective_from <= order_datetime removes future prices, tail(1) after a stable sort keeps the latest eligible candidate per order, and the final how="left" merge re-attaches orders that lost all candidates (no price yet), preserving them as NaN.
  • main() asserts the two implementations agree with pd.testing.assert_series_equal — two independent algorithms agreeing is a much stronger check than eyeballing a few rows.

Design decisions

  • merge_asof as the primary implementation. It is O(n log n) in the sorts and linear in the merge, states the temporal intent directly, and is the standard pandas idiom for “value in effect at time t”.
  • A second, primitive-based implementation as a built-in cross-check. The filter + groupby version is slower (it materializes every candidate pair) but easy to reason about; agreement between the two catches subtle errors (sort direction, boundary handling) that either alone could hide.
  • No-price-yet orders kept as NaN, not dropped. Whether to exclude, impute, or flag them is a business decision; the join layer’s job is to make the gap visible, not to hide it.
  • Deliberately adversarial synthetic data. Late-priced products and multiple revisions per product guarantee both failure modes (duplication, future prices) actually occur, so the diagnostics are non-trivial.

Common mistakes

  • Forgetting to sort before merge_asof — pandas raises ValueError: left keys must be sorted, and the “fix” of sorting only one side still fails on the other.
  • Using direction="nearest" (or "forward") — “nearest” can pick a price from after the order if it is closer in time; only "backward" matches the billing semantics.
  • Filtering with < instead of <=, which misprices orders placed at the exact moment a price takes effect.
  • Deduplicating the naive join with drop_duplicates("order_id") — which row survives depends on row order, not on temporal correctness.
  • Using an inner join in the filter + groupby approach’s final step, silently deleting orders that had no eligible price.

Alternative solution

The solution script itself contains the main alternative (filter_groupby_price_join). A third idiom replaces tail(1) with an index lookup: candidates.loc[candidates.groupby("order_id")["effective_from"].idxmax()] — same result, and convenient when you want the whole winning row without a prior sort. For large data, polars offers join_asof(..., by="product_id", strategy="backward") with the same semantics and much better performance, and DuckDB has a native ASOF JOIN. Prefer the primitive-based approaches when your engine lacks an as-of join (e.g. plain SQL without window tricks: correlated subquery selecting max(effective_from) <= order_datetime).

Extensions and harder variants

  • Add an effective_to column instead (validity intervals) and implement the join as an interval containment check; handle overlapping intervals.
  • Add promotional prices in a second table and resolve precedence (promo wins over list price when both are in effect).
  • Scale to 10M orders: benchmark merge_asof vs filter + groupby vs polars join_asof, and measure the memory blow-up of the candidate-pair approach.
  • Make prices timezone-aware (store UTC, order timestamps in local time) and show what breaks if you compare naive and aware datetimes.
  • Backfill policy: for no-price-yet orders, attach the first price instead (direction="forward") and discuss when that is defensible.

Review questions

Answer out loud before expanding.

Each order matches every price row of its product, so an order for a product with k price versions contributes k rows. Summing price over that result adds each order’s product prices across all versions — it multiple-counts orders and mixes past and future prices, so the total is not revenue under any definition.

Both frames must be sorted ascending by their time keys (left_on/right_on or on); with by=, the frames must still be globally sorted by the time key. If unsorted, pandas raises ValueError: left keys must be sorted (or the right-side equivalent) rather than returning wrong answers — but only because it checks; the underlying two-pointer algorithm fundamentally assumes order.

The new price applies: backward matching uses effective_from <= order_datetime, and the new row has the largest qualifying timestamp. That is the intended semantics of “effective from”: at that instant the price is already in force. Using < would bill the old price for exactly-boundary orders — an off-by-one that is tiny in aggregate and painful in audits.

The inequality filter removes all candidate rows for an order placed before its product’s first price, so that order vanishes from the grouped result. A left join from the full orders table re-attaches it with NaN price, keeping the row count at one per order and making the missing-price cases visible for an explicit downstream decision instead of a silent drop.

It bills every order at the product’s final price regardless of when the order happened, so any order placed before the last change is mispriced. Here prices drift via multiplicative steps averaging above 1, so final prices tend to exceed earlier ones and the shortcut overstates revenue (27153.39 vs 24803.74 with seed 42). With falling prices it would understate instead — the sign of the bias depends on the price paths, which is what makes it dangerous.

Personal reflection

What was hard? What would you do differently? Date your entries.