Python

merge_asof: time joins with no look-ahead leak

Match each event to the last known value of another series (backward direction), with a maximum tolerance.

Prerequisites

Python 3.9+, pandas

Python

import pandas as pd

trades = trades.sort_values("timestamp")
quotes = quotes.sort_values("timestamp")

# Chaque trade reçoit la dernière cotation CONNUE (pas de fuite future)
enriched = pd.merge_asof(
    trades,
    quotes[["timestamp", "bid", "ask"]],
    on="timestamp",
    direction="backward",
    tolerance=pd.Timedelta("2s"),   # au-delà : NaN plutôt qu'une valeur périmée
)
enriched["spread"] = enriched["ask"] - enriched["bid"]

Result

>>> enriched.head(3)
                timestamp  symbol  qty      bid      ask   spread
0 2026-06-01 09:00:00.120  EURUSD  1.0  1.08431  1.08444  0.00013
1 2026-06-01 09:00:02.480  EURUSD  0.5  1.08433  1.08447  0.00014
2 2026-06-01 09:00:05.910  EURUSD  2.0      NaN      NaN      NaN

>>> enriched["bid"].isna().sum()   # trades sans cotation < 2 s
1

Pandasmerge_asofTime SeriesFinance

Related snippets

← Back to the Data Lab