blog / 2026-10-31

Building an expiry-day dataset in Python: pandas and DuckDB recipes

Turn a pack of daily option files into one table of expiry sessions, with minutes to expiry, nearest strike and normalised column names. Works with old and new file layouts.

A pack arrives as thousands of daily files, one per trading day per expiry. For expiry-day research you want something else: one table holding only the expiry sessions, with the same column names throughout and a few derived fields. Here is a pipeline that does that in a few minutes on a laptop.

1. Pick the expiry files

An expiry session is a file whose trade date equals its expiry date. Both are in the file name, so no file has to be opened to choose.

import re
from pathlib import Path

pat = re.compile(r"_OPT_1min_(\d{4}-\d{2}-\d{2})_exp(\d{4}-\d{2}-\d{2})")
files = [f for f in Path("data").rglob("*.parquet")
         if (m := pat.search(f.name)) and m[1] == m[2]]

2. Normalise the two layouts

Newer files use datetime, strike_price, right, open_interest, expiry_date. Older ones use timestamp, strike, option_type, oi, expiry. Map both to one set:

import pandas as pd

RENAME = {"datetime": "ts", "timestamp": "ts", "strike_price": "strike",
          "right": "cp", "option_type": "cp", "open_interest": "oi",
          "expiry_date": "expiry"}

def load(f):
    d = pd.read_parquet(f).rename(columns=RENAME)
    d["cp"] = d["cp"].astype(str).str[0].str.upper().map({"C": "CE", "P": "PE"})
    d["expiry"] = pd.to_datetime(d["expiry"], dayfirst=True).dt.normalize()
    return d[["ts", "strike", "cp", "open", "high", "low", "close", "volume", "oi", "expiry"]]

exp = pd.concat(map(load, files), ignore_index=True)

3. Add minutes to the close

close_time = exp["expiry"] + pd.Timedelta(hours=15, minutes=30)
exp["mins_left"] = (close_time - exp["ts"]).dt.total_seconds() / 60

Bars are labelled at their start in IST, so the 15:29 bar shows one minute left, and a bar stamped 15:30 shows zero.

4. Or skip pandas and query with DuckDB

DuckDB reads the Parquet files in place, which is handy when a pack is larger than your memory:

import duckdb
con = duckdb.connect()
con.sql(r"""
  SELECT strike_price, "right", max(volume) AS peak_minute_volume
  FROM read_parquet('data/**/*_OPT_1min_*.parquet', filename = true)
  WHERE regexp_extract(filename, '_OPT_1min_(\d{4}-\d{2}-\d{2})', 1)
      = regexp_extract(filename, '_exp(\d{4}-\d{2}-\d{2})', 1)
  GROUP BY ALL ORDER BY peak_minute_volume DESC LIMIT 20
""").show()

Use one layout per query, or run the query separately on old and new files and union the results after renaming.

5. Save it once

Write the result to a single Parquet file partitioned by expiry. Every later study, from straddle decay to pinning tests, starts from that one table instead of thousands of files.