"""Build the site's static data: one aggregate JSON + CSV, no raw tables. Inputs (all local copies; the shared hasadna.duckdb was read once, read-only, by extract.py): data/x_budget.parquet, data/x_phys24.parquet CBS Local Authorities 2021-2024 (etl_cbs.py) data/lamas_muni.parquet CBS 2015 profile via Budget Key (budget.lamas_muni) data/muni_budgets.parquet Budget Key municipal budget lines (budget.muni_budgets) data/cbs_raw/bycode2024.xlsx CBS localities 2024: English names + ITM coordinates data/cpi_annual_2024base.json CBS CPI 120010, annual averages, 2024 = 100 """ import json import sys from pathlib import Path import duckdb import openpyxl import pandas as pd ROOT = Path(__file__).resolve().parent.parent OUT = Path(sys.argv[1]) if len(sys.argv) > 1 else ROOT / "site" SQL = (ROOT / "work" / "export.sql").read_text() RC_EN = { 5569: "Al-Kasom", 5565: "Al-Batuf", 5545: "Alona", 5538: "Eshkol", 5533: "Be'er Tuvia", 5566: "Bustan al-Marj", 5541: "Bnei Shimon", 5528: "Brenner", 5532: "Gderot", 5571: "Golan", 5576: "Gush Etzion", 5530: "Gezer", 5527: "Gan Raveh", 5520: "Drom HaSharon", 5508: "HaGilboa", 5501: "Upper Galilee", 5503: "Lower Galilee", 5554: "Central Arava", 5578: "Har Hevron", 5512: "Zevulun", 5553: "Hevel Eilot", 5529: "Hevel Yavne", 5525: "Hevel Modi'in", 5536: "Hof Ashkelon", 5515: "Hof HaCarmel", 5519: "Hof HaSharon", 5535: "Yoav", 5518: "Lev HaSharon", 5550: "Lakhish", 5555: "Mevo'ot HaHermon", 5513: "Megiddo", 5574: "Megilot Dead Sea", 5504: "Mateh Asher", 5573: "Mateh Binyamin", 5526: "Mateh Yehuda", 5514: "Menashe", 5552: "Ma'ale Yosef", 5502: "Merom HaGalil", 5542: "Merhavim", 5556: "Misgav", 5568: "Neve Midbar", 5531: "Nahal Sorek", 5506: "Emek HaYarden", 5507: "Emek HaMa'ayanot", 5516: "Emek Hefer", 5509: "Emek Yizre'el", 5575: "Arvot HaYarden", 5548: "Ramat HaNegev", 5540: "Sdot Dan", 5539: "Sdot Negev", 5572: "Shomron", 5537: "Sha'ar HaNegev", 5534: "Shafir", 5551: "Tamar", } DISTRICT_EN = {"ירושלים": "Jerusalem", "הצפון": "North", "חיפה": "Haifa", "המרכז": "Center", "תל אביב": "Tel Aviv", "הדרום": "South", "אזור יהודה והשומרון": "Judea & Samaria"} def localities(): wb = openpyxl.load_workbook(ROOT / "data/cbs_raw/bycode2024.xlsx", read_only=True, data_only=True) rows = list(wb.worksheets[0].iter_rows(values_only=True)) df = pd.DataFrame(rows[1:], columns=rows[0]).rename(columns={ "סמל יישוב": "loc_code", "שם יישוב באנגלית": "loc_en", "קואורדינטות": "coord", "סמל רשות מקומית": "auth", "סך הכל אוכלוסייה 2024": "loc_pop"}) def xy(v): s = str(int(v)) if v and v == v else "" if len(s) == 12: return int(s[:6]), int(s[6:]) if len(s) == 10: # 10-digit form is in units of 10 m return int(s[:5]) * 10, int(s[5:]) * 10 return None, None df[["x", "y"]] = df["coord"].apply(lambda v: pd.Series(xy(v))) return df[df["x"].notna()] def facts(con, auth): """Every number quoted in the page prose, computed here so text and charts cannot drift apart.""" cs = con.execute("select * from cluster_stats where kind = 'City'").df().set_index("se_cluster") tg = con.execute("select * from trend_groups").df() pa = con.execute("select * from plan_actual where flag is null").df() nat = con.execute("select * from national where year = 2024").df().iloc[0] by = auth.set_index("code") f = {"nat_exp_pc": nat.exp_pc, "n_auth": len(auth), "pop_auth": auth["pop"].sum()} for c in (2, 8): r = cs.loc[c] f |= {f"c{c}_{k}": r[k] for k in ("exp_pc", "own_pc", "gov_pc", "grant_pc", "own_share", "nonres_pc", "edu_per_child", "gov_edu_pc", "n")} f[f"c{c}_edu_ministry_child"] = r["gov_edu_pc"] * r["pop"] / (auth[(auth.kind == "City") & (auth.se_cluster == c)].eval("pop * pct_0_17 / 100").sum()) f["ratio_exp_8_2"] = f["c8_exp_pc"] / f["c2_exp_pc"] f["own_gap_8_2"] = f["c8_own_pc"] - f["c2_own_pc"] # Pooled cluster figures are dominated by Jerusalem (cluster 2) and Tel Aviv-Yafo (cluster 8), so the # headline numbers use the typical (median) city; the pooled ones stay in the charts. cities = auth[auth.kind == "City"].assign(min_child=lambda d: d.gov_edu_pc / (d.pct_0_17 / 100)) for c in (2, 8): g = cities[cities.se_cluster == c] for k in ("exp_pc", "own_pc", "gov_pc", "grant_pc", "own_share", "edu_per_child", "min_child"): f[f"m{c}_{k}"] = g[k].median() f["m_ratio_exp_8_2"] = f["m8_exp_pc"] / f["m2_exp_pc"] f["m_ratio_edu_8_2"] = f["m8_edu_per_child"] / f["m2_edu_per_child"] f["jlm_share_c2"] = by.loc[3000, "pop"] / cities[cities.se_cluster == 2]["pop"].sum() f["tlv_share_c8"] = by.loc[5000, "pop"] / cities[cities.se_cluster == 8]["pop"].sum() rk = cities.dropna(subset=["se_index"]).rank() for k in ("exp_pc", "own_pc", "gov_pc"): f[f"rho_{k}"] = rk["se_index"].corr(rk[k]) f["own_cross"] = next(c for c in range(1, 10) if cs.loc[c, "own_pc"] > cs.loc[c, "gov_pc"]) tot = auth.assign(nr=auth.nonres_pc * auth["pop"]).sort_values("nr", ascending=False) f["top10_nonres_share"] = tot.nr.head(10).sum() / tot.nr.sum() f["top10_pop_share"] = tot["pop"].head(10).sum() / tot["pop"].sum() for code, key in ((2630, "kgat"), (7500, "sakhnin"), (4000, "haifa"), (6400, "herzliya"), (5000, "tlv")): f[f"{key}_nonres"] = by.loc[code, "nonres_pc"] nonrc = auth[auth.kind != "Regional council"] for c in (1, 3, 8, 9): f[f"coll_med_c{c}"] = nonrc[nonrc.se_cluster == c].collection_rate.median() wel = nonrc.groupby("se_cluster")[["welfare_pc", "income_support_per_1000"]].median() f["welf_c3"], f["welf_c8"] = wel.loc[3, "welfare_pc"], wel.loc[8, "welfare_pc"] f["isr_c3"], f["isr_c8"] = wel.loc[3, "income_support_per_1000"], wel.loc[8, "income_support_per_1000"] allr = tg[tg.kind == "All"].iloc[0] f |= {"trend_n": allr.n, "trend_real": allr.exp_pc_2024 / allr.exp_pc_2015_real - 1, "trend_nominal": allr.exp_pc_2024 / allr.exp_pc_2015_nominal - 1} ct = tg[(tg.kind == "City") & tg.se_cluster.notna()].set_index("se_cluster") for key, lo, hi in (("trend_c23_real", 2, 3), ("trend_c67_real", 6, 7)): f[key] = con.execute(f"""select (sum(a.exp_pc * a.pop) / sum(a.pop)) / (1000 * sum(c.exp15) / sum(c.pop15) / (select defl from cpi where year = 2015)) - 1 from auth a join comparable c using (code) where a.not_comparable is null and a.kind = 'City' and a.se_cluster between {lo} and {hi}""").fetchone()[0] cg = cities[cities.exp_pc_2015_real.notna()].assign(g=lambda d: d.exp_pc / d.exp_pc_2015_real - 1) f["mtrend_c23"] = cg[cg.se_cluster.between(2, 3)].g.median() f["mtrend_c67"] = cg[cg.se_cluster.between(6, 7)].g.median() f["ratio82_2015"] = ct.loc[8, "exp_pc_2015_real"] / ct.loc[2, "exp_pc_2015_real"] f["ratio82_2024"] = ct.loc[8, "exp_pc_2024"] / ct.loc[2, "exp_pc_2024"] f["plan_excluded"] = con.execute("select count(*) from plan_actual where flag is not null").fetchone()[0] f |= {"plan_n": len(pa), "plan_over": int((pa.gap > 0).sum()), "plan_median_gap": pa.gap.median(), "plan_max_gap": pa.gap.max()} em = auth[auth.emergency.fillna(False)] f["emerg_n"] = len(em) f["emerg_total_m"] = (em.emergency_pc * em["pop"]).sum() / 1e6 return {k: (float(v) if hasattr(v, "__float__") else v) for k, v in f.items()} def main(): con = duckdb.connect() cpi = json.loads((ROOT / "data/cpi_annual_2024base.json").read_text()) con.register("cpi_df", pd.DataFrame({"year": [int(k) for k in cpi], "defl": [v / 100 for v in cpi.values()]})) con.execute("create table cpi as select * from cpi_df") con.execute(f"create view mb as select * from '{ROOT}/data/muni_budgets.parquet'") con.execute(f"create view lm as select * from '{ROOT}/data/lamas_muni.parquet'") con.execute(f"create view x_budget as select * from '{ROOT}/data/x_budget.parquet'") con.execute(f"create view x_phys24 as select * from '{ROOT}/data/x_phys24.parquet'") for stmt in [s for s in SQL.split(";\n") if s.strip()]: con.execute(stmt) loc = localities() w = loc.assign(w=pd.to_numeric(loc["loc_pop"], errors="coerce").fillna(0) + 1) # +1 so empty localities still count cent = w.assign(wx=w.x * w.w, wy=w.y * w.w).groupby("auth")[["wx", "wy", "w"]].sum() cent = pd.DataFrame({"code": cent.index.astype(int), "x": (cent.wx / cent.w).round(), "y": (cent.wy / cent.w).round()}) en = loc[["loc_code", "loc_en"]].dropna().rename(columns={"loc_code": "code"}).astype({"code": int}) con.register("cent", cent) con.register("en", en) auth = con.execute("select a.*, c.x, c.y, e.loc_en en_loc from auth a left join cent c on c.code=a.code left join en e on e.code=a.code order by a.code").df() auth["en"] = [RC_EN.get(c) or n for c, n in zip(auth["code"], auth["en_loc"])] assert auth["en"].notna().all(), auth[auth["en"].isna()][["code", "name"]] assert auth["x"].notna().all(), auth[auth["x"].isna()][["code", "name"]] auth["district_en"] = auth["district"].map(DISTRICT_EN) assert auth["district_en"].notna().all() auth = auth.drop(columns=["en_loc"]) def tidy(v): # shekel amounts to whole shekels; ratios, indices and percentages keep 3 decimals if isinstance(v, float): return None if v != v else (round(v) if abs(v) >= 100 else round(v, 3)) return v def recs(df): return [{k: tidy(v) for k, v in r.items()} for r in json.loads(df.to_json(orient="records"))] payload = { "meta": json.loads((ROOT / "work/meta.json").read_text()), "facts": facts(con, auth), "cpi": cpi, "authorities": recs(auth), "series": recs(con.execute("select * from series order by code, year").df()), "plan_actual": recs(con.execute("select * from plan_actual order by year, code").df()), "bk_coverage": recs(con.execute("select * from bk_coverage order by year").df()), "cluster_stats": recs(con.execute("select * from cluster_stats order by kind, se_cluster").df()), "trend_groups": recs(con.execute("select * from trend_groups order by kind, se_cluster").df()), "national": recs(con.execute("select * from national order by year").df()), "dots": loc[["x", "y"]].astype(int).values.tolist(), } OUT.mkdir(parents=True, exist_ok=True) (OUT / "data.json").write_text(json.dumps(payload, ensure_ascii=False, separators=(",", ":"))) auth.to_csv(OUT / "authorities_2024.csv", index=False) print("authorities", len(auth), "series", len(payload["series"]), "plan rows", len(payload["plan_actual"]), "dots", len(payload["dots"]), "data.json KB", round((OUT / "data.json").stat().st_size / 1024)) if __name__ == "__main__": main()