"""Build FactHub dataset tables for the Thanksgiving dinner cost check. Inputs: AFBF classic dinner totals (AFBF Year-Over-Year Price Comparison 2024 PDF + 2025 news release), BLS series pulled from api.bls.gov v1 (data/bls_raw.json): CUUR0000SA0 CPI-U all items NSA; CUUR0000SAF11 CPI-U food at home NSA; CEU0500000008 avg hourly earnings, production & nonsupervisory employees, total private, NSA.""" import json, csv, os here = os.path.dirname(os.path.abspath(__file__)) b = json.load(open(os.path.join(here, 'data', 'bls_raw.json'))) afbf = {1986:28.74,1987:24.51,1988:26.61,1989:24.70,1990:28.85,1991:25.95,1992:26.39,1993:27.49,1994:28.40,1995:29.64,1996:31.66,1997:31.75,1998:33.09,1999:33.83,2000:32.37,2001:35.04,2002:34.56,2003:36.28,2004:35.68,2005:36.78,2006:38.10,2007:42.26,2008:44.61,2009:42.91,2010:43.47,2011:49.20,2012:49.48,2013:49.04,2014:49.41,2015:50.11,2016:49.87,2017:49.12,2018:48.90,2019:48.91,2020:46.90,2021:53.31,2022:64.05,2023:61.17,2024:58.08,2025:55.18} cpi, fah, wage = b['CUUR0000SA0'], b['CUUR0000SAF11'], b['CEU0500000008'] base, fbase = float(cpi['2025-M11']), float(fah['2025-M11']) rows = [] for y, c in afbf.items(): ci, fi, w = float(cpi[f'{y}-M11']), float(fah[f'{y}-M11']), float(wage[f'{y}-M11']) rows.append(dict(year=y, classic_dinner_usd=c, per_person_usd=round(c/10, 2), cpi_u_nov=ci, classic_dinner_nov2025_usd=round(c*base/ci, 2), food_at_home_cpi_nov=fi, classic_dinner_food_deflated_nov2025_usd=round(c*fbase/fi, 2), hourly_earnings_nov_usd=w, minutes_of_work=round(c/w*60, 1))) out = os.path.join(here, 'data') def wcsv(name, rs): with open(os.path.join(out, name), 'w', newline='', encoding='utf-8') as f: wr = csv.DictWriter(f, fieldnames=list(rs[0].keys()), lineterminator='\n'); wr.writeheader(); wr.writerows(rs) wcsv('afbf-classic-dinner-annual.csv', rows) items = [("16-pound turkey",21.50,-16.3),("14-ounce cubed stuffing mix",3.71,-9.0),("Two frozen pie crusts",3.37,-0.8),("Half pint whipping cream",1.87,3.2),("1 pound frozen peas",2.03,17.2),("1 dozen dinner rolls",3.56,-14.6),("Misc. ingredients",3.61,-4.7),("30-ounce can pumpkin pie mix",4.16,0.1),("1 gallon whole milk",3.73,16.3),("3 pounds sweet potatoes",4.00,37.0),("1-pound veggie tray (carrots and celery)",1.36,61.3),("12-ounce bag fresh cranberries",2.28,-2.8)] irows = [dict(item=i, price_2025_usd=p, pct_change_vs_2024=c) for i,p,c in items] assert abs(sum(p for _,p,_ in items)-55.18) < 0.005 wcsv('afbf-2025-items.csv', irows) mrows = [] for y in (2024, 2025, 2026): for m in range(1, 13): k = f'{y}-M{m:02d}' if k not in cpi: continue v = cpi[k]; fv = fah[k] mrows.append(dict(month=f'{y}-{m:02d}', cpi_u_all_items=None if v=='-' else float(v), cpi_u_food_at_home=None if fv=='-' else float(fv), hourly_earnings_usd=float(wage[k]))) wcsv('cpi-monthly-2024-2026.csv', mrows) json.dump(dict(annual=rows, items=irows, monthly=mrows), open(os.path.join(out,'tables.json'),'w'), indent=1) srt = sorted(rows, key=lambda r: r['classic_dinner_nov2025_usd']) print('lowest real', [(r['year'], r['classic_dinner_nov2025_usd']) for r in srt[:5]]) print('highest real', [(r['year'], r['classic_dinner_nov2025_usd']) for r in srt[-3:]]) srt2 = sorted(rows, key=lambda r: r['classic_dinner_food_deflated_nov2025_usd']) print('lowest food-deflated', [(r['year'], r['classic_dinner_food_deflated_nov2025_usd']) for r in srt2[:5]]) print('minutes', [(r['year'], r['minutes_of_work']) for r in rows if r['year'] in (1986,2000,2019,2020,2022,2024,2025)]) print('per person minutes 1986/2025', rows[0]['minutes_of_work']/10, rows[-1]['minutes_of_work']/10)