{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f9806879",
   "metadata": {},
   "source": "# Week 3 — Monday: Cleaning & Grouping Data\n\n**DATA 202 · Calvin University**\n\n> Hagar, Sarah's Egyptian servant, flees into the desert after mistreatment. There, an angel of the Lord finds her by a spring and speaks to her. She responds:\n>\n> *\"You are a God of seeing,\"* she said, *\"for truly here I have seen him who looks after me.\"* — Genesis 16:13\n>\n> What if collecting, cleaning, and grouping data were themselves a form of *seeing* — of paying attention to people who are otherwise easy to overlook? Theologian Eric Stoddart calls this **\"com-veillance\"**: not watching *people*, but watching *for*, *with*, and *over* them.\n\nToday's dataset is about people experiencing homelessness — deliberately messy, because real records about real people always are. Cleaning it isn't just a technical chore; it's a decision about how carefully we're willing to look.\n\n**Today's plan (~50 min):**\n\n| Time | Section |\n|---|---|\n| ~5 min | Load and inspect the messy data |\n| ~22 min | Part 1 — Cleaning String Data with regex (SLO 03A) |\n| ~18 min | Part 2 — Grouping and Aggregating (SLO 03B) |\n| ~5 min | Careful with Aggregations + what's next |\n\nWatch for two kinds of stop-and-check along the way: **🎯 Predict First** (guess before we run the code) and **🙋 Quick Check** (a quick verbal question — no code, no pressure, just think and be ready to answer)."
  },
  {
   "cell_type": "markdown",
   "id": "56f47705",
   "metadata": {},
   "source": "---\n## Loading the Data"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2cd55616",
   "metadata": {},
   "outputs": [],
   "source": "import pandas as pd\n\nDATA_PATH = \"../../datasets/homeless.csv\"\nhomeless = pd.read_csv(DATA_PATH)\nhomeless.head()"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb0c79c5",
   "metadata": {},
   "outputs": [],
   "source": "homeless.info()"
  },
  {
   "cell_type": "markdown",
   "id": "053350c1",
   "metadata": {},
   "source": "**What to notice:** names carry stray spaces (`\"   peter\"`), the same city is spelled five different ways (`NEW-YORK`, `new-york`, `New york`, `SF`, `L.A.`...), and `shelter_status` mixes case and phrasing (`SHELTERED`, `shelter , pending`). Nothing here is usable for grouping yet — `groupby(\"city\")` right now would give you a dozen \"different\" cities that are really the same five.\n\n🙋 **Quick Check:** Before we fix anything — just by skimming the columns above, how many *actual* distinct cities do you think are hiding in this data? Say a number out loud."
  },
  {
   "cell_type": "markdown",
   "id": "c75dd6a2",
   "metadata": {},
   "source": "---\n## Part 1: Cleaning String Data (SLO 03A) · ~22 min\n\nWe'll use pandas' **string operations** (`.str.___`) and **regular expressions (regex)** to standardize this."
  },
  {
   "cell_type": "markdown",
   "id": "b9ae3a9f",
   "metadata": {},
   "source": "### Regex: Pattern Matching for Text\n\nA **regular expression** (regex) describes a *pattern* of characters instead of one exact string — a small language for \"find text that looks like this.\"\n\n| Syntax | Meaning | Example | Matches |\n|---|---|---|---|\n| literal text | the exact characters | `cat` | `cat` |\n| `\\|` | OR (alternation) | `cat\\|dog` | `cat` or `dog` |\n| `[A-Z]` | a character class | `[A-Z]+` | one or more uppercase letters |\n| `[^...]` | NOT this class | `[^a-zA-Z\\s]` | anything that isn't a letter or space |\n| `\\d` | a digit | `\\d{3}` | exactly 3 digits, e.g. `422` |\n| `\\s` | whitespace | `\\s+` | one or more spaces/tabs |\n| `.` | any single character | `h.t` | `hat`, `hot`, `h5t`... |\n| `*` `+` `?` | quantifiers | `go+gle` | `gogle`, `google`, `gooogle`... |\n| `^` ... `$` | start ... end of string | `^shelter$` | the *whole* string is exactly `shelter` |\n| `(...)`  | a group, often with `\\|` inside | `(shelter\\|street)` | `shelter` or `street`, treated as one unit |\n\nYou can practice more patterns at [regexone.com](https://regexone.com)."
  },
  {
   "cell_type": "markdown",
   "id": "5e7c5cff",
   "metadata": {},
   "source": "🎯 **Predict First:** Before we run any code — for each pair below, will the pattern match the string? (yes/no)\n\n1. Pattern `shelter.*pending` against `\"shelter , pending\"`\n2. Pattern `^sheltered$` against `\"sheltered\"`\n3. Pattern `^sheltered$` against `\"unsheltered\"`\n4. Pattern `^sheltered$` against `\"Sheltered\"`\n\nMake your guesses, *then* run the cell below."
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8886e9c",
   "metadata": {},
   "outputs": [],
   "source": "import re\n\ntests = [\n    (r\"shelter.*pending\", \"shelter , pending\"),\n    (r\"^sheltered$\", \"sheltered\"),\n    (r\"^sheltered$\", \"unsheltered\"),\n    (r\"^sheltered$\", \"Sheltered\"),\n]\nfor pattern, text in tests:\n    print(f\"{pattern!r:22} vs {text!r:22} -> {bool(re.search(pattern, text))}\")"
  },
  {
   "cell_type": "markdown",
   "id": "de07ed68",
   "metadata": {},
   "source": "Notice #3 and #4 are both `False`: `^...$` anchors mean the pattern must match the **entire** string (so `unsheltered` fails — it has extra letters before \"sheltered\"), and regex is **case-sensitive by default** (so `Sheltered` with a capital S doesn't match `sheltered`). This is exactly why we'll lowercase text *before* matching it, in a few cells.\n\n---\n### 🔨 Mini-Task A — Write a Regex (~2 min)\n\nWrite a regex pattern that matches a string made up **only of letters and spaces** — nothing else (no digits, no punctuation). Test it against the three example strings below using `re.fullmatch()`.\n\n*Hint:* you'll want a character class with `+` (one or more), anchored to the whole string."
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f57afe29",
   "metadata": {},
   "outputs": [],
   "source": "# Your code here\nmy_pattern = r\"\"  # fill this in\n\nfor s in [\"New York\", \"New York3\", \"New-York\"]:\n    print(s, \"->\", bool(re.fullmatch(my_pattern, s)))"
  },
  {
   "cell_type": "markdown",
   "id": "2d5c3662",
   "metadata": {},
   "source": "---\n### Now let's clean for real"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d91a8474",
   "metadata": {},
   "outputs": [],
   "source": "# 1. Strip stray whitespace\nhomeless[\"name\"] = homeless[\"name\"].str.strip()\nhomeless[\"city\"] = homeless[\"city\"].str.strip()\nhomeless[[\"name\", \"city\"]].head()"
  },
  {
   "cell_type": "markdown",
   "id": "ea992958",
   "metadata": {},
   "source": "🎯 **Predict First:** we're about to (a) turn dashes into spaces, (b) strip out anything that isn't a letter or space, and (c) title-case the result. What will `\"L.A.\"` become? What about `\"new-york\"`? Take a guess before running the next cell."
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d064d98f",
   "metadata": {},
   "outputs": [],
   "source": "# 2. Standardize separators, drop stray punctuation, then title-case\nhomeless[\"name\"] = (\n    homeless[\"name\"]\n    .str.replace(r\"[^a-zA-Z\\s-]\", \"\", regex=True)   # \"chloe.\" -> \"chloe\"\n    .str.title()                                     # -> \"Chloe\"\n)\n\nhomeless[\"city\"] = (\n    homeless[\"city\"]\n    .str.replace(\"-\", \" \", regex=False)               # \"new-york\" -> \"new york\"\n    .str.replace(r\"[^a-zA-Z\\s]\", \"\", regex=True)      # \"CHICAGO.\" -> \"CHICAGO\"\n    .str.title()                                       # -> \"Chicago\"\n)\n\nhomeless[[\"name\", \"city\"]].drop_duplicates(subset=\"city\").sort_values(\"city\")"
  },
  {
   "cell_type": "markdown",
   "id": "77cd5537",
   "metadata": {},
   "source": "Two cities are still abbreviated — `Sf` and `La` — because title-casing an abbreviation doesn't turn it into a full name. Regex and case rules can only take you so far; sometimes you need an explicit **lookup**.\n\n🙋 **Quick Check:** why *can't* a regex fix `\"SF\"` → `\"San Francisco\"`, when regex fixed `\"CHICAGO.\"` → `\"Chicago\"` just fine? What's fundamentally different about the two problems?"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49deed92",
   "metadata": {},
   "outputs": [],
   "source": "# 3. Map the remaining abbreviations explicitly\ncity_map = {\"Sf\": \"San Francisco\", \"La\": \"Los Angeles\"}\nhomeless[\"city\"] = homeless[\"city\"].replace(city_map)\nhomeless[\"city\"].value_counts()"
  },
  {
   "cell_type": "markdown",
   "id": "01693f83",
   "metadata": {},
   "source": "### Two regex, same job\n\nThere's rarely only one correct pattern. Both lines below flag the same rows — see if you can tell why before reading the explanation."
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6e1460d7",
   "metadata": {},
   "outputs": [],
   "source": "sample = pd.Series([\"shelter , pending\", \"shelter,pending\", \"shelter  ,  pending\", \"sheltered\"])\n\nversion_a = sample.str.contains(r\"shelter\\s*,\\s*pending\", regex=True)\nversion_b = sample.str.contains(r\"shelter[ ]*,[ ]*pending\", regex=True)\n\npd.DataFrame({\"text\": sample, \"version_a\": version_a, \"version_b\": version_b})"
  },
  {
   "cell_type": "markdown",
   "id": "0c58e141",
   "metadata": {},
   "source": "`\\s*` and `[ ]*` do almost the same thing here (zero-or-more spaces) — but `\\s` also matches tabs and newlines, while `[ ]` matches only the literal space character. Small choices like this matter once your data gets messier than expected."
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f2a7c24",
   "metadata": {},
   "outputs": [],
   "source": "# 4. Standardize shelter_status the same way — case first, then targeted replacements\nhomeless[\"shelter_status\"] = homeless[\"shelter_status\"].str.strip().str.lower()\n\nhomeless[\"shelter_status\"] = (\n    homeless[\"shelter_status\"]\n    .str.replace(r\"^sheltered$\", \"shelter\", regex=True)              # \"sheltered\" -> \"shelter\"\n    .str.replace(r\"shelter\\s*,\\s*pending\", \"shelter pending\", regex=True)\n    .str.replace(r\"temporary shelter\", \"shelter temporary\", regex=True)\n)\nhomeless[\"shelter_status\"].value_counts()"
  },
  {
   "cell_type": "markdown",
   "id": "2e0e3265",
   "metadata": {},
   "source": "---\n### 🔨 Mini-Task B — Extend the Pattern (~3 min)\n\nSuppose a few more rows had used the spelling `\"temp shelter\"` instead of `\"temporary shelter\"`. Write **one** regex pattern (using `|` for alternation) that matches *either* spelling in a single `.str.replace()` call, and test it below."
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0922a250",
   "metadata": {},
   "outputs": [],
   "source": "# Your code here\nsample = pd.Series([\"temporary shelter\", \"temp shelter\", \"shelter\"])\nmy_pattern = r\"\"  # fill this in — should match \"temporary shelter\" OR \"temp shelter\"\n\nsample.str.replace(my_pattern, \"shelter temporary\", regex=True)"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a250ac9f",
   "metadata": {},
   "outputs": [],
   "source": "# 5. education_level: strip + lowercase collapses most of the mess, then relabel nicely\nhomeless[\"education_level\"] = (\n    homeless[\"education_level\"]\n    .str.strip()\n    .str.lower()\n    .replace({\"none\": \"None\", \"primary\": \"Primary\", \"secondary\": \"Secondary\", \"higher\": \"Higher\"})\n)\nhomeless[\"education_level\"].value_counts()"
  },
  {
   "cell_type": "markdown",
   "id": "efa99a44",
   "metadata": {},
   "source": "---\n### 🔨 Task 1 — Flag a Pattern in Free Text (~5 min)\n\nThe `notes` column is unstructured text — but it still holds useful signal. Use `.str.contains()` with a regex to flag every row whose `notes` mention losing a job.\n\n- *Hint:* the notes use different phrasings — `\"job loss during pandemic\"`, `\"lost JOB; looking for work\"`. A pattern like `r\"job\"` with `case=False` will catch both.\n- Assign the result (a column of `True`/`False`) to `homeless[\"job_related\"]`.\n- **Bonus:** extend your pattern with `|` to *also* flag rows mentioning `\"healthcare\"`. How many rows match now?"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b2d802f3",
   "metadata": {},
   "outputs": [],
   "source": "# Your code here\n"
  },
  {
   "cell_type": "markdown",
   "id": "e9da9593",
   "metadata": {},
   "source": "---\n## Part 2: Grouping and Aggregating (SLO 03B) · ~18 min\n\nSometimes we don't want to look at each row (each person's record) — we want to **summarize groups**:\n\n* How many people are in each city?\n* What is the average support amount by shelter status?\n* Which group has the highest average years homeless?\n\nThis is what `groupby()` and aggregation functions are for — and there's more than one way to ask most of these questions."
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c4c93c9b",
   "metadata": {},
   "outputs": [],
   "source": "# 1. Grouping by one column: how many people per city?\nhomeless.groupby(\"city\")[\"id\"].count()"
  },
  {
   "cell_type": "markdown",
   "id": "c8ffc43c",
   "metadata": {},
   "source": "🎯 **Predict First:** before we look at money — which city do you guess has the **most** people in this dataset? Which has the **fewest**? Guess, then check against the output above."
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eeb61bba",
   "metadata": {},
   "outputs": [],
   "source": "# 2. Aggregating a numeric column: average monthly support by city\nhomeless.groupby(\"city\")[\"monthly_support_usd\"].mean()"
  },
  {
   "cell_type": "markdown",
   "id": "bdd04be7",
   "metadata": {},
   "source": "### Many ways to summarize the same numbers\n\n`.mean()` is only one lens. Passing a **list** of function names to `.agg()` runs several at once, side by side:"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f9a6dbda",
   "metadata": {},
   "outputs": [],
   "source": "homeless.groupby(\"shelter_status\")[\"monthly_support_usd\"].agg([\"mean\", \"median\", \"min\", \"max\", \"std\"])"
  },
  {
   "cell_type": "markdown",
   "id": "eeb5ee96",
   "metadata": {},
   "source": "* **mean** — the arithmetic average; sensitive to a few extreme values.\n* **median** — the middle value; barely moves even if one entry is way off.\n* **min / max** — the range of what's actually happening in each group.\n* **std** — how spread out the values are; a small std means the group is fairly uniform.\n\n🙋 **Quick Check:** if one person's `monthly_support_usd` were mistakenly entered as `50000` instead of `500`, which statistic above would be thrown off the most — the mean or the median? Which would barely notice?"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "625f4dff",
   "metadata": {},
   "outputs": [],
   "source": "# 3. Grouping by multiple columns: average years homeless by city AND education level\nhomeless.groupby([\"city\", \"education_level\"])[\"years_homeless\"].mean()"
  },
  {
   "cell_type": "markdown",
   "id": "3bf110f1",
   "metadata": {},
   "source": "### Naming your aggregations\n\nThe dict-style `.agg({...})` you'll see next works well when you're summarizing several *columns*. When you want several *statistics* from the same column with clean, custom output names, **named aggregation** is often nicer:"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8abffbbe",
   "metadata": {},
   "outputs": [],
   "source": "# Dict-of-lists style: several statistics on several columns\nhomeless.groupby(\"shelter_status\").agg({\n    \"family_size\": [\"mean\", \"max\"],\n    \"monthly_support_usd\": [\"mean\", \"sum\"],\n})"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e1dc0a9f",
   "metadata": {},
   "outputs": [],
   "source": "# Named-aggregation style: same idea, flatter and more readable output\nhomeless.groupby(\"city\").agg(\n    avg_support=(\"monthly_support_usd\", \"mean\"),\n    max_support=(\"monthly_support_usd\", \"max\"),\n    n_people=(\"id\", \"count\"),\n)"
  },
  {
   "cell_type": "markdown",
   "id": "43260e84",
   "metadata": {},
   "source": "Both cells above are doing the *same kind* of work — summarizing several statistics per group — just with different syntax. Pick whichever reads more clearly for the summary you're building.\n\n---\n### 🔨 Mini-Task C — Same Aggregation, Other Syntax (~3 min)\n\nRewrite this summary — **average and max `years_homeless` per `education_level`** — using named aggregation (`.agg(name=(...))`) instead of the dict style."
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "67fc3b34",
   "metadata": {},
   "outputs": [],
   "source": "# For reference, dict style:\nhomeless.groupby(\"education_level\").agg({\"years_homeless\": [\"mean\", \"max\"]})\n\n# Your code here — same result, named-aggregation style\n"
  },
  {
   "cell_type": "markdown",
   "id": "ed10a79e",
   "metadata": {},
   "source": "### Beyond the built-ins: custom aggregations\n\n`.agg()` also accepts **any function**, including a `lambda` — useful when no built-in does exactly what you want. For example, the *range* (max − min) of support per city:"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "741d6654",
   "metadata": {},
   "outputs": [],
   "source": "homeless.groupby(\"city\")[\"monthly_support_usd\"].agg(lambda x: x.max() - x.min())"
  },
  {
   "cell_type": "markdown",
   "id": "955a6e98",
   "metadata": {},
   "source": "Groupby results carry the grouping column as an **index**. Use `.reset_index()` to turn it back into a normal column — useful before sorting, plotting, or merging with other data."
  },
  {
   "cell_type": "markdown",
   "id": "67a7a248",
   "metadata": {},
   "source": "🎯 **Predict First:** which city do you think receives the highest **total** `monthly_support_usd`? Is that necessarily the same city with the highest **average**? Guess both, then check."
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e4594c73",
   "metadata": {},
   "outputs": [],
   "source": "# reset_index(), then sort to find the largest group\n(\n    homeless.groupby(\"city\")[\"monthly_support_usd\"]\n    .sum()\n    .reset_index()\n    .sort_values(\"monthly_support_usd\", ascending=False)\n)"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65a12c6a",
   "metadata": {},
   "outputs": [],
   "source": "# value_counts() is a shortcut for \"groupby + count\" on one column\nhomeless[\"education_level\"].value_counts()"
  },
  {
   "cell_type": "markdown",
   "id": "4aea404c",
   "metadata": {},
   "source": "---\n### 🔨 Task 2 — Group, Aggregate, Compare (~5 min)\n\n1. Which `shelter_status` has the highest **average** `monthly_support_usd`?\n2. Among people with more than 10 years homeless (`years_homeless > 10`), which `city` has the most people?\n3. Using **named aggregation**, compute both the **count** of people and the **average** `years_homeless`, per `education_level`, in a single `.agg(...)` call.\n\n*Hint for (2): filter first, then group.*"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7a6d68f8",
   "metadata": {},
   "outputs": [],
   "source": "# Your code here\n"
  },
  {
   "cell_type": "markdown",
   "id": "3edb320c",
   "metadata": {},
   "source": "---\n## Careful with Aggregations\n\nAggregations are powerful, but every one of them **distorts** the data on purpose:\n\n* **Counts** tell us *how many*, not *who*. 20 people in \"shelter\" in one city says nothing about their individual situations.\n* **Means** smooth over differences. An average of 7 years homeless could mean everyone is close to 7 — or half the group is brand new and half has been homeless for over a decade. Two very different realities, one number.\n* **Medians** resist outliers but hide them too — a median can look calm while a handful of extreme cases go completely unmentioned.\n* **Sums** favor big groups. A large city might show the highest *total* support while a smaller city actually gives more *per person*.\n\nEvery aggregation **reduces detail** in exchange for a pattern you can see. That trade-off is not a flaw to fix — it's the whole point of grouping, and it's also exactly what **this Friday's Forum 1** is about: Chapter 1 of *Counting* by Deborah Stone argues that no summary number is \"raw,\" because someone always had to decide what counts as alike before any counting could begin. `groupby()` is that decision, made in code."
  },
  {
   "cell_type": "markdown",
   "id": "15266f4e",
   "metadata": {},
   "source": "---\n## Coming Up\n\n| Day | Topic | Builds on today |\n|---|---|---|\n| Wed | Choosing the right plot | The same cleaned dataset, now visualized — histograms, scatter, line, and bar charts |\n| Fri | Forum 1 — *Counting*, Ch. 1 | What gets ignored when `groupby()` treats rows as \"the same\"? |\n| Week 4 | Joining tables | Combining datasets *before* you can group or plot them together |"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.11.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}