monthly.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "00e0ac44",
   "metadata": {
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "# Parse PATH monthly ridership PDF\n",
    "```bash\n",
    "papermill -p year $year [-p last_month $last_month] monthly.ipynb out/monthly-$year.ipynb\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b0e0e6e",
   "metadata": {
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "from utz import *\n",
    "import json\n",
    "from tabula import read_pdf\n",
    "from path_data import paths\n",
    "from path_data.paths import monthly_pdf, year_pqt, year_day_types_pqt, TEMPLATE_2023, TEMPLATE_2022"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4b44bab",
   "metadata": {
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "n = now()\n",
    "cur_year = n.year\n",
    "cur_month = n.month\n",
    "cur_year, cur_month"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "856cc866-4578-4e2b-86d8-0db63aa83bde",
   "metadata": {
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "Papermill parameters:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f354a72e",
   "metadata": {
    "slideshow": {
     "slide_type": ""
    },
    "tags": [
     "parameters"
    ]
   },
   "outputs": [],
   "source": [
    "year = None\n",
    "last_month = None\n",
    "template = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7bf54bda",
   "metadata": {
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "if year is None:\n",
    "    year = cur_year\n",
    "\n",
    "pdf = monthly_pdf(year)\n",
    "relpath(pdf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "69e65e93-7be8-4e84-8580-023527a52d05",
   "metadata": {
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "if last_month is None:\n",
    "    if year == cur_year:\n",
    "        from PyPDF2 import PdfReader\n",
    "\n",
    "        reader = PdfReader(pdf)\n",
    "        num_pages = len(reader.pages)\n",
    "        last_month = num_pages - 1\n",
    "        err(f\"Inferred {last_month=}\")\n",
    "    else:\n",
    "        last_month = 12\n",
    "\n",
    "if template is None:\n",
    "    template = paths.template(year)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6be0c906",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "with open(template, 'r') as f:\n",
    "    rects = json.load(f)\n",
    "rects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eb31a18e",
   "metadata": {
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "area = [ [ r[k] for k in [ 'y1', 'x1', 'y2', 'x2' ] ] for r in rects ]\n",
    "area"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dd2bc0e3",
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "tables = {\n",
    "    month: read_pdf(\n",
    "        pdf,\n",
    "        pages=month,\n",
    "        area=area,\n",
    "        pandas_options={'header': None},\n",
    "        stream=True,\n",
    "    ) \n",
    "    for month in range(1, last_month + 1)\n",
    "}\n",
    "tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bdc5a891",
   "metadata": {
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "cols1 = [ 'station', 'total', 'avg weekday', 'avg sat', 'avg sun', 'avg holiday' ]\n",
    "cols2 = [ 'station', 'avg daily', 'total weekday', 'total sat', 'total sun', 'total holiday' ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa2c55f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "for k, v in tables.items():\n",
    "    n = len(v)\n",
    "    msg = f'Pg {k}: {n} tables'\n",
    "    if n == 5:\n",
    "        print(msg)\n",
    "    else:\n",
    "        err(msg)\n",
    "len(tables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b6b79a4f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "avgs = pd.concat([\n",
    "    df.assign(date=date(year, month, 1)).astype({ 'date': 'datetime64[s]' })\n",
    "    for month, dfs in tables.items()\n",
    "    for df in dfs[:2]\n",
    "])\n",
    "avgs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "462a641d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "avgs.columns = cols1 + ['month']\n",
    "avgs = avgs.assign(**{\n",
    "    k: avgs[k].astype(str).str.replace(',', '').astype(int)\n",
    "    for k in cols1[1:]\n",
    "})\n",
    "avgs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0fd8fec9",
   "metadata": {},
   "outputs": [],
   "source": [
    "avgs.isna().sum(axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b15b15fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "avgs.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7195e6da",
   "metadata": {},
   "outputs": [],
   "source": [
    "sums = pd.concat([\n",
    "    df.assign(date=date(year, month, 1)).astype({ 'date': 'datetime64[s]' })\n",
    "    for month, dfs in tables.items()\n",
    "    for df in dfs[2:4]\n",
    "])\n",
    "sums"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "131a096e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "sums.columns = cols2 + ['month']\n",
    "sums = sums.assign(**{\n",
    "    k: sums[k].astype(str).str.replace(',', '').astype(int)\n",
    "    for k in cols2[1:]\n",
    "})\n",
    "sums"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a9eeba0c",
   "metadata": {},
   "outputs": [],
   "source": [
    "sums.isna().sum(axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d73a9a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "sums.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eab5968f",
   "metadata": {},
   "outputs": [],
   "source": [
    "def parse_nums(month, tbl):\n",
    "    assert len(tbl) == 3\n",
    "    assert all(tbl.iloc[0] == 'Average')\n",
    "    tbl.columns = tbl.iloc[1].str.lower()\n",
    "    tbl.columns.name = None\n",
    "    tbl = tbl.iloc[2:]\n",
    "    tbl.index = [month]\n",
    "    tbl.index.name = 'month'\n",
    "    return tbl.astype(int)\n",
    "    \n",
    "nums = pd.concat([\n",
    "    parse_nums(month=month, tbl=dfs[-1])\n",
    "    for month, dfs in tables.items()\n",
    "])\n",
    "nums.columns = [ f'{c}s' for c in nums.columns ]\n",
    "nums"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b4d389e4",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df = sxs(\n",
    "    avgs.set_index(['month', 'station']),\n",
    "    sums.set_index(['month', 'station']),\n",
    ")\n",
    "df = df[[cols2[1]] + cols1[2:] + [cols1[1]] + cols2[2:]]\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aba5369e",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "path = year_pqt(year)\n",
    "df.to_parquet(path)\n",
    "err(f\"Wrote {relpath(path)}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7acef22c",
   "metadata": {},
   "outputs": [],
   "source": [
    "nums_path = year_day_types_pqt(year)\n",
    "nums.to_parquet(nums_path)\n",
    "err(f\"Wrote {relpath(nums_path)}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea5b16cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "import plotly.express as px\n",
    "from IPython.display import Image"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "21330c90",
   "metadata": {
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "fig = px.bar(\n",
    "    avgs[~avgs.station.str.contains('TOTAL')],\n",
    "    x='month', y='avg weekday', color='station',\n",
    ")\n",
    "Image(fig.to_image(width=1200, height=600))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}