{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pick the right denominator — worked solution\n",
    "\n",
    "*Exercise solution · Data Analysis Foundations for M&E*\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/practice/indicator-denominator.python.en.ipynb)"
   ],
   "id": "cell-000"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## How to use this\n",
    "\n",
    "This is the solution, so read it after attempting the exercise rather than\n",
    "instead of. The five scenarios each have an agreed numerator and a contested\n",
    "denominator, and in every one the honest answer is not \"the right number\" but\n",
    "\"the number, plus what it excludes\".\n",
    "\n",
    "The dataset is the synthetic MUAC screening register from Artibonite. Nothing\n",
    "here describes a real child."
   ],
   "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",
    "    \"muac-screening-artibonite-2024.v1.csv\"\n",
    ")\n",
    "\n",
    "muac = pd.read_csv(\n",
    "    URL,\n",
    "    dtype={\"child_id\": \"string\", \"commune\": \"string\", \"sex\": \"string\"},\n",
    "    na_values={\"muac_mm\": [\"-99\"]},\n",
    ")\n",
    "muac[\"screening_date\"] = pd.to_datetime(muac[\"screening_date\"], format=\"%Y-%m-%d\")\n",
    "\n",
    "# The corrections from lesson 6, applied so the scenarios below argue about\n",
    "# denominators rather than about data quality.\n",
    "unit_error = muac[\"muac_mm\"].notna() & (muac[\"muac_mm\"] < 40)\n",
    "muac.loc[unit_error, \"muac_mm\"] = muac.loc[unit_error, \"muac_mm\"] * 10\n",
    "\n",
    "oedema_map = {\n",
    "    \"true\": True, \"TRUE\": True, \"Y\": True, \"y\": True, \"yes\": True,\n",
    "    \"false\": False, \"FALSE\": False, \"N\": False, \"n\": False, \"no\": False,\n",
    "}\n",
    "muac[\"oedema\"] = muac[\"oedema\"].astype(\"string\").str.strip().map(oedema_map)\n",
    "muac = muac.drop_duplicates()\n",
    "\n",
    "muac[\"gam\"] = (muac[\"muac_mm\"] < 125) | (muac[\"oedema\"] == True)\n",
    "len(muac)"
   ],
   "id": "cell-002"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Scenario 1 — \"What proportion of children screened were malnourished?\"\n",
    "\n",
    "The numerator is agreed: children meeting the GAM case definition. Four\n",
    "denominators are defensible and they give four different numbers."
   ],
   "id": "cell-003"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "candidates = {\n",
    "    \"all rows in the register\": len(muac),\n",
    "    \"rows with a MUAC measurement\": int(muac[\"muac_mm\"].notna().sum()),\n",
    "    \"rows with MUAC or an oedema assessment\": int(\n",
    "        (muac[\"muac_mm\"].notna() | muac[\"oedema\"].notna()).sum()\n",
    "    ),\n",
    "    \"rows with MUAC, oedema and age\": int(\n",
    "        (\n",
    "            (muac[\"muac_mm\"].notna() | muac[\"oedema\"].notna())\n",
    "            & muac[\"age_months\"].notna()\n",
    "        ).sum()\n",
    "    ),\n",
    "}\n",
    "\n",
    "cases = int(muac[\"gam\"].sum())\n",
    "pd.DataFrame(\n",
    "    {\n",
    "        \"denominator\": candidates,\n",
    "        \"rate\": {k: cases / v for k, v in candidates.items()},\n",
    "    }\n",
    ").round(4)"
   ],
   "id": "cell-004"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**The answer.** Use \"rows with MUAC or an oedema assessment\". A child with no\n",
    "assessment at all was not screened for this indicator, so including them in the\n",
    "denominator understates the rate by counting non-observations as negatives.\n",
    "Requiring age as well is over-strict: the MUAC thresholds for 6 to 59 months are\n",
    "a single band and do not use age, and demanding it drops one commune far more\n",
    "than the others.\n",
    "\n",
    "**What it excludes, stated out loud:** children who did not come to be screened.\n",
    "This is a rate among those reached, not a population prevalence, and the report\n",
    "has to say so.\n",
    "\n",
    "## Scenario 2 — \"What is our referral completion rate?\"\n",
    "\n",
    "The trap is that the decision column and the measurement column were filled by\n",
    "different people, so a handful of records carry a referral with no measurement\n",
    "behind it. Count them rather than assuming the number."
   ],
   "id": "cell-005"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "referred = muac[\"outcome\"].isin([\"referred-tsfp\", \"referred-otp\", \"referred-sc\"])\n",
    "no_measurement = referred & muac[\"muac_mm\"].isna()\n",
    "\n",
    "print(f\"referrals recorded          : {int(referred.sum())}\")\n",
    "print(f\"of which with no measurement: {int(no_measurement.sum())}\")"
   ],
   "id": "cell-006"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**The answer.** There are two different questions hiding here, and they need\n",
    "different denominators.\n",
    "\n",
    "- *Did the screening lead to a referral where it should have?* Denominator:\n",
    "  children meeting the referral case definition. This measures the screener.\n",
    "- *Did the referred child reach the service?* Denominator: children referred.\n",
    "  This measures the pathway, and this register cannot answer it at all — there\n",
    "  is no arrival record.\n",
    "\n",
    "Reporting the second using this file would be inventing a number. The correct\n",
    "output is the first, plus a sentence saying the second requires the CMAM\n",
    "admission register.\n",
    "\n",
    "## Scenario 3 — \"Coverage went up 12% this quarter\""
   ],
   "id": "cell-007"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "muac[\"quarter\"] = muac[\"screening_date\"].dt.to_period(\"Q\")\n",
    "by_quarter = muac.groupby(\"quarter\").size()\n",
    "by_quarter"
   ],
   "id": "cell-008"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**The answer.** This is not coverage, and the word should be refused. Coverage is\n",
    "cases reached over cases existing, and this register has no denominator of\n",
    "children in the population — only those who turned up. What went up is screening\n",
    "volume.\n",
    "\n",
    "Note also that Q1 and Q4 are short: the first screening is mid-January and the\n",
    "last mid-December, so a quarter-on-quarter comparison including either is\n",
    "comparing unequal windows."
   ],
   "id": "cell-009"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"first:\", muac[\"screening_date\"].min().date())\n",
    "print(\"last :\", muac[\"screening_date\"].max().date())"
   ],
   "id": "cell-010"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Scenario 4 — \"Which commune has the worst malnutrition?\""
   ],
   "id": "cell-011"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "by_commune = (\n",
    "    muac.assign(assessed=muac[\"muac_mm\"].notna() | muac[\"oedema\"].notna())\n",
    "    .groupby(\"commune\")\n",
    "    .agg(assessed=(\"assessed\", \"sum\"), cases=(\"gam\", \"sum\"))\n",
    ")\n",
    "by_commune[\"rate\"] = by_commune[\"cases\"] / by_commune[\"assessed\"]\n",
    "by_commune.sort_values(\"rate\", ascending=False).round(4)"
   ],
   "id": "cell-012"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**The answer.** The denominator is right, and the question is still wrong. The\n",
    "smallest commune has under 200 assessments, so its interval is wide enough that\n",
    "its rank is close to meaningless."
   ],
   "id": "cell-013"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from scipy.stats import beta\n",
    "\n",
    "def interval(cases, n):\n",
    "    if n == 0:\n",
    "        return (np.nan, np.nan)\n",
    "    lower = beta.ppf(0.025, cases, n - cases + 1) if cases > 0 else 0.0\n",
    "    upper = beta.ppf(0.975, cases + 1, n - cases) if cases < n else 1.0\n",
    "    return (lower, upper)\n",
    "\n",
    "bounds = by_commune.apply(lambda r: interval(r[\"cases\"], r[\"assessed\"]), axis=1)\n",
    "by_commune[\"low\"] = [b[0] for b in bounds]\n",
    "by_commune[\"high\"] = [b[1] for b in bounds]\n",
    "by_commune.sort_values(\"rate\", ascending=False)[\n",
    "    [\"assessed\", \"rate\", \"low\", \"high\"]\n",
    "].round(4)"
   ],
   "id": "cell-014"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Several intervals overlap. \"Worst\" is answerable only for the commune whose\n",
    "interval clears the others, and a table without intervals would have let you\n",
    "rank all twelve with false confidence.\n",
    "\n",
    "## Scenario 5 — \"Our programme reached 4,218 children this year\""
   ],
   "id": "cell-015"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f\"rows                    : {len(muac)}\")\n",
    "print(f\"distinct child_id       : {muac['child_id'].nunique()}\")\n",
    "\n",
    "key = [\"commune\", \"screening_date\", \"age_months\", \"sex\", \"muac_mm\"]\n",
    "suspected = muac.duplicated(subset=key, keep=False) & muac[\"muac_mm\"].notna()\n",
    "print(f\"suspected re-registration: {int(suspected.sum())}\")"
   ],
   "id": "cell-016"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**The answer.** Rows are not children. Exact duplicates have already been\n",
    "removed above, but a handful of children were re-registered under a new\n",
    "identifier and share no key — findable only by matching on commune, date, age,\n",
    "sex and measurement, and indistinguishable from genuine coincidence without the\n",
    "paper register.\n",
    "\n",
    "So the defensible statement is a figure with its caveat attached: so many\n",
    "distinct registrations, of which some number may be re-registrations of the same\n",
    "child. Note that the check above flags *suspects*, not duplicates — two children\n",
    "of the same age and sex screened in one commune on one day with the same\n",
    "measurement is entirely possible, and in a campaign this size it is likely.\n",
    "Resolving them needs the paper register, not another line of pandas.\n",
    "\n",
    "## The rule underneath all five\n",
    "\n",
    "Write the denominator as a sentence before you compute anything. If you cannot,\n",
    "you do not yet have an indicator — you have a column you are about to average.\n",
    "And whatever the denominator excludes goes in the report, not in your head."
   ],
   "id": "cell-017"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "file_extension": ".py"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}