Pandas merge_asof: match an earlier event within a declared tolerance

Operation contract

The receipt readings occur at times 10, 20 and 30. A backwards join with a three-unit tolerance selects events at 8 and 19; an event at 25 is too old for the reading at 30, which remains missing. Both inputs are sorted on their join key before the operation. The printed list converts missing values to a visible marker rather than silently treating an absent match as zero.

Failure and ownership boundary

Time units, group ownership and tie rules must be explicit in a real telemetry pipeline. A forward match could leak future information into a historical feature. Pandas joins: cardinality validation and missing lookup keys, Pandas rolling windows: minimum observations and causal boundaries and Python time-series validation: fit on past rows and leave a declared gap describe different boundaries.

Tested environment

Dependency check: this program was executed on CPython 3.14.6 with pandas==3.0.6, numpy==2.5.3. Install these versions in a separate virtual environment. The download includes the recorded environment snapshot; no third-party package is part of the website runtime.

Working program

import pandas as pd

readings = pd.DataFrame({"minute": [10, 20, 30], "receipt_id": [41, 42, 43]})
events = pd.DataFrame({"minute": [8, 19, 25], "state": ["opened", "checked", "closed"]})
joined = pd.merge_asof(readings, events, on="minute", direction="backward", tolerance=3)
print(["missing" if pd.isna(value) else value for value in joined["state"]])
print(joined["receipt_id"].tolist())

Output

['opened', 'checked', 'missing']
[41, 42, 43]

Costs and limits

For sorted keys, an as-of join scans/looks up nearby rows without a Cartesian product; materialized output still scales with left row count. Sorting unsorted inputs costs additional O(n log n) work and memory. This fixture has three rows per side.

Common Mistakes

• A backward join and a forward join answer different causal questions.
• Missing matched state is not the same as an event with state zero.

Connected lessons

Pandas joins: cardinality validation and missing lookup keys, Pandas rolling windows: minimum observations and causal boundaries, Python time-series validation: fit on past rows and leave a declared gap.