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.
