Download / Universe

Wide schema

Layout

One row per time slot, then one boolean column per asset, named by its ticker: true when the pair was listed during the slot. Wide covers a single quote currency, so the ticker alone names the pair.

close_ts	BTC	LUNC
2022-05-13T00:00:00Z	true	true
2022-05-14T00:00:00Z	true	true
2022-05-15T00:00:00Z	true	false

Use it with the wide OHLCV schema of the same quote and interval: the column BTC masks BTC_close, BTC_open, and so on, row for row.

One boolean column per asset. The examples null out every close of a wide OHLCV file whose pair wasn't tradable at that time.

pandas

Align the universe on the OHLCV timestamps and rename its columns to match, then where() keeps the closes where the mask is true.

import pandas as pd

closes = pd.read_parquet("ohlcv_usdt_1h.parquet").set_index("close_ts").filter(regex="_close$")
listed = pd.read_parquet("universe_binance_usdt_1h.parquet").set_index("close_ts")

# Null out every close of a pair that wasn't tradable at that time
mask = listed.reindex(closes.index).rename(columns=lambda base: f"{base}_close")
masked = closes.where(mask[closes.columns].fillna(False).astype(bool))

Polars

Join on close_ts, then keep each close only when its asset's column is true.

import polars as pl

closes = pl.read_parquet("ohlcv_usdt_1h.parquet").select("close_ts", pl.col("^.*_close$"))
listed = pl.read_parquet("universe_binance_usdt_1h.parquet")

# Null out every close of a pair that wasn't tradable at that time
bases = [c.removesuffix("_close") for c in closes.columns[1:]]
masked = closes.join(listed, on="close_ts", how="left", maintain_order="left").select(
    "close_ts",
    *[pl.when(pl.col(b)).then(pl.col(f"{b}_close")).alias(f"{b}_close") for b in bases],
)

Polars (lazy)

The same query on scanned files. collect_schema() gives the column names without reading any data.

import polars as pl

closes = pl.scan_parquet("ohlcv_usdt_1h.parquet").select("close_ts", pl.col("^.*_close$"))
listed = pl.scan_parquet("universe_binance_usdt_1h.parquet")

# Nothing is read yet: build the mask, then run it all on collect()
bases = [c.removesuffix("_close") for c in closes.collect_schema().names()[1:]]
masked = (
    closes.join(listed, on="close_ts", how="left", maintain_order="left")
    .select("close_ts", *[pl.when(pl.col(b)).then(pl.col(f"{b}_close")).alias(f"{b}_close") for b in bases])
    .collect()
)