In standard data (SQL), you join on exact matches (ID = ID). In time-series data (Finance, IoT), timestamps rarely match exactly, which is where Polars join_asof becomes especially useful.
- Table A (Trades): Trade happened at
10:00:05 - Table B (Quotes): Price was updated at
10:00:04
You want to know: “What was the price quote at the time of the trade?” A standard join fails. You need an As-Of Join (Join as of this time).
1. The Setup
import polars as pl
from datetime import datetime
# Trades happened at random times
trades = pl.DataFrame({
"time": [datetime(2025, 1, 1, 10, 0, 5), datetime(2025, 1, 1, 10, 0, 15)],
"ticker": ["AAPL", "AAPL"],
"quantity": [10, 20]
}).sort("time")
# Quotes happen every 10 seconds
quotes = pl.DataFrame({
"time": [datetime(2025, 1, 1, 10, 0, 0), datetime(2025, 1, 1, 10, 0, 10)],
"ticker": ["AAPL", "AAPL"],
"bid": [150.0, 150.5]
}).sort("time")2. The join_asof
We want to join quotes into trades. For the trade at 10:00:05, we want the quote from 10:00:00 (the most recent one before the trade).
# strategy="backward" looks for the last valid value
df = trades.join_asof(
quotes,
on="time",
by="ticker", # Match ticker symbols exactly
strategy="backward"
)
print(df)Output:
shape: (2, 4) โโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโโฌโโโโโโโโโโโฌโโโโโโโโ โ time โ ticker โ quantity โ bid โ โ --- โ --- โ --- โ --- โ โ datetime[ฮผs] โ str โ i64 โ f64 โ โโโโโโโโโโโโโโโโโโโโโโโชโโโโโโโโโชโโโโโโโโโโโชโโโโโโโโก โ 2025-01-01 10:00:05 โ AAPL โ 10 โ 150.0 โ <-- Matched with 10:00:00 โ 2025-01-01 10:00:15 โ AAPL โ 20 โ 150.5 โ <-- Matched with 10:00:10 โโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโโดโโโโโโโโโโโดโโโโโโโโ
This is the “killer feature” of Polars for anyone working in finance.
3. Advanced Strategy: Boundary Constraints with Tolerance
In a highly volatile production environment, you donโt always want to match back to any historical timestamp. If a market data feed drops and the last quote for a stock was published two hours ago, a blind backward search will still match that stale quote to your new trade execution.
To protect your algorithmic pipelines from stale data, utilize the tolerance argument to specify a strict temporal lookup window.
# Look backward, but ONLY match if a quote exists within a 2-second window
df_constrained = trades.join_asof(
quotes,
on="time",
by="ticker",
strategy="backward",
tolerance="2s" # Accurately drops connections to old, irrelevant data
)
print(df_constrained)Output:
shape: (2, 4) โโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโโฌโโโโโโโโโโโฌโโโโโโโ โ time โ ticker โ quantity โ bid โ โ --- โ --- โ --- โ --- โ โ datetime[ฮผs] โ str โ i64 โ f64 โ โโโโโโโโโโโโโโโโโโโโโโโชโโโโโโโโโชโโโโโโโโโโโชโโโโโโโก โ 2025-01-01 10:00:05 โ AAPL โ 10 โ null โ <-- Gap is 5s (Exceeds 2s tolerance) โ 2025-01-01 10:00:15 โ AAPL โ 20 โ null โ <-- Gap is 5s (Exceeds 2s tolerance) โโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโโดโโโโโโโโโโโดโโโโโโโ
Key Takeaways
- Standard SQL joins require exact matches, but time-series data often has timestamps that do not match exactly.
- Polars join_asof allows joining on the most recent timestamp before a specific time, which is crucial in finance and IoT.
- For instance, if a trade occurs at 10:00:05, you want the last quote before that time, like from 10:00:04.
- In volatile environments, use the tolerance argument to prevent matching stale data from a long time ago.
- This feature protects your algorithmic pipelines by ensuring relevant and timely data is used.
