#!/usr/bin/env python3 """Parse the Fund Budgets chapter (printed pp. 53-97) of Bellingham's 2026 budget. Each fund block: 'NNN - Fund Name' header, five fiscal-year columns (FY2022..FY2026), BEGINNING RESERVE, OPERATING and CAPITAL sections with revenue/expenditure category lines, Net Surplus, ESTIMATED ENDING RESERVES. Wide columns sometimes render glued ('101,393,910105,448,143'); money tokens are re-split with a comma-group regex. We keep the last two columns (FY2025 preliminary, FY2026 adopted). Outputs: bellingham/data/fund_lines.csv fund x section x line item (FY25, FY26) bellingham/data/fund_dept_matrix.csv fund x department expenditures (FY26) """ import csv import re import sys from pathlib import Path import pdfplumber sys.path.insert(0, str(Path(__file__).resolve().parent)) from parse_bellingham_budget_positions import rebuild_lines # noqa: E402 ROOT = Path(__file__).resolve().parent.parent PDF = ROOT / "bellingham/raw/budget/2026-Adopted-Budget.pdf" DATA = ROOT / "bellingham/data" FIRST, LAST = 56, 100 # 1-indexed PDF pages (printed 54-98) MONEY = re.compile(r"\(?\d{1,3}(?:,\d{3})+\)?|\(?\b\d{1,6}\b\)?") FUND_HDR = re.compile(r"^(\d{3}[A-Z]?)\s*[-–]?\s+(.+?)\s*$") LINE_LABELS = ( "BEGINNING RESERVE", "TAXES", "LICENSES AND PERMITS", "INTERGOVERNMENTAL REVENUE", "CHARGES FOR GOODS AND SRVS.", "FINES AND PENALTIES", "MISCELLANEOUS REVENUE", "NON REVENUES", "OTHER FINANCING SOURCES", "TOTAL REVENUE", "NON-EXPENDITURES", "SALARIES AND WAGES", "PERSONNEL BENEFITS", "SUPPLIES", "OTHER SERVICES AND CHARGES", "CAPITAL OUTLAYS", "DEBT SERVICE PRINCIPAL", "DEBT SERVICE INTEREST/COSTS", "TOTAL EXPENDITURE", "Net Surplus/(Deficit)", "ESTIMATED ENDING RESERVES", ) def parse_money(tok): if tok in ("-", "–"): return 0 neg = tok.startswith("(") v = int(tok.strip("()").replace(",", "")) return -v if neg else v def money_tokens(text): return [parse_money(t) for t in MONEY.findall(text)] VALUE = re.compile(r"\(?\d{1,3}(?:,\d{3})+\)?|\(?\d{1,6}\)?|[-–]") def value_tokens(text): """Tokenize a label-free column region; lone dashes are zero values.""" return [parse_money(t) for t in VALUE.findall(text)] def parse(): fund_lines, dept_rows = [], [] fund_code = fund_name = None section = "OPERATING" in_dept_table = False pending_nums = None # numbers-only line whose label renders on the next line with pdfplumber.open(PDF) as pdf: for pageno in range(FIRST, LAST + 1): for _, clusters in rebuild_lines(pdf.pages[pageno - 1]): line = " ".join(t for _, t in clusters).strip() if not line or line.startswith(("City of Bellingham", "Page ", "2026 Adopted Budget")): continue if line.startswith("Citywide Total"): fund_code, fund_name = "ALL", "Citywide Total - All Funds" section, in_dept_table = "OPERATING", False continue m = FUND_HDR.match(line) if (m and not MONEY.search(m.group(2)) and re.match(r"[A-Za-z]", m.group(2))): label = m.group(2).split("ACTUAL")[0].strip() if label and len(label.split()) <= 8: fund_code, fund_name = m.group(1), label section, in_dept_table = "OPERATING", False pending_nums = None continue if line.startswith("Expenditures by Department"): in_dept_table = True continue if line == "CAPITAL": section = "CAPITAL" continue if line == "OPERATING": section = "OPERATING" continue if fund_code is None: continue label_hit = next((lb for lb in LINE_LABELS if line.startswith(lb)), None) nums = (value_tokens(line[len(label_hit):]) if label_hit else money_tokens(line)) # Interleaved rendering (e.g. REET page): a numbers-only line # followed by its bare label on the next line. if not label_hit and nums and not re.search(r"[A-Za-z]", line): pending_nums = value_tokens(line) continue if label_hit and not nums and pending_nums: nums = pending_nums pending_nums = None elif label_hit: pending_nums = None if label_hit and len(nums) >= 2: if label_hit in ("BEGINNING RESERVE", "Net Surplus/(Deficit)", "ESTIMATED ENDING RESERVES"): sec = "RESERVES" else: sec = section fund_lines.append({ "fund_code": fund_code, "fund_name": fund_name, "section": sec, "line_item": label_hit, "fy2025": nums[-2], "fy2026": nums[-1], "page": pageno, }) in_dept_table = False elif in_dept_table and len(nums) >= 2: dept = MONEY.split(line)[0].strip(" -–") if dept and not dept[0].isdigit(): vals = value_tokens(line[len(dept):]) dept_rows.append({ "fund_code": fund_code, "fund_name": fund_name, "department": dept, "fy2025": vals[-2], "fy2026": vals[-1], "page": pageno, }) return fund_lines, dept_rows def main(): fund_lines, dept_rows = parse() with open(DATA / "fund_lines.csv", "w", newline="") as f: w = csv.DictWriter(f, fieldnames=list(fund_lines[0].keys())) w.writeheader() w.writerows(fund_lines) with open(DATA / "fund_dept_matrix.csv", "w", newline="") as f: w = csv.DictWriter(f, fieldnames=list(dept_rows[0].keys())) w.writeheader() w.writerows(dept_rows) funds = sorted({(r["fund_code"], r["fund_name"]) for r in fund_lines}) print(f"funds: {len(funds)}, lines: {len(fund_lines)}, " f"dept rows: {len(dept_rows)}") for code, name in funds: rev = sum(r["fy2026"] for r in fund_lines if r["fund_code"] == code and r["line_item"] == "TOTAL REVENUE") exp = sum(r["fy2026"] for r in fund_lines if r["fund_code"] == code and r["line_item"] == "TOTAL EXPENDITURE") print(f" {code} {name[:42]:42} rev {rev:>13,} exp {exp:>13,}") if __name__ == "__main__": main()