{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cell-000",
   "metadata": {},
   "source": [
    "# Lesson 4 — Aggregations, Grouping & Classical Actuarial Metrics\n",
    "\n",
    "**Companion notebook** for the Lesson 4 slides. It starts from the ABT you saved in\n",
    "Lesson 3 (`../data/processed/abt.parquet`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-001",
   "metadata": {},
   "outputs": [],
   "source": [
    "def check(label, got, expected, tol=1e-6):\n",
    "    \"\"\"Compare your answer with the expected value; prints ✅ or ❌ (never raises).\"\"\"\n",
    "    import math\n",
    "    try:\n",
    "        if isinstance(expected, float) or isinstance(got, float):\n",
    "            ok = math.isclose(float(got), float(expected), rel_tol=tol, abs_tol=tol)\n",
    "        elif isinstance(expected, (list, tuple)) and expected and isinstance(expected[0], str):\n",
    "            ok = list(got) == list(expected)\n",
    "        elif hasattr(expected, \"__len__\") and not isinstance(expected, str):\n",
    "            import numpy as np\n",
    "            ok = np.allclose(np.asarray(got, dtype=float), np.asarray(expected, dtype=float), rtol=tol, atol=tol)\n",
    "        else:\n",
    "            ok = got == expected\n",
    "    except Exception:\n",
    "        ok = False\n",
    "    print((\"✅ \" if ok else \"❌ \") + f\"{label}: got {got!r}, expected {expected!r}\")\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "abt = pd.read_parquet(\"../data/processed/abt.parquet\")\n",
    "abt[\"region\"] = pd.cut(abt[\"longitude\"], [-85, -81.2, -78.6, -75],\n",
    "                       labels=[\"Mountains\", \"Piedmont\", \"Coastal Plain\"])\n",
    "abt.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-002",
   "metadata": {},
   "source": [
    "## 1. Split–apply–combine with named aggregations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-003",
   "metadata": {},
   "outputs": [],
   "source": [
    "seg = abt.groupby(\"vehicle_style\").agg(\n",
    "    exposure=(\"exposure\", \"sum\"), claims=(\"n_claims\", \"sum\"),\n",
    "    incurred=(\"incurred\", \"sum\"), premium=(\"earned_premium\", \"sum\"))\n",
    "seg[\"frequency\"] = seg[\"claims\"] / seg[\"exposure\"]\n",
    "seg[\"severity\"] = seg[\"incurred\"] / seg[\"claims\"]\n",
    "seg[\"pure_premium\"] = seg[\"incurred\"] / seg[\"exposure\"]\n",
    "seg[\"loss_ratio\"] = seg[\"incurred\"] / seg[\"premium\"]\n",
    "seg.sort_values(\"pure_premium\", ascending=False).round(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-004",
   "metadata": {},
   "source": [
    "## 2. Ratio of sums — not mean of ratios"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-005",
   "metadata": {},
   "outputs": [],
   "source": [
    "sports = abt[abt[\"vehicle_style\"] == \"Sports\"]\n",
    "print(\"mean of row frequencies:\", round((sports[\"n_claims\"] / sports[\"exposure\"]).mean(), 4))\n",
    "print(\"ratio of sums:          \", round(sports[\"n_claims\"].sum() / sports[\"exposure\"].sum(), 4))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-006",
   "metadata": {},
   "source": [
    "## 3. `transform`, `pivot_table`, `melt`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-007",
   "metadata": {},
   "outputs": [],
   "source": [
    "by_region = abt.groupby(\"region\", observed=True)\n",
    "abt[\"region_share\"] = abt[\"exposure\"] / by_region[\"exposure\"].transform(\"sum\")\n",
    "print(abt[\"region_share\"].sum())\n",
    "\n",
    "pt = abt.pivot_table(index=\"vehicle_style\", columns=\"region\", values=[\"incurred\", \"exposure\"],\n",
    "                     aggfunc=\"sum\", observed=True)\n",
    "print((pt[\"incurred\"] / pt[\"exposure\"]).round(0))\n",
    "\n",
    "long = abt.melt(id_vars=[\"vehicle_id\", \"exposure\"], value_vars=[\"n_liab\", \"n_coll\", \"n_comp\"],\n",
    "                var_name=\"coverage\", value_name=\"n\")\n",
    "long.groupby(\"coverage\")[\"n\"].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-008",
   "metadata": {},
   "source": [
    "---\n",
    "## Your turn 1 — a reusable `metrics()` function\n",
    "\n",
    "Return exposure, claims, incurred, premium, frequency, severity, pure premium, and loss\n",
    "ratio for **any** grouping (`by` is a column name or a list of them)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-009",
   "metadata": {},
   "outputs": [],
   "source": [
    "def metrics(df: pd.DataFrame, by) -> pd.DataFrame:\n",
    "    ...  # your code here\n",
    "\n",
    "\n",
    "ages = metrics(abt, \"age_band\")\n",
    "if ages is not None:\n",
    "    display(ages.round(4))\n",
    "check(\"frequency 17-24\", None if ages is None else round(ages.loc[\"17-24\", \"frequency\"], 4), 0.2248)\n",
    "check(\"frequency 50-64\", None if ages is None else round(ages.loc[\"50-64\", \"frequency\"], 4), 0.1430)\n",
    "check(\"loss ratio 65-74\", None if ages is None else round(ages.loc[\"65-74\", \"loss_ratio\"], 3), 0.608)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-010",
   "metadata": {},
   "source": [
    "## Your turn 2 — coverage metrics need coverage exposure\n",
    "\n",
    "Only vehicles that **bought** collision can have collision claims. Compute collision\n",
    "frequency using only rows where `prem_coll` is present."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-011",
   "metadata": {},
   "outputs": [],
   "source": [
    "coll_frequency = None\n",
    "...  # your code here\n",
    "check(\"collision frequency\", None if coll_frequency is None else round(coll_frequency, 4), 0.0878)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-012",
   "metadata": {},
   "source": [
    "## Your turn 3 — how thin are the cells?\n",
    "Count the style × region × age band cells, and how many have fewer than 10 claims."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-013",
   "metadata": {},
   "outputs": [],
   "source": [
    "n_cells = n_thin = None\n",
    "if ages is not None:\n",
    "    ...  # your code here\n",
    "check(\"cells\", n_cells, 108)\n",
    "check(\"cells with < 10 claims\", n_thin, 25)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}