{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Water quality against chlorination\n",
    "\n",
    "*WASH household survey, 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/wash-household-survey-2024/chlorine-vs-ecoli.python.en.ipynb)"
   ],
   "id": "cell-000"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The question\n",
    "\n",
    "Does free residual chlorine at or above 0.2 mg/L predict a lower E. coli risk\n",
    "class? And — the part that decides whether the answer means anything — how much\n",
    "of the sample can actually answer it?\n",
    "\n",
    "Every dataset on this platform is synthetic. No real household is 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",
    "wash = pd.read_csv(URL, dtype={\"household_id\": \"string\"})\n",
    "wash[\"district\"] = (\n",
    "    wash[\"district\"].str.strip().str.lower().str.replace(\" \", \"-\", regex=False)\n",
    ")\n",
    "len(wash)"
   ],
   "id": "cell-002"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Start with the denominator, not the finding\n",
    "\n",
    "Water quality is tested on a subset. Reporting a quality result against the full\n",
    "sample overstates how much testing was done, and it is the first thing a\n",
    "reviewer will check."
   ],
   "id": "cell-003"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tested = pd.DataFrame({\n",
    "    \"households\": [\n",
    "        len(wash),\n",
    "        int(wash[\"free_residual_chlorine_mgl\"].notna().sum()),\n",
    "        int(wash[\"ecoli_cfu_100ml\"].notna().sum()),\n",
    "        int(\n",
    "            (\n",
    "                wash[\"free_residual_chlorine_mgl\"].notna()\n",
    "                & wash[\"ecoli_cfu_100ml\"].notna()\n",
    "            ).sum()\n",
    "        ),\n",
    "    ]\n",
    "}, index=[\"surveyed\", \"chlorine tested\", \"E. coli tested\", \"both tested\"])\n",
    "tested[\"share of sample\"] = (tested[\"households\"] / len(wash)).round(3)\n",
    "tested"
   ],
   "id": "cell-004"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Only the last row can answer the question.** Everything below is computed on\n",
    "that subset, and the report has to say so — a chlorination finding presented\n",
    "against 2,403 households when 320 were tested for both is a misstatement of the\n",
    "evidence, whatever the finding is.\n",
    "\n",
    "## The missingness is not random\n",
    "\n",
    "About 530 households report treating their water and have no chlorine\n",
    "measurement. That is a cross-field inconsistency, not a missing-at-random value,\n",
    "and it matters: dropping those rows removes households that treat their water\n",
    "more often than average, which biases the comparison in a predictable direction."
   ],
   "id": "cell-005"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "treated_untested = wash[\"water_treated_at_home\"] & wash[\n",
    "    \"free_residual_chlorine_mgl\"\n",
    "].isna()\n",
    "print(f\"treat water but no chlorine reading: {int(treated_untested.sum())}\")\n",
    "\n",
    "pd.crosstab(\n",
    "    wash[\"water_treated_at_home\"],\n",
    "    wash[\"free_residual_chlorine_mgl\"].notna().map(\n",
    "        {True: \"chlorine tested\", False: \"not tested\"}\n",
    "    ),\n",
    "    normalize=\"index\",\n",
    ").round(3)"
   ],
   "id": "cell-006"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Households that treat their water are *more* likely to be tested here, so the\n",
    "tested subset over-represents treatment. Any effect estimated on it is an effect\n",
    "among the tested, not among the population.\n",
    "\n",
    "## The comparison\n",
    "\n",
    "WHO's guideline for free residual chlorine at the point of delivery is\n",
    "0.2 mg/L. E. coli is reported in the standard risk classes rather than as a raw\n",
    "count, because the count is over-dispersed and the classes are what a programme\n",
    "acts on."
   ],
   "id": "cell-007"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "both = wash.dropna(subset=[\"free_residual_chlorine_mgl\", \"ecoli_cfu_100ml\"]).copy()\n",
    "\n",
    "both[\"adequate_chlorine\"] = both[\"free_residual_chlorine_mgl\"] >= 0.2\n",
    "both[\"risk_class\"] = pd.cut(\n",
    "    both[\"ecoli_cfu_100ml\"],\n",
    "    bins=[-1, 0, 10, 100, np.inf],\n",
    "    labels=[\"0 (conforms)\", \"1-10 (low)\", \"11-100 (intermediate)\", \">100 (high)\"],\n",
    ")\n",
    "\n",
    "table = pd.crosstab(\n",
    "    both[\"adequate_chlorine\"],\n",
    "    both[\"risk_class\"],\n",
    "    normalize=\"index\",\n",
    ").round(3)\n",
    "table.index = [\"below 0.2 mg/L\", \"at or above 0.2 mg/L\"]\n",
    "table"
   ],
   "id": "cell-008"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "counts = pd.crosstab(both[\"adequate_chlorine\"], both[\"risk_class\"])\n",
    "counts.index = [\"below 0.2 mg/L\", \"at or above 0.2 mg/L\"]\n",
    "counts"
   ],
   "id": "cell-009"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The pattern is strong: households at or above the guideline are far more likely\n",
    "to show no detectable E. coli, and none of them reach the high-risk class.\n",
    "\n",
    "## Is it more than sampling noise?"
   ],
   "id": "cell-010"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from scipy.stats import chi2_contingency\n",
    "\n",
    "conforms = pd.crosstab(\n",
    "    both[\"adequate_chlorine\"], both[\"ecoli_cfu_100ml\"] == 0\n",
    ")\n",
    "chi2, p, dof, expected = chi2_contingency(conforms)\n",
    "\n",
    "rate_low = conforms.loc[False, True] / conforms.loc[False].sum()\n",
    "rate_high = conforms.loc[True, True] / conforms.loc[True].sum()\n",
    "\n",
    "print(f\"conforming, chlorine below 0.2 : {rate_low:.1%}\")\n",
    "print(f\"conforming, chlorine at/above  : {rate_high:.1%}\")\n",
    "print(f\"difference                     : {rate_high - rate_low:+.1%}\")\n",
    "print(f\"chi-square p                   : {p:.2e}\")"
   ],
   "id": "cell-011"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Two cautions on that p-value. It says the association is unlikely to be chance;\n",
    "it does not say chlorination *caused* it, because households that chlorinate\n",
    "differ from those that do not in ways this survey does not record. And with a\n",
    "few hundred observations a p-value this small mostly reflects how large the\n",
    "difference is — the effect size is the number to report, not the p.\n",
    "\n",
    "## Where the untested households are"
   ],
   "id": "cell-012"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "by_district = wash.groupby(\"district\").agg(\n",
    "    households=(\"household_id\", \"size\"),\n",
    "    chlorine_tested=(\"free_residual_chlorine_mgl\", lambda s: s.notna().mean()),\n",
    "    ecoli_tested=(\"ecoli_cfu_100ml\", lambda s: s.notna().mean()),\n",
    ").round(3)\n",
    "by_district"
   ],
   "id": "cell-013"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If testing coverage differs by district, a district comparison of water quality\n",
    "is partly a comparison of which district got tested. Check this before ranking\n",
    "anything.\n",
    "\n",
    "## What to report\n",
    "\n",
    "The finding, the denominator it rests on, and the fact that the tested subset\n",
    "over-represents households that treat their water. Then the recommendation the\n",
    "programme can actually act on — which here is to test more households, not to\n",
    "conclude anything firm about chlorination from 320 of them."
   ],
   "id": "cell-014"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "file_extension": ".py"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}