{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Real Wage Growth Analysis in Python\n",
        "\n",
        "This notebook accompanies the dataclue article **Real Wage Growth Calculator in Python** and reproduces the analysis with official U.S. Bureau of Labor Statistics data.\n",
        "\n",
        "It downloads nominal hourly earnings, CPI-U, and the official BLS real earnings series; aligns them by month; calculates real earnings and growth; validates the result; creates charts; and exports the aligned dataset.\n",
        "\n",
        "**Reproducibility note:** BLS data can be revised. Record the retrieval date whenever you publish or cite results.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## BLS series used\n",
        "\n",
        "- `CES0500000003`: Average hourly earnings of all employees, total private, seasonally adjusted.\n",
        "- `CUSR0000SA0`: CPI-U, U.S. city average, all items, seasonally adjusted.\n",
        "- `CES0500000013`: Real average hourly earnings of all employees, constant 1982\u201384 dollars, seasonally adjusted.\n",
        "\n",
        "The official real earnings series is used as a validation benchmark.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "import os\n",
        "from datetime import date\n",
        "import requests\n",
        "import pandas as pd\n",
        "import matplotlib.pyplot as plt\n",
        "\n",
        "BLS_URL = \"https://api.bls.gov/publicAPI/v2/timeseries/data/\"\n",
        "SERIES = {\n",
        "    \"nominal_ahe\": \"CES0500000003\",\n",
        "    \"real_ahe_bls\": \"CES0500000013\",\n",
        "    \"cpi_u\": \"CUSR0000SA0\",\n",
        "}\n",
        "print(\"Retrieval date:\", date.today().isoformat())\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Exact real wage growth formula\n",
        "\n",
        "If nominal wage growth and inflation are already expressed as rates, use:\n",
        "\n",
        "`((1 + nominal_growth) / (1 + inflation) - 1) * 100`\n",
        "\n",
        "This is more exact than simply subtracting inflation from nominal wage growth.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "def exact_real_wage_growth(nominal_growth_pct, inflation_pct):\n",
        "    nominal = nominal_growth_pct / 100\n",
        "    inflation = inflation_pct / 100\n",
        "    return ((1 + nominal) / (1 + inflation) - 1) * 100\n",
        "\n",
        "print(f\"5% wage growth and 3% inflation -> {exact_real_wage_growth(5, 3):.2f}% real wage growth\")\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Download BLS data\n",
        "\n",
        "If you have a BLS API key, store it in the environment variable `BLS_API_KEY`. The notebook can also run without a key within BLS public API limits.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "def fetch_bls_block(start_year, end_year):\n",
        "    payload = {\n",
        "        \"seriesid\": list(SERIES.values()),\n",
        "        \"startyear\": str(start_year),\n",
        "        \"endyear\": str(end_year),\n",
        "    }\n",
        "    api_key = os.getenv(\"BLS_API_KEY\")\n",
        "    if api_key:\n",
        "        payload[\"registrationkey\"] = api_key\n",
        "\n",
        "    response = requests.post(BLS_URL, json=payload, timeout=30)\n",
        "    response.raise_for_status()\n",
        "    data = response.json()\n",
        "\n",
        "    if data.get(\"status\") != \"REQUEST_SUCCEEDED\":\n",
        "        raise RuntimeError(data.get(\"message\"))\n",
        "    return data\n",
        "\n",
        "START_YEAR = 2020\n",
        "END_YEAR = date.today().year\n",
        "payload = fetch_bls_block(START_YEAR, END_YEAR)\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Parse monthly observations\n",
        "\n",
        "BLS can include an `M13` annual-average record. It is excluded because this analysis uses monthly observations.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "def parse_bls_payload(payload):\n",
        "    reverse = {series_id: name for name, series_id in SERIES.items()}\n",
        "    rows = []\n",
        "\n",
        "    for series in payload[\"Results\"][\"series\"]:\n",
        "        name = reverse[series[\"seriesID\"]]\n",
        "        for obs in series[\"data\"]:\n",
        "            period = obs[\"period\"]\n",
        "            if not period.startswith(\"M\") or period == \"M13\":\n",
        "                continue\n",
        "            rows.append({\n",
        "                \"series\": name,\n",
        "                \"date\": pd.Timestamp(\n",
        "                    year=int(obs[\"year\"]),\n",
        "                    month=int(period[1:]),\n",
        "                    day=1,\n",
        "                ),\n",
        "                \"value\": pd.to_numeric(obs[\"value\"], errors=\"coerce\"),\n",
        "            })\n",
        "    return pd.DataFrame(rows)\n",
        "\n",
        "tidy = parse_bls_payload(payload)\n",
        "tidy.head()\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Align all series to common months\n",
        "\n",
        "This prevents a newer wage observation from being combined with an older CPI observation.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "wide = (\n",
        "    tidy\n",
        "    .pivot(index=\"date\", columns=\"series\", values=\"value\")\n",
        "    .sort_index()\n",
        "    .dropna(subset=[\"nominal_ahe\", \"real_ahe_bls\", \"cpi_u\"])\n",
        ")\n",
        "\n",
        "assert wide.index.is_unique\n",
        "assert (wide[\"nominal_ahe\"] > 0).all()\n",
        "assert (wide[\"cpi_u\"] > 0).all()\n",
        "\n",
        "print(\"First common month:\", wide.index.min().date())\n",
        "print(\"Latest common month:\", wide.index.max().date())\n",
        "wide.tail()\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Calculate real average hourly earnings and growth\n",
        "\n",
        "The reconstructed level is `nominal_ahe / cpi_u * 100`. Growth is then calculated from the real earnings level.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "wide[\"real_ahe_calc\"] = wide[\"nominal_ahe\"] / wide[\"cpi_u\"] * 100\n",
        "wide[\"real_growth_1m\"] = wide[\"real_ahe_calc\"].pct_change(1, fill_method=None) * 100\n",
        "wide[\"real_growth_12m\"] = wide[\"real_ahe_calc\"].pct_change(12, fill_method=None) * 100\n",
        "\n",
        "wide[[\"nominal_ahe\", \"cpi_u\", \"real_ahe_calc\", \"real_growth_1m\", \"real_growth_12m\"]].tail(15)\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Validate against official BLS real earnings\n",
        "\n",
        "Small gaps can occur because displayed component series are rounded and historical observations can be revised.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "wide[\"validation_gap\"] = wide[\"real_ahe_calc\"] - wide[\"real_ahe_bls\"]\n",
        "wide[\"validation_gap_abs\"] = wide[\"validation_gap\"].abs()\n",
        "\n",
        "pd.Series({\n",
        "    \"mean_absolute_gap\": wide[\"validation_gap_abs\"].mean(),\n",
        "    \"maximum_absolute_gap\": wide[\"validation_gap_abs\"].max(),\n",
        "    \"latest_gap\": wide[\"validation_gap\"].iloc[-1],\n",
        "})\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "wide[[\"nominal_ahe\", \"cpi_u\", \"real_ahe_calc\", \"real_ahe_bls\", \"validation_gap\"]].tail(12)\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Chart: Calculated vs. BLS real average hourly earnings\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "fig, ax = plt.subplots(figsize=(10, 5))\n",
        "ax.plot(wide.index, wide[\"real_ahe_calc\"], label=\"Calculated real AHE\")\n",
        "ax.plot(wide.index, wide[\"real_ahe_bls\"], label=\"BLS real AHE\")\n",
        "ax.set_title(\"Calculated vs. BLS Real Average Hourly Earnings\")\n",
        "ax.set_xlabel(\"Month\")\n",
        "ax.set_ylabel(\"Constant 1982\u201384 dollars\")\n",
        "ax.legend()\n",
        "ax.grid(True, alpha=0.25)\n",
        "plt.show()\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Chart: Year-over-year real wage growth\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "fig, ax = plt.subplots(figsize=(10, 5))\n",
        "ax.plot(wide.index, wide[\"real_growth_12m\"])\n",
        "ax.axhline(0, linewidth=1)\n",
        "ax.set_title(\"Year-over-Year Real Wage Growth\")\n",
        "ax.set_xlabel(\"Month\")\n",
        "ax.set_ylabel(\"Percent\")\n",
        "ax.grid(True, alpha=0.25)\n",
        "plt.show()\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Chart: Nominal wages and CPI-U rebased to 100\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "rebased = wide[[\"nominal_ahe\", \"cpi_u\"]].copy()\n",
        "rebased = rebased / rebased.iloc[0] * 100\n",
        "\n",
        "fig, ax = plt.subplots(figsize=(10, 5))\n",
        "ax.plot(rebased.index, rebased[\"nominal_ahe\"], label=\"Nominal hourly earnings\")\n",
        "ax.plot(rebased.index, rebased[\"cpi_u\"], label=\"CPI-U\")\n",
        "ax.set_title(\"Nominal Wages and CPI-U, Rebased to 100\")\n",
        "ax.set_xlabel(\"Month\")\n",
        "ax.set_ylabel(\"Index\")\n",
        "ax.legend()\n",
        "ax.grid(True, alpha=0.25)\n",
        "plt.show()\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Latest common-month summary\n",
        "\n",
        "This avoids hard-coding a supposedly current month into the analysis.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "latest = wide.iloc[-1]\n",
        "summary = pd.Series({\n",
        "    \"reference_month\": wide.index[-1].strftime(\"%Y-%m\"),\n",
        "    \"nominal_ahe\": latest[\"nominal_ahe\"],\n",
        "    \"cpi_u\": latest[\"cpi_u\"],\n",
        "    \"real_ahe_calculated\": latest[\"real_ahe_calc\"],\n",
        "    \"real_ahe_bls\": latest[\"real_ahe_bls\"],\n",
        "    \"real_wage_growth_1m_pct\": latest[\"real_growth_1m\"],\n",
        "    \"real_wage_growth_12m_pct\": latest[\"real_growth_12m\"],\n",
        "    \"validation_gap\": latest[\"validation_gap\"],\n",
        "})\n",
        "summary\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Export the aligned dataset\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "OUTPUT_CSV = \"real_wage_growth_bls_analysis.csv\"\n",
        "wide.to_csv(OUTPUT_CSV)\n",
        "print(\"Saved:\", OUTPUT_CSV)\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Interpretation and limitations\n",
        "\n",
        "- Average hourly earnings are averages, not median individual wages.\n",
        "- Workforce composition changes can affect the average.\n",
        "- CPI-U is a national consumer price index, not each household's personal inflation rate.\n",
        "- Benefits and total compensation are not captured by hourly earnings alone.\n",
        "- BLS observations can be revised.\n",
        "- Always align series to the same reference month before calculating.\n",
        "\n",
        "### Primary sources\n",
        "\n",
        "- U.S. Bureau of Labor Statistics, Real Earnings\n",
        "- U.S. Bureau of Labor Statistics, Current Employment Statistics\n",
        "- U.S. Bureau of Labor Statistics, Consumer Price Index\n",
        "- U.S. Bureau of Labor Statistics Public Data API\n",
        "\n",
        "Article: https://dataclue.tech/blogs/real-wage-growth-calculator-python\n"
      ]
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "name": "python",
      "version": "3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}