## 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.

```text
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:

```python
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.

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

```python
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.

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

```python
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.

4. Switch direction when the use case needs it:

```python
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.

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

```python
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