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.
