{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dropout risk from attendance patterns\n",
    "\n",
    "*School attendance, 2024*\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/datasets/examples/school-attendance-2024/dropout-risk.python.en.ipynb)"
   ],
   "id": "cell-000"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What this produces\n",
    "\n",
    "A per-student dropout risk ranking built from consecutive-absence patterns, in\n",
    "time to act before the term ends. Attendance decays over roughly three weeks\n",
    "before a student stops coming, and that decay is what makes early warning\n",
    "possible at all.\n",
    "\n",
    "Three things have to be right first, and each of them changes the ranking: the\n",
    "boolean coding, the join, and the difference between a closure and an absence.\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",
    "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",
    "\n",
    "attendance[\"attendance_date\"] = pd.to_datetime(attendance[\"attendance_date\"])\n",
    "\n",
    "print(f\"attendance rows: {len(attendance):,}\")\n",
    "print(f\"roster rows    : {len(roster):,}\")"
   ],
   "id": "cell-002"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The boolean that is not a boolean\n",
    "\n",
    "One school recorded attendance with `Y` and `N` instead of `true` and `false`.\n",
    "A boolean cast turns those into missing values silently — and it is not a random\n",
    "30% of the file, it is one school."
   ],
   "id": "cell-003"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(attendance[\"present\"].value_counts(dropna=False))"
   ],
   "id": "cell-004"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "naive = attendance[\"present\"] == \"true\"\n",
    "\n",
    "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\"] = (\n",
    "    attendance[\"present\"].str.strip().map(PRESENT)\n",
    ")\n",
    "\n",
    "print(f\"unparsed after mapping: {int(attendance['present_clean'].isna().sum())} \"\n",
    "      \"(genuinely blank — never marked either way)\")\n",
    "print(f\"naive cast attendance rate  : {naive.mean():.3f}\")\n",
    "print(f\"correct attendance rate     : {attendance['present_clean'].mean():.3f}\")"
   ],
   "id": "cell-005"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The overall difference looks small. Look at the affected school on its own:"
   ],
   "id": "cell-006"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "roster_unique = roster.drop_duplicates(\"student_id\")\n",
    "joined_check = attendance.merge(\n",
    "    roster_unique[[\"student_id\", \"school_id\"]], on=\"student_id\", how=\"left\"\n",
    ")\n",
    "\n",
    "by_school = joined_check.assign(naive=joined_check[\"present\"] == \"true\").groupby(\"school_id\").agg(\n",
    "    naive_rate=(\"naive\", \"mean\"),\n",
    "    correct_rate=(\"present_clean\", \"mean\"),\n",
    ")\n",
    "by_school[\"difference\"] = by_school[\"correct_rate\"] - by_school[\"naive_rate\"]\n",
    "by_school.sort_values(\"difference\", ascending=False).head(3).round(3)"
   ],
   "id": "cell-007"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One school appears to have 60% attendance instead of 86%. On a dropout ranking\n",
    "that school supplies most of the top of the list, and every intervention goes to\n",
    "the wrong students.\n",
    "\n",
    "## The join that fans out\n",
    "\n",
    "Two students appear on the roster twice, after a transfer that was never\n",
    "de-registered. A straight join multiplies their attendance rows and\n",
    "double-counts them."
   ],
   "id": "cell-008"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "duplicated = roster[roster.duplicated(\"student_id\", keep=False)]\n",
    "duplicated.sort_values(\"student_id\")"
   ],
   "id": "cell-009"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "before = len(attendance)\n",
    "naive_join = attendance.merge(roster, on=\"student_id\", how=\"left\")\n",
    "print(f\"rows before join: {before:,}\")\n",
    "print(f\"rows after naive join: {len(naive_join):,}  (+{len(naive_join) - before})\")"
   ],
   "id": "cell-010"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Resolve the duplicate deliberately rather than dropping arbitrarily: keep the\n",
    "# row with a grade recorded, which is the post-transfer registration.\n",
    "roster_resolved = (\n",
    "    roster.sort_values(\"grade\", na_position=\"last\")\n",
    "    .drop_duplicates(\"student_id\", keep=\"first\")\n",
    ")\n",
    "\n",
    "daily = attendance.merge(roster_resolved, on=\"student_id\", how=\"left\", validate=\"many_to_one\")\n",
    "assert len(daily) == before, \"join changed the row count\"\n",
    "print(f\"rows after resolved join: {len(daily):,}\")"
   ],
   "id": "cell-011"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`validate=\"many_to_one\"` is what turns this from a silent 3,000-row inflation\n",
    "into an error at the point it happens.\n",
    "\n",
    "## A missing row is a closure, not an absence\n",
    "\n",
    "**Attendance rows exist only for days a school was open.** A date with no row is\n",
    "a closure, and nothing in the file marks which is which."
   ],
   "id": "cell-012"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "school_days = daily.groupby(\"school_id\")[\"attendance_date\"].nunique().sort_values()\n",
    "all_days = daily[\"attendance_date\"].nunique()\n",
    "\n",
    "print(f\"distinct school days in the file: {all_days}\")\n",
    "school_days.head(4)"
   ],
   "id": "cell-013"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "calendar = sorted(daily[\"attendance_date\"].unique())\n",
    "\n",
    "closures = {}\n",
    "for school in school_days[school_days < all_days].index:\n",
    "    open_days = set(daily.loc[daily[\"school_id\"] == school, \"attendance_date\"])\n",
    "    closures[school] = sorted(set(calendar) - open_days)\n",
    "    missing = closures[school]\n",
    "    print(f\"{school}: {len(missing)} days closed, \"\n",
    "          f\"{pd.Timestamp(missing[0]).date()} to {pd.Timestamp(missing[-1]).date()}\")\n",
    "\n",
    "closed_schools = list(closures)"
   ],
   "id": "cell-014"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fifteen consecutive school days in March. That is a strike.\n",
    "\n",
    "## What the closure does if you fill it\n",
    "\n",
    "The damage happens the moment you build a student-by-date matrix — the natural\n",
    "shape for a run-length feature — because reindexing to the full calendar\n",
    "manufactures rows that were never recorded, and the obvious fill value is\n",
    "\"absent\"."
   ],
   "id": "cell-015"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "matrix = (\n",
    "    daily.set_index([\"student_id\", \"attendance_date\"])[\"present_clean\"]\n",
    "    .unstack()\n",
    "    .reindex(columns=calendar)\n",
    ")\n",
    "\n",
    "filled_rate = matrix.fillna(False).mean(axis=1)     # closure counted as absence\n",
    "recorded_rate = daily.groupby(\"student_id\")[\"present_clean\"].mean()\n",
    "\n",
    "comparison = pd.DataFrame({\n",
    "    \"closure filled as absent\": filled_rate,\n",
    "    \"recorded days only\": recorded_rate,\n",
    "}).join(roster_resolved.set_index(\"student_id\")[[\"school_id\"]])\n",
    "\n",
    "(\n",
    "    comparison.groupby(\"school_id\")[\n",
    "        [\"closure filled as absent\", \"recorded days only\"]\n",
    "    ]\n",
    "    .mean()\n",
    "    .assign(gap=lambda d: d[\"recorded days only\"] - d[\"closure filled as absent\"])\n",
    "    .sort_values(\"gap\", ascending=False)\n",
    "    .head(4)\n",
    "    .round(3)\n",
    ")"
   ],
   "id": "cell-016"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Twenty-two points, at two schools, out of nowhere. Now watch what that does to a\n",
    "watchlist:"
   ],
   "id": "cell-017"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "THRESHOLD = 0.70\n",
    "in_closed = comparison[\"school_id\"].isin(closed_schools)\n",
    "\n",
    "for label, rate in [\n",
    "    (\"closure filled as absent\", filled_rate),\n",
    "    (\"recorded days only\", recorded_rate),\n",
    "]:\n",
    "    flagged = rate < THRESHOLD\n",
    "    share = comparison.loc[flagged, \"school_id\"].isin(closed_schools).mean()\n",
    "    print(f\"{label:26} {int(flagged.sum()):>4} students flagged, \"\n",
    "          f\"{share:.1%} of them from the two closed schools\")\n",
    "\n",
    "print(f\"\\nthose two schools are {in_closed.mean():.1%} of the roster\")"
   ],
   "id": "cell-018"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A hundred and thirty students instead of eighty-five, and nearly half the list\n",
    "drawn from schools holding a tenth of the roster. The fix is not a clever\n",
    "adjustment — it is **not reindexing in the first place**. Compute every feature\n",
    "on the days the student's school actually recorded.\n",
    "\n",
    "## Build the risk features on recorded days only\n",
    "\n",
    "The signal is not total absence — it is a *recent run* of it. A student who\n",
    "missed a fortnight in February and came back is not the same as one who has\n",
    "missed the last fortnight."
   ],
   "id": "cell-019"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The 271 rows never marked either way are dropped here rather than earlier: they\n",
    "# are missing marks on days the school was open, which is a different thing from\n",
    "# a closure and should not inflate an absence run.\n",
    "daily = (\n",
    "    daily.dropna(subset=[\"present_clean\"])\n",
    "    .sort_values([\"student_id\", \"attendance_date\"])\n",
    ")\n",
    "term_end = daily[\"attendance_date\"].max()\n",
    "\n",
    "def student_features(group):\n",
    "    # astype(bool) because the mapped column is a nullable boolean, and numpy\n",
    "    # will not index with an object array.\n",
    "    present = group[\"present_clean\"].astype(bool).to_numpy()\n",
    "    dates = group[\"attendance_date\"].to_numpy()\n",
    "\n",
    "    # Trailing run of absences, in school days the student's own school opened.\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",
    "        \"last_present\": last_present,\n",
    "        # School days missed since last attending. Closure days are absent from\n",
    "        # this count because they were never recorded, which is the whole point.\n",
    "        \"school_days_missed\": int((dates > last_present).sum()) if present.any() else len(group),\n",
    "    })\n",
    "\n",
    "features = daily.groupby(\"student_id\").apply(student_features, include_groups=False)\n",
    "features = features.join(\n",
    "    roster_resolved.set_index(\"student_id\")[[\"school_id\", \"grade\", \"feeding_programme\"]]\n",
    ")\n",
    "features.head()"
   ],
   "id": "cell-020"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DISENGAGED_DAYS = 15   # school days, not calendar days\n",
    "\n",
    "features[\"at_risk\"] = features[\"school_days_missed\"] > DISENGAGED_DAYS\n",
    "\n",
    "print(f\"flagged: {int(features['at_risk'].sum())} of {len(features)} \"\n",
    "      f\"({features['at_risk'].mean():.1%})\")\n",
    "\n",
    "flagged_share = features.loc[features[\"at_risk\"], \"school_id\"].isin(closed_schools).mean()\n",
    "print(f\"of those, {flagged_share:.1%} are at the two closed schools \"\n",
    "      f\"(which hold {features['school_id'].isin(closed_schools).mean():.1%} of the roster)\")"
   ],
   "id": "cell-021"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The closure schools are still somewhat over-represented, and that is worth\n",
    "saying plainly rather than adjusting away: a three-week closure is a plausible\n",
    "trigger for genuine disengagement. Counting closure days as absences invents\n",
    "dropouts; counting them as nothing leaves a real signal that a head teacher\n",
    "should be told about.\n",
    "\n",
    "## The list a head teacher can use"
   ],
   "id": "cell-022"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "watchlist = (\n",
    "    features[features[\"at_risk\"]]\n",
    "    .sort_values([\"school_days_missed\", \"attendance_rate\"], ascending=[False, True])\n",
    "    .loc[:, [\"school_id\", \"grade\", \"attendance_rate\",\n",
    "             \"trailing_absences\", \"school_days_missed\"]]\n",
    "    .round(3)\n",
    ")\n",
    "watchlist.head(15)"
   ],
   "id": "cell-023"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sorted by how long the student has been gone, then by how they were attending\n",
    "before. That ordering matters: a student who was at 95% and stopped three weeks\n",
    "ago is a different case from one who was at 40% all term.\n",
    "\n",
    "## What to report\n",
    "\n",
    "The list, the threshold used, the closure adjustment and which schools it applied\n",
    "to. And the caveat that matters most: this ranks *risk*, not dropout. A student\n",
    "on this list may have transferred, be ill, or be temporarily helping at home —\n",
    "the output is a conversation to have, not a status to record."
   ],
   "id": "cell-024"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "file_extension": ".py"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}