How do I join two tables on the nearest key rather than an exact match in pandas?

The short version

Use pd.merge_asof to join on the nearest key instead of an exact match, which is the standard way to line up two time series whose timestamps do not coincide. Both frames must be sorted on the join key. By default it matches backwards, pairing each left row with the closest right key that is less than or equal to it.

A worked example

import pandas as pd

left = pd.DataFrame({"t": [1, 5, 10], "val": ["a", "b", "c"]})
right = pd.DataFrame({"t": [1, 4, 11], "tag": ["x", "y", "z"]})

print(pd.merge_asof(left, right, on="t"))

    t val tag
0   1   a   x
1   5   b   y
2  10   c   y

Left row t=5 has no exact match, so it takes the nearest earlier right key, t=4 (tag y). Row t=10 also falls back to t=4, because the only later key, t=11, is after it and the default looks backward.

Why an ordinary merge does not work here

A plain merge on t keeps only exact matches, so rows at t=5 and t=10 would be dropped entirely, because no right row has those exact keys. merge_asof is designed precisely for the case where you want the closest available value rather than an exact hit, which is extremely common with sensor readings, prices and events that are timestamped independently.

The sorting requirement

Both frames must be sorted ascending on the join key, or merge_asof raises. This is not optional, because the algorithm walks both frames in order. Sort first every time.

left = left.sort_values("t")
right = right.sort_values("t")

Controlling the direction

The default direction="backward" takes the nearest earlier key. Use "forward" for the nearest later key, or "nearest" for whichever is closest in either direction.

pd.merge_asof(left, right, on="t", direction="nearest")

Pick the direction that matches your meaning. For "the most recent price at or before this trade", backward is right. For "the next scheduled event after this moment", forward is right.

Matching within groups

To match nearest keys separately within groups (per city, per product), pass by, so the nearest-key search never crosses group boundaries.

pd.merge_asof(left, right, on="t", by="city")

A tolerance limit

If a match should only count when it is close enough, tolerance caps the allowed gap, leaving NaN when the nearest key is too far away. This prevents pairing a reading with a stale value from long before, which would otherwise pass silently.