{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Risque d'abandon à partir des profils de présence\n", "\n", "*Présence scolaire, 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.fr.ipynb)" ], "id": "cell-000" }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Ce que produit ce document\n", "\n", "Un classement du risque d'abandon par élève, construit à partir des séquences\n", "d'absences consécutives, à temps pour agir avant la fin du trimestre. La présence\n", "décline sur environ trois semaines avant qu'un élève ne cesse de venir, et c'est\n", "ce déclin qui rend l'alerte précoce possible.\n", "\n", "Trois choses doivent d'abord être justes, et chacune modifie le classement : le\n", "codage booléen, la jointure, et la différence entre une fermeture et une absence.\n", "\n", "Tous les jeux de données de cette plateforme sont synthétiques. Aucun élève réel\n", "n'est représenté.\n", "\n", "## Mise en place" ], "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\"lignes de presence : {len(attendance):,}\")\n", "print(f\"lignes de liste : {len(roster):,}\")" ], "id": "cell-002" }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Le booléen qui n'en est pas un\n", "\n", "Une école a consigné la présence avec `Y` et `N` plutôt qu'avec `true` et\n", "`false`. Un transtypage booléen les convertit silencieusement en valeurs\n", "manquantes — et il ne s'agit pas de 30 % du fichier pris au hasard, mais d'une\n", "seule école." ], "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": [ "L'écart global paraît faible. Regardez l'école concernée isolément :" ], "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": [ "Une école semble afficher 60 % de présence au lieu de 86 %. Dans un classement\n", "du risque d'abandon, cette école fournit l'essentiel du haut de la liste, et\n", "toutes les interventions partent vers les mauvais élèves.\n", "\n", "## La jointure qui démultiplie\n", "\n", "Deux élèves figurent deux fois sur la liste, après un transfert jamais\n", "désinscrit. Une jointure directe multiplie leurs lignes de présence et les compte\n", "deux fois." ], "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\"` est ce qui transforme une inflation silencieuse de\n", "lignes en une erreur levée à l'endroit exact où elle se produit.\n", "\n", "## Une ligne absente est une fermeture, non une absence\n", "\n", "**Les lignes de présence n'existent que pour les jours d'ouverture de l'école.**\n", "Une date sans ligne correspond à une fermeture, et rien dans le fichier ne\n", "distingue les deux cas." ], "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": [ "Quinze jours de classe consécutifs en mars. Il s'agit d'une grève.\n", "\n", "## Ce que produit la fermeture si vous la remplissez\n", "\n", "Le dégât survient dès que vous construisez une matrice élève par date — la forme\n", "naturelle pour une variable de séquence — car la réindexation sur le calendrier\n", "complet fabrique des lignes jamais consignées, et la valeur de remplissage\n", "évidente est « 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": [ "Vingt-deux points, dans deux écoles, sortis de nulle part. Voyez maintenant\n", "l'effet sur une liste de veille :" ], "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": [ "Cent trente élèves au lieu de quatre-vingt-cinq, et près de la moitié de la liste\n", "issue d'écoles pesant un dixième de l'effectif. La correction n'est pas un\n", "ajustement astucieux : c'est de **ne pas réindexer du tout**. Calculez chaque\n", "variable sur les jours que l'école de l'élève a effectivement consignés.\n", "\n", "## Construire les variables de risque sur les seuls jours consignés\n", "\n", "Le signal n'est pas l'absence totale : c'est une *séquence récente* d'absences.\n", "Un élève qui a manqué quinze jours en février puis est revenu n'est pas dans la\n", "même situation qu'un élève absent depuis quinze jours." ], "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": [ "Les écoles fermées restent un peu surreprésentées, et cela mérite d'être énoncé\n", "plutôt que corrigé : une fermeture de trois semaines est un déclencheur plausible\n", "de décrochage réel. Compter les jours de fermeture comme des absences invente des\n", "abandons ; ne pas les compter du tout laisse subsister un signal réel dont un\n", "directeur doit être informé.\n", "\n", "## La liste qu'un directeur peut utiliser" ], "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": [ "Trié par la durée d'absence, puis par l'assiduité antérieure. Cet ordre compte :\n", "un élève qui était à 95 % et a cessé de venir il y a trois semaines constitue un\n", "cas différent d'un élève à 40 % depuis le début du trimestre.\n", "\n", "## Ce qu'il faut rapporter\n", "\n", "La liste, le seuil employé, le traitement des fermetures et les écoles\n", "concernées. Et la réserve qui compte le plus : ceci classe un *risque*, non un\n", "abandon. Un élève de cette liste peut avoir été transféré, être malade, ou aider\n", "temporairement à la maison — le résultat est une conversation à mener, non un\n", "statut à enregistrer." ], "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 }