"""Reproduce AMR RECS2024 point-estimate comparisons from published cells. Published counts rounded to0.01million are retained. No joint hardship counts or ratio confidence intervals are inferred from marginal table cells. """ from pathlib import Path from decimal import Decimal as D, ROUND_HALF_UP import csv,json,sys P=Path(__file__).resolve().parent;sys.path.insert(0,str(P)) from xlsx_read import rows OUT=P/'data';OUT.mkdir(exist_ok=True) LABELS={'B':'all_households','C':'any_insecurity','D':'food_medicine_tradeoff','E':'unhealthy_temperature','F':'disconnect_or_delivery_notice','G':'unable_use_heating','H':'unable_use_cooling'} def fmt(v,n=6):return str(v.quantize(D(1).scaleb(-n),rounding=ROUND_HALF_UP)) def num(v): try:return D(v) except:return None 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(): data=dict(rows(P/'sources/insecurity-2024.xlsx'));rse=dict(rows(P/'sources/insecurity-2024.xlsx',1)) groups={6:'National',8:'Census region',11:'Census region',14:'Census region',18:'Census region',24:'Urban or rural',25:'Urban or rural',27:'Climate region',28:'Climate region',29:'Climate region',30:'Climate region',31:'Climate region'} for a,b,label in [(33,37,'Housing type'),(39,47,'Construction period'),(49,52,'Insulation'),(58,63,'Household members'),(65,71,'Annual household income'),(78,79,'Householder 60 or older'),(81,82,'Children under 18'),(98,99,'Tenure')]: for i in range(a,b+1):groups[i]=label source=[];points=[];precision=[] for i,group in groups.items(): row=data[i];uncertainty=rse[i];label=row['A'].replace('Rentedi','Rented');den=num(row['B']);assert den is not None and den>0 for col,measure in LABELS.items(): raw=row[col];value=num(raw);assert value is not None,(i,label,col,raw) # Producer estimates are displayed to two decimal million. count=D(fmt(value,2));rse_raw=uncertainty.get(col,'');rse_value=num(rse_raw) source.append(dict(source_row=i,source_cell=col+str(i),group=group,label=label,measure=measure,count_millions=fmt(count,2),rse_percent=rse_raw,raw_workbook_value=raw)) if col=='B':continue points.append(dict(group=group,label=label,measure=measure,households_millions=fmt(count,2),denominator_millions=fmt(den,2),point_estimate_percent=fmt(count/den*100),source_cell=col+str(i),denominator_cell='B'+str(i),numerator_rse_percent=rse_raw,denominator_rse_percent=uncertainty['B'],status='Preliminary RECS 2024 estimate, released March 2026')) precision.append(dict(group=group,label=label,measure=measure,count_millions=fmt(count,2),numerator_rse_percent=rse_raw,denominator_rse_percent=uncertainty['B'],count_standard_error_millions=''if rse_value is None else fmt(count*rse_value/100),ratio_ci='Not calculated, joint covariance unavailable in this table',source_row=i)) lookup={(r['group'],r['label'],r['measure']):r for r in points};comparisons=[] pairs=[('Income extremes','Annual household income','Less than $10,000','$200,000 or more'),('Rent versus ownership','Tenure','Rented','Owned'),('Mobile versus detached','Housing type','Mobile home','Single-family detached'),('Uninsulated versus well insulated','Insulation','Not insulated','Well insulated'),('Urban versus rural','Urban or rural','Urban','Rural'),('South versus Northeast','Census region','South','Northeast'),('Children present versus absent','Children under 18','Yes','No')] for title,group,left,right in pairs: a=lookup[group,left,'any_insecurity'];b=lookup[group,right,'any_insecurity'];av=D(a['point_estimate_percent']);bv=D(b['point_estimate_percent']) comparisons.append(dict(comparison=title,left_group=left,right_group=right,left_percent=fmt(av),right_percent=fmt(bv),difference_percentage_points=fmt(av-bv),point_estimate_ratio=fmt(av/bv),qualification='Descriptive point estimates, not a significance test or causal effect',left_cell=a['source_cell'],right_cell=b['source_cell'])) national=[r for r in points if r['group']=='National'];national_den=D(data[6]['B']);partition=[] for group in ['Census region','Urban or rural','Climate region','Housing type','Construction period','Insulation','Household members','Annual household income','Householder 60 or older','Children under 18','Tenure']: rr=[r for r in points if r['group']==group and r['measure']=='any_insecurity'] partition.append(dict(group=group,category_count=len(rr),sum_households_millions=fmt(sum(D(r['denominator_millions'])for r in rr),2),published_all_homes_millions=fmt(national_den,2),denominator_residual_millions=fmt(sum(D(r['denominator_millions'])for r in rr)-national_den,2),insecurity_sum_millions=fmt(sum(D(r['households_millions'])for r in rr),2),published_any_insecurity_millions='43.56',insecurity_residual_millions=fmt(sum(D(r['households_millions'])for r in rr)-D('43.56'),2),note='Source-rounded categories may not sum, not redistributed')) assert all(abs(D(r['denominator_residual_millions']))<=D('.03')for r in partition) save('source-counts-and-rse.csv',source);save('national-hardship-measures.csv',national);save('subgroup-hardship-rates.csv',points);save('descriptive-group-comparisons.csv',comparisons);save('survey-precision-register.csv',precision);save('source-rounded-partition-audit.csv',partition) result={'status':'PASS','source_group_rows':len(groups),'source_count_cells':len(source),'derived_rate_rows':len(points),'national_denominator_millions':'132.54','national_insecurity_millions':'43.56','national_insecurity_pct':next(r['point_estimate_percent']for r in national if r['measure']=='any_insecurity'),'date_status':'Preliminary2024 observations, March2026release','numerator_denominator_consistent':True,'no_marginal_hardship_sum':True,'no_significance_or_causal_claim':True,'comparisons':comparisons} (P/'verification.json').write_text(json.dumps(result,indent=2)+'\n');print(json.dumps({k:v for k,v in result.items()if k!='comparisons'},indent=2)) if __name__=='__main__':run()