pandas merge_asof nearest timestamp matching

TL;DR

Sort both frames by the timestamp key and use pd.merge_asof(left, right, on="ts") to attach the nearest prior row instead of requiring exact timestamp equality. Regular merges fail on timestamps because the two series almost never share exact values.

pandas merge_asof nearest timestamp matching

Use this when

• You need the latest prior event for each row (trades to quotes, events to sessions)
• A regular merge on timestamps returns zero rows
• You want nearest instead of strictly-backward matching

Not for this skill when

• Keys should match exactly, use a regular merge
• You need to regularize an irregular series, thats resampling
• You need to expand list-valued cells into rows, thats explode

Steps

1. Sort both frames by the timestamp column. merge_asof requires sorted keys and fails otherwise:

trades = trades.sort_values("ts")
quotes = quotes.sort_values("ts")
print(trades["ts"].is_monotonic_increasing, quotes["ts"].is_monotonic_increasing)

Expected output: True True. If either is False, the asof merge raises.

1. Run the backward asof merge, the default and most common case:

matched = pd.merge_asof(trades, quotes, on="ts")
print(matched[["ts", "price", "bid", "ask"]].head())

Expected output: each trade row carries the most recent quote at or before its timestamp. No exact timestamp equality required.

1. Bound how far back a match may reach with a tolerance, so stale quotes do not attach:

matched = pd.merge_asof(trades, quotes, on="ts", tolerance=pd.Timedelta("5s"))
print(matched["bid"].isna().sum())

Expected output: trades with no quote within 5 seconds get NaN instead of a stale quote. The NA count tells you how many trades had no fresh quote.

1. Switch direction when the use case needs it:

fwd = pd.merge_asof(trades, quotes, on="ts", direction="forward")
near = pd.merge_asof(trades, quotes, on="ts", direction="nearest")

Expected output: forward attaches the next quote at or after the timestamp, nearest attaches the closest in either direction. Backward stays the default for causal analyses where future data must not leak in.

1. Match within groups (per-symbol, per-user) with the by parameter:

matched = pd.merge_asof(
    trades.sort_values("ts"), quotes.sort_values("ts"),
    on="ts", by="symbol", tolerance=pd.Timedelta("5s"),
)

Expected output: each trade matches only quotes for its own symbol. Both frames must be sorted by the timestamp key globally, with the by groups as a secondary concern.

Variant phrasings

pandas asof join on datetime

merge_asof is the asof join. Remember both frames sorted by the key, and make the key dtype identical (both datetime64, same timezone handling) on both sides.

merge_asof left keys must be sorted error

Sort the left frame by the merge key first (step 1). The error is literal: unsorted keys are not allowed.

pandas join on nearest date

direction="nearest" with a tolerance gives you closest-date matching with a sanity bound. Without tolerance, every row matches something, however far away.

Why it happens

Time series from different sources are sampled at different instants, so exact-timestamp equi-joins match almost nothing. merge_asof replaces equality with ordering: for each left key it takes the nearest right key in the chosen direction, which is the semantically correct join for event-time data.

Edge cases

• Duplicate timestamps on the right side: asof takes the last one, dedupe if that is ambiguous.
• Timezone-aware vs naive keys never align, normalize both first.
• allow_exact_matches=False excludes rows with identical timestamps when you need strictly-prior semantics.
• For very large frames, consider whether the right side should be downsampled first, asof scans it per left row.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_IUHdqlvUYIV8jPQoS3PY1Q