{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cell-000",
   "metadata": {},
   "source": [
    "# Lesson 3 — Relational Data Modeling & Multi-Table Joins\n",
    "\n",
    "**Companion notebook** for the Lesson 3 slides. At the end you save the **analytical\n",
    "base table** (ABT) to `../data/processed/abt.parquet` — Lessons 4–8 start from it."
   ]
  },
  {
   "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",
    "from pathlib import Path\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "M = Path(\"../data/motor\")\n",
    "policies = pd.read_csv(M / \"policies.csv\", parse_dates=[\"inception_date\", \"expiry_date\", \"cancellation_date\"])\n",
    "vehicles = pd.read_csv(M / \"vehicles.csv\")\n",
    "persons = pd.read_csv(M / \"persons.csv\", parse_dates=[\"date_of_birth\", \"licence_date\"])\n",
    "policy_drivers = pd.read_csv(M / \"policy_drivers.csv\")\n",
    "coverages = pd.read_csv(M / \"coverages.csv\")\n",
    "claims = pd.read_csv(M / \"claims.csv\", parse_dates=[\"accident_date\", \"report_date\"])\n",
    "{name: len(t) for name, t in [(\"policies\", policies), (\"vehicles\", vehicles), (\"persons\", persons),\n",
    "                              (\"policy_drivers\", policy_drivers), (\"coverages\", coverages), (\"claims\", claims)]}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-002",
   "metadata": {},
   "source": [
    "## 1. Test the keys before joining"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-003",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(policies[\"policy_id\"].duplicated().sum(), coverages.duplicated([\"vehicle_id\", \"coverage\"]).sum())\n",
    "print((~claims[\"policy_id\"].isin(policies[\"policy_id\"])).sum())\n",
    "print(vehicles.groupby(\"policy_id\").size().value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-004",
   "metadata": {},
   "source": [
    "## 2. Merge types and `indicator=True`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-005",
   "metadata": {},
   "outputs": [],
   "source": [
    "L = pd.DataFrame({\"policy_id\": [\"P1\", \"P2\", \"P3\"], \"channel\": [\"Agent\", \"Direct\", \"Agent\"]})\n",
    "R = pd.DataFrame({\"policy_id\": [\"P1\", \"P1\", \"P3\", \"P9\"], \"paid\": [500, 1200, 300, 800]})\n",
    "for how in [\"inner\", \"left\", \"right\", \"outer\"]:\n",
    "    print(how, len(L.merge(R, on=\"policy_id\", how=how)))\n",
    "L.merge(R, on=\"policy_id\", how=\"outer\", indicator=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-006",
   "metadata": {},
   "source": [
    "## 3. Cardinality — and the wrong-grain trap"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-007",
   "metadata": {},
   "outputs": [],
   "source": [
    "claims[\"incurred\"] = claims[\"paid\"] + claims[\"outstanding\"]\n",
    "wrong = claims.merge(policy_drivers, on=\"policy_id\")\n",
    "print(f\"{len(claims):,} claims → {len(wrong):,} rows; incurred {claims['incurred'].sum():,.0f} → {wrong['incurred'].sum():,.0f}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-008",
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    policies.merge(claims, on=\"policy_id\", validate=\"one_to_one\")\n",
    "except pd.errors.MergeError as err:\n",
    "    print(\"MergeError:\", str(err).splitlines()[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-009",
   "metadata": {},
   "source": [
    "## 4. Exposure (from Lesson 2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-010",
   "metadata": {},
   "outputs": [],
   "source": [
    "EVAL = pd.Timestamp(\"2025-12-31\")\n",
    "\n",
    "\n",
    "def calendar_exposure(start, end, year):\n",
    "    y0, y1 = pd.Timestamp(f\"{year}-01-01\"), pd.Timestamp(f\"{year}-12-31\")\n",
    "    days = (end.clip(upper=y1) - start.clip(lower=y0)).dt.days + 1\n",
    "    return days.clip(lower=0) / (366 if y1.is_leap_year else 365)\n",
    "\n",
    "\n",
    "policies[\"earned_end\"] = policies[[\"expiry_date\", \"cancellation_date\"]].min(axis=1).clip(upper=EVAL)\n",
    "policies[\"exposure\"] = sum(calendar_exposure(policies[\"inception_date\"], policies[\"earned_end\"], y)\n",
    "                           for y in range(2022, 2026))\n",
    "term_days = (policies[\"expiry_date\"] - policies[\"inception_date\"]).dt.days + 1\n",
    "policies[\"earned_fraction\"] = ((policies[\"earned_end\"] - policies[\"inception_date\"]).dt.days + 1).clip(lower=0) / term_days"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-011",
   "metadata": {},
   "source": [
    "---\n",
    "## Your turn — aggregate the children to the grain, then build the ABT\n",
    "\n",
    "The grain is **one row per vehicle per policy term**. Build:\n",
    "\n",
    "* `drivers` — one row per `policy_id`: `n_drivers`, `youngest_driver_age` (age at inception)\n",
    "* `cov_wide` — one row per `vehicle_id`: `prem_liab`, `prem_coll`, `prem_comp`\n",
    "* `clm` — one row per `vehicle_id`: `n_liab`, `n_coll`, `n_comp`, `inc_liab`, `inc_coll`, `inc_comp`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-012",
   "metadata": {},
   "outputs": [],
   "source": [
    "drivers = cov_wide = clm = None\n",
    "...  # your code here\n",
    "\n",
    "check(\"drivers rows\", None if drivers is None else len(drivers), 22185)\n",
    "check(\"cov_wide rows\", None if cov_wide is None else len(cov_wide), 23993)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-013",
   "metadata": {},
   "source": [
    "Now merge onto `vehicles` with `validate=` on every step, fill missing claim columns\n",
    "with 0, and reconcile the control totals."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-014",
   "metadata": {},
   "outputs": [],
   "source": [
    "abt = None\n",
    "if drivers is not None and cov_wide is not None and clm is not None:\n",
    "    abt = (vehicles\n",
    "           .merge(policies[[\"policy_id\", \"inception_date\", \"channel\", \"latitude\", \"longitude\",\n",
    "                            \"exposure\", \"earned_fraction\"]], on=\"policy_id\", how=\"left\", validate=\"many_to_one\")\n",
    "           .merge(drivers, on=\"policy_id\", how=\"left\", validate=\"many_to_one\")\n",
    "           .merge(cov_wide, on=\"vehicle_id\", how=\"left\", validate=\"one_to_one\")\n",
    "           .merge(clm, on=\"vehicle_id\", how=\"left\", validate=\"one_to_one\"))\n",
    "    claim_cols = [c for c in abt.columns if c.startswith((\"n_liab\", \"n_coll\", \"n_comp\", \"inc_\"))]\n",
    "    abt[claim_cols] = abt[claim_cols].fillna(0)\n",
    "\n",
    "check(\"ABT rows\", None if abt is None else len(abt), 23993)\n",
    "check(\"claims in ABT\", None if abt is None else int(abt[[\"n_liab\", \"n_coll\", \"n_comp\"]].sum().sum()), 3354)\n",
    "check(\"incurred in ABT\", None if abt is None else round(abt.filter(like=\"inc_\").sum().sum(), 2), 24669861.81)\n",
    "check(\"car years\", None if abt is None else round(abt[\"exposure\"].sum(), 2), 20100.95)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-015",
   "metadata": {},
   "source": [
    "## Save the ABT for the next lessons\n",
    "\n",
    "We add a few columns Lessons 4–6 use: earned premium by coverage (Lesson 2's pro-rata\n",
    "earning), total claims and incurred, and the youngest-driver age band."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-016",
   "metadata": {},
   "outputs": [],
   "source": [
    "if abt is not None:\n",
    "    for c in (\"liab\", \"coll\", \"comp\"):\n",
    "        abt[f\"ep_{c}\"] = abt[f\"prem_{c}\"].fillna(0) * abt[\"earned_fraction\"]\n",
    "    abt[\"earned_premium\"] = abt[[\"ep_liab\", \"ep_coll\", \"ep_comp\"]].sum(axis=1)\n",
    "    abt[\"n_claims\"] = abt[[\"n_liab\", \"n_coll\", \"n_comp\"]].sum(axis=1)\n",
    "    abt[\"incurred\"] = abt[[\"inc_liab\", \"inc_coll\", \"inc_comp\"]].sum(axis=1)\n",
    "    abt[\"age_band\"] = pd.cut(abt[\"youngest_driver_age\"], [16, 25, 35, 50, 65, 75, 100], right=False,\n",
    "                             labels=[\"17-24\", \"25-34\", \"35-49\", \"50-64\", \"65-74\", \"75+\"])\n",
    "    Path(\"../data/processed\").mkdir(exist_ok=True)\n",
    "    abt.to_parquet(\"../data/processed/abt.parquet\")\n",
    "    print(\"saved\", abt.shape)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}