# Measure real bid-ask spreads of Nifty ATM/near-ATM weekly options from Upstox live ATM feed snapshots (Oct-2026). Read-only.
import json,glob,pandas as pd,numpy as np
rows=[]
for f in sorted(glob.glob('/workspace/market-live-stack/feeds/upstox-atm-options/2026-*.jsonl')):
    for ln in open(f):
        try: j=json.loads(ln)
        except: continue
        if j.get('status')!='ok': continue
        ts=pd.Timestamp(j['ts']); hm=ts.strftime('%H:%M')
        for s in j.get('strikes',[]):
            for t in ('ce','pe'):
                o=s.get(t) or {}
                b,a=o.get('bid'),o.get('ask')
                if not b or not a or a<=b*0 or a<b: continue
                rows.append(dict(date=ts.strftime('%Y-%m-%d'),hm=hm,expiry=j['expiry'],dte=(pd.Timestamp(j['expiry'])-pd.Timestamp(ts.date())).days,offset=s['offset'],type=t,bid=b,ask=a,mid=(a+b)/2,spread=a-b,bq=o.get('bid_qty'),aq=o.get('ask_qty')))
D=pd.DataFrame(rows); D=D[(D.hm>='09:15')&(D.hm<='15:30')]
D['tod']=pd.cut(D.hm.map(lambda h:int(h[:2])*60+int(h[3:])),[0,9*60+20,9*60+30,10*60+15,14*60,15*60+15,999],labels=['09:15-19','09:20-29','09:30-10:14','10:15-13:59','14:00-15:14','15:15+'])
D['spread_pct']=D.spread/D.mid*100
D.to_parquet('data/spreads_snapshots.parquet')
print('snapshots',len(D),'days',D.date.nunique(),D.date.min(),D.date.max())
a=D[D.offset.abs()<=1]
print(a.groupby('tod',observed=True).spread.describe(percentiles=[.5,.75,.9,.95]).round(2).to_string())
print(D.groupby('offset').spread.describe(percentiles=[.5,.9]).round(2).to_string())
print(a.groupby('dte').spread.describe(percentiles=[.5,.9]).round(2).to_string())
s=a.groupby('tod',observed=True).spread.agg(n='count',mean='mean',median='median',p90=lambda x:x.quantile(.9)).round(3)
s['half_spread_med']=s['median']/2; s.to_csv('results/spread_by_tod.csv')
a.groupby('dte').spread.agg(n='count',mean='mean',median='median',p90=lambda x:x.quantile(.9)).round(3).to_csv('results/spread_by_dte.csv')
D.groupby('offset').spread.agg(n='count',mean='mean',median='median',p90=lambda x:x.quantile(.9)).round(3).to_csv('results/spread_by_offset.csv')
print('top-of-book qty median (units):',a.bq.median(),a.aq.median())