parse-njsp-xmls.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7cbe9e6d",
   "metadata": {},
   "source": [
    "# Parse/Clean NJSP Fatal Crash XMLs\n",
    "- Load XMLs\n",
    "- Clean / Assign some dtypes\n",
    "- Write to parquet and SQLite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "72e99ff2",
   "metadata": {},
   "outputs": [],
   "source": [
    "from utz import *\n",
    "from nj_crashes.paths import RUNDATE_PATH\n",
    "from nj_crashes.utils import parse_file"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b71e0351",
   "metadata": {},
   "source": [
    "### Load+Parse NJSP fatal crash data (2008-2023)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "73f94411",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur_month = to_dt(now().strftime('%Y-%m'))\n",
    "cur_year = cur_month.year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "462a435a",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "540 \"COUNTY\" entries, 519 containing \"MUNICIPALITY\"/crash info, 555 accidents, 414 injuries, 590 fatalities\n",
      "528 \"COUNTY\" entries, 507 containing \"MUNICIPALITY\"/crash info, 550 accidents, 352 injuries, 584 fatalities\n",
      "525 \"COUNTY\" entries, 504 containing \"MUNICIPALITY\"/crash info, 530 accidents, 366 injuries, 556 fatalities\n",
      "575 \"COUNTY\" entries, 554 containing \"MUNICIPALITY\"/crash info, 586 accidents, 517 injuries, 627 fatalities\n",
      "546 \"COUNTY\" entries, 525 containing \"MUNICIPALITY\"/crash info, 553 accidents, 382 injuries, 589 fatalities\n",
      "498 \"COUNTY\" entries, 477 containing \"MUNICIPALITY\"/crash info, 508 accidents, 393 injuries, 542 fatalities\n",
      "517 \"COUNTY\" entries, 496 containing \"MUNICIPALITY\"/crash info, 523 accidents, 345 injuries, 556 fatalities\n",
      "517 \"COUNTY\" entries, 496 containing \"MUNICIPALITY\"/crash info, 522 accidents, 374 injuries, 562 fatalities\n",
      "550 \"COUNTY\" entries, 529 containing \"MUNICIPALITY\"/crash info, 570 accidents, 398 injuries, 602 fatalities\n",
      "580 \"COUNTY\" entries, 559 containing \"MUNICIPALITY\"/crash info, 591 accidents, 368 injuries, 624 fatalities\n",
      "519 \"COUNTY\" entries, 498 containing \"MUNICIPALITY\"/crash info, 524 accidents, 358 injuries, 563 fatalities\n",
      "515 \"COUNTY\" entries, 494 containing \"MUNICIPALITY\"/crash info, 524 accidents, 340 injuries, 558 fatalities\n",
      "538 \"COUNTY\" entries, 517 containing \"MUNICIPALITY\"/crash info, 550 accidents, 263 injuries, 587 fatalities\n",
      "644 \"COUNTY\" entries, 623 containing \"MUNICIPALITY\"/crash info, 667 accidents, 397 injuries, 697 fatalities\n",
      "642 \"COUNTY\" entries, 621 containing \"MUNICIPALITY\"/crash info, 656 accidents, 233 injuries, 699 fatalities\n",
      "100 \"COUNTY\" entries, 79 containing \"MUNICIPALITY\"/crash info, 81 accidents, 8 injuries, 84 fatalities\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>accidents</th>\n",
       "      <th>injuries</th>\n",
       "      <th>fatalities</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <td>555</td>\n",
       "      <td>414</td>\n",
       "      <td>590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009</th>\n",
       "      <td>550</td>\n",
       "      <td>352</td>\n",
       "      <td>584</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010</th>\n",
       "      <td>530</td>\n",
       "      <td>366</td>\n",
       "      <td>556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011</th>\n",
       "      <td>586</td>\n",
       "      <td>517</td>\n",
       "      <td>627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <td>553</td>\n",
       "      <td>382</td>\n",
       "      <td>589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>508</td>\n",
       "      <td>393</td>\n",
       "      <td>542</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014</th>\n",
       "      <td>523</td>\n",
       "      <td>345</td>\n",
       "      <td>556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>522</td>\n",
       "      <td>374</td>\n",
       "      <td>562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>570</td>\n",
       "      <td>398</td>\n",
       "      <td>602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>591</td>\n",
       "      <td>368</td>\n",
       "      <td>624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>524</td>\n",
       "      <td>358</td>\n",
       "      <td>563</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019</th>\n",
       "      <td>524</td>\n",
       "      <td>340</td>\n",
       "      <td>558</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020</th>\n",
       "      <td>550</td>\n",
       "      <td>263</td>\n",
       "      <td>587</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021</th>\n",
       "      <td>667</td>\n",
       "      <td>397</td>\n",
       "      <td>697</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022</th>\n",
       "      <td>656</td>\n",
       "      <td>233</td>\n",
       "      <td>699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023</th>\n",
       "      <td>81</td>\n",
       "      <td>8</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      accidents  injuries  fatalities\n",
       "year                                 \n",
       "2008        555       414         590\n",
       "2009        550       352         584\n",
       "2010        530       366         556\n",
       "2011        586       517         627\n",
       "2012        553       382         589\n",
       "2013        508       393         542\n",
       "2014        523       345         556\n",
       "2015        522       374         562\n",
       "2016        570       398         602\n",
       "2017        591       368         624\n",
       "2018        524       358         563\n",
       "2019        524       340         558\n",
       "2020        550       263         587\n",
       "2021        667       397         697\n",
       "2022        656       233         699\n",
       "2023         81         8          84"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "parsed_files = {\n",
    "    year: parse_file(f'data/FAUQStats{year}.xml')\n",
    "    for year in range(2008, cur_year + 1)\n",
    "}\n",
    "crashes, totals = [\n",
    "    pd.concat(dfs)\n",
    "    for dfs in\n",
    "    zip(*[ \n",
    "        [ dfs['crashes'], dfs['totals'] ]\n",
    "        for dfs in parsed_files.values() \n",
    "    ])\n",
    "]\n",
    "totals = totals.set_index('year')\n",
    "totals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "429b36e6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(Timestamp('2023-01-01 00:00:00-0500', tz='pytz.FixedOffset(-300)'),\n",
       " Timestamp('2023-02-01 00:00:00-0500', tz='pytz.FixedOffset(-300)'),\n",
       " Timestamp('2023-02-24 10:04:16-0500', tz='pytz.FixedOffset(-300)'),\n",
       " Timestamp('2024-01-01 00:00:00-0500', tz='pytz.FixedOffset(-300)'))"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rundate = to_dt(parsed_files[cur_year]['rundate'])\n",
    "rundate_str = rundate.strftime('%Y-%m-%d')\n",
    "with open(RUNDATE_PATH, 'w') as f:\n",
    "    json.dump({ 'rundate': str(rundate), }, f)\n",
    "\n",
    "cur_year_dt = to_dt(str(cur_year)).tz_localize(rundate.tz)\n",
    "nxt_year_dt = to_dt(str(cur_year + 1)).tz_localize(rundate.tz)\n",
    "cur_month_dt = cur_month.tz_localize(rundate.tz)\n",
    "\n",
    "cur_year_dt, cur_month_dt, rundate, nxt_year_dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "6eda6e00",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CCODE</th>\n",
       "      <th>CNAME</th>\n",
       "      <th>MCODE</th>\n",
       "      <th>MNAME</th>\n",
       "      <th>HIGHWAY</th>\n",
       "      <th>LOCATION</th>\n",
       "      <th>FATALITIES</th>\n",
       "      <th>INJURIES</th>\n",
       "      <th>STREET</th>\n",
       "      <th>FATAL_D</th>\n",
       "      <th>FATAL_P</th>\n",
       "      <th>FATAL_T</th>\n",
       "      <th>FATAL_B</th>\n",
       "      <th>dt</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ACCID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1703</th>\n",
       "      <td>01</td>\n",
       "      <td>Atlantic</td>\n",
       "      <td>0102</td>\n",
       "      <td>Atlantic City</td>\n",
       "      <td>446</td>\n",
       "      <td>State/Interstate Authority 446 S MP 1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-01-01 00:35:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1681</th>\n",
       "      <td>09</td>\n",
       "      <td>Hudson</td>\n",
       "      <td>0910</td>\n",
       "      <td>Union City</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Bergenline Ave S MP 0 at 6th St</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Bergenline Ave</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-01-01 04:11:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1659</th>\n",
       "      <td>04</td>\n",
       "      <td>Camden</td>\n",
       "      <td>0415</td>\n",
       "      <td>Gloucester Twsp</td>\n",
       "      <td>42</td>\n",
       "      <td>State Highway 42 N MP 8.2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-01-01 06:46:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1661</th>\n",
       "      <td>20</td>\n",
       "      <td>Union</td>\n",
       "      <td>2004</td>\n",
       "      <td>Elizabeth City</td>\n",
       "      <td>624</td>\n",
       "      <td>County 624 W MP 2.2 at Ikea Dr</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-01-01 12:29:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1811</th>\n",
       "      <td>07</td>\n",
       "      <td>Essex</td>\n",
       "      <td>0716</td>\n",
       "      <td>Nutley Town</td>\n",
       "      <td>648</td>\n",
       "      <td>County 648 E MP .87 at Franklin Ave</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-01-01 18:53:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12344</th>\n",
       "      <td>16</td>\n",
       "      <td>Passaic</td>\n",
       "      <td>1604</td>\n",
       "      <td>Hawthorne Boro</td>\n",
       "      <td>659</td>\n",
       "      <td>County 659</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2023-02-17 20:37:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12337</th>\n",
       "      <td>11</td>\n",
       "      <td>Mercer</td>\n",
       "      <td>1103</td>\n",
       "      <td>Hamilton Twsp</td>\n",
       "      <td>295</td>\n",
       "      <td>Interstate 295 MP 65</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2023-02-18 12:07:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12338</th>\n",
       "      <td>06</td>\n",
       "      <td>Cumberland</td>\n",
       "      <td>0613</td>\n",
       "      <td>Upper Deerfield Tws</td>\n",
       "      <td>522</td>\n",
       "      <td>County 522 MP 2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2023-02-19 02:11:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12347</th>\n",
       "      <td>03</td>\n",
       "      <td>Burlington</td>\n",
       "      <td>0317</td>\n",
       "      <td>Willingboro Twsp</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Veterans Parkway</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Veterans Parkway</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2023-02-19 14:54:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12345</th>\n",
       "      <td>08</td>\n",
       "      <td>Gloucester</td>\n",
       "      <td>0822</td>\n",
       "      <td>Woodbury City</td>\n",
       "      <td>644</td>\n",
       "      <td>County 644</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2023-02-21 18:49:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8490 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      CCODE       CNAME MCODE                MNAME HIGHWAY  \\\n",
       "ACCID                                                        \n",
       "1703     01    Atlantic  0102        Atlantic City     446   \n",
       "1681     09      Hudson  0910           Union City     NaN   \n",
       "1659     04      Camden  0415      Gloucester Twsp      42   \n",
       "1661     20       Union  2004       Elizabeth City     624   \n",
       "1811     07       Essex  0716          Nutley Town     648   \n",
       "...     ...         ...   ...                  ...     ...   \n",
       "12344    16     Passaic  1604       Hawthorne Boro     659   \n",
       "12337    11      Mercer  1103        Hamilton Twsp     295   \n",
       "12338    06  Cumberland  0613  Upper Deerfield Tws     522   \n",
       "12347    03  Burlington  0317     Willingboro Twsp     NaN   \n",
       "12345    08  Gloucester  0822        Woodbury City     644   \n",
       "\n",
       "                                    LOCATION  FATALITIES  INJURIES  \\\n",
       "ACCID                                                                \n",
       "1703   State/Interstate Authority 446 S MP 1         1.0       1.0   \n",
       "1681         Bergenline Ave S MP 0 at 6th St         1.0       NaN   \n",
       "1659               State Highway 42 N MP 8.2         1.0       1.0   \n",
       "1661          County 624 W MP 2.2 at Ikea Dr         1.0       1.0   \n",
       "1811     County 648 E MP .87 at Franklin Ave         1.0       NaN   \n",
       "...                                      ...         ...       ...   \n",
       "12344                             County 659         1.0       0.0   \n",
       "12337                   Interstate 295 MP 65         1.0       0.0   \n",
       "12338                        County 522 MP 2         1.0       NaN   \n",
       "12347                       Veterans Parkway         1.0       NaN   \n",
       "12345                             County 644         1.0       NaN   \n",
       "\n",
       "                 STREET  FATAL_D  FATAL_P  FATAL_T  FATAL_B  \\\n",
       "ACCID                                                         \n",
       "1703                NaN      NaN      NaN      NaN      NaN   \n",
       "1681     Bergenline Ave      NaN      NaN      NaN      NaN   \n",
       "1659                NaN      NaN      NaN      NaN      NaN   \n",
       "1661                NaN      NaN      NaN      NaN      NaN   \n",
       "1811                NaN      NaN      NaN      NaN      NaN   \n",
       "...                 ...      ...      ...      ...      ...   \n",
       "12344               NaN      0.0      0.0      1.0      0.0   \n",
       "12337               NaN      1.0      0.0      0.0      0.0   \n",
       "12338               NaN      0.0      1.0      0.0      0.0   \n",
       "12347  Veterans Parkway      1.0      0.0      0.0      0.0   \n",
       "12345               NaN      0.0      0.0      1.0      0.0   \n",
       "\n",
       "                       dt  \n",
       "ACCID                      \n",
       "1703  2008-01-01 00:35:00  \n",
       "1681  2008-01-01 04:11:00  \n",
       "1659  2008-01-01 06:46:00  \n",
       "1661  2008-01-01 12:29:00  \n",
       "1811  2008-01-01 18:53:00  \n",
       "...                   ...  \n",
       "12344 2023-02-17 20:37:00  \n",
       "12337 2023-02-18 12:07:00  \n",
       "12338 2023-02-19 02:11:00  \n",
       "12347 2023-02-19 14:54:00  \n",
       "12345 2023-02-21 18:49:00  \n",
       "\n",
       "[8490 rows x 14 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crashes['dt'] = crashes[['DATE','TIME']].apply(lambda r: to_dt(f'{r[\"DATE\"]} {r[\"TIME\"]}'), axis=1)\n",
    "crashes = (\n",
    "    crashes\n",
    "    .astype({\n",
    "        'FATALITIES': float,\n",
    "        'FATAL_D': float,\n",
    "        'FATAL_P': float,\n",
    "        'FATAL_T': float,\n",
    "        'FATAL_B': float,\n",
    "        'INJURIES': float,\n",
    "    })\n",
    "    .drop(columns=['DATE', 'TIME'])\n",
    "    .set_index('ACCID')\n",
    ")\n",
    "crashes = crashes.sort_values('dt')\n",
    "crashes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1f4efa1d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CCODE\n",
       "01      Atlantic\n",
       "02        Bergen\n",
       "03    Burlington\n",
       "04        Camden\n",
       "05      Cape May\n",
       "06    Cumberland\n",
       "07         Essex\n",
       "08    Gloucester\n",
       "09        Hudson\n",
       "10     Hunterdon\n",
       "11        Mercer\n",
       "12     Middlesex\n",
       "13      Monmouth\n",
       "14        Morris\n",
       "15         Ocean\n",
       "16       Passaic\n",
       "17         Salem\n",
       "18      Somerset\n",
       "19        Sussex\n",
       "20         Union\n",
       "21        Warren\n",
       "Name: CNAME, dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "counties = crashes[['CCODE', 'CNAME']].value_counts().rename('accidents').reset_index().set_index('CCODE').sort_index().CNAME\n",
    "counties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a4ea1872",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>MNAME</th>\n",
       "      <th>CCODE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MCODE</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0101</th>\n",
       "      <td>Absecon City</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0102</th>\n",
       "      <td>Atlantic City</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0103</th>\n",
       "      <td>Brigantine City</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0104</th>\n",
       "      <td>Buena Boro</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0105</th>\n",
       "      <td>Buena Vista Twsp</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2119</th>\n",
       "      <td>Phillipsburg To</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2120</th>\n",
       "      <td>Pohatcong Twsp</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2121</th>\n",
       "      <td>Washington Boro</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2122</th>\n",
       "      <td>Washington Twsp</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2123</th>\n",
       "      <td>White Twsp</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>517 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  MNAME CCODE\n",
       "MCODE                        \n",
       "0101       Absecon City    01\n",
       "0102      Atlantic City    01\n",
       "0103    Brigantine City    01\n",
       "0104         Buena Boro    01\n",
       "0105   Buena Vista Twsp    01\n",
       "...                 ...   ...\n",
       "2119    Phillipsburg To    21\n",
       "2120     Pohatcong Twsp    21\n",
       "2121    Washington Boro    21\n",
       "2122    Washington Twsp    21\n",
       "2123         White Twsp    21\n",
       "\n",
       "[517 rows x 2 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "munis = (\n",
    "    crashes\n",
    "    .groupby('MCODE')\n",
    "    .apply(\n",
    "        lambda df: (\n",
    "            df\n",
    "            [['MNAME', 'CCODE']]\n",
    "            .drop_duplicates()\n",
    "            .set_index('MNAME', drop=True)\n",
    "        )\n",
    "    )\n",
    "    .reset_index(1)\n",
    "    .sort_index()\n",
    ")\n",
    "munis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "756bdb55",
   "metadata": {},
   "outputs": [],
   "source": [
    "counties.to_frame().to_parquet('data/counties.pqt')\n",
    "munis.to_parquet('data/munis.pqt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "5e112ee5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MNAME\n",
       "Aberdeen Twsp        [Monmouth]\n",
       "Absecon City         [Atlantic]\n",
       "Alexandria Twsp     [Hunterdon]\n",
       "Allamuchy Twsp         [Warren]\n",
       "Allendale Boro         [Bergen]\n",
       "                       ...     \n",
       "Woodland Twsp      [Burlington]\n",
       "Woodlynne Boro         [Camden]\n",
       "Woodstown Boro          [Salem]\n",
       "Woolwich Twsp      [Gloucester]\n",
       "Wyckoff Twsp           [Bergen]\n",
       "Name: counties, Length: 500, dtype: object"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "muni_counties = crashes.groupby('MNAME').apply(lambda df: df['CNAME'].unique()).rename('counties')\n",
    "muni_counties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a5bf2658",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MNAME\n",
       "Monroe Twsp         2\n",
       "Hamilton Twsp       2\n",
       "Ocean Twsp          2\n",
       "Union Twsp          2\n",
       "Mansfield Twsp      2\n",
       "Springfield Twsp    2\n",
       "Hopewell Twsp       2\n",
       "Lawrence Twsp       2\n",
       "Greenwich Twsp      3\n",
       "Franklin Twsp       4\n",
       "Washington Twsp     5\n",
       "Name: muni_county_counts, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "muni_county_counts = muni_counties.apply(len).rename('muni_county_counts').sort_values()\n",
    "multi_county_counts = muni_county_counts[muni_county_counts > 1]\n",
    "multi_county_counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "be10a231",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MNAME             CNAME     \n",
       "Hamilton Twsp     Atlantic      92\n",
       "                  Mercer        84\n",
       "Washington Twsp   Bergen        11\n",
       "                  Burlington     7\n",
       "                  Gloucester    43\n",
       "                  Morris        10\n",
       "                  Warren        15\n",
       "Mansfield Twsp    Burlington    21\n",
       "                  Warren         9\n",
       "Springfield Twsp  Burlington    15\n",
       "                  Union         13\n",
       "Greenwich Twsp    Cumberland     1\n",
       "                  Gloucester     2\n",
       "                  Warren         7\n",
       "Hopewell Twsp     Cumberland    12\n",
       "                  Mercer        16\n",
       "Lawrence Twsp     Cumberland     9\n",
       "                  Mercer        35\n",
       "Franklin Twsp     Gloucester    70\n",
       "                  Hunterdon      1\n",
       "                  Somerset      54\n",
       "                  Warren         9\n",
       "Monroe Twsp       Gloucester    92\n",
       "                  Middlesex     31\n",
       "Union Twsp        Hunterdon      9\n",
       "                  Union         53\n",
       "Ocean Twsp        Monmouth      12\n",
       "                  Ocean         14\n",
       "Name: accidents, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    crashes\n",
    "    .groupby(['CNAME', 'MNAME'])\n",
    "    .size()\n",
    "    .rename('accidents')\n",
    "    .reset_index()\n",
    "    .merge(\n",
    "        multi_county_counts,\n",
    "        left_on='MNAME',\n",
    "        right_index=True,\n",
    "    )\n",
    "    .set_index(['MNAME', 'CNAME'])\n",
    "    .accidents\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ce242d6",
   "metadata": {},
   "source": [
    "### Save to file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "151eb61b",
   "metadata": {},
   "outputs": [],
   "source": [
    "from nj_crashes.paths import DB_URI\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "engine = create_engine(DB_URI)\n",
    "\n",
    "tables = {\n",
    "    'totals': totals,\n",
    "    'crashes': crashes,\n",
    "}\n",
    "\n",
    "for name, table in tables.items():\n",
    "    table.to_sql(name, con=engine, if_exists='replace',)\n",
    "    table.to_parquet(f'data/{name}.pqt')"
   ]
  }
 ],
 "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}