Order Book Downsampling: Resample 1s Snapshots to 1m

Which resampling method fits each order book field?

Order book downsampling goes wrong when one operation is applied to every column. A limit order book export mixes three kinds of quantity, and each has its own valid reduction.

State is what the book looks like at an instant: bid and ask prices and sizes at each level. It is a point-in-time sample of a process, so the only valid reduction is to pick a moment, normally the bar edge. Flow is what happened during an interval: trades, adds, cancels, order flow imbalance. It is additive, so you sum it. Derived features such as spread, mid and top-N imbalance are scalars, so you can summarize them with mean, min, max, first and last.

The table maps each field to its operation.

Why can't I average order book levels across snapshots?

Level k is a rank, not a price. When the market moves, the best bid at 12:00:01 and the best bid at 12:00:40 are different prices, so their average describes no real order.

The sizes are worse: the average size sits at an average price where nobody was quoting. The result can look plausible, which is why the bug survives for a long time.

The example below shows two bid-side snapshots after the book ticked up by 0.1.

import pandas as pd

t1 = pd.DataFrame({"px": [100.0, 99.9], "sz": [5, 7]})   # level 1, 2 at t1
t2 = pd.DataFrame({"px": [100.1, 100.0], "sz": [4, 6]})  # book moved up

fake = (t1 + t2) / 2
print(fake)
#      px   sz
# 0  100.05  4.5   <- no order ever rested at 100.05
# 1   99.95  6.5   <- nor at 99.95

# Aligning by price instead of rank shows the real change:
real = pd.concat([t1.set_index("px").sz, t2.set_index("px").sz], axis=1,
                 keys=["t1", "t2"]).sort_index(ascending=False)
print(real)
# 100.1    NaN  4.0
# 100.0    5.0  6.0
# 99.9     7.0  NaN

How do I resample 1s order book snapshots to 1m in pandas?

Compute scalar features and event-level flow at 1s first, then reduce each group of columns with its own operation. Time-weighting matters when rows are irregular: weight each state by how long it persisted, using the gap to the next row (capped so an outage does not dominate).

This assumes a UTC DatetimeIndex, columns bid_px_1..N, ask_px_1..N, bid_sz_1..N, ask_sz_1..N, plus per-second buy_vol and sell_vol.

import numpy as np, pandas as pd

N = 5
R = dict(closed="right", label="right")

df = df.sort_index()
df["mid"] = (df.bid_px_1 + df.ask_px_1) / 2
df["spread"] = df.ask_px_1 - df.bid_px_1
bs = df[[f"bid_sz_{i}" for i in range(1, N+1)]].sum(axis=1)
as_ = df[[f"ask_sz_{i}" for i in range(1, N+1)]].sum(axis=1)
df["imb"] = (bs - as_) / (bs + as_)
df["r2"] = np.log(df.mid).diff() ** 2
df["signed_vol"] = df.buy_vol - df.sell_vol

# Cont et al. event-level OFI at the best quotes, computed on the FINE data
bp, bq = df.bid_px_1, df.bid_sz_1
ap, aq = df.ask_px_1, df.ask_sz_1
df["ofi"] = (np.where(bp >= bp.shift(), bq, 0) - np.where(bp <= bp.shift(), bq.shift(), 0)
              - np.where(ap <= ap.shift(), aq, 0) + np.where(ap >= ap.shift(), aq.shift(), 0))
df.loc[df.index[0], "ofi"] = np.nan

# duration each row persisted, capped at 5s
w = df.index.to_series().diff().shift(-1).dt.total_seconds().clip(upper=5).fillna(1)
df["w"] = w
for c in ("mid", "spread", "imb"):
    df[c + "_w"] = df[c] * df.w

state_cols = [c for c in df if c.startswith(("bid_px", "ask_px", "bid_sz", "ask_sz"))]
g = df.resample("1min", **R)

state = g[state_cols].last()
flow = g[["buy_vol", "sell_vol", "signed_vol", "ofi"]].sum()
feat = g.agg(mid_open=("mid", "first"), mid_high=("mid", "max"),
             mid_low=("mid", "min"), mid_close=("mid", "last"),
             spr_min=("spread", "min"), spr_max=("spread", "max"),
             imb_last=("imb", "last"), rv=("r2", "sum"),
             n_rows=("mid", "size"), wsum=("w", "sum"),
             mid_wn=("mid_w", "sum"), spr_wn=("spread_w", "sum"), imb_wn=("imb_w", "sum"))
