{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cell-000",
   "metadata": {},
   "source": [
    "# Lesson 1 — Tabular Data Structures with pandas\n",
    "\n",
    "**Companion notebook** for the Lesson 1 slides. It expects the course data pack in\n",
    "`../data/` (that is, this notebook sits in your project's `notebooks/` folder).\n",
    "\n",
    "Run cells top to bottom; replace the `...` in **Your turn** cells; the `check()` cells\n",
    "print ✅ or ❌."
   ]
  },
  {
   "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",
    "RAW = \"../data/raw/claims_raw.csv\"\n",
    "POLICIES = \"../data/motor/policies.csv\"\n",
    "print(pd.__version__)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-002",
   "metadata": {},
   "source": [
    "## 1. Series and DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-003",
   "metadata": {},
   "outputs": [],
   "source": [
    "paid = pd.Series([1200.0, 350.0, 8800.0], index=[\"CLM1\", \"CLM2\", \"CLM3\"], name=\"paid\")\n",
    "print(paid.index, paid.values, paid.dtype)\n",
    "paid.loc[\"CLM2\"], paid.iloc[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-004",
   "metadata": {},
   "outputs": [],
   "source": [
    "raw = pd.read_csv(RAW)\n",
    "print(raw.shape)\n",
    "raw.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-005",
   "metadata": {},
   "source": [
    "`paid` and `outstanding` should be numbers, but they came in as text (`str`).\n",
    "Profile the text columns to see why."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-006",
   "metadata": {},
   "outputs": [],
   "source": [
    "raw[\"coverage\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-007",
   "metadata": {},
   "outputs": [],
   "source": [
    "raw[\"status\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-008",
   "metadata": {},
   "outputs": [],
   "source": [
    "raw.loc[raw[\"paid\"].str.startswith(\"$\"), \"paid\"].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-009",
   "metadata": {},
   "source": [
    "## 2. Missing values that don't look missing\n",
    "`-` isn't on pandas' default list of missing-value markers, so it turned `outstanding`\n",
    "into text. Tell `read_csv` about it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-010",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(raw[\"outstanding\"].dtype, raw[\"outstanding\"].isna().sum())\n",
    "raw = pd.read_csv(RAW, na_values=[\"-\"])\n",
    "print(raw[\"outstanding\"].dtype, raw[\"outstanding\"].isna().sum())\n",
    "raw.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-011",
   "metadata": {},
   "source": [
    "## 3. Label vs. position indexing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-012",
   "metadata": {},
   "outputs": [],
   "source": [
    "ref = pd.read_csv(\"../data/motor/claims.csv\").set_index(\"claim_id\").sort_index()\n",
    "print(ref.loc[\"CLM000001\", [\"coverage\", \"paid\", \"status\"]].to_dict())\n",
    "print(ref.loc[\"CLM000001\":\"CLM000003\", [\"coverage\", \"paid\"]])   # .loc includes the end label\n",
    "print(ref.iloc[:3, [2, 7]])                                     # .iloc excludes the end"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-013",
   "metadata": {},
   "source": [
    "## 4. Strings, types, and `.query()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-014",
   "metadata": {},
   "outputs": [],
   "source": [
    "key = raw[\"coverage\"].str.strip().str.upper()\n",
    "sorted(key.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-015",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.Series([1, None, 3], dtype=\"Int64\"), np.nan == np.nan, pd.isna(np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-016",
   "metadata": {},
   "outputs": [],
   "source": [
    "ref.query(\"status == 'Open' and outstanding > 50_000\")[[\"coverage\", \"paid\", \"outstanding\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-017",
   "metadata": {},
   "source": [
    "---\n",
    "## Your turn — clean `claims_raw.csv`\n",
    "\n",
    "Write `clean_claims(raw)` that returns a cleaned copy:\n",
    "\n",
    "1. `paid`: strip `$` and `,`, convert to numbers\n",
    "2. `coverage`: strip, upper-case, map synonyms to `LIAB` / `COLL` / `COMP`; make it `category`\n",
    "3. `status`: strip and title-case (`Open` / `Closed`); make it `category`\n",
    "4. Drop exact duplicate rows\n",
    "5. Parse `accident_date` and `report_date`\n",
    "\n",
    "Then build the **validation table** (one boolean column per rule) and keep only the valid rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-018",
   "metadata": {},
   "outputs": [],
   "source": [
    "COVERAGE_MAP = {\"LIAB\": \"LIAB\", \"LIABILITY\": \"LIAB\", \"THIRD PARTY\": \"LIAB\",\n",
    "                \"COLL\": \"COLL\", \"COLLISION\": \"COLL\", \"COMP\": \"COMP\", \"COMPREHENSIVE\": \"COMP\"}\n",
    "\n",
    "\n",
    "def clean_claims(raw: pd.DataFrame) -> pd.DataFrame:\n",
    "    df = raw.copy()\n",
    "    ...  # your code here\n",
    "    return df\n",
    "\n",
    "\n",
    "df = clean_claims(pd.read_csv(RAW, na_values=[\"-\"]))\n",
    "check(\"rows after de-duplication\", len(df), 3354)\n",
    "check(\"coverage codes\", sorted(df[\"coverage\"].dropna().astype(str).unique()), [\"COLL\", \"COMP\", \"LIAB\"])\n",
    "check(\"status values\", sorted(df[\"status\"].dropna().astype(str).unique()), [\"Closed\", \"Open\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-019",
   "metadata": {},
   "outputs": [],
   "source": [
    "policy_ids = pd.read_csv(POLICIES, usecols=[\"policy_id\"])[\"policy_id\"]\n",
    "checks = None\n",
    "...  # your code here\n",
    "\n",
    "if checks is not None:\n",
    "    print(checks.sum())\n",
    "    bad = checks.any(axis=1)\n",
    "    clean = df[~bad]\n",
    "    rejects = df[bad].assign(reason=checks[bad].idxmax(axis=1))\n",
    "    check(\"invalid rows\", int(bad.sum()), 39)\n",
    "    check(\"clean rows\", len(clean), 3315)\n",
    "else:\n",
    "    check(\"clean rows\", None, 3315)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}