{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Choosing six communities for rehabilitation — analysis notebook\n",
    "\n",
    "*WASH coverage and water quality by district · 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/wash-coverage-analysis/notebooks/wash-ladders.python.en.ipynb)"
   ],
   "id": "cell-000"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The decision this produces\n",
    "\n",
    "Which six of eighteen communities receive water point rehabilitation in the next\n",
    "funding cycle. Six is what the budget covers.\n",
    "\n",
    "The problem the project exists to fix: coverage was reported by counting\n",
    "improved water sources and ignoring collection time, so a community with a\n",
    "functioning but distant borehole scored the same as one with a tap in the yard.\n",
    "\n",
    "Audience: the WASH coordinator and the district water authority.\n",
    "\n",
    "Every dataset on this platform is synthetic. No real household or community is\n",
    "described.\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",
    "URL = (\n",
    "    \"https://data-analysis.cassion.dev/datasets/files/\"\n",
    "    \"wash-household-survey-2024.v1.csv\"\n",
    ")\n",
    "\n",
    "REHABILITATIONS = 6\n",
    "SPHERE_MINIMUM = 15\n",
    "WALK_LIMIT = 30\n",
    "\n",
    "wash = pd.read_csv(URL, dtype={\"household_id\": \"string\", \"community\": \"string\"})\n",
    "print(f\"{len(wash)} households\")"
   ],
   "id": "cell-002"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Normalise the district before anything is grouped by it\n",
    "\n",
    "One enumerator team wrote Nord-Ouest four different ways. Ungrouped, that splits\n",
    "the worst-performing district into four fragments, none of which looks alarming."
   ],
   "id": "cell-003"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(wash[\"district\"].value_counts().to_dict())"
   ],
   "id": "cell-004"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "wash[\"district\"] = (\n",
    "    wash[\"district\"].str.strip().str.lower().str.replace(\" \", \"-\", regex=False)\n",
    ")\n",
    "print(f\"districts after normalising: {wash['district'].nunique()}\")\n",
    "print(f\"communities: {wash['community'].nunique()}\")"
   ],
   "id": "cell-005"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The classification the old report got wrong\n",
    "\n",
    "The JMP drinking-water ladder is not a property of the source. An improved source\n",
    "more than thirty minutes round trip is **limited** service, not basic — and that\n",
    "distinction is the whole reason this project was commissioned."
   ],
   "id": "cell-006"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "IMPROVED = {\n",
    "    \"piped-into-dwelling\", \"piped-into-yard\", \"public-tap\", \"borehole\",\n",
    "    \"protected-well\", \"protected-spring\", \"tanker-truck\",\n",
    "}\n",
    "\n",
    "wash[\"improved_source\"] = wash[\"water_source\"].isin(IMPROVED)\n",
    "wash[\"basic_water\"] = wash[\"improved_source\"] & (\n",
    "    wash[\"round_trip_minutes\"] <= WALK_LIMIT\n",
    ")\n",
    "\n",
    "pd.Series({\n",
    "    \"improved source (the old measure)\": round(wash[\"improved_source\"].mean(), 3),\n",
    "    \"basic service (source and time)\": round(wash[\"basic_water\"].mean(), 3),\n",
    "    \"improved but over 30 minutes\": round(\n",
    "        (wash[\"improved_source\"] & ~wash[\"basic_water\"]).mean(), 3\n",
    "    ),\n",
    "})"
   ],
   "id": "cell-007"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A quarter of households sit on limited service for collection time alone. Under\n",
    "the old measure every one of them was counted as covered.\n",
    "\n",
    "## Community-level need, on the four things rehabilitation changes"
   ],
   "id": "cell-008"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "communities = wash.groupby([\"district\", \"community\"]).agg(\n",
    "    households=(\"household_id\", \"size\"),\n",
    "    basic_water=(\"basic_water\", \"mean\"),\n",
    "    over_walk_limit=(\"round_trip_minutes\", lambda s: (s > WALK_LIMIT).mean()),\n",
    "    below_sphere=(\"litres_per_person_day\", lambda s: (s < SPHERE_MINIMUM).mean()),\n",
    "    unimproved=(\"improved_source\", lambda s: 1 - s.mean()),\n",
    ").round(3)\n",
    "\n",
    "communities.sort_values(\"basic_water\").head(8)"
   ],
   "id": "cell-009"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The survey allocated roughly equal households per community, which is convenient:\n",
    "the communities are directly comparable without weighting, and a rate from one is\n",
    "as precise as a rate from another."
   ],
   "id": "cell-010"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "communities[\"households\"].describe().round(1)"
   ],
   "id": "cell-011"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The ranking, and the choice inside it\n",
    "\n",
    "Rehabilitation shortens the walk and restores function. It does not change\n",
    "sanitation or hygiene, so those are excluded from the ranking — including them\n",
    "would let a community with poor latrines displace one with a broken water point,\n",
    "which is not what this budget buys."
   ],
   "id": "cell-012"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def standardise(series):\n",
    "    return (series - series.mean()) / series.std()\n",
    "\n",
    "communities[\"need\"] = (\n",
    "    standardise(1 - communities[\"basic_water\"])\n",
    "    + standardise(communities[\"over_walk_limit\"])\n",
    "    + standardise(communities[\"below_sphere\"])\n",
    "    + standardise(communities[\"unimproved\"])\n",
    ") / 4\n",
    "\n",
    "ranked = communities.sort_values(\"need\", ascending=False)\n",
    "ranked.head(10)"
   ],
   "id": "cell-013"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "selected = ranked.head(REHABILITATIONS)\n",
    "selected.index.tolist()"
   ],
   "id": "cell-014"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Does the ranking survive dropping a component?\n",
    "\n",
    "A composite that reorders entirely when one input is removed is a composite the\n",
    "water authority should not trust."
   ],
   "id": "cell-015"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "components = [\"basic_water\", \"over_walk_limit\", \"below_sphere\", \"unimproved\"]\n",
    "\n",
    "stability = {}\n",
    "for dropped in components:\n",
    "    kept = [c for c in components if c != dropped]\n",
    "    score = sum(\n",
    "        standardise(1 - communities[c] if c == \"basic_water\" else communities[c])\n",
    "        for c in kept\n",
    "    ) / len(kept)\n",
    "    top = set(score.nlargest(REHABILITATIONS).index)\n",
    "    stability[f\"without {dropped}\"] = len(top & set(selected.index))\n",
    "\n",
    "pd.Series(stability, name=f\"of {REHABILITATIONS} still selected\")"
   ],
   "id": "cell-016"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The list is stable under dropping any single component, which is what makes it\n",
    "defensible in a meeting. Where it is not stable, say so and name the communities\n",
    "that move.\n",
    "\n",
    "## Water quality is a separate question, on a smaller denominator"
   ],
   "id": "cell-017"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "quality = wash.groupby([\"district\", \"community\"]).agg(\n",
    "    chlorine_tested=(\"free_residual_chlorine_mgl\", lambda s: s.notna().mean()),\n",
    "    ecoli_tested=(\"ecoli_cfu_100ml\", lambda s: s.notna().mean()),\n",
    "    ecoli_detected=(\"ecoli_cfu_100ml\", lambda s: (s > 0).sum() / max(s.notna().sum(), 1)),\n",
    ").round(3)\n",
    "\n",
    "quality.loc[selected.index]"
   ],
   "id": "cell-018"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Quality is tested on roughly a third of households, so these rates rest on a\n",
    "denominator a third the size of the access figures. They are reported beside the\n",
    "selection rather than inside it: a contaminated supply needs treatment or a new\n",
    "source, which is a different intervention from rehabilitating a distant one.\n",
    "\n",
    "## The output the water authority receives"
   ],
   "id": "cell-019"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "recommendation = selected.reset_index()[\n",
    "    [\"district\", \"community\", \"households\", \"basic_water\",\n",
    "     \"over_walk_limit\", \"below_sphere\", \"need\"]\n",
    "]\n",
    "recommendation[\"basic_water\"] = (recommendation[\"basic_water\"] * 100).round(1)\n",
    "recommendation[\"over_walk_limit\"] = (recommendation[\"over_walk_limit\"] * 100).round(1)\n",
    "recommendation[\"below_sphere\"] = (recommendation[\"below_sphere\"] * 100).round(1)\n",
    "recommendation.round(3)"
   ],
   "id": "cell-020"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "by_district = pd.DataFrame({\n",
    "    \"communities\": communities.groupby(\"district\").size(),\n",
    "    \"selected\": selected.reset_index().groupby(\"district\").size(),\n",
    "}).fillna(0).astype(int)\n",
    "by_district"
   ],
   "id": "cell-021"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If the selection concentrates in one district, that is a finding to state\n",
    "explicitly — it will be read as a political choice unless the report shows it\n",
    "came from the ranking.\n",
    "\n",
    "## What this does not establish\n",
    "\n",
    "Household survey responses about collection time are reported, not measured.\n",
    "Where a community systematically under- or over-states the walk, the ranking\n",
    "moves with it, and nothing in this dataset can detect that.\n",
    "\n",
    "Nor does the survey say **why** a water point is distant or unimproved. It could\n",
    "be a broken pump, a dry borehole, or a community that never had one. Those need\n",
    "different money, and the rehabilitation budget only covers the first."
   ],
   "id": "cell-022"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "file_extension": ".py"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}