feat["mid_twap"] = feat.mid_wn / feat.wsum
feat["spr_twa"] = feat.spr_wn / feat.wsum
feat["imb_twa"] = feat.imb_wn / feat.wsum
out = pd.concat([state, flow, feat.drop(columns=["mid_wn", "spr_wn", "imb_wn"])], axis=1)

# gaps: flag first, fill only short ones
out["gap"] = out.n_rows < 30
out[state_cols] = out[state_cols].ffill(limit=2)

What is the Polars equivalent for large data?

Polars group_by_dynamic gives the same right-closed, right-labelled windows and runs multithreaded, which matters once you hold days of multi-level data. The input must be sorted on the time column.

Use last() for state and sum() for flow, in a single pass.

import polars as pl

state = [pl.col(c).last() for c in lf.columns if c.startswith(("bid_", "ask_"))]
out = (
    lf.sort("ts")
      .group_by_dynamic("ts", every="1m", closed="right", label="right")
      .agg(
          *state,
          pl.col("buy_vol").sum(), pl.col("sell_vol").sum(), pl.col("ofi").sum(),
          pl.col("mid").first().alias("mid_open"),
          pl.col("mid").max().alias("mid_high"),
          pl.col("mid").min().alias("mid_low"),
          pl.col("mid").last().alias("mid_close"),
          pl.col("spread").max().alias("spr_max"),
          pl.col("r2").sum().alias("rv"),
          pl.len().alias("n_rows"),
      )
      .collect()
)
out.write_parquet("book_1m.parquet", compression="zstd")

How do I avoid look-ahead leakage when labelling bars?

Pandas defaults to closed='left', label='left' for minute frequencies. A bar labelled 12:00 then holds 12:00:00 to 12:00:59, so its last() value is from the future relative to its own label. Joining that to anything stamped 12:00 leaks up to 59 seconds.

With closed='right', label='right', the bar stamped 12:01:00 holds (12:00:00, 12:01:00], and everything in it was known at the stamp. Run this checklist before trusting any backtest on the result.

1. Set closed='right' and label='right' on every resample, and assert that out.index[i] >= the max source timestamp used in row i.
2. Compute features at bar t from rows at or before t only; never use centered windows or shift(-1) on features.
3. Build targets (for example the next 1m mid return) from the same grid: target = log(mid_close).shift(-1) - log(mid_close), then drop the final row.
4. Forward-fill stale snapshots only with a limit (for example 2 bars) and keep the gap flag, so outages are not silently turned into flat prices.
5. Normalize timestamps to UTC at ingestion and use the exchange clock if both exist; a timezone or feed-latency offset shifts all bars by a constant and leaks quietly.
6. Fit scalers and thresholds on the training period only, not on the full downsampled file.

Time bars or event bars, and what does a 59/60 reduction cost?

Fixed time sampling oversamples quiet periods and undersamples busy ones, so 1m bars during an open auction hide hundreds of updates while lunchtime bars hold almost none. Lopez de Prado style bars sample by activity instead.

Pick by research goal, not habit.

# phase sensitivity: same 1m grid, four snapshot phases
phases = {}
for off in ("0s", "15s", "30s", "45s"):
    phases[off] = df.resample("1min", closed="right", label="right", offset=off)["mid"].last()
# Run the study on each phase. A result that holds on one phase only is aliasing, not signal.

How do I check a downsampled order book is still valid?

If the source is event-based (L2 updates or MBO), rebuild the book by replaying events to each bar edge, then sample. Sampling from an already sampled feed adds a second layer of loss. If the source is already 1s snapshots, intra-second information is gone and no method restores it.

Then verify the output against the original before research starts.

1. Compare distributions: quantiles of mid_close and spread from the 1m file against the same-timestamp 1s rows should match exactly for last-based columns.
2. Assert no crossed or locked books: (ask_px_1 > bid_px_1).all(), and that bid prices strictly decrease and ask prices strictly increase with level.
3. Keep depth N fixed across files; padding missing levels with NaN, not zero, so size sums are not biased.
4. Check n_rows per bar and the gap flag; list bars with fewer rows than expected.
5. Store as Parquet with explicit float64 prices and a UTC timestamp column, partitioned by date.
6. Treat 1m results as prototypes: re-run candidates on finer data, because fill probability, queue position and latency depend on microstructure that 1m bars do not contain.