#!/usr/bin/env python3 """Regenerate every AMR analytical CSV offline from pinned source inputs. No blank or printed dash is converted to an observed zero. Decimal preserves money. """ from pathlib import Path from decimal import Decimal, ROUND_HALF_UP import csv,json,hashlib,re P=Path(__file__).resolve().parent D=P/'data';D.mkdir(exist_ok=True) DOI='https://www.doi.gov/sites/default/files/documents/2026-11/owpo-annual-report-congress-202610092026.pdf' USGS='https://www.usgs.gov/data/united-states-documented-orphaned-well-database-2026' CAT='https://catalog.data.gov/dataset/united-states-plugged-orphaned-oil-and-gas-well-dataset' def readable(v): if not isinstance(v,str) or v.startswith(('http://','https://')):return v fixes={'throughJune30,2026':'through June 30, 2026','throughSeptember30,2026':'through September 30, 2026','throughJanuary31,2026':'through January 31, 2026','through2026':'through 2026','September30':'September 30','June30':'June 30','January31':'January 31','August2022':'August 2022','FY2026':'FY 2026','FY2025':'FY 2025','FY2022':'FY 2022','posted2026':'posted 2026','all-US':'all U.S.','Table1':'Table 1','Table2':'Table 2','over13000':'over 13,000','27states':'27 states','all18':'all 18','28states':'28 states','pp13–14':'pp13 through14'} for a,b in fixes.items():v=v.replace(a,b) v=re.sub(r'(?<=[A-Za-z])(?=\d)|(?<=\d)(?=[A-Za-z])',' ',v) return v.replace('–',' through ') def write(name,rows): rows=[{k:readable(v) for k,v in r.items()} for r in rows] with (D/name).open('w',newline='') as f: w=csv.DictWriter(f,fieldnames=list(rows[0]));w.writeheader();w.writerows(rows) def read(name): with (P/'sources'/name).open(newline='') as f:return list(csv.DictReader(f)) def pct(a,b):return str((Decimal(a)*100/Decimal(b)).quantize(Decimal('.01'),rounding=ROUND_HALF_UP)) def main(): text=(P/'sources/doi-orphan-wells-fy2026.txt').read_text() raw=read('input-state-awards.csv') assert len(raw)==27 and len({r['recipient'] for r in raw})==27 totals={'initial_usd':574806743,'formula_usd':1131801208,'matching_usd':156267058,'total_usd':1862875009} state=[] for r in raw: nums=[int(r[c]) for c in totals if r[c] not in ('','-')] assert nums[-1]==sum(nums[:-1]),r # Independently compare every source dollar value in each original PDF text row. line=next(l for l in text.splitlines() if re.match('^'+re.escape(r['recipient'])+r'\s+\$',l.strip())) pdfnums=[int(v.replace(',','')) for v in re.findall(r'\$([\d,]+)',line)] assert nums==pdfnums,(r['recipient'],nums,pdfnums) state.append({**r,'share_of_state_awards_percent':pct(r['total_usd'],totals['total_usd']),'cutoff':'2026-09-30','measure':'cumulative State Program awards, not expenditures','source_locator':'DOI FY2026 Table2 pp13–14','source_url':DOI}) for c,v in totals.items(): assert sum(int(r[c]) for r in raw if r[c] not in ('','-'))==v,(c,v) assert f'${v:,}' in text for rank,r in enumerate(sorted(state,key=lambda r:int(r['total_usd']),reverse=True),1):r['rank_by_reported_awards']=rank write('state-awards.csv',state) grant=[] for c,label in [('initial_usd','Initial'),('formula_usd','Formula'),('matching_usd','Matching')]: grant.append({'grant_type':label,'awarded_usd':totals[c],'share_percent':pct(totals[c],totals['total_usd']),'cutoff':'2026-09-30','source_locator':'DOI FY2026 Table2 pp13–14','source_url':DOI,'value_type':'AMR sum of reported numeric cells, independently equals source total'}) write('state-grant-composition.csv',grant) ordered=sorted(state,key=lambda r:int(r['total_usd']),reverse=True) concentration=[] for n in [1,2,5,10]: s=sum(int(r['total_usd']) for r in ordered[:n]) concentration.append({'group':f'Top {n} recipient'+('s' if n>1 else ''),'recipients':'; '.join(r['recipient'] for r in ordered[:n]),'awarded_usd':s,'share_percent':pct(s,totals['total_usd']),'denominator_usd':totals['total_usd'],'cutoff':'2026-09-30','source_url':DOI,'value_type':'AMR calculation, funding concentration only'}) write('state-award-concentration.csv',concentration) funding=read('input-program-funding.csv');write('program-funding-ledger.csv',funding) assert len(funding)==5 # Compare literal printed cells rather than forcing rounded billion totals to add. expected={'Federal Program':['$250 M','$8.7 M','$241.3 M','-','$241.3 M'],'State Program':['$4.3 B','$435 M','$3.9 B','$326 M','$1.9 B'],'Tribal Program':['$150 M','$5.3 M','$144.7 M','$12.3 M','$93.2 M'],'IOGCC Funding':['$2 M','$0.07 M','$1.93 M','-','-'],'Totals':['$4.7 B','$449.1 M','$4.3 B','$338.3 M','$2.2 B']} for r in funding: assert [r[k] for k in ['omb_apportioned_printed','deductions_printed','direct_program_amount_printed','fy2026_transferred_or_awarded_printed','cumulative_fy2022_2026_distribution_printed']]==expected[r['program']] orig=[{'program':k,'original_appropriation_usd':v,'source_locator':loc,'source_url':DOI} for k,v,loc in [('Federal',250000000,'p4'),('State',4275000000,'p4'),('Tribal',150000000,'p4'),('IOGCC',2000000,'p5')]] assert sum(r['original_appropriation_usd'] for r in orig)==4677000000 write('original-allocations.csv',orig) federal=read('input-federal-recipient-targets.csv');write('federal-recipient-targets.csv',federal) tribal=read('input-tribal-map-awards.csv');assert len(tribal)==18 write('tribal-map-awards.csv',tribal) inventory=[{'observation':'DOI description of August2022 inventory','count':117672,'states_covered':'27, per superseded USGS description','date_or_release':'2022-08','population':'documented unplugged orphaned wells','source_url':DOI,'source_locator':'p15','qualification':'historical inventory; not all abandoned wells'}, {'observation':'Current USGS release description','count':130121,'states_covered':'28','date_or_release':'2026-09-30','population':'documented unplugged orphaned wells in contributing state data','source_url':USGS,'source_locator':'release description paragraph1','qualification':'inventory publication date, not uniform state observation date'}, {'observation':'DOI FY2026 report inventory statement','count':130122,'states_covered':'not stated in this report paragraph','date_or_release':'FY2026 report posted2026-11','population':'documented unplugged orphaned wells attributed to USGS','source_url':DOI,'source_locator':'p15','qualification':'one more than current USGS page; reason unresolved'}] write('documented-inventory-vintages.csv',inventory) progress=[{'program':'IIJA all recipients, catalog records','wells':11409,'cutoff':'2026-01-31','measure':'submissions received by cutoff','source_url':CAT,'source_locator':'description paragraph1','qualification':'released2026-04-22; as reported; not matched against later report records'}, {'program':'IIJA all programs, national report','wells':13108,'cutoff':'2026-06-30','measure':'cumulative wells plugged','source_url':DOI,'source_locator':'p6 callout','qualification':'p16 narrative rounds to over13000'}, {'program':'State Program','wells':12658,'cutoff':'2026-06','measure':'cumulative wells plugged','source_url':DOI,'source_locator':'p11 callout','qualification':'state and private lands,27states; source reports June2026 at month precision, not an exact day'}, {'program':'Federal Program','wells':346,'cutoff':'2026-06-30','measure':'cumulative wells plugged','source_url':DOI,'source_locator':'p9 callout','qualification':'federal land program'}, {'program':'Tribal Program','wells':127,'cutoff':'2026-09-30','measure':'cumulative wells plugged','source_url':DOI,'source_locator':'p7 narrative','qualification':'later cutoff, do not add to June30 state/federal counts'}] write('plugging-reporting-windows.csv',progress) reconciliation=[{'comparison':'DOI report inventory minus current USGS page','left_value':'130122','right_value':'130121','difference':'1','unit':'well','interpretation':'source discrepancy, cause unresolved; not rounding of whole-well count'}, {'comparison':'Current USGS documented inventory minus August2022 inventory','left_value':'130121','right_value':'117672','difference':str(130121-117672),'unit':'wells','interpretation':'different database vintages and state coverage; not new abandonments'}, {'comparison':'DOI inventory comparison on its own terms','left_value':'130122','right_value':'117672','difference':str(130122-117672),'unit':'wells','interpretation':'report attributes increase to improved documentation'}, {'comparison':'All five rounded federal bureau amounts minus federal direct program total','left_value':str(sum(Decimal(r['funding_million_usd']) for r in federal)),'right_value':'241.3','difference':str(sum(Decimal(r['funding_million_usd']) for r in federal)-Decimal('241.3')),'unit':'million USD','interpretation':'individually rounded figures, not an additional transfer'}, {'comparison':'Tribal map label sum versus rounded program cumulative awards','left_value':str(sum(Decimal(r['awarded_million_usd_printed']) for r in tribal)),'right_value':'93.2','difference':str(sum(Decimal(r['awarded_million_usd_printed']) for r in tribal)-Decimal('93.2')),'unit':'million USD','interpretation':'printed map labels do not exactly reconcile; cause unresolved'}, {'comparison':'Federal/State/Tribal Table1 cumulative rounded row sum versus printed table total','left_value':'2234.5','right_value':'2200','difference':'34.5','unit':'million USD','interpretation':'different printed precision; not an unexplained expenditure gap'}] write('source-reconciliation.csv',reconciliation) definitions=[{'measure':'Documented unplugged inventory','question_answered':'How many qualifying known unplugged orphans are in the release?','denominator_or_unit':'wells in contributing state databases','source_url':USGS,'not_equivalent_to':'all abandoned wells; newly abandoned wells; completion denominator'}, {'measure':'Cumulative grant awards/transfers','question_answered':'How much funding has been awarded or moved to recipients?','denominator_or_unit':'USD across stated fiscal years','source_url':DOI,'not_equivalent_to':'paid plugging invoices; cost per completed well'}, {'measure':'Cumulative plugged wells','question_answered':'How many wells were reported plugged with IIJA funds through the cutoff?','denominator_or_unit':'reported completed wells','source_url':DOI,'not_equivalent_to':'all national plugging; fully restored sites'}, {'measure':'Bureau project target','question_answered':'How many wells did the published federal allocations intend to plug?','denominator_or_unit':'planned wells','source_url':DOI,'not_equivalent_to':'completed wells or common risk/cost cohort'}, {'measure':'Restored acres','question_answered':'What habitat restoration area does OWPO report including estimates?','denominator_or_unit':'acres,6554through2026-06-30','source_url':DOI,'not_equivalent_to':'measured-only acreage or acres-per-well benchmark'}] write('measure-dictionary.csv',definitions) restoration=[{'measure':'Land remediated and available for beneficial use','value':'6554','unit':'acres','cutoff':'2026-06-30','source_url':DOI,'source_locator':'p16 Habitat Restoration','qualification':'recipient reporting for most wells; missing areas estimated using median prior restoration data, not all independently measured'}] write('restoration-accounting.csv',restoration) # Plot datasets are complete subsets, not undisclosed ranking exclusions. plot=[] for r in ordered[:7]:plot.append({k:r[k] for k in ['recipient','initial_usd','formula_usd','matching_usd','total_usd','share_of_state_awards_percent']}) rest=ordered[7:] plot.append({'recipient':'Other 20 recipients','initial_usd':sum(int(r['initial_usd']) for r in rest if r['initial_usd'] not in ('','-')),'formula_usd':sum(int(r['formula_usd']) for r in rest if r['formula_usd'] not in ('','-')),'matching_usd':sum(int(r['matching_usd']) for r in rest if r['matching_usd'] not in ('','-')),'total_usd':sum(int(r['total_usd']) for r in rest),'share_of_state_awards_percent':pct(sum(int(r['total_usd']) for r in rest),totals['total_usd'])}) write('state-awards-figure.csv',plot) summary={'states':len(raw),'state_total_usd':totals['total_usd'],'grant_shares':{r['grant_type']:r['share_percent'] for r in grant},'top5_share':concentration[2]['share_percent'],'top5_usd':concentration[2]['awarded_usd'],'inventory_difference_usgs':12449,'inventory_difference_doi':12450,'federal_target_wells':sum(int(r['planned_wells']) for r in federal),'tribal_map_sum_million':str(sum(Decimal(r['awarded_million_usd_printed']) for r in tribal)),'national_plugged':13108,'state_plugged':12658,'federal_plugged':346,'tribal_plugged_later_cutoff':127,'source_dashes_preserved':True,'blank_to_zero':False,'network_required':False} (D/'summary.json').write_text(json.dumps(summary,indent=2)+'\n') print(json.dumps(summary,indent=2)) if __name__=='__main__':main()