Fuzzy Time Series Matching with merge_asof() in Pandas

Question

Given `trades` (trade_time, ticker, shares) and `quotes` (quote_time, ticker, bid_price), match each trade to the most recent quote available on or before the trade time using `pd.merge_asof()`.

Accepted Answer

Optimal Solution:
import pandas as pd

trades['trade_time'] = pd.to_datetime(trades['trade_time'])
quotes['quote_time'] = pd.to_datetime(quotes['quote_time'])

# Both DataFrames must be sorted by the merge key
trades = trades.sort_values('trade_time')
quotes = quotes.sort_values('quote_time')

matched = pd.merge_asof(
    trades,
    quotes,
    left_on='trade_time',
    right_on='quote_time',
    by='ticker',
    direction='backward'
)

Technical Explanation:
`pd.merge_asof()` performs an approximate timestamp match, selecting the nearest row where `quote_time <= trade_time` (`direction='backward'`).

trade_time	ticker	shares	quote_time	bid_price
2026-01-01 09:30:05	AAPL	100	2026-01-01 09:30:00	150
2026-01-01 09:31:00	AAPL	50	2026-01-01 09:30:30	150.25
2026-01-01 09:30:10	MSFT	30	2026-01-01 09:30:05	300

trade_time	ticker	shares	quote_time	bid_price
2026-01-01 09:30:05	AAPL	100	2026-01-01 09:30:00	150
2026-01-01 09:30:10	MSFT	30	2026-01-01 09:30:05	300
2026-01-01 09:31:00	AAPL	50	2026-01-01 09:30:30	150.25