"""AMR state generation comparison, preserving published rows and absent fuels. An absent producer fuel row is never materialized as a measured zero. """ from pathlib import Path from decimal import Decimal as D, ROUND_HALF_UP import csv,json,sys,collections P=Path(__file__).resolve().parent;sys.path.insert(0,str(P)) from xlsx_read import rows OUT=P/'data';OUT.mkdir(exist_ok=True) def fmt(v):return str(D(v).quantize(D('.000001'),rounding=ROUND_HALF_UP)) def save(n,rs): with(OUT/n).open('w',newline='')as f: w=csv.DictWriter(f,fieldnames=list(rs[0]));w.writeheader();w.writerows(rs) def run(): names=json.loads((P/'state-names.json').read_text()) src=[];lookup={};selected=[] for i,r in rows(P/'sources/generation.xlsx'): if not r.get('A','').isdigit()or r.get('C')!='Total Electric Power Industry':continue year=int(r['A']) if not 2015<=year<=2025:continue state=r['B'];fuel=r['D'];v=D(r['E']);key=(year,state,fuel);assert key not in lookup,key out=dict(year=year,state=state,fuel=fuel,net_generation_mwh=str(v),source_row=i,producer='Total Electric Power Industry') src.append(out);lookup[key]=v geos={r['state']for r in src};assert len(geos)==52 and'US-Total'in geos for year in range(2015,2026):assert len([r for r in src if r['year']==year and r['fuel']=='Total'])==52 reconcile=[];totals=[];changes=[];ranked=[] for year in range(2015,2026): for state in sorted(geos): total=lookup[year,state,'Total'];assert total>0 entries=[r for r in src if r['year']==year and r['state']==state and r['fuel']!='Total'] # Include negative net pumped-storage generation, exactly as the source does. components=sum(D(r['net_generation_mwh'])for r in entries) reconcile.append(dict(year=year,state=state,state_name=names[state],all_source_total_mwh=str(total),sum_reported_fuel_rows_mwh=str(components),sum_minus_total_mwh=str(components-total),reported_component_rows=len(entries),note='Published total retained; signed pumped storage included; no absent fuel imputed')) leader=max(entries,key=lambda x:D(x['net_generation_mwh'])) totals.append(dict(year=year,state=state,state_name=names[state],total_generation_mwh=str(total),largest_reported_fuel=leader['fuel'],largest_fuel_mwh=leader['net_generation_mwh'],largest_fuel_pct_total=fmt(D(leader['net_generation_mwh'])/total*100))) for r in entries: selected.append(dict(**r,state_name=names[state],total_generation_mwh=str(total),share_pct=fmt(D(r['net_generation_mwh'])/total*100))) if year==2025: for fuel in ['Natural Gas','Coal','Nuclear','Wind','Solar Thermal and Photovoltaic']: if(year,state,fuel)not in lookup:continue value=lookup[year,state,fuel];ranked.append(dict(state=state,state_name=names[state],fuel=fuel,net_generation_mwh=str(value),total_generation_mwh=str(total),share_pct=fmt(value/total*100))) for fuel in ['Natural Gas','Coal','Nuclear','Wind','Solar Thermal and Photovoltaic']: states=[r for r in ranked if r['fuel']==fuel and r['state']!='US-Total'] for r in states: r['volume_rank_reported']=1+sum(D(x['net_generation_mwh'])>D(r['net_generation_mwh'])for x in states) r['share_rank_reported']=1+sum(D(x['share_pct'])>D(r['share_pct'])for x in states) r['reporting_state_count']=len(states) for r in ranked: if r['fuel']==fuel and r['state']=='US-Total':r.update(volume_rank_reported='Not ranked, U.S. total',share_rank_reported='Not ranked, U.S. total',reporting_state_count=len(states)) for state in sorted(geos): for fuel in ['Natural Gas','Coal']: if all((year,state,fuel)in lookup for year in [2015,2025]): a,b=[lookup[year,state,fuel]for year in [2015,2025]];ta,tb=[lookup[year,state,'Total']for year in[2015,2025]] changes.append(dict(state=state,state_name=names[state],fuel=fuel,begin_year=2015,end_year=2025,begin_mwh=str(a),end_mwh=str(b),change_mwh=str(b-a),begin_share_pct=fmt(a/ta*100),end_share_pct=fmt(b/tb*100),share_change_percentage_points=fmt((b/tb-a/ta)*100),comparison='Matched reported fuel observations and same-year state total denominators')) latest=[r for r in selected if r['year']==2025];national=[r for r in selected if r['state']=='US-Total'];latest_total=[r for r in totals if r['year']==2025] save('reported-source-inputs-2015-2025.csv',src);save('state-generation-total-history.csv',totals);save('state-fuel-mix-2025.csv',latest);save('national-fuel-history-2015-2025.csv',national);save('reported-state-fuel-rankings-2025.csv',ranked);save('matched-gas-coal-changes-2015-2025.csv',changes);save('fuel-total-reconciliation.csv',reconcile) residual=max(abs(D(r['sum_minus_total_mwh']))for r in reconcile) info={'status':'PASS','years':list(range(2015,2026)),'geographies':52,'source_rows':len(src),'latest_fuel_rows':len(latest),'max_component_sum_minus_published_total_mwh':str(residual),'absence_policy':'An omitted fuel row is not a measured zero. Fuel rankings include only states with numeric published rows. State total and largest reported fuel comparisons cover all51states/DC.','signed_pumped_storage_retained':True,'national_2025': [r for r in latest if r['state']=='US-Total'],'largest_total_states':sorted([r for r in latest_total if r['state']!='US-Total'],key=lambda r:D(r['total_generation_mwh']),reverse=True)[:5]} (P/'verification.json').write_text(json.dumps(info,indent=2)+'\n');print(json.dumps({k:v for k,v in info.items()if k not in['national_2025','largest_total_states']},indent=2)) if __name__=='__main__':run()