docs/cities/miami/probe/ccd_trend.py

import os as _os; _os.chdir(_os.environ.get("MIAMI_PROBE_DIR", "data/cities/miami/sources/_probe"))
import pandas as pd, numpy as np, glob
f = glob.glob("ccd/unz_1819_dir/*.csv")[0]
d18 = pd.read_csv(f, dtype=str, low_memory=False, encoding="latin1")
d18 = d18[d18.ST_LEAID=="FL-13"][["ST_SCHID","SCH_NAME","SY_STATUS_TEXT","SCH_TYPE_TEXT","CHARTER_TEXT","LEVEL"]]
d24 = pd.read_csv("ccd/mdc_directory_enrollment_2324.csv", dtype={"ST_SCHID": str})
def totals(path):
    m = pd.read_csv(path, dtype=str)
    t = m[m.TOTAL_INDICATOR=="Education Unit Total"][["ST_SCHID","STUDENT_COUNT"]].copy()
    t["enr"] = pd.to_numeric(t.STUDENT_COUNT, errors="coerce"); return t[["ST_SCHID","enr"]], m
t18, m18 = totals("ccd/mdc_membership_1819.csv"); t24, m24 = totals("ccd/mdc_membership_2324.csv")
a = d18.merge(t18, on="ST_SCHID", how="left").rename(columns={"enr":"enr18"})
a = a[a.SY_STATUS_TEXT.isin(["Open","New","Added"])]
b = d24[["ST_SCHID","enr","CHARTER_TEXT","SCH_TYPE_TEXT","LEVEL","SCH_NAME"]].rename(columns={"enr":"enr24"})
print("2018-19 open MDC schools:", len(a), "enrollment", int(a.enr18.sum()))
print("2023-24 open MDC schools:", len(b), "enrollment", int(b.enr24.sum()))
print("\nBy sector (all open schools each year):")
print(" 2018-19:", a.groupby("CHARTER_TEXT").enr18.agg(["count","sum"]).to_dict("index"))
print(" 2023-24:", b.groupby("CHARTER_TEXT").enr24.agg(["count","sum"]).to_dict("index"))
j = a.merge(b, on="ST_SCHID", how="outer", suffixes=("_18","_24"), indicator=True)
print("\nmerge:", j._merge.value_counts().to_dict())
gone = j[j._merge=="left_only"]; new = j[j._merge=="right_only"]
print("schools in 2018-19 but not 2023-24:", len(gone), "by charter:", gone.CHARTER_TEXT_18.value_counts().to_dict(), "enrollment 2018-19:", int(gone.enr18.sum()))
print("schools in 2023-24 but not 2018-19:", len(new), "by charter:", new.CHARTER_TEXT_24.value_counts().to_dict(), "enrollment 2023-24:", int(new.enr24.sum()))
both = j[(j._merge=="both") & (j.enr18>0) & (j.enr24>0)].copy()
both["pct"] = (both.enr24/both.enr18 - 1)*100
reg = both[(both.CHARTER_TEXT_24=="No") & (both.SCH_TYPE_TEXT_24=="Regular School")]
print(f"\nContinuing district-run regular schools: n={len(reg)}; total {int(reg.enr18.sum())} → {int(reg.enr24.sum())} ({(reg.enr24.sum()/reg.enr18.sum()-1)*100:.1f}%)")
print("school-level % change distribution:\n", reg.pct.describe(percentiles=[.1,.25,.5,.75,.9]).round(1).to_string())
for t in [-10,-20,-25,-33]:
    print(f"  schools with change <= {t}%: {(reg.pct<=t).sum()} ({(reg.pct<=t).mean()*100:.1f}%)")
print("  schools that grew:", (reg.pct>0).sum())
print("\nby level (continuing district-run regular): total change %")
print(reg.groupby("LEVEL_24").apply(lambda g: pd.Series({"n":len(g),"enr18":int(g.enr18.sum()),"enr24":int(g.enr24.sum()),"pct":round((g.enr24.sum()/g.enr18.sum()-1)*100,1),"median_school_pct":round(g.pct.median(),1)})).to_string())
ch = both[both.CHARTER_TEXT_24=="Yes"]
print(f"\nContinuing charters: n={len(ch)}; total {int(ch.enr18.sum())} → {int(ch.enr24.sum())} ({(ch.enr24.sum()/ch.enr18.sum()-1)*100:.1f}%)")
# grade pipeline both years
def bygrade(m):
    g = m[m.TOTAL_INDICATOR=="Subtotal 4 - By Grade"].copy(); g["n"]=pd.to_numeric(g.STUDENT_COUNT, errors="coerce"); return g.groupby("GRADE").n.sum()
g18, g24 = bygrade(m18), bygrade(m24)
order = ["Pre-Kindergarten","Kindergarten"]+[f"Grade {i}" for i in range(1,13)]
gg = pd.DataFrame({"2018-19":g18.reindex(order),"2023-24":g24.reindex(order)}); gg["pct"]=((gg["2023-24"]/gg["2018-19"]-1)*100).round(1)
print("\nBy grade, all MDC schools incl. charters:\n", gg.astype({"2018-19":int,"2023-24":int}).to_string())
# the 25 largest losers among continuing district-run regular schools
print("\n25 largest % losses, continuing district-run regular schools (enr18>=200):")
print(reg[reg.enr18>=200].sort_values("pct").head(25)[["ST_SCHID","SCH_NAME_24","LEVEL_24","enr18","enr24","pct"]].round(1).to_string(index=False))
reg[["ST_SCHID","SCH_NAME_24","LEVEL_24","enr18","enr24","pct"]].to_csv("ccd/mdc_district_regular_trend_1819_2324.csv", index=False)