"""Offline reproducible AMR calculations, Python 3 standard library only. Inputs are verbatim cells from pinned EIA original annual PDFs and workbooks. Main analytical window: one 2024 Annual edition, 2020 through 2024. Archive snapshots are a separate vintage audit, never a continuous trend. """ from pathlib import Path from decimal import Decimal,ROUND_HALF_UP import csv,json,hashlib,collections P=Path(__file__).resolve().parent;D=P/'data';Q=P/'qa';Q.mkdir(exist_ok=True) def read(n):return list(csv.DictReader((D/n).open())) def write(n,rs): assert rs,n with (D/n).open('w',newline='') as f: w=csv.DictWriter(f,fieldnames=list(rs[0]));w.writeheader();w.writerows(rs) def number(x): try:return int(x.replace(',','')) except (ValueError,AttributeError):return None def ratio(n,d):return Decimal(n)*100/Decimal(d) if n is not None and d is not None and d>0 else None def dec(x,places=6):return '' if x is None else str(x.quantize(Decimal(1).scaleb(-places),rounding=ROUND_HALF_UP)) def run(): inp=read('annual-source-inputs.csv');cells=read('current-series-source-cells.csv');lookup={(x['area'],int(x['year']),x['variable']):x for x in cells};main=[];archive=[];audit=[] for r in inp: y=int(r['year']);g=number(r['gross_withdrawals_raw']);v=number(r['vented_flared_raw']);st='reported_positive' if v and v>0 else 'reported_zero' if v==0 else {'NA':'not_available','--':'not_applicable','*':'below_reporting_threshold','W':'withheld'}.get(r['vented_flared_raw'],'unreported') a=dict(year=y,area=r['area'],geography_type=r['geography_type'],gross_withdrawals_mmcf='' if g is None else g,vented_flared_mmcf='' if v is None else v,vented_flared_status=st,vented_flared_source_marker=r['vented_flared_raw'],vented_flared_pct_gross=dec(ratio(v,g)),gross_source_marker=r['gross_withdrawals_raw'],source_file=r['source_file'],source_table=r['source_table'],vintage=r['vintage']) (main if y>=2020 else archive).append(a) for var in ['gross_withdrawals','vented_flared']: x=lookup.get((r['area'],y,var));annual=number(r[var+'_raw']);latest=number(x['raw']) if x else None status='no_series_cell' if not x else 'same_numeric' if annual is not None and latest==annual else 'numeric_revision' if annual is not None and latest is not None else 'annual_NA_series_zero' if r[var+'_raw']=='NA' and latest==0 else 'availability_difference' if annual!=latest or r[var+'_raw']!=(x['raw'] or '') else 'same_marker' audit.append(dict(year=y,area=r['area'],variable=var,annual_raw=r[var+'_raw'],current_series_raw=x['raw'] if x else '',comparison=status,numeric_difference='' if annual is None or latest is None else latest-annual,annual_file=r['source_file'],series_file=x['source_file'] if x else '',series_sheet=x['sheet'] if x else '',series_cell=x['cell'] if x else '')) main.sort(key=lambda r:(r['year'],r['area']));archive.sort(key=lambda r:(r['year'],r['area'])) write('state-panel-2020-2024.csv',main);write('archived-as-published-2012-2019.csv',archive);write('annual-versus-current-series-audit.csv',audit) ranks=[];coverage=[];national=[];concentration=[];byyear={} for y in range(2020,2025): rs=[r for r in main if r['year']==y];states=[r for r in rs if r['geography_type']=='state'];n=next(r for r in rs if r['geography_type']=='national');nr=next(r for r in inp if int(r['year'])==y and r['area']=='United States');byyear[y]=rs eligible=[r for r in states if r['vented_flared_mmcf']!='' and r['gross_withdrawals_mmcf']!='' and int(r['gross_withdrawals_mmcf'])>0] volumes=sorted(eligible,key=lambda r:(-int(r['vented_flared_mmcf']),r['area']));rates=sorted(eligible,key=lambda r:(-Decimal(r['vented_flared_pct_gross']),r['area'])) for r in eligible: # Competition ranks retain ties at source precision, not alphabetical tie breakers. vr=1+sum(int(x['vented_flared_mmcf'])>int(r['vented_flared_mmcf']) for x in eligible);rr=1+sum(int(x['vented_flared_mmcf'])*int(r['gross_withdrawals_mmcf'])>int(r['vented_flared_mmcf'])*int(x['gross_withdrawals_mmcf']) for x in eligible) ranks.append(dict(**r,volume_rank=vr,rate_rank=rr,share_of_reported_us_vented_flared_pct=dec(ratio(int(r['vented_flared_mmcf']),int(n['vented_flared_mmcf']))))) reported=[r for r in rs if r['geography_type']!='national' and r['vented_flared_mmcf']!=''];s=sum(int(r['vented_flared_mmcf']) for r in reported);gs=sum(int(r['gross_withdrawals_mmcf']) for r in rs if r['geography_type']!='national' and r['gross_withdrawals_mmcf']!='') vals={k:number(nr[k+'_raw']) for k in ['gross_withdrawals','repressuring','vented_flared','nonhydrocarbon_removed','marketed_production','ngpl_equivalent','dry_production']};market_resid=vals['gross_withdrawals']-vals['repressuring']-vals['vented_flared']-vals['nonhydrocarbon_removed']-vals['marketed_production'];dry_resid=vals['marketed_production']-vals['ngpl_equivalent']-vals['dry_production'] national.append(dict(year=y,**{k+'_mmcf':v for k,v in vals.items()},vented_flared_pct_gross=dec(ratio(vals['vented_flared'],vals['gross_withdrawals'])),sum_numeric_state_and_gulf_vented_flared_mmcf=s,us_minus_reported_geographies_vented_flared_mmcf=vals['vented_flared']-s,sum_state_district_gulf_gross_mmcf=gs,us_minus_geographies_gross_mmcf=vals['gross_withdrawals']-gs,marketed_accounting_rounding_residual_mmcf=market_resid,dry_accounting_rounding_residual_mmcf=dry_resid)) for k in [1,2,3,4,5,10]: top=volumes[:k];sv=sum(int(r['vented_flared_mmcf']) for r in top) concentration.append(dict(year=y,top_state_count=k,states='; '.join(r['area'] for r in top),combined_vented_flared_mmcf=sv,reported_us_vented_flared_mmcf=vals['vented_flared'],share_reported_us_pct=dec(ratio(sv,vals['vented_flared'])))) coverage.append(dict(year=y,state_rows=len(states),district_rows=sum(r['geography_type']=='district' for r in rs),offshore_region_rows=sum(r['geography_type']=='offshore_region' for r in rs),states_positive_gross=sum(r['gross_withdrawals_mmcf']!='' and int(r['gross_withdrawals_mmcf'])>0 for r in states),states_numeric_vented_flared=sum(r['vented_flared_mmcf']!='' for r in states),states_positive_vented_flared=sum(r['vented_flared_mmcf']!='' and int(r['vented_flared_mmcf'])>0 for r in states),states_reported_zero_vented_flared=sum(r['vented_flared_status']=='reported_zero' for r in states),states_NA_vented_flared=sum(r['vented_flared_source_marker']=='NA' for r in states),rate_eligible_states=len(eligible),production_state_gross_with_missing_vented_flared_mmcf=sum(int(r['gross_withdrawals_mmcf']) for r in states if r['gross_withdrawals_mmcf']!='' and int(r['gross_withdrawals_mmcf'])>0 and r['vented_flared_mmcf']==''))) write('annual-national-reconciliation.csv',national);write('annual-coverage-audit.csv',coverage);write('annual-state-ranks.csv',sorted(ranks,key=lambda r:(r['year'],r['volume_rank'],r['area'])));write('2024-state-rankings.csv',[r for r in sorted(ranks,key=lambda r:(r['year'],r['volume_rank'],r['area'])) if r['year']==2024]);write('annual-volume-concentration.csv',concentration) changes=[] for end in [2021,2022,2023,2024]: for begin in ([2020,2023] if end==2024 else [end-1]): for r in byyear[end]: s=next(z for z in byyear[begin] if z['area']==r['area']);v0=s['vented_flared_mmcf'];v1=r['vented_flared_mmcf'];g0=s['gross_withdrawals_mmcf'];g1=r['gross_withdrawals_mmcf'] if '' in [v0,v1,g0,g1] or int(g0)<=0 or int(g1)<=0:continue a,b,c,d=map(int,[v0,v1,g0,g1]);r0=ratio(a,c);r1=ratio(b,d) changes.append(dict(begin_year=begin,end_year=end,area=r['area'],geography_type=r['geography_type'],begin_vented_flared_mmcf=a,end_vented_flared_mmcf=b,change_vented_flared_mmcf=b-a,change_vented_flared_pct=dec(ratio(b-a,a)),begin_gross_mmcf=c,end_gross_mmcf=d,change_gross_pct=dec(ratio(d-c,c)),begin_vented_flared_pct_gross=dec(r0),end_vented_flared_pct_gross=dec(r1),change_rate_percentage_points=dec(r1-r0),relative_rate_change_pct=dec((r1/r0-1)*100) if r0 else '')) write('matched-period-changes.csv',changes) seriesonly=[x for x in cells if x['year']=='2025'];write('2025-incomplete-series-excluded.csv',seriesonly) checks={'main_rows':len(main),'main_years':list(byyear),'rows_per_year':{y:len(rs) for y,rs in byyear.items()},'no_missing_to_zero':all(r['vented_flared_mmcf']=='' for r in main if r['vented_flared_source_marker'] in ['NA','--','W','*']),'no_zero_denominator_ratios':all(r['vented_flared_pct_gross']=='' for r in main if r['gross_withdrawals_mmcf'] in ['',0,'0']),'reported_geography_sum_residuals':{r['year']:r['us_minus_reported_geographies_vented_flared_mmcf'] for r in national},'national_bridge_residuals':{r['year']:r['marketed_accounting_rounding_residual_mmcf'] for r in national},'archive_snapshots_separate':True,'main_numeric_revisions_against_current_workbooks':[r for r in audit if r['year']>=2020 and r['comparison']=='numeric_revision'],'annual_NA_current_zero_cell_count':sum(r['comparison']=='annual_NA_series_zero' for r in audit),'rank_scope':'Numeric Annual state observations with positive matched gross denominator. Gulf separately retained, excluded from state ranks.'} assert len(main)==265 and set(x['state_rows'] for x in coverage)=={50} assert checks['no_missing_to_zero'] and checks['no_zero_denominator_ratios'] assert not checks['main_numeric_revisions_against_current_workbooks'] assert all(abs(r['us_minus_reported_geographies_vented_flared_mmcf'])<=3 for r in national) assert all(abs(r['marketed_accounting_rounding_residual_mmcf'])<=2 and abs(r['dry_accounting_rounding_residual_mmcf'])<=2 for r in national) (Q/'analysis-checks.json').write_text(json.dumps(checks,indent=2));(D/'summary.json').write_text(json.dumps({'national':national,'coverage':coverage,'rankings_2024':[r for r in ranks if r['year']==2024],'changes_2020_2024':[r for r in changes if r['begin_year']==2020 and r['end_year']==2024]},indent=2)) print(json.dumps(checks,indent=2)) if __name__=='__main__':run()