#!/usr/bin/env python3 """Reproduce the study from the twelve official BTS ZIPs. Standard library only. Usage: python3 calculate.py RAW_DIRECTORY OUTPUT_DIRECTORY --lookup airport-id-lookup.csv Downloads are intentionally separate: inputs are identified in source-manifest.json. """ import argparse, csv, hashlib, io, json, re, zipfile from collections import defaultdict from pathlib import Path from fractions import Fraction PERIODS = [(2025,m) for m in range(8,13)] + [(2026,m) for m in range(1,8)] COUNTS = ['scheduled','cancelled','diverted','completed','measurable','missing','delayed15','delayed60','positive_minutes','delayed15_minutes'] def blank(): return dict.fromkeys(COUNTS, 0) def include(b, cancelled, diverted, delay): b['scheduled'] += 1 assert not (cancelled and diverted), 'Overlapping cancellation/diversion' if cancelled: b['cancelled'] += 1 elif diverted: b['diverted'] += 1 else: b['completed'] += 1 if delay is None: b['missing'] += 1 else: b['measurable'] += 1 b['positive_minutes'] += max(delay, 0) if delay >= 15: b['delayed15'] += 1 b['delayed15_minutes'] += delay if delay >= 60: b['delayed60'] += 1 def metrics(b): n=b['measurable']; assert n > 0 assert b['scheduled']==b['cancelled']+b['diverted']+b['completed'] assert b['completed']==n+b['missing'] assert 0<=b['delayed60']<=b['delayed15']<=n return {**b,'delay_rate':round(100*b['delayed15']/n,6), 'hour_delay_rate':round(100*b['delayed60']/n,6), 'mean_positive':round(b['positive_minutes']/n,6), 'mean_delayed15':round(b['delayed15_minutes']/b['delayed15'],6) if b['delayed15'] else 0, 'cancel_rate':round(100*b['cancelled']/b['scheduled'],6), 'divert_rate':round(100*b['diverted']/b['scheduled'],6)} def main(): p=argparse.ArgumentParser();p.add_argument('raw',type=Path);p.add_argument('output',type=Path);p.add_argument('--lookup',type=Path,required=True);a=p.parse_args() names={r['Code']:r['Description'] for r in csv.DictReader(a.lookup.open(encoding='cp1252'))} airports=defaultdict(blank);months=defaultdict(lambda:defaultdict(blank));identity={};sources=[];carriers=set();total=0 for year,month in PERIODS: path=a.raw/f'On_Time_Reporting_Carrier_On_Time_Performance_1987_present_{year}_{month}.zip' digest=hashlib.file_digest(path.open('rb'),'sha256').hexdigest();count=0 with zipfile.ZipFile(path) as z: member=next(n for n in z.namelist() if n.endswith('.csv')) with z.open(member) as f: rows=csv.reader(io.TextIOWrapper(f,encoding='utf-8-sig'));fields=next(rows);ix={v:i for i,v in enumerate(fields)} for r in rows: assert int(r[ix['Year']])==year and int(r[ix['Month']])==month aid=r[ix['OriginAirportID']];code=r[ix['Origin']];city=r[ix['OriginCityName']];state=r[ix['OriginState']] cancelled=float(r[ix['Cancelled']])==1;diverted=float(r[ix['Diverted']])==1 raw=r[ix['DepDelay']];delay=float(raw) if raw else None if not cancelled and not diverted and delay is not None: assert delay.is_integer() delay=int(delay) assert float(r[ix['DepDelayMinutes']])==max(delay,0) assert float(r[ix['DepDel15']])==int(delay>=15) identity[aid]={'id':aid,'code':code,'city':city,'state':state,'name':names[aid].split(': ',1)[-1]} include(airports[aid],cancelled,diverted,delay) include(months[f'{year}-{month:02}'][aid],cancelled,diverted,delay) carriers.add(r[ix['Reporting_Airline']]);count+=1 total+=count sources.append({'period':f'{year}-{month:02}','url':'https://transtats.bts.gov/PREZIP/'+path.name,'file':path.name,'sha256':digest,'bytes':path.stat().st_size,'records':count}) print(year,month,count,flush=True) # Select by flight volume before ranking delay, so the outcome cannot decide eligibility. eligible=[k for k,v in airports.items() if v['measurable']>=20000 and v['missing']/v['completed']<=0.005] selected=sorted(eligible,key=lambda k:(-airports[k]['scheduled'],k))[:50] assert len(selected)==50 rows=[{**identity[k],**metrics(airports[k])} for k in selected] rows.sort(key=lambda r:(-Fraction(r['positive_minutes'],r['measurable']),r['code'])) for rank,r in enumerate(rows,1): r['rank']=rank combined=blank();month_rows=[] for period,values in sorted(months.items()): b=blank() for k in selected: for field in COUNTS: b[field]+=values[k][field];combined[field]+=values[k][field] month_rows.append({'month':period,**metrics(b)}) per_airport={k:[{'month':period,**metrics(values[k])} for period,values in sorted(months.items())] for k in selected} assert sum(v['scheduled'] for v in airports.values())==total assert combined['scheduled']==sum(r['scheduled'] for r in rows) result={'period_start':'2025-08-01','period_end':'2026-07-31','retrieved':'2026-09-29','selection':'50 highest-volume origin airports by scheduled flights in the reporting-carrier dataset, with at least 20,000 measurable completed flights and at most 0.5% missing departure delay among completed flights.', 'source_records':total,'source_airports':len(airports),'eligible_airports':len(eligible),'carriers':sorted(carriers),'summary':metrics(combined),'airports':rows,'months':month_rows,'airport_months':per_airport} a.output.mkdir(parents=True,exist_ok=True) (a.output/'data.json').write_text(json.dumps(result,indent=2)+'\n') manifest={'retrieved':'2026-09-29','lookup':{'url':'https://www.transtats.bts.gov/Download_Lookup.asp?Y11x72=Y_NVecbeg_VQ','sha256':hashlib.file_digest(a.lookup.open('rb'),'sha256').hexdigest()},'files':sources} (a.output/'source-manifest.json').write_text(json.dumps(manifest,indent=2)+'\n') for filename,records in [('airport-comparison.csv',rows),('monthly-comparison.csv',month_rows),('all-origin-airports.csv',[{**identity[k],**metrics(v)} for k,v in sorted(airports.items()) if v['measurable']])]: with (a.output/filename).open('w',newline='') as f: writer=csv.DictWriter(f,fieldnames=list(records[0]));writer.writeheader();writer.writerows(records) print(json.dumps({'records':total,'top':rows[:3],'lowest':rows[-1],'summary':metrics(combined)},indent=2)) if __name__=='__main__': main()