{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Home visit list before term end — analysis notebook\n",
    "\n",
    "*School attendance and dropout early warning · project deliverable*\n",
    "\n",
    "Cassion · data-analysis.cassion.dev\n",
    "\n",
    "[![Open In Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/thecassion/cassion-learning-platform/blob/main/apps/data-analysis/public/projects/school-attendance-analytics/notebooks/dropout-early-warning.python.en.ipynb)"
   ],
   "id": "cell-000"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The decision this produces\n",
    "\n",
    "Which students receive a home visit before the end of term.\n",
    "\n",
    "The problem the project exists to fix: dropout was measured at year end, by which\n",
    "point the student had already left and the programme could report the number but\n",
    "not change it. Attendance decays over roughly three weeks before a student stops\n",
    "coming, and that decay is the only reason early warning is possible.\n",
    "\n",
    "Audience: the education programme officer and head teachers.\n",
    "\n",
    "Every dataset on this platform is synthetic. No real student is represented.\n",
    "\n",
    "## Setup"
   ],
   "id": "cell-001"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "BASE = \"https://data-analysis.cassion.dev/datasets/files/\"\n",
    "\n",
    "DISENGAGED_DAYS = 15   # school days, not calendar days\n",
    "\n",
    "attendance = pd.read_csv(BASE + \"school-attendance-2024.v1.csv\",\n",
    "                         dtype={\"student_id\": \"string\", \"present\": \"string\"})\n",
    "roster = pd.read_csv(BASE + \"school-roster-2024.v1.csv\",\n",
    "                     dtype={\"student_id\": \"string\", \"school_id\": \"string\"})\n",
    "attendance[\"attendance_date\"] = pd.to_datetime(attendance[\"attendance_date\"])\n",
    "\n",
    "print(f\"{len(attendance):,} attendance rows, {len(roster)} roster rows\")"
   ],
   "id": "cell-002"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Three data problems, each of which changes the list"
   ],
   "id": "cell-003"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "PRESENT = {\n",
    "    \"true\": True, \"TRUE\": True, \"Y\": True, \"y\": True, \"yes\": True,\n",
    "    \"false\": False, \"FALSE\": False, \"N\": False, \"n\": False, \"no\": False,\n",
    "}\n",
    "attendance[\"present_clean\"] = attendance[\"present\"].str.strip().map(PRESENT)\n",
    "\n",
    "naive = attendance[\"present\"] == \"true\"\n",
    "\n",
    "pd.Series({\n",
    "    \"rows\": len(attendance),\n",
    "    \"never marked either way\": int(attendance[\"present_clean\"].isna().sum()),\n",
    "    \"attendance, naive boolean cast\": round(naive.mean(), 3),\n",
    "    \"attendance, coding harmonised\": round(attendance[\"present_clean\"].mean(), 3),\n",
    "})"
   ],
   "id": "cell-004"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One school recorded `Y`/`N`. A boolean cast turns those into missing values\n",
    "silently, and that school then supplies most of the top of any risk ranking —\n",
    "for a coding accident rather than for dropout."
   ],
   "id": "cell-005"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "duplicated = roster[roster.duplicated(\"student_id\", keep=False)]\n",
    "print(f\"students on the roster twice: {duplicated['student_id'].nunique()}\")\n",
    "\n",
    "roster_resolved = (\n",
    "    roster.sort_values(\"grade\", na_position=\"last\")\n",
    "    .drop_duplicates(\"student_id\", keep=\"first\")\n",
    ")\n",
    "\n",
    "before = len(attendance)\n",
    "daily = attendance.merge(\n",
    "    roster_resolved, on=\"student_id\", how=\"left\", validate=\"many_to_one\"\n",
    ")\n",
    "assert len(daily) == before, \"join changed the row count\"\n",
    "daily = daily.dropna(subset=[\"present_clean\"]).sort_values(\n",
    "    [\"student_id\", \"attendance_date\"]\n",
    ")\n",
    "print(f\"student-days analysed: {len(daily):,}\")"
   ],
   "id": "cell-006"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`validate=\"many_to_one\"` turns a silent row inflation into an error where it\n",
    "happens. Two students were registered twice after an unregistered transfer.\n",
    "\n",
    "## A missing row is a closure, not an absence"
   ],
   "id": "cell-007"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "calendar = sorted(daily[\"attendance_date\"].unique())\n",
    "school_days = daily.groupby(\"school_id\")[\"attendance_date\"].nunique()\n",
    "\n",
    "closures = {}\n",
    "for school in school_days[school_days < len(calendar)].index:\n",
    "    open_days = set(daily.loc[daily[\"school_id\"] == school, \"attendance_date\"])\n",
    "    closures[school] = sorted(set(calendar) - open_days)\n",
    "    print(f\"{school}: {len(closures[school])} days closed, \"\n",
    "          f\"{pd.Timestamp(closures[school][0]).date()} to \"\n",
    "          f\"{pd.Timestamp(closures[school][-1]).date()}\")\n",
    "\n",
    "closed_schools = list(closures)"
   ],
   "id": "cell-008"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Two schools were shut for fifteen days in March. Nothing goes wrong until the\n",
    "features are built on a student-by-date matrix — the natural shape for a\n",
    "run-length feature — because reindexing to the full calendar invents rows that\n",
    "were never recorded, and the obvious fill is \"absent\"."
   ],
   "id": "cell-009"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "matrix = (\n",
    "    daily.set_index([\"student_id\", \"attendance_date\"])[\"present_clean\"]\n",
    "    .unstack().reindex(columns=calendar)\n",
    ")\n",
    "filled = matrix.fillna(False).mean(axis=1)\n",
    "recorded = daily.groupby(\"student_id\")[\"present_clean\"].mean()\n",
    "\n",
    "in_closed = roster_resolved.set_index(\"student_id\")[\"school_id\"].isin(closed_schools)\n",
    "\n",
    "for label, rate in [(\"closure filled as absent\", filled), (\"recorded days only\", recorded)]:\n",
    "    flagged = rate < 0.70\n",
    "    share = in_closed.reindex(rate.index).fillna(False)[flagged].mean()\n",
    "    print(f\"{label:26} {int(flagged.sum()):>4} flagged, \"\n",
    "          f\"{share:.1%} from the two closed schools\")\n",
    "\n",
    "print(f\"\\nthose schools hold {in_closed.mean():.1%} of the roster\")"
   ],
   "id": "cell-010"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The fix is not an adjustment. It is **not reindexing** — every feature is\n",
    "computed on the days the student's own school recorded.\n",
    "\n",
    "## The early-warning features\n",
    "\n",
    "The signal is a recent run of absence, not a low total. A student who missed a\n",
    "fortnight in February and came back is not the same as one who has missed the\n",
    "last fortnight."
   ],
   "id": "cell-011"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "term_end = daily[\"attendance_date\"].max()\n",
    "\n",
    "def features(group):\n",
    "    present = group[\"present_clean\"].astype(bool).to_numpy()\n",
    "    dates = group[\"attendance_date\"].to_numpy()\n",
    "\n",
    "    run = 0\n",
    "    for value in present[::-1]:\n",
    "        if value:\n",
    "            break\n",
    "        run += 1\n",
    "\n",
    "    last_present = dates[present].max() if present.any() else pd.NaT\n",
    "    return pd.Series({\n",
    "        \"days_recorded\": len(group),\n",
    "        \"attendance_rate\": present.mean(),\n",
    "        \"trailing_absences\": run,\n",
    "        \"school_days_missed\": int((dates > last_present).sum()) if present.any() else len(group),\n",
    "        \"last_present\": last_present,\n",
    "    })\n",
    "\n",
    "students = daily.groupby(\"student_id\").apply(features, include_groups=False)\n",
    "students = students.join(\n",
    "    roster_resolved.set_index(\"student_id\")[[\"school_id\", \"grade\", \"feeding_programme\"]]\n",
    ")\n",
    "students[\"at_risk\"] = students[\"school_days_missed\"] > DISENGAGED_DAYS\n",
    "\n",
    "print(f\"flagged: {int(students['at_risk'].sum())} of {len(students)} \"\n",
    "      f\"({students['at_risk'].mean():.1%})\")"
   ],
   "id": "cell-012"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Closure days are absent from `school_days_missed` because they were never\n",
    "recorded, which is exactly the behaviour wanted — but note that the two closed\n",
    "schools are still somewhat over-represented among the flags, and that is\n",
    "reported rather than adjusted away. A three-week closure is a plausible trigger\n",
    "for real disengagement."
   ],
   "id": "cell-013"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "share = students.loc[students[\"at_risk\"], \"school_id\"].isin(closed_schools).mean()\n",
    "print(f\"{share:.1%} of flags are at the closed schools, \"\n",
    "      f\"which hold {students['school_id'].isin(closed_schools).mean():.1%} of students\")"
   ],
   "id": "cell-014"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The visit list\n",
    "\n",
    "Sorted by how long the student has been gone, then by how they attended before —\n",
    "a student who was at 95% and stopped three weeks ago is a different case from one\n",
    "at 40% all term."
   ],
   "id": "cell-015"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "visits = (\n",
    "    students[students[\"at_risk\"]]\n",
    "    .sort_values([\"school_days_missed\", \"attendance_rate\"], ascending=[False, True])\n",
    "    [[\"school_id\", \"grade\", \"attendance_rate\", \"trailing_absences\", \"school_days_missed\"]]\n",
    "    .round(3)\n",
    ")\n",
    "visits.head(15)"
   ],
   "id": "cell-016"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "by_school = students.groupby(\"school_id\").agg(\n",
    "    students=(\"at_risk\", \"size\"),\n",
    "    flagged=(\"at_risk\", \"sum\"),\n",
    ")\n",
    "by_school[\"rate\"] = (by_school[\"flagged\"] / by_school[\"students\"]).round(3)\n",
    "by_school.sort_values(\"flagged\", ascending=False).head(8)"
   ],
   "id": "cell-017"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Head teachers get their own school's list; the programme officer gets the school\n",
    "counts. A school with a flag rate well above the others is a conversation about\n",
    "the school, not about fifteen separate students.\n",
    "\n",
    "## What this ranks, and what it does not\n",
    "\n",
    "This ranks **risk**, not dropout. A student on the list may have transferred, be\n",
    "ill, or be helping at home during harvest. The output is a conversation to have\n",
    "before the term ends, not a status to record — and a home visit that arrives\n",
    "treating the student as a dropout will be the last one that family accepts.\n",
    "\n",
    "Roughly 3% of roster rows have no grade recorded. Those students are still\n",
    "ranked, because grade is not used in the score; it appears in the list only so a\n",
    "head teacher knows which class to ask about."
   ],
   "id": "cell-018"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "file_extension": ".py"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}