process_schools.py

"""Aggregate Ofsted school ratings to LSOA level.

Input:  data/Management_information_-_state-funded_schools_-_latest_inspections_as_at_31_Jan_2026.csv
        data/mapping/lookup_pc_lsoa.parquet
Output: data/schools.csv — Ofsted grades inverted so higher = better
"""

import pandas as pd

SCHOOLS_PATH = "data/Management_information_-_state-funded_schools_-_latest_inspections_as_at_31_Jan_2026.csv"
LOOKUP_PATH  = "data/mapping/lookup_pc_lsoa.parquet"
OUTPUT       = "data/schools.csv"

schools = pd.read_csv(SCHOOLS_PATH, encoding="latin1", usecols=[
    "Postcode",
    "Latest OEIF overall effectiveness",
])
schools = schools.rename(columns={
    "Postcode":                          "postcode",
    "Latest OEIF overall effectiveness": "grade",
})

schools = schools.dropna(subset=["postcode", "grade"])
schools["grade"] = pd.to_numeric(schools["grade"], errors="coerce")
schools = schools.dropna(subset=["grade"])
schools["grade"] = schools["grade"].astype(int)
schools = schools[schools["grade"].between(1, 4)]

print(f"Schools with valid grades: {len(schools)}")

lookup = pd.read_parquet(LOOKUP_PATH)[["postcode", "lsoa_code"]]
lookup["postcode"] = lookup["postcode"].str.strip().str.upper()
schools["postcode"] = schools["postcode"].str.strip().str.upper()

schools = schools.merge(lookup, on="postcode", how="left")
unmatched = schools["lsoa_code"].isna().sum()
print(f"Unmatched postcodes: {unmatched} ({unmatched/len(schools)*100:.1f}%)")
schools = schools.dropna(subset=["lsoa_code"])

# Invert: 1=Outstanding -> 4, 4=Inadequate -> 1
schools["grade_inv"] = 5 - schools["grade"]

agg = schools.groupby("lsoa_code").agg(
    school_count          = ("grade", "count"),
    school_avg_grade      = ("grade_inv", "mean"),
    school_pct_good       = ("grade", lambda x: (x <= 2).sum() / len(x) * 100),
    school_pct_outstanding= ("grade", lambda x: (x == 1).sum() / len(x) * 100),
).reset_index()

print(f"\nLSOAs with schools: {len(agg)}")
print(f"Nulls:\n{agg.isnull().sum()}")
print(agg.head(3).to_string())

agg.to_csv(OUTPUT, index=False)
print(f"\nSaved -> {OUTPUT}")