#!/usr/bin/env python3 """Parse the Budgeted Positions chapter of Bellingham's adopted budget PDF. Pages render inconsistently (some with per-character text runs), so all text is rebuilt from character coordinates rather than pdfplumber's default extraction. Outputs: bellingham/data/budget_positions.csv — one row per job classification per department bellingham/data/budget_positions.json — same rows plus dept subtotals/totals and staffing-summary-by-unit rows for validation """ import csv import json import re import sys from collections import defaultdict from pathlib import Path import pdfplumber ROOT = Path(__file__).resolve().parent.parent PDF = ROOT / "bellingham/raw/budget/2026-Adopted-Budget.pdf" OUT_DIR = ROOT / "bellingham/data" # 1-indexed page range of the Budgeted Positions department tables (from TOC). FIRST_PAGE, LAST_PAGE = 182, 198 # p.199 is the citywide 10-year history EMPLOYEE_GROUPS = [ "Local 1937", "Non-Union", "Teamster 231", "Police Guild", "FOP", "W-C Disp Guild", "AFSCME 114F", "AFSCME 114L", "IAFF 106S", "IAFF 106", "Temporary", "What-Comm Guild", "Elected", ] NUM_RE = re.compile(r"^\d{1,4}\.\d$") FUNC_AREA_RE = re.compile(r"Budgeted Positions\s*[-–]\s*(.+)$") def rebuild_lines(page, line_tol=2.0): """Return [(doctop, [(x0, text), ...])] word clusters per visual line.""" lines = defaultdict(list) for c in page.chars: key = round(c["doctop"] / line_tol) * line_tol lines[key].append(c) result = [] for key in sorted(lines): chars = sorted(lines[key], key=lambda c: c["x0"]) clusters = [] cur, cur_x0, prev_x1 = "", None, None for c in chars: if prev_x1 is not None and c["x0"] - prev_x1 > c["size"] * 1.2: clusters.append((cur_x0, cur)) cur, cur_x0 = "", None if cur_x0 is None: cur_x0 = c["x0"] cur += c["text"] prev_x1 = c["x1"] if cur: clusters.append((cur_x0, cur)) result.append((key, clusters)) return result def classify_clusters(clusters): """Split a line's clusters into (title_parts, numbers[(x, val)], groups).""" title_parts, numbers, groups = [], [], [] for x0, text in clusters: text = text.strip() if not text: continue # A cluster can glue value+group together (e.g. "1.0Local 1937") or two # values ("46.0 46.0"); split into tokens and re-scan. rem = text matched_group = None for g in EMPLOYEE_GROUPS: if rem.endswith(g): matched_group = g rem = rem[: -len(g)].strip() break toks = rem.split() if rem else [] nums_here = [t for t in toks if NUM_RE.match(t)] if nums_here and len(nums_here) == len(toks): # cluster is purely numeric; x-position of the cluster start width = 30.0 for i, t in enumerate(nums_here): numbers.append((x0 + i * width, float(t))) elif toks: title_parts.append((x0, " ".join(toks))) if matched_group: groups.append(matched_group) return title_parts, numbers, groups def is_header_or_footer(text): t = text.strip() return ( t in ("Budgeted Positions", "DEPARTMENT", "FTE") or t.startswith("City of Bellingham") or t.startswith("2026 Adopted Budget") or t.startswith("Page ") or t.endswith("Adopted Budget") or "RevisedAdopted" in t or re.match(r"^Revised\s*Adopted$", t) or t in ("Employee", "Group", "Revised", "Adopted", "2025 2026", "2025", "2026") or re.match(r"^Revised\s*Adopted\s*Employee$", t) or re.match(r"^DEPARTMENT\b", t) ) def parse(): rows = [] # classification rows meta_rows = [] # subtotal / total / summary rows for validation func_area = None department = None in_summary = False pending_title = [] # title-only lines awaiting a data row with pdfplumber.open(PDF) as pdf: for pageno in range(FIRST_PAGE, LAST_PAGE + 1): page = pdf.pages[pageno - 1] for _, clusters in rebuild_lines(page): full_text = " ".join(t for _, t in sorted(clusters)).strip() if not full_text or is_header_or_footer(full_text): continue m = FUNC_AREA_RE.search(full_text) if m: func_area = m.group(1).strip() continue title_parts, numbers, groups = classify_clusters(clusters) title = " ".join(t for _, t in title_parts).strip() # Page-continuation department header, e.g. "PUBLIC WORKS (Continued)" cont = re.match(r"^([A-Z][A-Z &]+?)\s*\(Continued\)$", title) if cont and not numbers: department = cont.group(1).strip().title() pending_title = [] continue # Group-only line: unit label wrapped below its row's numbers if groups and not title and not numbers: if rows and rows[-1]["employee_group"] is None: rows[-1]["employee_group"] = groups[0] continue # Department header: all-caps title line, no numbers if title and not numbers and title.isupper() and len(title) > 3: if title.startswith("STAFFING SUMMARY"): in_summary = True continue if title.startswith(("TOTAL ", "SUBTOTAL")): pass # handled below (totals usually carry numbers) else: department = title.title() in_summary = False pending_title = [] continue kind = "position" tl = title.lower() if tl.startswith("subtotal"): kind = "subtotal" elif tl.startswith("total "): kind = "total" elif in_summary: kind = "unit_summary" if not numbers: if title and kind == "position": # A wrapped title tail belongs to the previous row when # that row's title ends mid-phrase (trailing comma). if rows and rows[-1]["title"].rstrip().endswith(","): rows[-1]["title"] = rows[-1]["title"].rstrip() + " " + title else: pending_title.append(title) continue # Assemble values: expect 2 numbers (2025, 2026); if one, flag # for column assignment in the per-page post-pass. pairs = sorted(numbers) nums = [v for _, v in pairs] fte_2025 = nums[0] if len(nums) >= 1 else None fte_2026 = nums[1] if len(nums) >= 2 else None flag = "" if len(nums) == 2 else f"parsed_{len(nums)}_values" if pending_title and kind == "position": title = (" ".join(pending_title) + " " + title).strip() pending_title = [] # Numeric row with no recoverable label = a TOTAL line whose # caption was lost in the scrambled render; keep as meta only. if not title and kind == "position": kind = "unlabeled_total" rec = { "functional_area": func_area, "department": department, "title": title, "fte_2025_revised": fte_2025, "fte_2026_adopted": fte_2026, "employee_group": groups[0] if groups else None, "kind": kind, "page": pageno, "flag": flag, "_num_x": [x for x, _ in pairs], } if kind == "position": rows.append(rec) else: meta_rows.append(rec) # Post-pass: place single values into the right year column using the # x-positions of that page's unambiguous two-value rows. col1_by_page = defaultdict(list) for r in rows + meta_rows: if len(r["_num_x"]) == 2: col1_by_page[r["page"]].append(r["_num_x"][0]) for r in rows + meta_rows: if len(r["_num_x"]) == 1 and col1_by_page[r["page"]]: c1 = sum(col1_by_page[r["page"]]) / len(col1_by_page[r["page"]]) if r["_num_x"][0] > c1 + 12: # right of the 2025 column: it's 2026 r["fte_2026_adopted"] = r["fte_2025_revised"] r["fte_2025_revised"] = 0.0 r["flag"] += "|assigned_to_2026" else: r["fte_2026_adopted"] = 0.0 r["flag"] += "|assigned_to_2025" for r in rows + meta_rows: del r["_num_x"] return rows, meta_rows def validate(rows, meta_rows): report = [] by_dept = defaultdict(lambda: [0.0, 0.0]) for r in rows: if r["employee_group"] == "Temporary": continue by_dept[r["department"]][0] += r["fte_2025_revised"] or 0 by_dept[r["department"]][1] += r["fte_2026_adopted"] or 0 for m in meta_rows: if m["kind"] == "subtotal" and m["department"] in by_dept: got = by_dept[m["department"]] exp = (m["fte_2025_revised"], m["fte_2026_adopted"]) ok = ( exp[0] is not None and abs(got[0] - exp[0]) < 0.05 and exp[1] is not None and abs(got[1] - exp[1]) < 0.05 ) report.append( f"{'OK ' if ok else 'MISMATCH'} {m['department']}: " f"summed {got[0]:.1f}/{got[1]:.1f} vs printed subtotal " f"{exp[0]}/{exp[1]}" ) return report HIST_NUM_RE = re.compile(r"\d{1,3}(?:,\d{3})?\.\d") def parse_citywide_history(): """Parse p.199's 10-year FTE history by employee group (2017-2026).""" out = [] with pdfplumber.open(PDF) as pdf: page = pdf.pages[198] for _, clusters in rebuild_lines(page): text = " ".join(t for _, t in clusters).strip() nums = [float(n.replace(",", "")) for n in HIST_NUM_RE.findall(text)] label = HIST_NUM_RE.split(text)[0].strip() if len(nums) == 10 and label and not label.startswith("Employee Group"): out.append({"employee_group": label, **{str(y): v for y, v in zip(range(2017, 2027), nums)}}) return out def main(): rows, meta_rows = parse() history = parse_citywide_history() OUT_DIR.mkdir(parents=True, exist_ok=True) with open(OUT_DIR / "budget_positions.csv", "w", newline="") as f: w = csv.DictWriter(f, fieldnames=list(rows[0].keys())) w.writeheader() w.writerows(rows) with open(OUT_DIR / "citywide_fte_history.csv", "w", newline="") as f: w = csv.DictWriter(f, fieldnames=list(history[0].keys())) w.writeheader() w.writerows(history) with open(OUT_DIR / "budget_positions.json", "w") as f: json.dump({"positions": rows, "meta": meta_rows, "citywide_history": history}, f, indent=1) print(f"positions: {len(rows)} meta rows: {len(meta_rows)} " f"history rows: {len(history)}") flagged = [r for r in rows if r["flag"]] print(f"flagged rows: {len(flagged)}") for r in flagged: print(" FLAG", r["page"], r["department"], "|", r["title"], "|", r["fte_2025_revised"], r["fte_2026_adopted"], r["flag"]) for line in validate(rows, meta_rows): print(line) # Citywide cross-check: department rows vs the history page's totals. reg25 = sum(r["fte_2025_revised"] or 0 for r in rows if r["employee_group"] not in ("Temporary",)) reg26 = sum(r["fte_2026_adopted"] or 0 for r in rows if r["employee_group"] not in ("Temporary",)) tmp25 = sum(r["fte_2025_revised"] or 0 for r in rows if r["employee_group"] == "Temporary") tmp26 = sum(r["fte_2026_adopted"] or 0 for r in rows if r["employee_group"] == "Temporary") hist = {h["employee_group"]: h for h in history} print(f"CITYWIDE regular: {reg25:.1f}/{reg26:.1f} " f"(history TOTAL REGULAR: {hist['TOTAL REGULAR']['2025']}/" f"{hist['TOTAL REGULAR']['2026']})") print(f"CITYWIDE temporary: {tmp25:.1f}/{tmp26:.1f} " f"(history Temporary Labor: {hist['Temporary Labor']['2025']}/" f"{hist['Temporary Labor']['2026']})") if __name__ == "__main__": sys.exit(main())