Nearest-key and range joins: merge_asof

Some joins have no equal key to match on. A train’s position report at 06:20 belongs with the last station the train was due to pass before 06:20, and a meter reading of 250 units belongs with the tariff slab whose range contains 250. A nearest-key joinnearest-key join: A join that selects a right-side partner by ordered key distance under a chosen direction, group and eligibility rule. attaches to each row the row of another table whose key is closest in one direction, usually the latest one at or before it. Range joins attach the interval that contains a value. pandas writes nearest-key joins with pd.merge_asof, and range joins with pd.cut or an IntervalIndex, because merge only matches equal keys. This lesson builds both on invented railway and electricity data, then shows the same joins in polars and DuckDB.

Direction selects the eligible ordered key

merge_asof works like a left join in which “equal” is replaced by “nearest in one direction”. For every left row it searches the right frame’s key column and takes one row:

• direction="backward", the default, takes the latest right key at or before the left key.
• direction="forward" takes the first right key at or after it.
• direction="nearest" takes whichever of those two is closer.
• by= adds an exact match on other columns first, so a train only looks at its own timetable.
• tolerance= caps how far away a match may be, so an hours-old record is not passed off as current.

Analysts need nearest-key joins whenever one table records events and the other records states that change over time: the exchange rate in force at each payment, the price list valid on each order date, the last known status of a parcel at each scan. Every left row stays in the result once, as in a left join, so a nearest-key join never fans out. The price is a strict rule: both frames must be sorted by their key, or pandas raises a ValueError (pandas.merge_asof). Follow T202’s 06:20 key back to 06:00; key ordering alone does not establish information availability.

A ping searches only its own train’s timetable

Two invented trains send position pings, and the timetable says when each is due at each station. Each ping is matched with the last station its own train was due to pass:

import pandas as pd

pings = pd.read_csv("rail/pings.csv", parse_dates=["ping_at"])
timetable = pd.read_csv("rail/timetable.csv", parse_dates=["due_at"])

matched = pd.merge_asof(
    pings.sort_values("ping_at"),
    timetable.sort_values("due_at"),
    left_on="ping_at",
    right_on="due_at",
    by="train",  # match within the same train only
)
matched["minutes_since"] = ((matched["ping_at"] - matched["due_at"]).dt.total_seconds() // 60).astype("Int64")

hhmm = {c: matched[c].dt.strftime("%H:%M") for c in ["ping_at", "due_at"]}
print(matched.assign(**hhmm).sort_values(["train", "ping_at"]).to_string(index=False))

train ping_at station due_at  minutes_since
 T101   06:05     AMP  06:00              5
 T101   06:41     BLG  06:30             11
 T101   07:30     CDP  07:10             20
 T101   08:10     DVN  07:45             25
 T202   05:50     NaN    NaN           <NA>
 T202   06:20     XRD  06:00             20
 T202   07:05     YLM  06:45             20
 T202   07:40     ZTP  07:30             10

Train T101’s 07:30 ping gets CDP, due at 07:10: its time is 20 minutes after the scheduled stop. A timetable match does not establish the train’s observed position or that it actually passed the station. The T202 ping at 05:50 comes before its first due time and gets no station: NaN, as a left join gives a row without a partner. The same pings with each direction:

import pandas as pd

pings = pd.read_csv("rail/pings.csv", parse_dates=["ping_at"]).sort_values("ping_at")
timetable = pd.read_csv("rail/timetable.csv", parse_dates=["due_at"]).sort_values("due_at")

table = pings[["train", "ping_at"]].copy()
for direction in ["backward", "forward", "nearest"]:
    m = pd.merge_asof(pings, timetable, left_on="ping_at", right_on="due_at", by="train", direction=direction)
    table[direction] = m["station"].to_numpy()

table["ping_at"] = table["ping_at"].dt.strftime("%H:%M")
print(table.sort_values(["train", "ping_at"]).to_string(index=False))

train ping_at backward forward nearest
 T101   06:05      AMP     BLG     AMP
 T101   06:41      BLG     CDP     BLG
 T101   07:30      CDP     DVN     DVN
 T101   08:10      DVN     NaN     DVN
 T202   05:50      NaN     XRD     XRD
 T202   06:20      XRD     YLM     XRD
 T202   07:05      YLM     ZTP     YLM
 T202   07:40      ZTP     NaN     ZTP

Backward chooses the latest eligible key at or before the event. That models a rate in force only when the key’s meaning and availability rules support it; a backdated correction published later can still introduce information that was unknown at the event. Forward answers “what comes next”, and nearest suits two sensors whose clocks drift a little in both directions. Compare the 06:00 and 06:45 candidates within T202 before selecting a direction.

Interval closure decides where a value belongs

Range joins match a value with the interval that contains it. Electricity tariffs are a typical case: the rate depends on which slab a reading falls into. Tax brackets, age bands in a survey, delivery zones by distance and discount tiers by basket value are the same problem with other labels.

import pandas as pd

meters = pd.DataFrame({"meter": ["M1", "M2", "M3", "M4", "M5"], "units": [40, 100, 101, 250, 640]})

edges = [0, 100, 200, 500, float("inf")]
names = ["(0, 100]", "(100, 200]", "(200, 500]", "(500, inf)"]
meters["slab"] = pd.cut(meters["units"], bins=edges, labels=names)

print(meters.to_string(index=False))

meter  units       slab
   M1     40   (0, 100]
   M2    100   (0, 100]
   M3    101 (100, 200]
   M4    250 (200, 500]
   M5    640 (500, inf)

pd.cut takes the slab edges as bins and gives each interval a label. Its intervals are closed on the rightright-closed interval: An interval that includes its upper endpoint. In (a, b], values equal to b are included and values equal to a are excluded. by default, so a reading of exactly 100 lands in the first slab and 101 in the second (pandas.cut). An IntervalIndex holds the same intervals as an index and can look values up directly, and SQL engines such as DuckDB write range joins as a join on an inequality, units > lo AND units <= hi. Which side of the boundary is closed decides real money, so write it down with the slab table.

Write continuous boundaries as (0, 100] and (100, 200]. The label “101 to 200” would misdescribe a fractional reading such as 100.4, even though the numeric interval contains it. Follow exactly 100 and fractional 100.4 across the shared edges and identify their containing intervals.

Check yourself

merge_asof in depth: tolerance, exact matches and sorting rules Pro: opens with a Pro, Premium or Ultimate pass

Range joins: slab boundaries, IntervalIndex and SQL, with a bill Pro: opens with a Pro, Premium or Ultimate pass

The same joins in polars and DuckDB, and a silent wrong answer Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• A nearest-key join attaches to each row the closest row of another table in one direction; pandas writes it as merge_asof, which keeps every left row once.
• Sort pandas inputs globally by the time key, use by= for exact group identity, and choose direction, availability and eligibility rules from the event-state contract.
• Range joins attach the interval that contains a value: pd.cut and an IntervalIndex in pandas, an inequality join in SQL. Decide which end of each interval is closed, and write it down.