{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "71bf14a6",
   "metadata": {},
   "source": [
    "# Census API Python Tutorial: Regional Analysis Guide\n",
    "\n",
    "This notebook contains the complete analysis workflow used for the article.\n",
    "\n",
    "It uses the **2024 American Community Survey 5 year Data Profiles** and is designed for U.S. regional analysis with Python, pandas, requests, and matplotlib.\n",
    "\n",
    "The notebook covers:\n",
    "\n",
    "1. Census API setup\n",
    "2. API key handling\n",
    "3. Variable metadata checks\n",
    "4. Regional ACS data retrieval\n",
    "5. Data cleaning and numeric conversion\n",
    "6. Census region, state, county, FIPS, and GEOID handling\n",
    "7. Regional population, income, poverty, and education analysis\n",
    "8. ACS margins of error\n",
    "9. Regional median household income chart\n",
    "10. State income versus poverty scatter plot\n",
    "11. California county example\n",
    "12. Validation and reproducibility\n",
    "13. Saving clean data and metadata\n",
    "\n",
    "All live statistics are requested from the U.S. Census Bureau API when the notebook runs.\n",
    "\n",
    "**Important:** Set a valid `CENSUS_API_KEY` environment variable before running live requests."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54840eaf",
   "metadata": {},
   "source": [
    "## 1. Install and import the Python packages\n",
    "\n",
    "Run the installation cell only if the packages are not already available in your notebook environment."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e08a5896",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Uncomment this line if needed.\n",
    "# %pip install requests pandas matplotlib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cb8c44f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import json\n",
    "from pathlib import Path\n",
    "from datetime import date\n",
    "\n",
    "import requests\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib.ticker import FuncFormatter\n",
    "\n",
    "pd.set_option(\"display.max_columns\", 30)\n",
    "pd.set_option(\"display.width\", 140)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4d2a699",
   "metadata": {},
   "source": [
    "## 2. Define the Census dataset and API key\n",
    "\n",
    "The main dataset is the 2024 ACS 5 year Data Profiles dataset.\n",
    "\n",
    "The API key is read from an environment variable so it is not stored in the notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97290b96",
   "metadata": {},
   "outputs": [],
   "source": [
    "YEAR = 2024\n",
    "DATASET = \"acs/acs5/profile\"\n",
    "BASE_URL = f\"https://api.census.gov/data/{YEAR}/{DATASET}\"\n",
    "\n",
    "API_KEY = os.getenv(\"CENSUS_API_KEY\")\n",
    "\n",
    "if not API_KEY:\n",
    "    raise RuntimeError(\n",
    "        \"CENSUS_API_KEY is not set. Add your Census API key to the environment \"\n",
    "        \"before running the live analysis.\"\n",
    "    )\n",
    "\n",
    "print(\"Dataset:\", BASE_URL)\n",
    "print(\"API key found:\", bool(API_KEY))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b620e5ee",
   "metadata": {},
   "source": [
    "### Example ways to set the key\n",
    "\n",
    "**Windows PowerShell**\n",
    "\n",
    "```powershell\n",
    "$env:CENSUS_API_KEY=\"YOUR_KEY_HERE\"\n",
    "```\n",
    "\n",
    "**macOS or Linux**\n",
    "\n",
    "```bash\n",
    "export CENSUS_API_KEY=\"YOUR_KEY_HERE\"\n",
    "```\n",
    "\n",
    "Restart the notebook kernel if the variable was set after the notebook was opened."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "08a98dc2",
   "metadata": {},
   "source": [
    "## 3. Define and verify the ACS variables\n",
    "\n",
    "The article uses four main regional measures and their matching margins of error.\n",
    "\n",
    "The `E` suffix is an estimate.\n",
    "\n",
    "The `M` suffix is a margin of error.\n",
    "\n",
    "The `PE` suffix is a percent estimate.\n",
    "\n",
    "The `PM` suffix is a percent margin of error."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "43fa8158",
   "metadata": {},
   "outputs": [],
   "source": [
    "VARIABLES = [\n",
    "    \"NAME\",\n",
    "    \"DP05_0001E\",\n",
    "    \"DP05_0001M\",\n",
    "    \"DP03_0062E\",\n",
    "    \"DP03_0062M\",\n",
    "    \"DP03_0128PE\",\n",
    "    \"DP03_0128PM\",\n",
    "    \"DP02_0068PE\",\n",
    "    \"DP02_0068PM\",\n",
    "]\n",
    "\n",
    "VARIABLE_INFO = pd.DataFrame(\n",
    "    [\n",
    "        [\"DP05_0001E\", \"Population\", \"Estimate\", \"DP05_0001M\"],\n",
    "        [\"DP03_0062E\", \"Median household income\", \"Estimate\", \"DP03_0062M\"],\n",
    "        [\"DP03_0128PE\", \"Poverty rate\", \"Percent estimate\", \"DP03_0128PM\"],\n",
    "        [\"DP02_0068PE\", \"Bachelor degree or higher\", \"Percent estimate\", \"DP02_0068PM\"],\n",
    "    ],\n",
    "    columns=[\"Variable\", \"Readable name\", \"Estimate type\", \"Margin of error variable\"],\n",
    ")\n",
    "\n",
    "VARIABLE_INFO"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f683a24c",
   "metadata": {},
   "source": [
    "The next cell checks the official variable metadata for the exact year and dataset before the analysis continues."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f82021b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "variables_url = f\"{BASE_URL}/variables.json\"\n",
    "\n",
    "metadata_response = requests.get(variables_url, timeout=30)\n",
    "metadata_response.raise_for_status()\n",
    "variable_metadata = metadata_response.json()[\"variables\"]\n",
    "\n",
    "rows = []\n",
    "for variable in VARIABLES:\n",
    "    if variable == \"NAME\":\n",
    "        continue\n",
    "\n",
    "    details = variable_metadata.get(variable)\n",
    "    if details is None:\n",
    "        raise KeyError(f\"{variable} was not found in {YEAR} {DATASET} metadata.\")\n",
    "\n",
    "    rows.append(\n",
    "        {\n",
    "            \"variable\": variable,\n",
    "            \"label\": details.get(\"label\"),\n",
    "            \"concept\": details.get(\"concept\"),\n",
    "            \"predicateType\": details.get(\"predicateType\"),\n",
    "        }\n",
    "    )\n",
    "\n",
    "verified_variables = pd.DataFrame(rows)\n",
    "verified_variables"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be6558c6",
   "metadata": {},
   "source": [
    "## 4. Build a reusable Census API function\n",
    "\n",
    "A reusable request function keeps the code consistent for regions, states, and counties.\n",
    "\n",
    "It checks the API key, variable count, HTTP response, and empty responses."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b3000368",
   "metadata": {},
   "outputs": [],
   "source": [
    "def census_get(\n",
    "    year,\n",
    "    dataset,\n",
    "    variables,\n",
    "    for_clause,\n",
    "    in_clause=None,\n",
    "    timeout=30,\n",
    "):\n",
    "    api_key = os.getenv(\"CENSUS_API_KEY\")\n",
    "\n",
    "    if not api_key:\n",
    "        raise RuntimeError(\"Set CENSUS_API_KEY before running a Census query.\")\n",
    "\n",
    "    if not variables:\n",
    "        raise ValueError(\"Pass at least one variable.\")\n",
    "\n",
    "    if len(variables) > 50:\n",
    "        raise ValueError(\n",
    "            \"A standard Census Data API query supports up to 50 variables.\"\n",
    "        )\n",
    "\n",
    "    url = f\"https://api.census.gov/data/{year}/{dataset}\"\n",
    "\n",
    "    params = {\n",
    "        \"get\": \",\".join(variables),\n",
    "        \"for\": for_clause,\n",
    "        \"key\": api_key,\n",
    "    }\n",
    "\n",
    "    if in_clause:\n",
    "        params[\"in\"] = in_clause\n",
    "\n",
    "    response = requests.get(url, params=params, timeout=timeout)\n",
    "    response.raise_for_status()\n",
    "\n",
    "    rows = response.json()\n",
    "\n",
    "    if not rows:\n",
    "        return pd.DataFrame()\n",
    "\n",
    "    if len(rows) == 1:\n",
    "        return pd.DataFrame(columns=rows[0])\n",
    "\n",
    "    return pd.DataFrame(rows[1:], columns=rows[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d9622b8d",
   "metadata": {},
   "source": [
    "## 5. Pull the four Census regions\n",
    "\n",
    "The geography request `region:*` asks the API for all four Census regions directly.\n",
    "\n",
    "This matters for statistics such as median household income. A regional median should be queried at the regional geography level rather than created by averaging state medians."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03f750c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "regions_raw = census_get(\n",
    "    year=YEAR,\n",
    "    dataset=DATASET,\n",
    "    variables=VARIABLES,\n",
    "    for_clause=\"region:*\",\n",
    ")\n",
    "\n",
    "regions_raw"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "afc4c12f",
   "metadata": {},
   "source": [
    "## 6. Clean the regional data\n",
    "\n",
    "Census API values are usually returned as strings.\n",
    "\n",
    "Measurement fields are converted to numeric values.\n",
    "\n",
    "Geography identifiers stay as strings so leading zeros are preserved where they are relevant."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0db69c76",
   "metadata": {},
   "outputs": [],
   "source": [
    "RENAME_MAP = {\n",
    "    \"NAME\": \"region_name\",\n",
    "    \"DP05_0001E\": \"population\",\n",
    "    \"DP05_0001M\": \"population_moe\",\n",
    "    \"DP03_0062E\": \"median_household_income\",\n",
    "    \"DP03_0062M\": \"median_household_income_moe\",\n",
    "    \"DP03_0128PE\": \"poverty_rate\",\n",
    "    \"DP03_0128PM\": \"poverty_rate_moe\",\n",
    "    \"DP02_0068PE\": \"bachelor_or_higher_rate\",\n",
    "    \"DP02_0068PM\": \"bachelor_or_higher_rate_moe\",\n",
    "}\n",
    "\n",
    "regions = regions_raw.rename(columns=RENAME_MAP).copy()\n",
    "\n",
    "NUMERIC_COLUMNS = [\n",
    "    \"population\",\n",
    "    \"population_moe\",\n",
    "    \"median_household_income\",\n",
    "    \"median_household_income_moe\",\n",
    "    \"poverty_rate\",\n",
    "    \"poverty_rate_moe\",\n",
    "    \"bachelor_or_higher_rate\",\n",
    "    \"bachelor_or_higher_rate_moe\",\n",
    "]\n",
    "\n",
    "for column in NUMERIC_COLUMNS:\n",
    "    regions[column] = pd.to_numeric(regions[column], errors=\"coerce\")\n",
    "\n",
    "regions[\"region\"] = regions[\"region\"].astype(\"string\")\n",
    "\n",
    "REGION_CODE_TO_NAME = {\n",
    "    \"1\": \"Northeast\",\n",
    "    \"2\": \"Midwest\",\n",
    "    \"3\": \"South\",\n",
    "    \"4\": \"West\",\n",
    "}\n",
    "\n",
    "regions[\"region_standard\"] = regions[\"region\"].map(REGION_CODE_TO_NAME)\n",
    "\n",
    "regions = regions.sort_values(\"region\").reset_index(drop=True)\n",
    "\n",
    "regions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1cab7117",
   "metadata": {},
   "source": [
    "## 7. Validate the regional result\n",
    "\n",
    "These checks catch common problems before the data reaches a table or chart."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c2109bca",
   "metadata": {},
   "outputs": [],
   "source": [
    "expected_regions = {\"1\", \"2\", \"3\", \"4\"}\n",
    "actual_regions = set(regions[\"region\"].dropna())\n",
    "\n",
    "if actual_regions != expected_regions:\n",
    "    raise ValueError(f\"Unexpected region codes: {actual_regions}\")\n",
    "\n",
    "if regions[\"region\"].duplicated().any():\n",
    "    raise ValueError(\"Duplicate region rows found.\")\n",
    "\n",
    "if len(regions) != 4:\n",
    "    raise ValueError(f\"Expected 4 Census regions but received {len(regions)}.\")\n",
    "\n",
    "if regions[NUMERIC_COLUMNS].isna().any().any():\n",
    "    missing = regions[NUMERIC_COLUMNS].isna().sum()\n",
    "    raise ValueError(f\"Missing numeric values found:\\n{missing[missing > 0]}\")\n",
    "\n",
    "if (regions[\"population\"] <= 0).any():\n",
    "    raise ValueError(\"Population must be positive.\")\n",
    "\n",
    "if not regions[\"poverty_rate\"].between(0, 100).all():\n",
    "    raise ValueError(\"Poverty rate must be between 0 and 100.\")\n",
    "\n",
    "if not regions[\"bachelor_or_higher_rate\"].between(0, 100).all():\n",
    "    raise ValueError(\"Education rate must be between 0 and 100.\")\n",
    "\n",
    "if (regions[\"median_household_income\"] <= 0).any():\n",
    "    raise ValueError(\"Median household income must be positive.\")\n",
    "\n",
    "print(\"Regional validation checks passed.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24d6b23a",
   "metadata": {},
   "source": [
    "## 8. Build the regional results table\n",
    "\n",
    "This is the main result table used for regional comparison.\n",
    "\n",
    "Margins of error are kept beside the estimates so uncertainty is not separated from the reported statistic."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "643e33cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "regional_results = regions[\n",
    "    [\n",
    "        \"region_standard\",\n",
    "        \"population\",\n",
    "        \"population_moe\",\n",
    "        \"median_household_income\",\n",
    "        \"median_household_income_moe\",\n",
    "        \"poverty_rate\",\n",
    "        \"poverty_rate_moe\",\n",
    "        \"bachelor_or_higher_rate\",\n",
    "        \"bachelor_or_higher_rate_moe\",\n",
    "    ]\n",
    "].rename(\n",
    "    columns={\n",
    "        \"region_standard\": \"Region\",\n",
    "        \"population\": \"Population\",\n",
    "        \"population_moe\": \"Population MOE\",\n",
    "        \"median_household_income\": \"Median household income\",\n",
    "        \"median_household_income_moe\": \"Income MOE\",\n",
    "        \"poverty_rate\": \"Poverty rate\",\n",
    "        \"poverty_rate_moe\": \"Poverty rate MOE\",\n",
    "        \"bachelor_or_higher_rate\": \"Bachelor degree or higher\",\n",
    "        \"bachelor_or_higher_rate_moe\": \"Education rate MOE\",\n",
    "    }\n",
    ")\n",
    "\n",
    "regional_results"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a97369e",
   "metadata": {},
   "source": [
    "### A formatted display table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e3a37472",
   "metadata": {},
   "outputs": [],
   "source": [
    "regional_display = regional_results.copy()\n",
    "\n",
    "regional_display[\"Population\"] = regional_display[\"Population\"].map(\n",
    "    lambda x: f\"{int(x):,}\"\n",
    ")\n",
    "regional_display[\"Population MOE\"] = regional_display[\"Population MOE\"].map(\n",
    "    lambda x: f\"{int(x):,}\"\n",
    ")\n",
    "regional_display[\"Median household income\"] = regional_display[\n",
    "    \"Median household income\"\n",
    "].map(lambda x: f\"${int(x):,}\")\n",
    "regional_display[\"Income MOE\"] = regional_display[\"Income MOE\"].map(\n",
    "    lambda x: f\"±${int(x):,}\"\n",
    ")\n",
    "regional_display[\"Poverty rate\"] = regional_display[\"Poverty rate\"].map(\n",
    "    lambda x: f\"{x:.1f}%\"\n",
    ")\n",
    "regional_display[\"Poverty rate MOE\"] = regional_display[\"Poverty rate MOE\"].map(\n",
    "    lambda x: f\"±{x:.1f}\"\n",
    ")\n",
    "regional_display[\"Bachelor degree or higher\"] = regional_display[\n",
    "    \"Bachelor degree or higher\"\n",
    "].map(lambda x: f\"{x:.1f}%\")\n",
    "regional_display[\"Education rate MOE\"] = regional_display[\n",
    "    \"Education rate MOE\"\n",
    "].map(lambda x: f\"±{x:.1f}\")\n",
    "\n",
    "regional_display"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e6c3778",
   "metadata": {},
   "source": [
    "## 9. Plot regional median household income with margins of error\n",
    "\n",
    "The error bars use `DP03_0062M`, the ACS margin of error paired with the median household income estimate.\n",
    "\n",
    "The chart uses a zero baseline because bar length is the main visual comparison."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "47657b3d",
   "metadata": {},
   "outputs": [],
   "source": [
    "plot_df = regions.sort_values(\"region\").copy()\n",
    "\n",
    "fig, ax = plt.subplots(figsize=(9, 5.5))\n",
    "\n",
    "bars = ax.bar(\n",
    "    plot_df[\"region_standard\"],\n",
    "    plot_df[\"median_household_income\"],\n",
    "    yerr=plot_df[\"median_household_income_moe\"],\n",
    "    capsize=6,\n",
    ")\n",
    "\n",
    "ax.set_title(\"Median Household Income by Census Region\")\n",
    "ax.set_xlabel(\"Census region\")\n",
    "ax.set_ylabel(\"Median household income in 2024 dollars\")\n",
    "ax.set_ylim(bottom=0)\n",
    "ax.yaxis.set_major_formatter(\n",
    "    FuncFormatter(lambda value, position: f\"${value / 1000:.0f}K\")\n",
    ")\n",
    "ax.grid(axis=\"y\", alpha=0.25)\n",
    "\n",
    "for bar, value in zip(bars, plot_df[\"median_household_income\"]):\n",
    "    ax.text(\n",
    "        bar.get_x() + bar.get_width() / 2,\n",
    "        value + plot_df[\"median_household_income_moe\"].max() + 500,\n",
    "        f\"${value:,.0f}\",\n",
    "        ha=\"center\",\n",
    "        va=\"bottom\",\n",
    "        fontsize=9,\n",
    "    )\n",
    "\n",
    "fig.text(\n",
    "    0.5,\n",
    "    -0.02,\n",
    "    \"Source: U.S. Census Bureau, 2024 ACS 5 year Data Profiles. \"\n",
    "    \"Error bars show ACS margins of error.\",\n",
    "    ha=\"center\",\n",
    "    fontsize=9,\n",
    ")\n",
    "\n",
    "fig.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "461bd871",
   "metadata": {},
   "source": [
    "### Interpret the regional chart carefully\n",
    "\n",
    "The chart is descriptive. It shows the regional estimates returned by the ACS API.\n",
    "\n",
    "A higher estimate does not automatically mean the difference is statistically significant.\n",
    "\n",
    "If margins of error overlap, treat simple rankings with care. Formal comparisons should follow Census ACS significance testing guidance."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "438c2f46",
   "metadata": {},
   "source": [
    "## 10. Pull state data for the income and poverty scatter plot\n",
    "\n",
    "This request retrieves all available state level rows for median household income and poverty rate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16b1683d",
   "metadata": {},
   "outputs": [],
   "source": [
    "STATE_VARIABLES = [\n",
    "    \"NAME\",\n",
    "    \"DP03_0062E\",\n",
    "    \"DP03_0128PE\",\n",
    "]\n",
    "\n",
    "states_raw = census_get(\n",
    "    year=YEAR,\n",
    "    dataset=DATASET,\n",
    "    variables=STATE_VARIABLES,\n",
    "    for_clause=\"state:*\",\n",
    ")\n",
    "\n",
    "states_raw.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3f1a188",
   "metadata": {},
   "source": [
    "## 11. Map states to the four Census regions\n",
    "\n",
    "The state geography code is stored as a string.\n",
    "\n",
    "Puerto Rico is not one of the four Census regions used in this analysis, so rows without a region mapping are excluded from the regional scatter plot."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d584f460",
   "metadata": {},
   "outputs": [],
   "source": [
    "REGION_BY_STATE = {\n",
    "    \"09\": \"Northeast\", \"23\": \"Northeast\", \"25\": \"Northeast\",\n",
    "    \"33\": \"Northeast\", \"44\": \"Northeast\", \"50\": \"Northeast\",\n",
    "    \"34\": \"Northeast\", \"36\": \"Northeast\", \"42\": \"Northeast\",\n",
    "\n",
    "    \"17\": \"Midwest\", \"18\": \"Midwest\", \"26\": \"Midwest\",\n",
    "    \"39\": \"Midwest\", \"55\": \"Midwest\", \"19\": \"Midwest\",\n",
    "    \"20\": \"Midwest\", \"27\": \"Midwest\", \"29\": \"Midwest\",\n",
    "    \"31\": \"Midwest\", \"38\": \"Midwest\", \"46\": \"Midwest\",\n",
    "\n",
    "    \"10\": \"South\", \"11\": \"South\", \"12\": \"South\", \"13\": \"South\",\n",
    "    \"24\": \"South\", \"37\": \"South\", \"45\": \"South\", \"51\": \"South\",\n",
    "    \"54\": \"South\", \"01\": \"South\", \"21\": \"South\", \"28\": \"South\",\n",
    "    \"47\": \"South\", \"05\": \"South\", \"22\": \"South\", \"40\": \"South\",\n",
    "    \"48\": \"South\",\n",
    "\n",
    "    \"04\": \"West\", \"08\": \"West\", \"16\": \"West\",\n",
    "    \"30\": \"West\", \"32\": \"West\", \"35\": \"West\", \"49\": \"West\",\n",
    "    \"56\": \"West\", \"02\": \"West\", \"06\": \"West\", \"15\": \"West\",\n",
    "    \"41\": \"West\", \"53\": \"West\",\n",
    "}\n",
    "\n",
    "states = states_raw.rename(\n",
    "    columns={\n",
    "        \"NAME\": \"state_name\",\n",
    "        \"DP03_0062E\": \"median_household_income\",\n",
    "        \"DP03_0128PE\": \"poverty_rate\",\n",
    "    }\n",
    ").copy()\n",
    "\n",
    "states[\"state\"] = states[\"state\"].astype(\"string\").str.zfill(2)\n",
    "\n",
    "states[\"median_household_income\"] = pd.to_numeric(\n",
    "    states[\"median_household_income\"],\n",
    "    errors=\"coerce\",\n",
    ")\n",
    "\n",
    "states[\"poverty_rate\"] = pd.to_numeric(\n",
    "    states[\"poverty_rate\"],\n",
    "    errors=\"coerce\",\n",
    ")\n",
    "\n",
    "states[\"census_region\"] = states[\"state\"].map(REGION_BY_STATE)\n",
    "\n",
    "states = states.dropna(\n",
    "    subset=[\n",
    "        \"median_household_income\",\n",
    "        \"poverty_rate\",\n",
    "        \"census_region\",\n",
    "    ]\n",
    ").reset_index(drop=True)\n",
    "\n",
    "states.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a91b4b64",
   "metadata": {},
   "source": [
    "## 12. Validate state level data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b37730d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "if states[\"state\"].str.len().ne(2).any():\n",
    "    raise ValueError(\"Every state FIPS code should have two characters.\")\n",
    "\n",
    "if states[\"state\"].duplicated().any():\n",
    "    raise ValueError(\"Duplicate state rows found.\")\n",
    "\n",
    "if not states[\"poverty_rate\"].between(0, 100).all():\n",
    "    raise ValueError(\"State poverty rates must be between 0 and 100.\")\n",
    "\n",
    "if (states[\"median_household_income\"] <= 0).any():\n",
    "    raise ValueError(\"State median household income must be positive.\")\n",
    "\n",
    "print(\"State validation checks passed.\")\n",
    "print(\"States included in four Census regions:\", len(states))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2eadc585",
   "metadata": {},
   "source": [
    "## 13. Create the state income versus poverty scatter plot\n",
    "\n",
    "The scatter plot adds a second level of analysis beyond a simple regional ranking.\n",
    "\n",
    "Each point is a state. The grouping shows its Census region."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3eb012ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(figsize=(9, 6))\n",
    "\n",
    "for region, group in states.groupby(\"census_region\"):\n",
    "    ax.scatter(\n",
    "        group[\"median_household_income\"],\n",
    "        group[\"poverty_rate\"],\n",
    "        label=region,\n",
    "        alpha=0.8,\n",
    "        s=60,\n",
    "    )\n",
    "\n",
    "ax.set_title(\"State Income and Poverty by Census Region\")\n",
    "ax.set_xlabel(\"Median household income in 2024 dollars\")\n",
    "ax.set_ylabel(\"Poverty rate in percent\")\n",
    "ax.xaxis.set_major_formatter(\n",
    "    FuncFormatter(lambda value, position: f\"${value / 1000:.0f}K\")\n",
    ")\n",
    "ax.legend(title=\"Census region\", frameon=False)\n",
    "ax.grid(alpha=0.2)\n",
    "\n",
    "fig.text(\n",
    "    0.5,\n",
    "    -0.02,\n",
    "    \"Source: U.S. Census Bureau, 2024 ACS 5 year Data Profiles.\",\n",
    "    ha=\"center\",\n",
    "    fontsize=9,\n",
    ")\n",
    "\n",
    "fig.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c80dc553",
   "metadata": {},
   "source": [
    "### Optional summary by region\n",
    "\n",
    "This table describes the state level points in the scatter plot.\n",
    "\n",
    "It does **not** replace direct Census regional estimates.\n",
    "\n",
    "For example, the mean of state median incomes is not a regional median household income."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3e525bb0",
   "metadata": {},
   "outputs": [],
   "source": [
    "state_summary = (\n",
    "    states.groupby(\"census_region\")\n",
    "    .agg(\n",
    "        states=(\"state\", \"count\"),\n",
    "        mean_state_income=(\"median_household_income\", \"mean\"),\n",
    "        median_state_income=(\"median_household_income\", \"median\"),\n",
    "        mean_state_poverty=(\"poverty_rate\", \"mean\"),\n",
    "    )\n",
    "    .round(1)\n",
    ")\n",
    "\n",
    "state_summary"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "249e9dff",
   "metadata": {},
   "source": [
    "## 14. County example with FIPS and GEOID\n",
    "\n",
    "California has state FIPS code `06`.\n",
    "\n",
    "County codes contain three digits inside a state.\n",
    "\n",
    "Joining the two gives a five digit county GEOID."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3973c5cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "COUNTY_VARIABLES = [\n",
    "    \"NAME\",\n",
    "    \"DP05_0001E\",\n",
    "]\n",
    "\n",
    "california_counties = census_get(\n",
    "    year=YEAR,\n",
    "    dataset=DATASET,\n",
    "    variables=COUNTY_VARIABLES,\n",
    "    for_clause=\"county:*\",\n",
    "    in_clause=\"state:06\",\n",
    ")\n",
    "\n",
    "california_counties[\"state\"] = (\n",
    "    california_counties[\"state\"]\n",
    "    .astype(\"string\")\n",
    "    .str.zfill(2)\n",
    ")\n",
    "\n",
    "california_counties[\"county\"] = (\n",
    "    california_counties[\"county\"]\n",
    "    .astype(\"string\")\n",
    "    .str.zfill(3)\n",
    ")\n",
    "\n",
    "california_counties[\"GEOID\"] = (\n",
    "    california_counties[\"state\"]\n",
    "    + california_counties[\"county\"]\n",
    ")\n",
    "\n",
    "california_counties[\"DP05_0001E\"] = pd.to_numeric(\n",
    "    california_counties[\"DP05_0001E\"],\n",
    "    errors=\"coerce\",\n",
    ")\n",
    "\n",
    "california_counties[\n",
    "    [\"NAME\", \"state\", \"county\", \"GEOID\", \"DP05_0001E\"]\n",
    "].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30e0496b",
   "metadata": {},
   "source": [
    "### Validate the county identifiers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f377cc65",
   "metadata": {},
   "outputs": [],
   "source": [
    "if california_counties[\"state\"].str.len().ne(2).any():\n",
    "    raise ValueError(\"State FIPS codes must contain two characters.\")\n",
    "\n",
    "if california_counties[\"county\"].str.len().ne(3).any():\n",
    "    raise ValueError(\"County codes must contain three characters.\")\n",
    "\n",
    "if california_counties[\"GEOID\"].str.len().ne(5).any():\n",
    "    raise ValueError(\"County GEOIDs must contain five characters.\")\n",
    "\n",
    "if california_counties[\"GEOID\"].duplicated().any():\n",
    "    raise ValueError(\"Duplicate county GEOIDs found.\")\n",
    "\n",
    "print(\"County FIPS and GEOID validation checks passed.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66cca57c",
   "metadata": {},
   "source": [
    "## 15. Draw the Census API workflow used in the article"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9702f2da",
   "metadata": {},
   "outputs": [],
   "source": [
    "steps = [\n",
    "    \"Census API\",\n",
    "    \"requests\",\n",
    "    \"JSON\",\n",
    "    \"pandas\",\n",
    "    \"Validation\",\n",
    "    \"Analysis\",\n",
    "    \"Charts\",\n",
    "]\n",
    "\n",
    "fig, ax = plt.subplots(figsize=(11, 3))\n",
    "ax.axis(\"off\")\n",
    "\n",
    "x_positions = [0.07, 0.21, 0.35, 0.49, 0.63, 0.78, 0.93]\n",
    "\n",
    "for i, (x, label) in enumerate(zip(x_positions, steps)):\n",
    "    ax.text(\n",
    "        x,\n",
    "        0.5,\n",
    "        label,\n",
    "        ha=\"center\",\n",
    "        va=\"center\",\n",
    "        fontsize=11,\n",
    "        bbox=dict(\n",
    "            boxstyle=\"round,pad=0.55\",\n",
    "            fc=\"white\",\n",
    "            ec=\"0.35\",\n",
    "            lw=1.2,\n",
    "        ),\n",
    "    )\n",
    "\n",
    "    if i < len(steps) - 1:\n",
    "        ax.annotate(\n",
    "            \"\",\n",
    "            xy=(x_positions[i + 1] - 0.055, 0.5),\n",
    "            xytext=(x + 0.055, 0.5),\n",
    "            arrowprops=dict(\n",
    "                arrowstyle=\"->\",\n",
    "                lw=1.4,\n",
    "                color=\"0.35\",\n",
    "            ),\n",
    "        )\n",
    "\n",
    "ax.set_title(\"A Simple Census API Python Workflow\", fontsize=14, pad=14)\n",
    "\n",
    "fig.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d19a47be",
   "metadata": {},
   "source": [
    "## 16. Save clean data and analysis metadata\n",
    "\n",
    "Saving the source details beside the results makes the analysis easier to reproduce later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "febf4f0f",
   "metadata": {},
   "outputs": [],
   "source": [
    "output_dir = Path(\"census_analysis_output\")\n",
    "output_dir.mkdir(parents=True, exist_ok=True)\n",
    "\n",
    "regions.to_csv(\n",
    "    output_dir / \"acs_2024_5year_regions.csv\",\n",
    "    index=False,\n",
    ")\n",
    "\n",
    "states.to_csv(\n",
    "    output_dir / \"acs_2024_5year_states.csv\",\n",
    "    index=False,\n",
    ")\n",
    "\n",
    "california_counties.to_csv(\n",
    "    output_dir / \"acs_2024_5year_california_counties.csv\",\n",
    "    index=False,\n",
    ")\n",
    "\n",
    "verified_variables.to_csv(\n",
    "    output_dir / \"acs_2024_variable_metadata_check.csv\",\n",
    "    index=False,\n",
    ")\n",
    "\n",
    "analysis_metadata = {\n",
    "    \"vintage\": YEAR,\n",
    "    \"dataset\": DATASET,\n",
    "    \"region_geography\": \"region:*\",\n",
    "    \"state_geography\": \"state:*\",\n",
    "    \"county_geography\": \"county:* in state:06\",\n",
    "    \"retrieval_date\": date.today().isoformat(),\n",
    "    \"regional_variables\": VARIABLES,\n",
    "    \"state_variables\": STATE_VARIABLES,\n",
    "    \"county_variables\": COUNTY_VARIABLES,\n",
    "    \"source\": \"U.S. Census Bureau Census Data API\",\n",
    "}\n",
    "\n",
    "with open(\n",
    "    output_dir / \"analysis_metadata.json\",\n",
    "    \"w\",\n",
    "    encoding=\"utf-8\",\n",
    ") as file:\n",
    "    json.dump(analysis_metadata, file, indent=2)\n",
    "\n",
    "print(\"Files saved to:\", output_dir.resolve())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32b3f2b4",
   "metadata": {},
   "source": [
    "## 17. Reproducibility checklist\n",
    "\n",
    "Before publishing or sharing results, confirm the following:\n",
    "\n",
    "1. The year and dataset match the intended Census product.\n",
    "2. Every variable code was checked against official metadata for that exact vintage.\n",
    "3. Geography codes remain strings.\n",
    "4. State FIPS codes contain two characters.\n",
    "5. County codes contain three characters.\n",
    "6. County GEOIDs contain five characters.\n",
    "7. Estimate fields and margin of error fields are paired correctly.\n",
    "8. Regional medians come from direct regional geography when available.\n",
    "9. Percentages stay within a sensible range.\n",
    "10. Missing values are reviewed rather than automatically changed to zero.\n",
    "11. The retrieval date, dataset, variables, and geography are saved with the output.\n",
    "12. ACS estimates are interpreted with their margins of error.\n",
    "13. Comparisons across ACS 5 year periods account for overlapping collection periods and changing dollar years."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6bc4fe1",
   "metadata": {},
   "source": [
    "## 18. Notes for interpreting the analysis\n",
    "\n",
    "The ACS is a survey, so many values are estimates rather than exact counts.\n",
    "\n",
    "Margins of error help describe the uncertainty around those estimates.\n",
    "\n",
    "The regional bar chart should be read as a descriptive comparison. Overlapping margins of error are a warning that a simple ranking may overstate precision.\n",
    "\n",
    "The state scatter plot shows association, not cause. Higher income and lower poverty can appear together, but the chart does not prove that one variable causes the other.\n",
    "\n",
    "For maps, use statistical values from the Census Data API and geographic shapes from an appropriate Census TIGER or TIGERweb source. Join them with stable FIPS or GEOID fields."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf60ed32",
   "metadata": {},
   "source": [
    "## 19. Optional static preview values from the published article\n",
    "\n",
    "The article included a small static preview so readers could see the expected chart structure before running the live API request.\n",
    "\n",
    "These values were used only as article previews. The live analysis above should remain the source for notebook results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5ce34a67",
   "metadata": {},
   "outputs": [],
   "source": [
    "article_regional_preview = pd.DataFrame(\n",
    "    {\n",
    "        \"Region\": [\"Northeast\", \"Midwest\", \"South\", \"West\"],\n",
    "        \"Population\": [57414970, 69108736, 129288316, 79110477],\n",
    "        \"Median household income\": [88896, 75973, 74552, 91752],\n",
    "        \"Poverty rate\": [11.7, 12.0, 13.6, 11.6],\n",
    "        \"Bachelor degree or higher\": [40.5, 33.8, 33.8, 36.8],\n",
    "    }\n",
    ")\n",
    "\n",
    "article_state_preview = pd.DataFrame(\n",
    "    {\n",
    "        \"State\": [\n",
    "            \"New York\",\n",
    "            \"Pennsylvania\",\n",
    "            \"Illinois\",\n",
    "            \"Ohio\",\n",
    "            \"Texas\",\n",
    "            \"Florida\",\n",
    "            \"California\",\n",
    "            \"Washington\",\n",
    "        ],\n",
    "        \"Region\": [\n",
    "            \"Northeast\",\n",
    "            \"Northeast\",\n",
    "            \"Midwest\",\n",
    "            \"Midwest\",\n",
    "            \"South\",\n",
    "            \"South\",\n",
    "            \"West\",\n",
    "            \"West\",\n",
    "        ],\n",
    "        \"Median household income\": [\n",
    "            85974,\n",
    "            77971,\n",
    "            83390,\n",
    "            71389,\n",
    "            78476,\n",
    "            74568,\n",
    "            99122,\n",
    "            98141,\n",
    "        ],\n",
    "        \"Poverty rate\": [\n",
    "            14.0,\n",
    "            11.7,\n",
    "            11.8,\n",
    "            13.3,\n",
    "            13.8,\n",
    "            12.6,\n",
    "            12.0,\n",
    "            9.9,\n",
    "        ],\n",
    "    }\n",
    ")\n",
    "\n",
    "article_regional_preview"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "101a33ed",
   "metadata": {},
   "source": [
    "### Notebook output\n",
    "\n",
    "After running all live cells with a valid Census API key, the notebook produces:\n",
    "\n",
    "1. Verified ACS variable metadata\n",
    "2. A cleaned four region DataFrame\n",
    "3. A regional table with estimates and margins of error\n",
    "4. A median household income bar chart with ACS error bars\n",
    "5. A state DataFrame mapped to Census regions\n",
    "6. A state income versus poverty scatter plot\n",
    "7. A California county table with five digit GEOIDs\n",
    "8. Validation results\n",
    "9. CSV files and a JSON metadata file for reproducibility\n",
    "\n",
    "This is the complete analysis workflow supporting the article."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}