scripts/analysis/inspect_sqr.py

#!/usr/bin/env python
"""Verify what comparison-group data NYC publishes in the School Quality Report
(SQR) 'Citywide Results' Excel files. Downloads a few years of EMS/HS files and
reports: which sheet holds the metrics, the per-metric column family
(Metric Value / N Count / Comparison Group / Metric Rating), a sample row, and
school-level coverage. Read-only; writes nothing back. Files are public.

Run: scripts/analysis/.venv/bin/python scripts/analysis/inspect_sqr.py
(needs openpyxl + requests in the venv)
"""
import io
import re
import openpyxl
import urllib.request

BASE = "https://infohub.nyced.org/docs/default-source/default-document-library"
FILES = {
    "2024-25 EMS": "202425-ems-sqr-results.xlsx",
    "2023-24 EMS": "202324-ems-sqr-results.xlsx",
    "2024-25 HS": "202425-hs-sqr-results.xlsx",
    "2023-24 HS": "202324-hs-sqr-results.xlsx",
    "2018-19 EMS": "201819-ems-sqr-results.xlsx",  # older format, for comparison
}

def header_row(ws, maxr=6):
    rows = [list(r) for r in ws.iter_rows(min_row=1, max_row=maxr, values_only=True)]
    i = max(range(len(rows)), key=lambda i: sum(1 for c in rows[i] if isinstance(c, str)))
    return i, [(str(c).replace("\n", " ").strip() if c is not None else None) for c in rows[i]]

for label, fname in FILES.items():
    raw = urllib.request.urlopen(f"{BASE}/{fname}", timeout=120).read()
    wb = openpyxl.load_workbook(io.BytesIO(raw), read_only=True, data_only=True)
    ms = next((s for s in ("Instruction and Performance", "Student Achievement") if s in wb.sheetnames),
              wb.sheetnames[1])
    ws = wb[ms]
    hi, hdr = header_row(ws)
    mv = [c for c in hdr if c and c.startswith("Metric Value")]
    cg = [c for c in hdr if c and c.startswith("Comparison Group")]
    mr = [c for c in hdr if c and c.startswith("Metric Rating")]
    print(f"\n{label}: sheets={wb.sheetnames}")
    print(f"  metric sheet='{ms}'  Metric Value cols={len(mv)}  "
          f"Comparison Group cols={len(cg)}  Metric Rating cols={len(mr)}")
    wb.close()

print("""
Finding: each metric X is published as a family — 'Metric Value - X' (the school),
'N Count - X', 'Comparison Group - X' (the peer pool's benchmark VALUE, not a
within-group percentile), and 'Metric Rating - X'. No peer-school roster is
published. Sheet names and metric sets change across years (2018-19 differs from
2023-24), so ingestion needs a per-year-format mapping.""")