njdot/harmonize-muni-codes.ipynb

{
    "cells": [
        {
            "cell_type": "code",
            "execution_count": 1,
            "metadata": {
                "tags": []
            },
            "outputs": [],
            "source": [
                "from utz import *\n",
                "import geopandas as gpd\n",
                "from nj_crashes.paths import COUNTY_CITY_CODES_PQT\n",
                "from njdot.crashes import name_renames\n",
                "import njdot\n",
                "import njsp\n",
                "from njdot.paths import CC2MC2MN\n",
                "from njdot import YEARS, Data\n",
                "from njdot.load import pk_astype, pk_renames"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {
                "tags": []
            },
            "source": [
                "## Load DOT county/muni codes"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 2,
            "metadata": {
                "tags": []
            },
            "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>year</th>\n",
                            "      <th>cc</th>\n",
                            "      <th>cn</th>\n",
                            "      <th>mc</th>\n",
                            "      <th>mn</th>\n",
                            "      <th>num</th>\n",
                            "    </tr>\n",
                            "  </thead>\n",
                            "  <tbody>\n",
                            "    <tr>\n",
                            "      <th>0</th>\n",
                            "      <td>2001</td>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>1</td>\n",
                            "      <td>Absecon City</td>\n",
                            "      <td>312</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>1</th>\n",
                            "      <td>2002</td>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>1</td>\n",
                            "      <td>Absecon City</td>\n",
                            "      <td>367</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>2</th>\n",
                            "      <td>2003</td>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>1</td>\n",
                            "      <td>Absecon City</td>\n",
                            "      <td>371</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>3</th>\n",
                            "      <td>2004</td>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>1</td>\n",
                            "      <td>Absecon City</td>\n",
                            "      <td>322</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>4</th>\n",
                            "      <td>2005</td>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>1</td>\n",
                            "      <td>Absecon City</td>\n",
                            "      <td>352</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",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>12299</th>\n",
                            "      <td>2018</td>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>23</td>\n",
                            "      <td>White Twp</td>\n",
                            "      <td>156</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>12300</th>\n",
                            "      <td>2019</td>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>23</td>\n",
                            "      <td>White Twp</td>\n",
                            "      <td>141</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>12301</th>\n",
                            "      <td>2020</td>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>23</td>\n",
                            "      <td>White Twp</td>\n",
                            "      <td>121</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>12302</th>\n",
                            "      <td>2021</td>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>23</td>\n",
                            "      <td>White Twp</td>\n",
                            "      <td>105</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>12303</th>\n",
                            "      <td>2022</td>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>23</td>\n",
                            "      <td>White Twp</td>\n",
                            "      <td>128</td>\n",
                            "    </tr>\n",
                            "  </tbody>\n",
                            "</table>\n",
                            "<p>12304 rows \u00d7 6 columns</p>\n",
                            "</div>"
                        ],
                        "text/plain": [
                            "       year  cc        cn  mc            mn  num\n",
                            "0      2001   1  Atlantic   1  Absecon City  312\n",
                            "1      2002   1  Atlantic   1  Absecon City  367\n",
                            "2      2003   1  Atlantic   1  Absecon City  371\n",
                            "3      2004   1  Atlantic   1  Absecon City  322\n",
                            "4      2005   1  Atlantic   1  Absecon City  352\n",
                            "...     ...  ..       ...  ..           ...  ...\n",
                            "12299  2018  21    Warren  23     White Twp  156\n",
                            "12300  2019  21    Warren  23     White Twp  141\n",
                            "12301  2020  21    Warren  23     White Twp  121\n",
                            "12302  2021  21    Warren  23     White Twp  105\n",
                            "12303  2022  21    Warren  23     White Twp  128\n",
                            "\n",
                            "[12304 rows x 6 columns]"
                        ]
                    },
                    "execution_count": 2,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "data = Data(types=['Accidents'], columns=['County Code', 'County Name', 'Municipality Code', 'Municipality Name'])\n",
                "c = data.df(index=False)\n",
                "c = c.value_counts(c.columns.tolist()).sort_index().rename('num').reset_index()\n",
                "c = c.rename(columns={\n",
                "    k: v for k, v in {\n",
                "        'Year': 'year',\n",
                "        **pk_renames,\n",
                "        **name_renames,\n",
                "    }.items()\n",
                "    if k in c\n",
                "}).astype(pk_astype)\n",
                "c['cn'] = c['cn'].str.title()\n",
                "c['mn'] = c['mn'].str.title()\n",
                "c = c[['year'] + [k for k in c if k != 'year']]\n",
                "c"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {
                "tags": []
            },
            "source": [
                "Helper that returns:\n",
                "1. unique sets of values found in `cols`\n",
                "2. rows from 1. that match all columns but the last"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 3,
            "metadata": {
                "tags": []
            },
            "outputs": [],
            "source": [
                "def ambiguous_mappings(df, cols):\n",
                "    uniqs = (\n",
                "        df\n",
                "        #.reset_index()\n",
                "        [cols]\n",
                "        .drop_duplicates()\n",
                "        .sort_values(cols)\n",
                "    )\n",
                "    hist = uniqs.value_counts(cols[:-1], sort=False)\n",
                "    conflicts = hist[hist > 1]\n",
                "    conflicts = uniqs.set_index(cols[:-1]).loc[conflicts.index].reset_index()\n",
                "    return uniqs, conflicts"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {
                "tags": []
            },
            "source": [
                "### County Code \u27f9 County Name"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 4,
            "metadata": {
                "scrolled": true,
                "tags": []
            },
            "outputs": [
                {
                    "data": {
                        "text/plain": [
                            "cc  cn        \n",
                            "1   Atlantic      199592\n",
                            "2   Bergen        682254\n",
                            "3   Burlington    275382\n",
                            "4   Camden        338799\n",
                            "5   Cape May       67876\n",
                            "6   Cumberland    102238\n",
                            "7   Essex         637442\n",
                            "8   Gloucester    162997\n",
                            "9   Hudson        440379\n",
                            "10  Hunterdon      86997\n",
                            "11  Mercer        280890\n",
                            "12  Middlesex     632026\n",
                            "13  Monmouth      431841\n",
                            "14  Morris        336447\n",
                            "15  Ocean         355144\n",
                            "16  Passaic       406831\n",
                            "17  Salem          38454\n",
                            "18  Somerset      245646\n",
                            "19  Sussex         81456\n",
                            "20  Union         443518\n",
                            "21  Warren         73585\n",
                            "Name: num, dtype: int64"
                        ]
                    },
                    "execution_count": 4,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "cn_cols = [ 'cc', 'cn' ]\n",
                "cc2cn, county_name_conflicts = ambiguous_mappings(c, cn_cols)\n",
                "assert len(county_name_conflicts) == 0\n",
                "cc2cn = cc2cn.set_index('cc')['cn']\n",
                "assert cc2cn.to_dict() == njdot.data.cc2cn\n",
                "c.groupby(cn_cols)['num'].sum()"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {
                "tags": []
            },
            "source": [
                "### { County Code, Muni Code, Year } \u27f9 Muni Name"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 5,
            "metadata": {
                "scrolled": true,
                "tags": []
            },
            "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></th>\n",
                            "      <th></th>\n",
                            "      <th></th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>cc</th>\n",
                            "      <th>mc</th>\n",
                            "      <th>mn</th>\n",
                            "      <th>year</th>\n",
                            "    </tr>\n",
                            "  </thead>\n",
                            "  <tbody>\n",
                            "    <tr>\n",
                            "      <th rowspan=\"5\" valign=\"top\">1</th>\n",
                            "      <th rowspan=\"5\" valign=\"top\">1</th>\n",
                            "      <th rowspan=\"5\" valign=\"top\">Absecon City</th>\n",
                            "      <th>2001</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>2002</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>2003</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>2004</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>2005</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>...</th>\n",
                            "      <th>...</th>\n",
                            "      <th>...</th>\n",
                            "      <th>...</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th rowspan=\"5\" valign=\"top\">21</th>\n",
                            "      <th rowspan=\"5\" valign=\"top\">23</th>\n",
                            "      <th rowspan=\"5\" valign=\"top\">White Twp</th>\n",
                            "      <th>2018</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>2019</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>2020</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>2021</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>2022</th>\n",
                            "    </tr>\n",
                            "  </tbody>\n",
                            "</table>\n",
                            "<p>12304 rows \u00d7 0 columns</p>\n",
                            "</div>"
                        ],
                        "text/plain": [
                            "Empty DataFrame\n",
                            "Columns: []\n",
                            "Index: [(1, 1, Absecon City, 2001), (1, 1, Absecon City, 2002), (1, 1, Absecon City, 2003), (1, 1, Absecon City, 2004), (1, 1, Absecon City, 2005), (1, 1, Absecon City, 2006), (1, 1, Absecon City, 2007), (1, 1, Absecon City, 2008), (1, 1, Absecon City, 2009), (1, 1, Absecon City, 2010), (1, 1, Absecon City, 2011), (1, 1, Absecon City, 2012), (1, 1, Absecon City, 2013), (1, 1, Absecon City, 2014), (1, 1, Absecon City, 2015), (1, 1, Absecon City, 2016), (1, 1, Absecon City, 2017), (1, 1, Absecon City, 2018), (1, 1, Absecon City, 2019), (1, 1, Absecon City, 2020), (1, 1, Absecon City, 2021), (1, 1, Absecon City, 2022), (1, 2, Atlantic City, 2001), (1, 2, Atlantic City, 2002), (1, 2, Atlantic City, 2003), (1, 2, Atlantic City, 2004), (1, 2, Atlantic City, 2005), (1, 2, Atlantic City, 2006), (1, 2, Atlantic City, 2007), (1, 2, Atlantic City, 2008), (1, 2, Atlantic City, 2009), (1, 2, Atlantic City, 2010), (1, 2, Atlantic City, 2011), (1, 2, Atlantic City, 2012), (1, 2, Atlantic City, 2013), (1, 2, Atlantic City, 2014), (1, 2, Atlantic City, 2015), (1, 2, Atlantic City, 2016), (1, 2, Atlantic City, 2017), (1, 2, Atlantic City, 2018), (1, 2, Atlantic City, 2019), (1, 2, Atlantic City, 2020), (1, 2, Atlantic City, 2021), (1, 2, Atlantic City, 2022), (1, 3, Brigantine City, 2001), (1, 3, Brigantine City, 2002), (1, 3, Brigantine City, 2003), (1, 3, Brigantine City, 2004), (1, 3, Brigantine City, 2005), (1, 3, Brigantine City, 2006), (1, 3, Brigantine City, 2007), (1, 3, Brigantine City, 2008), (1, 3, Brigantine City, 2009), (1, 3, Brigantine City, 2010), (1, 3, Brigantine City, 2011), (1, 3, Brigantine City, 2012), (1, 3, Brigantine City, 2013), (1, 3, Brigantine City, 2014), (1, 3, Brigantine City, 2015), (1, 3, Brigantine City, 2016), (1, 3, Brigantine City, 2017), (1, 3, Brigantine City, 2018), (1, 3, Brigantine City, 2019), (1, 3, Brigantine City, 2020), (1, 3, Brigantine City, 2021), (1, 3, Brigantine City, 2022), (1, 4, Buena Boro, 2001), (1, 4, Buena Boro, 2002), (1, 4, Buena Boro, 2003), (1, 4, Buena Boro, 2004), (1, 4, Buena Boro, 2005), (1, 4, Buena Boro, 2006), (1, 4, Buena Boro, 2007), (1, 4, Buena Boro, 2008), (1, 4, Buena Boro, 2009), (1, 4, Buena Boro, 2010), (1, 4, Buena Boro, 2011), (1, 4, Buena Boro, 2012), (1, 4, Buena Boro, 2013), (1, 4, Buena Boro, 2014), (1, 4, Buena Boro, 2015), (1, 4, Buena Boro, 2016), (1, 4, Buena Boro, 2017), (1, 4, Buena Boro, 2018), (1, 4, Buena Boro, 2019), (1, 4, Buena Boro, 2020), (1, 4, Buena Boro, 2021), (1, 4, Buena Boro, 2022), (1, 5, Buena Vista Twp, 2001), (1, 5, Buena Vista Twp, 2002), (1, 5, Buena Vista Twp, 2003), (1, 5, Buena Vista Twp, 2004), (1, 5, Buena Vista Twp, 2005), (1, 5, Buena Vista Twp, 2006), (1, 5, Buena Vista Twp, 2007), (1, 5, Buena Vista Twp, 2008), (1, 5, Buena Vista Twp, 2009), (1, 5, Buena Vista Twp, 2010), (1, 5, Buena Vista Twp, 2011), (1, 5, Buena Vista Twp, 2012), ...]\n",
                            "\n",
                            "[12304 rows x 0 columns]"
                        ]
                    },
                    "execution_count": 5,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "mny_keys = [ 'cc', 'mc', 'year' ]\n",
                "mn_keys = [ 'cc', 'mc' ]\n",
                "mn_val = 'mn'\n",
                "mn_cols = mn_keys + [ mn_val ]\n",
                "mny_cols = mny_keys + [ mn_val ]\n",
                "mny_uniqs, mny_conflicts = ambiguous_mappings(c, mny_cols)\n",
                "assert len(mny_conflicts) == 0\n",
                "mny_uniqs.set_index(mn_cols + ['year'])"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {
                "tags": []
            },
            "source": [
                "### Check NJDOT county/muni codes"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {
                "tags": []
            },
            "source": [
                "#### { County Code, Muni Code } \u27f9 Muni Name conflicts"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 6,
            "metadata": {
                "tags": []
            },
            "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></th>\n",
                            "      <th></th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>cc</th>\n",
                            "      <th>mc</th>\n",
                            "      <th>mn</th>\n",
                            "    </tr>\n",
                            "  </thead>\n",
                            "  <tbody>\n",
                            "    <tr>\n",
                            "      <th rowspan=\"2\" valign=\"top\">14</th>\n",
                            "      <th rowspan=\"2\" valign=\"top\">30</th>\n",
                            "      <th>Long Hill Twp</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>Passaic Twp</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th rowspan=\"2\" valign=\"top\">15</th>\n",
                            "      <th rowspan=\"2\" valign=\"top\">7</th>\n",
                            "      <th>Dover Twp</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>Toms River Twp</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th rowspan=\"2\" valign=\"top\">16</th>\n",
                            "      <th rowspan=\"2\" valign=\"top\">16</th>\n",
                            "      <th>West Paterson Boro</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>Woodland Park Boro</th>\n",
                            "    </tr>\n",
                            "  </tbody>\n",
                            "</table>\n",
                            "</div>"
                        ],
                        "text/plain": [
                            "Empty DataFrame\n",
                            "Columns: []\n",
                            "Index: [(14, 30, Long Hill Twp), (14, 30, Passaic Twp), (15, 7, Dover Twp), (15, 7, Toms River Twp), (16, 16, West Paterson Boro), (16, 16, Woodland Park Boro)]"
                        ]
                    },
                    "execution_count": 6,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "_, muni_name_conflicts = ambiguous_mappings(c, mn_cols)\n",
                "muni_name_conflicts.set_index(mn_cols)"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {
                "tags": []
            },
            "source": [
                "#### Muni Name transitions"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 7,
            "metadata": {
                "scrolled": true,
                "tags": []
            },
            "outputs": [
                {
                    "data": {
                        "text/plain": [
                            "cc  mc  year\n",
                            "14  30  2001           Passaic Twp\n",
                            "        2002           Passaic Twp\n",
                            "        2003           Passaic Twp\n",
                            "        2004           Passaic Twp\n",
                            "        2005           Passaic Twp\n",
                            "        2006           Passaic Twp\n",
                            "        2007           Passaic Twp\n",
                            "        2008           Passaic Twp\n",
                            "        2009           Passaic Twp\n",
                            "        2010           Passaic Twp\n",
                            "        2011           Passaic Twp\n",
                            "        2012           Passaic Twp\n",
                            "        2013         Long Hill Twp\n",
                            "        2014         Long Hill Twp\n",
                            "        2015         Long Hill Twp\n",
                            "        2016         Long Hill Twp\n",
                            "        2017           Passaic Twp\n",
                            "        2018           Passaic Twp\n",
                            "        2019           Passaic Twp\n",
                            "        2021           Passaic Twp\n",
                            "        2022           Passaic Twp\n",
                            "15  7   2001             Dover Twp\n",
                            "        2002             Dover Twp\n",
                            "        2003             Dover Twp\n",
                            "        2004             Dover Twp\n",
                            "        2005             Dover Twp\n",
                            "        2006             Dover Twp\n",
                            "        2007             Dover Twp\n",
                            "        2008        Toms River Twp\n",
                            "        2009        Toms River Twp\n",
                            "        2010        Toms River Twp\n",
                            "        2011        Toms River Twp\n",
                            "        2012        Toms River Twp\n",
                            "        2013        Toms River Twp\n",
                            "        2014        Toms River Twp\n",
                            "        2015        Toms River Twp\n",
                            "        2016        Toms River Twp\n",
                            "        2017        Toms River Twp\n",
                            "        2018        Toms River Twp\n",
                            "        2019        Toms River Twp\n",
                            "        2020        Toms River Twp\n",
                            "        2021        Toms River Twp\n",
                            "        2022        Toms River Twp\n",
                            "16  16  2001    West Paterson Boro\n",
                            "        2002    West Paterson Boro\n",
                            "        2003    West Paterson Boro\n",
                            "        2004    West Paterson Boro\n",
                            "        2005    West Paterson Boro\n",
                            "        2006    West Paterson Boro\n",
                            "        2007    West Paterson Boro\n",
                            "        2008    Woodland Park Boro\n",
                            "        2009    Woodland Park Boro\n",
                            "        2010    Woodland Park Boro\n",
                            "        2011    Woodland Park Boro\n",
                            "        2012    Woodland Park Boro\n",
                            "        2013    Woodland Park Boro\n",
                            "        2014    Woodland Park Boro\n",
                            "        2015    Woodland Park Boro\n",
                            "        2016    Woodland Park Boro\n",
                            "        2017    Woodland Park Boro\n",
                            "        2018    Woodland Park Boro\n",
                            "        2019    Woodland Park Boro\n",
                            "        2020    Woodland Park Boro\n",
                            "        2021    Woodland Park Boro\n",
                            "        2022    Woodland Park Boro\n",
                            "Name: mn, dtype: string"
                        ]
                    },
                    "execution_count": 7,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "mnys = (\n",
                "    mny_uniqs\n",
                "    .merge(muni_name_conflicts, on=mn_cols)\n",
                "    .set_index(mny_keys)\n",
                "    .sort_index()\n",
                "    [mn_val]\n",
                ")\n",
                "mnys"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 8,
            "metadata": {
                "tags": []
            },
            "outputs": [
                {
                    "data": {
                        "text/plain": [
                            "cc  mc  year\n",
                            "14  30  2013         Long Hill Twp\n",
                            "        2017           Passaic Twp\n",
                            "15  7   2001             Dover Twp\n",
                            "        2008        Toms River Twp\n",
                            "16  16  2001    West Paterson Boro\n",
                            "        2008    Woodland Park Boro\n",
                            "Name: mn, dtype: string"
                        ]
                    },
                    "execution_count": 8,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "mnys[mnys != mnys.shift(1)]"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {
                "tags": []
            },
            "source": [
                "### Use 2021 muni names as canonical"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 9,
            "metadata": {
                "tags": []
            },
            "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>cc</th>\n",
                            "      <th>cn</th>\n",
                            "      <th>mc</th>\n",
                            "      <th>mn</th>\n",
                            "    </tr>\n",
                            "  </thead>\n",
                            "  <tbody>\n",
                            "    <tr>\n",
                            "      <th>0</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>1</td>\n",
                            "      <td>Absecon City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>1</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>2</td>\n",
                            "      <td>Atlantic City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>2</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>3</td>\n",
                            "      <td>Brigantine City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>3</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>4</td>\n",
                            "      <td>Buena Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>4</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>5</td>\n",
                            "      <td>Buena Vista Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>...</th>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>563</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>19</td>\n",
                            "      <td>Phillipsburg Town</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>564</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>20</td>\n",
                            "      <td>Pohatcong Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>565</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>21</td>\n",
                            "      <td>Washington Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>566</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>22</td>\n",
                            "      <td>Washington Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>567</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>23</td>\n",
                            "      <td>White Twp</td>\n",
                            "    </tr>\n",
                            "  </tbody>\n",
                            "</table>\n",
                            "<p>568 rows \u00d7 4 columns</p>\n",
                            "</div>"
                        ],
                        "text/plain": [
                            "     cc        cn  mc                 mn\n",
                            "0     1  Atlantic   1       Absecon City\n",
                            "1     1  Atlantic   2      Atlantic City\n",
                            "2     1  Atlantic   3    Brigantine City\n",
                            "3     1  Atlantic   4         Buena Boro\n",
                            "4     1  Atlantic   5    Buena Vista Twp\n",
                            "..   ..       ...  ..                ...\n",
                            "563  21    Warren  19  Phillipsburg Town\n",
                            "564  21    Warren  20      Pohatcong Twp\n",
                            "565  21    Warren  21    Washington Boro\n",
                            "566  21    Warren  22     Washington Twp\n",
                            "567  21    Warren  23          White Twp\n",
                            "\n",
                            "[568 rows x 4 columns]"
                        ]
                    },
                    "execution_count": 9,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "mn21 = (\n",
                "    mny_uniqs\n",
                "    [['cc', 'mc', 'mn', 'year']]\n",
                "    .groupby(['cc', 'mc'])\n",
                "    .apply(lambda df: df.sort_values('year').iloc[-1].mn)\n",
                "    .rename('mn')\n",
                "    .reset_index()\n",
                "    .merge(cc2cn, left_on='cc', right_index=True, how='left', validate='m:1')\n",
                "    [['cc', 'cn', 'mc', 'mn']]\n",
                ")\n",
                "\n",
                "# Fix typos / normalize muni names\n",
                "mn_nits = {\n",
                "    'Mount Ephriam Boro': 'Mount Ephraim Boro',\n",
                "    'Ho Ho Kus Boro': 'Ho-Ho-Kus Boro',\n",
                "    'Lower Alloways Crk': 'Lower Alloways Creek',\n",
                "    'Sandvston Twp': 'Sandyston Twp',\n",
                "    'Passaic Twp': 'Long Hill Twp',  # renamed in 1992; https://en.wikipedia.org/wiki/Long_Hill_Township,_New_Jersey\n",
                "    'Orange City': 'Orange Twp',\n",
                "    'Avon-By-The-Sea Boro': 'Avon-by-the-Sea Boro',\n",
                "    'South Orange Village Twp': 'South Orange Village',\n",
                "    'Pt Pleasant Beach Boro': 'Point Pleasant Beach Boro',\n",
                "    'South Orange Village Twp': 'South Orange Twp',\n",
                "    'Lower Alloways Crk Twp': 'Lower Alloways Creek Twp',\n",
                "    # Princeton Twp and Boro merged in 2013: https://en.wikipedia.org/wiki/Princeton,_New_Jersey\n",
                "    'Princeton Twp': 'Princeton',\n",
                "    'Princeton Boro': 'Princeton',\n",
                "    # Dissolved/Merged in 2022: https://en.wikipedia.org/wiki/Pine_Valley,_New_Jersey\n",
                "    'Pine Valley Boro': 'Pine Hill Boro',\n",
                "    # Dissolved/Merged in 1997: https://en.wikipedia.org/wiki/Pahaquarry_Township,_New_Jersey\n",
                "    'Pahaquarry Twp': 'Hardwick Twp',\n",
                "}\n",
                "for src, dst in mn_nits.items():\n",
                "    mn21['mn'] = mn21['mn'].replace(src, dst, regex=False)\n",
                "mn21"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {
                "tags": []
            },
            "source": [
                "## Load NJSP county/muni codes"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 10,
            "metadata": {
                "tags": []
            },
            "outputs": [],
            "source": [
                "suffixes = [ 'Boro', 'City', 'Village', 'Twp', 'Town', ]"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 11,
            "metadata": {
                "tags": []
            },
            "outputs": [
                {
                    "name": "stderr",
                    "output_type": "stream",
                    "text": [
                        "      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        646       463         689\n",
                        "2023        574       371         606\n",
                        "2024        578       107         610\n",
                        "      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",
                        "13587    20       Union  2001  Berkeley Heights Tw     NaN   \n",
                        "13588    08  Gloucester  0816  South Harrison Twsp      45   \n",
                        "13589    12   Middlesex  1217      Piscataway Twsp     NaN   \n",
                        "13590    15       Ocean  1507           Toms River     NaN   \n",
                        "13591    01    Atlantic  0113       Hammonton Town     NaN   \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",
                        "13587                               Park Ave         2.0       NaN   \n",
                        "13588                       State Highway 45         1.0       NaN   \n",
                        "13589                    1345 Centennial Ave         1.0       NaN   \n",
                        "13590                             hooper ave         1.0       NaN   \n",
                        "13591                     s white horse pike         1.0       NaN   \n",
                        "\n",
                        "                    STREET                        dt  FATAL_D  FATAL_P  \\\n",
                        "ACCID                                                                    \n",
                        "1703                   NaN 2008-01-01 00:35:00-05:00      NaN      NaN   \n",
                        "1681        Bergenline Ave 2008-01-01 04:11:00-05:00      NaN      NaN   \n",
                        "1659                   NaN 2008-01-01 06:46:00-05:00      NaN      NaN   \n",
                        "1661                   NaN 2008-01-01 12:29:00-05:00      NaN      NaN   \n",
                        "1811                   NaN 2008-01-01 18:53:00-05:00      NaN      NaN   \n",
                        "...                    ...                       ...      ...      ...   \n",
                        "13587             Park Ave 2024-11-20 14:26:00-05:00      1.0      1.0   \n",
                        "13588                  NaN 2024-11-20 15:23:00-05:00      0.0      1.0   \n",
                        "13589  1345 Centennial Ave 2024-11-22 12:29:00-05:00      0.0      0.0   \n",
                        "13590           hooper ave 2024-11-22 21:23:00-05:00      0.0      0.0   \n",
                        "13591   s white horse pike 2024-11-24 03:18:00-05:00      0.0      0.0   \n",
                        "\n",
                        "       FATAL_T  FATAL_B  \n",
                        "ACCID                    \n",
                        "1703       NaN      NaN  \n",
                        "1681       NaN      NaN  \n",
                        "1659       NaN      NaN  \n",
                        "1661       NaN      NaN  \n",
                        "1811       NaN      NaN  \n",
                        "...        ...      ...  \n",
                        "13587      0.0      0.0  \n",
                        "13588      0.0      0.0  \n",
                        "13589      1.0      0.0  \n",
                        "13590      0.0      1.0  \n",
                        "13591      1.0      0.0  \n",
                        "\n",
                        "[9551 rows x 14 columns]\n",
                        "/opt/hostedtoolcache/Python/3.11.8/x64/lib/python3.11/site-packages/dateutil/parser/_parser.py:1207: UnknownTimezoneWarning: tzname EST identified but not understood.  Pass `tzinfos` argument in order to correctly return a timezone-aware datetime.  In a future version, this will raise an exception.\n",
                        "  warnings.warn(\"tzname {tzname} identified but not understood.  \"\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>dt</th>\n",
                            "      <th>cc</th>\n",
                            "      <th>mc</th>\n",
                            "      <th>cn</th>\n",
                            "      <th>mn</th>\n",
                            "      <th>tk</th>\n",
                            "      <th>ti</th>\n",
                            "      <th>dk</th>\n",
                            "      <th>ok</th>\n",
                            "      <th>pk</th>\n",
                            "      <th>bk</th>\n",
                            "      <th>street</th>\n",
                            "      <th>highway</th>\n",
                            "      <th>location</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>2008-01-01 00:35:00-05:00</td>\n",
                            "      <td>1</td>\n",
                            "      <td>2</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>Atlantic City</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>446</td>\n",
                            "      <td>State/Interstate Authority 446 S MP 1</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>1681</th>\n",
                            "      <td>2008-01-01 04:11:00-05:00</td>\n",
                            "      <td>9</td>\n",
                            "      <td>10</td>\n",
                            "      <td>Hudson</td>\n",
                            "      <td>Union City</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>Bergenline Ave</td>\n",
                            "      <td>NaN</td>\n",
                            "      <td>Bergenline Ave S MP 0 at 6th St</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>1659</th>\n",
                            "      <td>2008-01-01 06:46:00-05:00</td>\n",
                            "      <td>4</td>\n",
                            "      <td>15</td>\n",
                            "      <td>Camden</td>\n",
                            "      <td>Gloucester Twp</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>42</td>\n",
                            "      <td>State Highway 42 N MP 8.2</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>1661</th>\n",
                            "      <td>2008-01-01 12:29:00-05:00</td>\n",
                            "      <td>20</td>\n",
                            "      <td>4</td>\n",
                            "      <td>Union</td>\n",
                            "      <td>Elizabeth City</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>624</td>\n",
                            "      <td>County 624 W MP 2.2 at Ikea Dr</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>1811</th>\n",
                            "      <td>2008-01-01 18:53:00-05:00</td>\n",
                            "      <td>7</td>\n",
                            "      <td>16</td>\n",
                            "      <td>Essex</td>\n",
                            "      <td>Nutley Town</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>648</td>\n",
                            "      <td>County 648 E MP .87 at Franklin Ave</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>13587</th>\n",
                            "      <td>2024-11-20 14:26:00-05:00</td>\n",
                            "      <td>20</td>\n",
                            "      <td>1</td>\n",
                            "      <td>Union</td>\n",
                            "      <td>Berkeley Heights Twp</td>\n",
                            "      <td>2.0</td>\n",
                            "      <td>NaN</td>\n",
                            "      <td>1.0</td>\n",
                            "      <td>1.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>Park Ave</td>\n",
                            "      <td>NaN</td>\n",
                            "      <td>Park Ave</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>13588</th>\n",
                            "      <td>2024-11-20 15:23:00-05:00</td>\n",
                            "      <td>8</td>\n",
                            "      <td>16</td>\n",
                            "      <td>Gloucester</td>\n",
                            "      <td>South Harrison Twp</td>\n",
                            "      <td>1.0</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>NaN</td>\n",
                            "      <td>45</td>\n",
                            "      <td>State Highway 45</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>13589</th>\n",
                            "      <td>2024-11-22 12:29:00-05:00</td>\n",
                            "      <td>12</td>\n",
                            "      <td>17</td>\n",
                            "      <td>Middlesex</td>\n",
                            "      <td>Piscataway Twp</td>\n",
                            "      <td>1.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>1345 Centennial Ave</td>\n",
                            "      <td>NaN</td>\n",
                            "      <td>1345 Centennial Ave</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>13590</th>\n",
                            "      <td>2024-11-22 21:23:00-05:00</td>\n",
                            "      <td>15</td>\n",
                            "      <td>7</td>\n",
                            "      <td>Ocean</td>\n",
                            "      <td>Toms River</td>\n",
                            "      <td>1.0</td>\n",
                            "      <td>NaN</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>0.0</td>\n",
                            "      <td>1.0</td>\n",
                            "      <td>hooper ave</td>\n",
                            "      <td>NaN</td>\n",
                            "      <td>hooper ave</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>13591</th>\n",
                            "      <td>2024-11-24 03:18:00-05:00</td>\n",
                            "      <td>1</td>\n",
                            "      <td>13</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>Hammonton Town</td>\n",
                            "      <td>1.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>s white horse pike</td>\n",
                            "      <td>NaN</td>\n",
                            "      <td>s white horse pike</td>\n",
                            "    </tr>\n",
                            "  </tbody>\n",
                            "</table>\n",
                            "<p>9551 rows \u00d7 14 columns</p>\n",
                            "</div>"
                        ],
                        "text/plain": [
                            "                             dt  cc  mc          cn                    mn  \\\n",
                            "ACCID                                                                       \n",
                            "1703  2008-01-01 00:35:00-05:00   1   2    Atlantic         Atlantic City   \n",
                            "1681  2008-01-01 04:11:00-05:00   9  10      Hudson            Union City   \n",
                            "1659  2008-01-01 06:46:00-05:00   4  15      Camden        Gloucester Twp   \n",
                            "1661  2008-01-01 12:29:00-05:00  20   4       Union        Elizabeth City   \n",
                            "1811  2008-01-01 18:53:00-05:00   7  16       Essex           Nutley Town   \n",
                            "...                         ...  ..  ..         ...                   ...   \n",
                            "13587 2024-11-20 14:26:00-05:00  20   1       Union  Berkeley Heights Twp   \n",
                            "13588 2024-11-20 15:23:00-05:00   8  16  Gloucester    South Harrison Twp   \n",
                            "13589 2024-11-22 12:29:00-05:00  12  17   Middlesex        Piscataway Twp   \n",
                            "13590 2024-11-22 21:23:00-05:00  15   7       Ocean            Toms River   \n",
                            "13591 2024-11-24 03:18:00-05:00   1  13    Atlantic        Hammonton Town   \n",
                            "\n",
                            "        tk   ti   dk   ok   pk   bk               street highway  \\\n",
                            "ACCID                                                              \n",
                            "1703   1.0  1.0  NaN  NaN  NaN  NaN                  NaN     446   \n",
                            "1681   1.0  NaN  NaN  NaN  NaN  NaN       Bergenline Ave     NaN   \n",
                            "1659   1.0  1.0  NaN  NaN  NaN  NaN                  NaN      42   \n",
                            "1661   1.0  1.0  NaN  NaN  NaN  NaN                  NaN     624   \n",
                            "1811   1.0  NaN  NaN  NaN  NaN  NaN                  NaN     648   \n",
                            "...    ...  ...  ...  ...  ...  ...                  ...     ...   \n",
                            "13587  2.0  NaN  1.0  1.0  0.0  0.0             Park Ave     NaN   \n",
                            "13588  1.0  NaN  0.0  1.0  0.0  0.0                  NaN      45   \n",
                            "13589  1.0  NaN  0.0  0.0  1.0  0.0  1345 Centennial Ave     NaN   \n",
                            "13590  1.0  NaN  0.0  0.0  0.0  1.0           hooper ave     NaN   \n",
                            "13591  1.0  NaN  0.0  0.0  1.0  0.0   s white horse pike     NaN   \n",
                            "\n",
                            "                                    location  \n",
                            "ACCID                                         \n",
                            "1703   State/Interstate Authority 446 S MP 1  \n",
                            "1681         Bergenline Ave S MP 0 at 6th St  \n",
                            "1659               State Highway 42 N MP 8.2  \n",
                            "1661          County 624 W MP 2.2 at Ikea Dr  \n",
                            "1811     County 648 E MP .87 at Franklin Ave  \n",
                            "...                                      ...  \n",
                            "13587                               Park Ave  \n",
                            "13588                       State Highway 45  \n",
                            "13589                    1345 Centennial Ave  \n",
                            "13590                             hooper ave  \n",
                            "13591                     s white horse pike  \n",
                            "\n",
                            "[9551 rows x 14 columns]"
                        ]
                    },
                    "execution_count": 11,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "from njsp.cli.update_pqts import get_crashes_df\n",
                "\n",
                "renames = {\n",
                "    'CCODE': 'cc',\n",
                "    'MCODE': 'mc',\n",
                "    'CNAME': 'cn',\n",
                "    'MNAME': 'mn',\n",
                "    'FATALITIES': 'tk',\n",
                "    'INJURIES': 'ti',\n",
                "    'FATAL_D': 'dk',\n",
                "    'FATAL_P': 'ok',\n",
                "    'FATAL_T': 'pk',\n",
                "    'FATAL_B': 'bk',\n",
                "    **{\n",
                "        c: c.lower()\n",
                "        for c in ['STREET', 'HIGHWAY', 'LOCATION']\n",
                "    },\n",
                "}\n",
                "\n",
                "def parse_mc(r):\n",
                "    assert r.mc[:2] == r.cc\n",
                "    return r.mc[2:]\n",
                "\n",
                "sp = get_crashes_df()[0].rename(columns=renames)\n",
                "sp['mc'] = sp.apply(parse_mc, axis=1)\n",
                "sp = sp.astype({ 'cc': int, 'mc': int })\n",
                "sp = sp[['dt'] + list(renames.values())]\n",
                "sp['mn'] = sp.mn.replace(' Twsp?$', ' Twp', regex=True)\n",
                "\n",
                "for tpe in suffixes:\n",
                "    full_suffix = f' {tpe}'\n",
                "    for idx in range(1, len(tpe)):\n",
                "        suffix = f' {tpe[:idx]}$'\n",
                "        sp['mn'] = sp.mn.replace(suffix, full_suffix, regex=True)\n",
                "\n",
                "for src, dst in {\n",
                "    'Easthampton Twp': 'Eastampton Twp',\n",
                "    'Hohokus Boro': 'Ho-Ho-Kus Boro',\n",
                "    'Ridgewood Twp': 'Ridgewood Village',\n",
                "    'Ridgefield Park Twp': 'Ridgefield Park Village',\n",
                "    'Parsippany-Troy Hil': 'Parsippany-Troy Hills',\n",
                "    'Lower Alloways Cree': 'Lower Alloways Creek',\n",
                "    'Orange City': 'Orange Twp',\n",
                "    'Avon-By-The-Sea Boro': 'Avon-by-the-Sea Boro',\n",
                "    'South Orange Village': 'South Orange Twp', \n",
                "    'Point Pleasant Beac': 'Point Pleasant Beach',\n",
                "}.items():\n",
                "    sp['mn'] = sp.mn.replace(src, dst, regex=False)\n",
                "sp"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {
                "tags": []
            },
            "source": [
                "## Load NJGIN muni codes\n",
                "See [Municipal_Boundaries_of_NJ.geojson]('../www/public/Municipal_Boundaries_of_NJ.geojson'), downloaded from [this NJGIN ArcGIS layer](https://njogis-newjersey.opendata.arcgis.com/datasets/3d5d1db8a1b34b418c331f4ce1fd0fef/explore)."
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 12,
            "metadata": {
                "tags": []
            },
            "outputs": [
                {
                    "name": "stderr",
                    "output_type": "stream",
                    "text": [
                        "Running: dvc pull www/public/Municipal_Boundaries_of_NJ.geojson\n"
                    ]
                },
                {
                    "name": "stdout",
                    "output_type": "stream",
                    "text": [
                        "A       www/public/Municipal_Boundaries_of_NJ.geojson\n",
                        "1 file added and 1 file fetched\n"
                    ]
                },
                {
                    "data": {
                        "text/plain": [
                            "0       3\n",
                            "1      12\n",
                            "2       2\n",
                            "3      15\n",
                            "4      13\n",
                            "       ..\n",
                            "559    22\n",
                            "560    27\n",
                            "561    12\n",
                            "562     8\n",
                            "563    21\n",
                            "Name: mc, Length: 564, dtype: int64"
                        ]
                    },
                    "execution_count": 12,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "from nj_crashes import load_munis_geojson\n",
                "mdf = load_munis_geojson().reset_index()\n",
                "mn2 = mdf.NAME.rename('mn')\n",
                "for src, dst in {\n",
                "    'Borough': 'Boro',\n",
                "    'Township': 'Twp',\n",
                "}.items():\n",
                "    mn2 = mn2.replace(f' {src}$', f' {dst}', regex=True)\n",
                "for src, dst in {\n",
                "    'South Orange Village Twp': 'South Orange Twp',\n",
                "    'Boonton': 'Boonton Town',\n",
                "    'City of Orange Twp': 'Orange Twp',\n",
                "}.items():\n",
                "    mn2 = mn2.replace(src, dst, regex=False)\n",
                "\n",
                "cc2 = mdf.cc\n",
                "cn2 = mdf.COUNTY.str.title().rename('cn')\n",
                "mc2 = mdf.mc\n",
                "mc2"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {
                "tags": []
            },
            "source": [
                "## Align county and muni codes (NJDOT, NJSP, NJGIN)"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 13,
            "metadata": {
                "tags": []
            },
            "outputs": [],
            "source": [
                "def split_stem_suffix(r):\n",
                "    for suffix in suffixes:\n",
                "        if r.mn.endswith(f' {suffix}'):\n",
                "            return Series(dict(\n",
                "                stem=r.mn[:-(len(suffix) + 1)],\n",
                "                type=suffix,\n",
                "            ))\n",
                "    return dict(stem=r.mn, type=None)\n",
                "\n",
                "def add_stems(df, id_name):\n",
                "    df = df[['cc', 'cn', 'mc', 'mn']].drop_duplicates()\n",
                "    df = sxs(df, df.apply(split_stem_suffix, axis=1)).sort_values(['cc', 'mc']).reset_index(drop=True)\n",
                "    dupe_mask = df.duplicated(keep='last', subset=['cc', 'mc'])\n",
                "    dupes = df[dupe_mask]\n",
                "    if not dupes.empty:\n",
                "        all_dupes = df[df.duplicated(keep=False, subset=['cc', 'mc'])]\n",
                "        err(f\"Dropping {len(dupes)} non-last duplicate (cc,mc) entries. All dupes:\")\n",
                "        err(str(all_dupes))\n",
                "    df = df[~dupe_mask]\n",
                "    assert df[df.duplicated(keep=False, subset=['cc', 'mc'])].empty\n",
                "    df.index.name = id_name\n",
                "    return df"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 14,
            "metadata": {
                "scrolled": true,
                "tags": []
            },
            "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>cc</th>\n",
                            "      <th>cn</th>\n",
                            "      <th>mc</th>\n",
                            "      <th>mn</th>\n",
                            "      <th>stem</th>\n",
                            "      <th>type</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>dot</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>0</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>1</td>\n",
                            "      <td>Absecon City</td>\n",
                            "      <td>Absecon</td>\n",
                            "      <td>City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>1</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>2</td>\n",
                            "      <td>Atlantic City</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>2</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>3</td>\n",
                            "      <td>Brigantine City</td>\n",
                            "      <td>Brigantine</td>\n",
                            "      <td>City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>3</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>4</td>\n",
                            "      <td>Buena Boro</td>\n",
                            "      <td>Buena</td>\n",
                            "      <td>Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>4</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>5</td>\n",
                            "      <td>Buena Vista Twp</td>\n",
                            "      <td>Buena Vista</td>\n",
                            "      <td>Twp</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",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>563</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>19</td>\n",
                            "      <td>Phillipsburg Town</td>\n",
                            "      <td>Phillipsburg</td>\n",
                            "      <td>Town</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>564</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>20</td>\n",
                            "      <td>Pohatcong Twp</td>\n",
                            "      <td>Pohatcong</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>565</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>21</td>\n",
                            "      <td>Washington Boro</td>\n",
                            "      <td>Washington</td>\n",
                            "      <td>Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>566</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>22</td>\n",
                            "      <td>Washington Twp</td>\n",
                            "      <td>Washington</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>567</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>23</td>\n",
                            "      <td>White Twp</td>\n",
                            "      <td>White</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "  </tbody>\n",
                            "</table>\n",
                            "<p>568 rows \u00d7 6 columns</p>\n",
                            "</div>"
                        ],
                        "text/plain": [
                            "     cc        cn  mc                 mn          stem  type\n",
                            "dot                                                         \n",
                            "0     1  Atlantic   1       Absecon City       Absecon  City\n",
                            "1     1  Atlantic   2      Atlantic City      Atlantic  City\n",
                            "2     1  Atlantic   3    Brigantine City    Brigantine  City\n",
                            "3     1  Atlantic   4         Buena Boro         Buena  Boro\n",
                            "4     1  Atlantic   5    Buena Vista Twp   Buena Vista   Twp\n",
                            "..   ..       ...  ..                ...           ...   ...\n",
                            "563  21    Warren  19  Phillipsburg Town  Phillipsburg  Town\n",
                            "564  21    Warren  20      Pohatcong Twp     Pohatcong   Twp\n",
                            "565  21    Warren  21    Washington Boro    Washington  Boro\n",
                            "566  21    Warren  22     Washington Twp    Washington   Twp\n",
                            "567  21    Warren  23          White Twp         White   Twp\n",
                            "\n",
                            "[568 rows x 6 columns]"
                        ]
                    },
                    "execution_count": 14,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "df0 = add_stems(mn21, 'dot')\n",
                "df0"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 15,
            "metadata": {
                "tags": []
            },
            "outputs": [
                {
                    "name": "stderr",
                    "output_type": "stream",
                    "text": [
                        "Dropping 4 non-last duplicate (cc,mc) entries. All dupes:\n",
                        "     cc        cn  mc                mn             stem  type\n",
                        "270  11    Mercer  10     Princeton Twp        Princeton   Twp\n",
                        "271  11    Mercer  10         Princeton        Princeton  None\n",
                        "273  11    Mercer  12    Washington Twp       Washington   Twp\n",
                        "274  11    Mercer  12  Robbinsville Twp     Robbinsville   Twp\n",
                        "306  13  Monmouth   9   Brielle Borowsp  Brielle Borowsp  None\n",
                        "307  13  Monmouth   9      Brielle Boro          Brielle  Boro\n",
                        "351  14    Morris  10  East Honover Twp     East Honover   Twp\n",
                        "352  14    Morris  10  East Hanover Twp     East Hanover   Twp\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>cc</th>\n",
                            "      <th>cn</th>\n",
                            "      <th>mc</th>\n",
                            "      <th>mn</th>\n",
                            "      <th>stem</th>\n",
                            "      <th>type</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>sp</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>0</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>1</td>\n",
                            "      <td>Absecon City</td>\n",
                            "      <td>Absecon</td>\n",
                            "      <td>City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>1</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>2</td>\n",
                            "      <td>Atlantic City</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>2</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>3</td>\n",
                            "      <td>Brigantine City</td>\n",
                            "      <td>Brigantine</td>\n",
                            "      <td>City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>3</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>4</td>\n",
                            "      <td>Buena Boro</td>\n",
                            "      <td>Buena</td>\n",
                            "      <td>Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>4</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>5</td>\n",
                            "      <td>Buena Vista Twp</td>\n",
                            "      <td>Buena Vista</td>\n",
                            "      <td>Twp</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",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>515</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>19</td>\n",
                            "      <td>Phillipsburg Town</td>\n",
                            "      <td>Phillipsburg</td>\n",
                            "      <td>Town</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>516</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>20</td>\n",
                            "      <td>Pohatcong Twp</td>\n",
                            "      <td>Pohatcong</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>517</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>21</td>\n",
                            "      <td>Washington Boro</td>\n",
                            "      <td>Washington</td>\n",
                            "      <td>Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>518</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>22</td>\n",
                            "      <td>Washington Twp</td>\n",
                            "      <td>Washington</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>519</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>23</td>\n",
                            "      <td>White Twp</td>\n",
                            "      <td>White</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "  </tbody>\n",
                            "</table>\n",
                            "<p>516 rows \u00d7 6 columns</p>\n",
                            "</div>"
                        ],
                        "text/plain": [
                            "     cc        cn  mc                 mn          stem  type\n",
                            "sp                                                          \n",
                            "0     1  Atlantic   1       Absecon City       Absecon  City\n",
                            "1     1  Atlantic   2      Atlantic City      Atlantic  City\n",
                            "2     1  Atlantic   3    Brigantine City    Brigantine  City\n",
                            "3     1  Atlantic   4         Buena Boro         Buena  Boro\n",
                            "4     1  Atlantic   5    Buena Vista Twp   Buena Vista   Twp\n",
                            "..   ..       ...  ..                ...           ...   ...\n",
                            "515  21    Warren  19  Phillipsburg Town  Phillipsburg  Town\n",
                            "516  21    Warren  20      Pohatcong Twp     Pohatcong   Twp\n",
                            "517  21    Warren  21    Washington Boro    Washington  Boro\n",
                            "518  21    Warren  22     Washington Twp    Washington   Twp\n",
                            "519  21    Warren  23          White Twp         White   Twp\n",
                            "\n",
                            "[516 rows x 6 columns]"
                        ]
                    },
                    "execution_count": 15,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "df1 = add_stems(sp, 'sp')\n",
                "df1"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 16,
            "metadata": {
                "scrolled": true,
                "tags": []
            },
            "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>cc</th>\n",
                            "      <th>cn</th>\n",
                            "      <th>mc</th>\n",
                            "      <th>mn</th>\n",
                            "      <th>stem</th>\n",
                            "      <th>type</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>gin</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>0</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>1</td>\n",
                            "      <td>Absecon</td>\n",
                            "      <td>Absecon</td>\n",
                            "      <td>None</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>1</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>2</td>\n",
                            "      <td>Atlantic City</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>2</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>3</td>\n",
                            "      <td>Brigantine</td>\n",
                            "      <td>Brigantine</td>\n",
                            "      <td>None</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>3</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>4</td>\n",
                            "      <td>Buena Boro</td>\n",
                            "      <td>Buena</td>\n",
                            "      <td>Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>4</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>5</td>\n",
                            "      <td>Buena Vista Twp</td>\n",
                            "      <td>Buena Vista</td>\n",
                            "      <td>Twp</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",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>559</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>19</td>\n",
                            "      <td>Phillipsburg</td>\n",
                            "      <td>Phillipsburg</td>\n",
                            "      <td>None</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>560</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>20</td>\n",
                            "      <td>Pohatcong Twp</td>\n",
                            "      <td>Pohatcong</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>561</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>21</td>\n",
                            "      <td>Washington Boro</td>\n",
                            "      <td>Washington</td>\n",
                            "      <td>Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>562</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>22</td>\n",
                            "      <td>Washington Twp</td>\n",
                            "      <td>Washington</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>563</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>23</td>\n",
                            "      <td>White Twp</td>\n",
                            "      <td>White</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "  </tbody>\n",
                            "</table>\n",
                            "<p>564 rows \u00d7 6 columns</p>\n",
                            "</div>"
                        ],
                        "text/plain": [
                            "     cc        cn  mc               mn          stem  type\n",
                            "gin                                                       \n",
                            "0     1  Atlantic   1          Absecon       Absecon  None\n",
                            "1     1  Atlantic   2    Atlantic City      Atlantic  City\n",
                            "2     1  Atlantic   3       Brigantine    Brigantine  None\n",
                            "3     1  Atlantic   4       Buena Boro         Buena  Boro\n",
                            "4     1  Atlantic   5  Buena Vista Twp   Buena Vista   Twp\n",
                            "..   ..       ...  ..              ...           ...   ...\n",
                            "559  21    Warren  19     Phillipsburg  Phillipsburg  None\n",
                            "560  21    Warren  20    Pohatcong Twp     Pohatcong   Twp\n",
                            "561  21    Warren  21  Washington Boro    Washington  Boro\n",
                            "562  21    Warren  22   Washington Twp    Washington   Twp\n",
                            "563  21    Warren  23        White Twp         White   Twp\n",
                            "\n",
                            "[564 rows x 6 columns]"
                        ]
                    },
                    "execution_count": 16,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "df2 = add_stems(sxs(cc2, cn2, mc2, mn2), 'gin')\n",
                "df2"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 17,
            "metadata": {
                "tags": []
            },
            "outputs": [],
            "source": [
                "def align(l, r, validate1='1:1'):\n",
                "    on = [ 'cn', 'mn', ]\n",
                "    common = [ 'cc', 'mc', 'type', ]\n",
                "    cols = [ *on, *common ]\n",
                "    ln = l.index.name\n",
                "    rn = r.index.name\n",
                "    lr1 = (\n",
                "        l\n",
                "        .reset_index()\n",
                "        [[ln] + cols].merge(\n",
                "            r\n",
                "            .reset_index()\n",
                "            [[rn] + cols],\n",
                "            on=on,\n",
                "            suffixes=[f'_{ln}', f'_{rn}'],\n",
                "            validate=validate1,\n",
                "        )\n",
                "    )\n",
                "\n",
                "    lcc = f'cc_{ln}'\n",
                "    rcc = f'cc_{rn}'\n",
                "    lmc = f'mc_{ln}'\n",
                "    rmc = f'mc_{rn}'\n",
                "    ltc = f'type_{ln}'\n",
                "    rtc = f'type_{rn}'\n",
                "    lt = lr1[ltc]\n",
                "    rt = lr1[rtc]\n",
                "    \n",
                "    assert (lr1[lcc] == lr1[rcc]).all()\n",
                "    types_match = (lt == rt) | (lt.isna() & rt.isna())\n",
                "    assert (types_match).all(), lr1[~types_match]\n",
                "    mc_match_hist = (lr1[lmc] == lr1[rmc]).value_counts()\n",
                "    err(f\"Found {len(lr1)} exact ({','.join(on)}) matches from {len(l)} {ln} and {len(r)} {rn} entries\")\n",
                "\n",
                "    cc = lr1[lcc].rename('cc')\n",
                "    tpe = lr1[ltc].rename('type')\n",
                "    mcl = lr1[lmc]\n",
                "    mcr = lr1[rmc]\n",
                "    m1 = sxs(cc, mcl, mcr, tpe)\n",
                "\n",
                "    # Filter out exact matches, re-match on \"stems\"\n",
                "    l2 = l[~l.index.isin(lr1[ln])]\n",
                "    r2 = r[~r.index.isin(lr1[rn])]\n",
                "    l2_dupes = l2[l2.duplicated(keep=False, subset=['cn', 'stem'])]\n",
                "    r2_dupes = r2[r2.duplicated(keep=False, subset=['cn', 'stem'])]\n",
                "    assert l2_dupes.empty, f\"{len(l2_dupes)} (cn,stem) dupes found:\\n{l2_dupes}\"\n",
                "    assert r2_dupes.empty, f\"{len(r2_dupes)} (cn,stem) dupes found:\\n{r2_dupes}\"\n",
                "\n",
                "    on = [ 'cn', 'stem', ]\n",
                "    cols2 = [ *on, *common, ]\n",
                "    lr2 = (\n",
                "        l2\n",
                "        .reset_index()\n",
                "        [[ln] + cols2]\n",
                "        .merge(\n",
                "            r2\n",
                "            .reset_index()\n",
                "            [[rn] + cols2],\n",
                "            on=on,\n",
                "            suffixes=[f'_{ln}', f'_{rn}'],\n",
                "            validate='1:1',\n",
                "        )\n",
                "    )\n",
                "\n",
                "    assert (lr2[lcc] == lr2[rcc]).all()\n",
                "    mc_match_hist = (lr2[lmc] == lr2[rmc]).value_counts()\n",
                "    err(f\"Found {len(lr2)} ({','.join(on)}) matches from {len(l2)} {ln} and {len(r2)} {rn} entries\")\n",
                "\n",
                "    l3 = l2[~l2.index.isin(lr2[ln])]\n",
                "    r3 = r2[~r2.index.isin(lr2[rn])]\n",
                "    assert l3.empty, f\"Found {len(l3)} unaligned items from l:\\n{l3}\"\n",
                "\n",
                "    err(f'{ln}: {len(l)} entries, {len(lr1)} exact matches, {len(lr2)} stem matches, {len(l3)} unmatched')\n",
                "    err(f'{rn}: {len(r)} entries, {len(lr1)} exact matches, {len(lr2)} stem matches, {len(r3)} unmatched')\n",
                "\n",
                "    cc = lr2[lcc].rename('cc')\n",
                "    mcl = lr2[lmc]\n",
                "    mcr = lr2[rmc]\n",
                "    tcl = lr2[ltc]\n",
                "    tcr = lr2[rtc]\n",
                "\n",
                "    m2 = sxs(cc, mcl, mcr)\n",
                "    m2['type'] = tcr  # default to right `type`\n",
                "    has_tcl = ~tcl.isna()\n",
                "    has_tcr = ~tcr.isna()\n",
                "    m2.loc[has_tcl & ~has_tcr, 'type'] = tcl  # fallback to left `type`\n",
                "    m2t = m2.merge(r[['cc', 'mc', 'stem']], left_on=['cc', rmc], right_on=['cc', 'mc'], how='left').drop(columns='mc')\n",
                "    type_conflicts = sxs(m2t.drop(columns='type'), tcl, tcr)[(tcl != tcr) & has_tcl & has_tcr]\n",
                "    if not type_conflicts.empty:\n",
                "        err(f\"{len(type_conflicts)} conflicting types:\")\n",
                "        err(str(type_conflicts))\n",
                "\n",
                "    m = pd.concat([m1, m2])\n",
                "    m = m.merge(r[['cc', 'mc', 'stem']], left_on=['cc', rmc], right_on=['cc', 'mc'], how='left').drop(columns='mc')\n",
                "    m['mn'] = m.apply(lambda r: r.stem + (f' {r[\"type\"]}' if r[\"type\"] else ''), axis=1)\n",
                "    err(f\"{(m[lmc] != m[rmc]).sum()} mc's don't match\")\n",
                "    return m"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 18,
            "metadata": {
                "tags": []
            },
            "outputs": [
                {
                    "name": "stderr",
                    "output_type": "stream",
                    "text": [
                        "Found 446 exact (cn,mn) matches from 516 sp and 564 gin entries\n",
                        "Found 70 (cn,stem) matches from 70 sp and 118 gin entries\n",
                        "sp: 516 entries, 446 exact matches, 70 stem matches, 0 unmatched\n",
                        "gin: 564 entries, 446 exact matches, 70 stem matches, 48 unmatched\n",
                        "10 conflicting types:\n",
                        "    cc  mc_sp  mc_gin           stem type_sp type_gin\n",
                        "21   7      1       1     Belleville    Town      Twp\n",
                        "22   7      2       2     Bloomfield    Boro      Twp\n",
                        "23   7      4       7      Fairfield    Boro      Twp\n",
                        "25   7      9       9      Irvington    Town      Twp\n",
                        "26   7     13      13      Montclair    Town      Twp\n",
                        "28   7     16      16         Nutley    Town      Twp\n",
                        "29   7     20      20         Verona    Boro      Twp\n",
                        "30   7     21      21  West Caldwell    Boro      Twp\n",
                        "31   7     22      22    West Orange    Town      Twp\n",
                        "48  13     39      18         Hazlet    Boro      Twp\n",
                        "105 mc's don't match\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>cc</th>\n",
                            "      <th>mc_sp</th>\n",
                            "      <th>mc_gin</th>\n",
                            "      <th>type</th>\n",
                            "      <th>stem</th>\n",
                            "      <th>mn</th>\n",
                            "    </tr>\n",
                            "  </thead>\n",
                            "  <tbody>\n",
                            "    <tr>\n",
                            "      <th>0</th>\n",
                            "      <td>1</td>\n",
                            "      <td>2</td>\n",
                            "      <td>2</td>\n",
                            "      <td>City</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>Atlantic City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>1</th>\n",
                            "      <td>1</td>\n",
                            "      <td>4</td>\n",
                            "      <td>4</td>\n",
                            "      <td>Boro</td>\n",
                            "      <td>Buena</td>\n",
                            "      <td>Buena Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>2</th>\n",
                            "      <td>1</td>\n",
                            "      <td>5</td>\n",
                            "      <td>5</td>\n",
                            "      <td>Twp</td>\n",
                            "      <td>Buena Vista</td>\n",
                            "      <td>Buena Vista Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>3</th>\n",
                            "      <td>1</td>\n",
                            "      <td>7</td>\n",
                            "      <td>7</td>\n",
                            "      <td>City</td>\n",
                            "      <td>Egg Harbor</td>\n",
                            "      <td>Egg Harbor City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>4</th>\n",
                            "      <td>1</td>\n",
                            "      <td>8</td>\n",
                            "      <td>8</td>\n",
                            "      <td>Twp</td>\n",
                            "      <td>Egg Harbor</td>\n",
                            "      <td>Egg Harbor Twp</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",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>511</th>\n",
                            "      <td>20</td>\n",
                            "      <td>18</td>\n",
                            "      <td>18</td>\n",
                            "      <td>City</td>\n",
                            "      <td>Summit</td>\n",
                            "      <td>Summit City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>512</th>\n",
                            "      <td>20</td>\n",
                            "      <td>20</td>\n",
                            "      <td>20</td>\n",
                            "      <td>Town</td>\n",
                            "      <td>Westfield</td>\n",
                            "      <td>Westfield Town</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>513</th>\n",
                            "      <td>21</td>\n",
                            "      <td>3</td>\n",
                            "      <td>3</td>\n",
                            "      <td>Town</td>\n",
                            "      <td>Belvidere</td>\n",
                            "      <td>Belvidere Town</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>514</th>\n",
                            "      <td>21</td>\n",
                            "      <td>8</td>\n",
                            "      <td>8</td>\n",
                            "      <td>Town</td>\n",
                            "      <td>Hackettstown</td>\n",
                            "      <td>Hackettstown Town</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>515</th>\n",
                            "      <td>21</td>\n",
                            "      <td>19</td>\n",
                            "      <td>19</td>\n",
                            "      <td>Town</td>\n",
                            "      <td>Phillipsburg</td>\n",
                            "      <td>Phillipsburg Town</td>\n",
                            "    </tr>\n",
                            "  </tbody>\n",
                            "</table>\n",
                            "<p>516 rows \u00d7 6 columns</p>\n",
                            "</div>"
                        ],
                        "text/plain": [
                            "     cc  mc_sp  mc_gin  type          stem                 mn\n",
                            "0     1      2       2  City      Atlantic      Atlantic City\n",
                            "1     1      4       4  Boro         Buena         Buena Boro\n",
                            "2     1      5       5   Twp   Buena Vista    Buena Vista Twp\n",
                            "3     1      7       7  City    Egg Harbor    Egg Harbor City\n",
                            "4     1      8       8   Twp    Egg Harbor     Egg Harbor Twp\n",
                            "..   ..    ...     ...   ...           ...                ...\n",
                            "511  20     18      18  City        Summit        Summit City\n",
                            "512  20     20      20  Town     Westfield     Westfield Town\n",
                            "513  21      3       3  Town     Belvidere     Belvidere Town\n",
                            "514  21      8       8  Town  Hackettstown  Hackettstown Town\n",
                            "515  21     19      19  Town  Phillipsburg  Phillipsburg Town\n",
                            "\n",
                            "[516 rows x 6 columns]"
                        ]
                    },
                    "execution_count": 18,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "m12 = align(df1, df2)\n",
                "m12"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 19,
            "metadata": {
                "tags": []
            },
            "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>cc</th>\n",
                            "      <th>mc_sp</th>\n",
                            "      <th>mc_gin</th>\n",
                            "    </tr>\n",
                            "  </thead>\n",
                            "  <tbody>\n",
                            "    <tr>\n",
                            "      <th>0</th>\n",
                            "      <td>1</td>\n",
                            "      <td>2</td>\n",
                            "      <td>2</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>1</th>\n",
                            "      <td>1</td>\n",
                            "      <td>4</td>\n",
                            "      <td>4</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>2</th>\n",
                            "      <td>1</td>\n",
                            "      <td>5</td>\n",
                            "      <td>5</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>3</th>\n",
                            "      <td>1</td>\n",
                            "      <td>7</td>\n",
                            "      <td>7</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>4</th>\n",
                            "      <td>1</td>\n",
                            "      <td>8</td>\n",
                            "      <td>8</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>...</th>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>511</th>\n",
                            "      <td>20</td>\n",
                            "      <td>18</td>\n",
                            "      <td>18</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>512</th>\n",
                            "      <td>20</td>\n",
                            "      <td>20</td>\n",
                            "      <td>20</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>513</th>\n",
                            "      <td>21</td>\n",
                            "      <td>3</td>\n",
                            "      <td>3</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>514</th>\n",
                            "      <td>21</td>\n",
                            "      <td>8</td>\n",
                            "      <td>8</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>515</th>\n",
                            "      <td>21</td>\n",
                            "      <td>19</td>\n",
                            "      <td>19</td>\n",
                            "    </tr>\n",
                            "  </tbody>\n",
                            "</table>\n",
                            "<p>516 rows \u00d7 3 columns</p>\n",
                            "</div>"
                        ],
                        "text/plain": [
                            "     cc  mc_sp  mc_gin\n",
                            "0     1      2       2\n",
                            "1     1      4       4\n",
                            "2     1      5       5\n",
                            "3     1      7       7\n",
                            "4     1      8       8\n",
                            "..   ..    ...     ...\n",
                            "511  20     18      18\n",
                            "512  20     20      20\n",
                            "513  21      3       3\n",
                            "514  21      8       8\n",
                            "515  21     19      19\n",
                            "\n",
                            "[516 rows x 3 columns]"
                        ]
                    },
                    "execution_count": 19,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "sp2gin = m12[['cc', 'mc_sp', 'mc_gin']]\n",
                "sp2gin.to_parquet(njsp.paths.MC_PQT)\n",
                "sp2gin"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 20,
            "metadata": {
                "tags": []
            },
            "outputs": [
                {
                    "name": "stderr",
                    "output_type": "stream",
                    "text": [
                        "Found 509 exact (cn,mn) matches from 568 dot and 564 gin entries\n",
                        "Found 59 (cn,stem) matches from 59 dot and 59 gin entries\n",
                        "dot: 568 entries, 509 exact matches, 59 stem matches, 0 unmatched\n",
                        "gin: 564 entries, 509 exact matches, 59 stem matches, 0 unmatched\n",
                        "2 conflicting types:\n",
                        "    cc  mc_dot  mc_gin       stem type_dot type_gin\n",
                        "23   7       7       7  Fairfield     Boro      Twp\n",
                        "35  10      20      20    Milford      Twp     Boro\n",
                        "103 mc's don't match\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>cc</th>\n",
                            "      <th>mc_dot</th>\n",
                            "      <th>mc_gin</th>\n",
                            "      <th>type</th>\n",
                            "      <th>stem</th>\n",
                            "      <th>mn</th>\n",
                            "    </tr>\n",
                            "  </thead>\n",
                            "  <tbody>\n",
                            "    <tr>\n",
                            "      <th>0</th>\n",
                            "      <td>1</td>\n",
                            "      <td>2</td>\n",
                            "      <td>2</td>\n",
                            "      <td>City</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>Atlantic City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>1</th>\n",
                            "      <td>1</td>\n",
                            "      <td>4</td>\n",
                            "      <td>4</td>\n",
                            "      <td>Boro</td>\n",
                            "      <td>Buena</td>\n",
                            "      <td>Buena Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>2</th>\n",
                            "      <td>1</td>\n",
                            "      <td>5</td>\n",
                            "      <td>5</td>\n",
                            "      <td>Twp</td>\n",
                            "      <td>Buena Vista</td>\n",
                            "      <td>Buena Vista Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>3</th>\n",
                            "      <td>1</td>\n",
                            "      <td>6</td>\n",
                            "      <td>6</td>\n",
                            "      <td>City</td>\n",
                            "      <td>Corbin</td>\n",
                            "      <td>Corbin City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>4</th>\n",
                            "      <td>1</td>\n",
                            "      <td>7</td>\n",
                            "      <td>7</td>\n",
                            "      <td>City</td>\n",
                            "      <td>Egg Harbor</td>\n",
                            "      <td>Egg Harbor City</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",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>563</th>\n",
                            "      <td>20</td>\n",
                            "      <td>18</td>\n",
                            "      <td>18</td>\n",
                            "      <td>City</td>\n",
                            "      <td>Summit</td>\n",
                            "      <td>Summit City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>564</th>\n",
                            "      <td>20</td>\n",
                            "      <td>20</td>\n",
                            "      <td>20</td>\n",
                            "      <td>Town</td>\n",
                            "      <td>Westfield</td>\n",
                            "      <td>Westfield Town</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>565</th>\n",
                            "      <td>21</td>\n",
                            "      <td>3</td>\n",
                            "      <td>3</td>\n",
                            "      <td>Town</td>\n",
                            "      <td>Belvidere</td>\n",
                            "      <td>Belvidere Town</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>566</th>\n",
                            "      <td>21</td>\n",
                            "      <td>8</td>\n",
                            "      <td>8</td>\n",
                            "      <td>Town</td>\n",
                            "      <td>Hackettstown</td>\n",
                            "      <td>Hackettstown Town</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>567</th>\n",
                            "      <td>21</td>\n",
                            "      <td>19</td>\n",
                            "      <td>19</td>\n",
                            "      <td>Town</td>\n",
                            "      <td>Phillipsburg</td>\n",
                            "      <td>Phillipsburg Town</td>\n",
                            "    </tr>\n",
                            "  </tbody>\n",
                            "</table>\n",
                            "<p>568 rows \u00d7 6 columns</p>\n",
                            "</div>"
                        ],
                        "text/plain": [
                            "     cc  mc_dot  mc_gin  type          stem                 mn\n",
                            "0     1       2       2  City      Atlantic      Atlantic City\n",
                            "1     1       4       4  Boro         Buena         Buena Boro\n",
                            "2     1       5       5   Twp   Buena Vista    Buena Vista Twp\n",
                            "3     1       6       6  City        Corbin        Corbin City\n",
                            "4     1       7       7  City    Egg Harbor    Egg Harbor City\n",
                            "..   ..     ...     ...   ...           ...                ...\n",
                            "563  20      18      18  City        Summit        Summit City\n",
                            "564  20      20      20  Town     Westfield     Westfield Town\n",
                            "565  21       3       3  Town     Belvidere     Belvidere Town\n",
                            "566  21       8       8  Town  Hackettstown  Hackettstown Town\n",
                            "567  21      19      19  Town  Phillipsburg  Phillipsburg Town\n",
                            "\n",
                            "[568 rows x 6 columns]"
                        ]
                    },
                    "execution_count": 20,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "m02 = align(df0, df2, validate1='m:1')\n",
                "m02"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 21,
            "metadata": {
                "tags": []
            },
            "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>cc</th>\n",
                            "      <th>mc_dot</th>\n",
                            "      <th>mc_gin</th>\n",
                            "    </tr>\n",
                            "  </thead>\n",
                            "  <tbody>\n",
                            "    <tr>\n",
                            "      <th>0</th>\n",
                            "      <td>1</td>\n",
                            "      <td>2</td>\n",
                            "      <td>2</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>1</th>\n",
                            "      <td>1</td>\n",
                            "      <td>4</td>\n",
                            "      <td>4</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>2</th>\n",
                            "      <td>1</td>\n",
                            "      <td>5</td>\n",
                            "      <td>5</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>3</th>\n",
                            "      <td>1</td>\n",
                            "      <td>6</td>\n",
                            "      <td>6</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>4</th>\n",
                            "      <td>1</td>\n",
                            "      <td>7</td>\n",
                            "      <td>7</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>...</th>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "      <td>...</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>563</th>\n",
                            "      <td>20</td>\n",
                            "      <td>18</td>\n",
                            "      <td>18</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>564</th>\n",
                            "      <td>20</td>\n",
                            "      <td>20</td>\n",
                            "      <td>20</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>565</th>\n",
                            "      <td>21</td>\n",
                            "      <td>3</td>\n",
                            "      <td>3</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>566</th>\n",
                            "      <td>21</td>\n",
                            "      <td>8</td>\n",
                            "      <td>8</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>567</th>\n",
                            "      <td>21</td>\n",
                            "      <td>19</td>\n",
                            "      <td>19</td>\n",
                            "    </tr>\n",
                            "  </tbody>\n",
                            "</table>\n",
                            "<p>568 rows \u00d7 3 columns</p>\n",
                            "</div>"
                        ],
                        "text/plain": [
                            "     cc  mc_dot  mc_gin\n",
                            "0     1       2       2\n",
                            "1     1       4       4\n",
                            "2     1       5       5\n",
                            "3     1       6       6\n",
                            "4     1       7       7\n",
                            "..   ..     ...     ...\n",
                            "563  20      18      18\n",
                            "564  20      20      20\n",
                            "565  21       3       3\n",
                            "566  21       8       8\n",
                            "567  21      19      19\n",
                            "\n",
                            "[568 rows x 3 columns]"
                        ]
                    },
                    "execution_count": 21,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "dot2gin = m02[['cc', 'mc_dot', 'mc_gin']]\n",
                "dot2gin.to_parquet(njdot.paths.MC_PQT)\n",
                "dot2gin"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 22,
            "metadata": {
                "tags": []
            },
            "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>cc</th>\n",
                            "      <th>cn</th>\n",
                            "      <th>mc_gin</th>\n",
                            "      <th>mc_dot</th>\n",
                            "      <th>mc_sp</th>\n",
                            "      <th>mn</th>\n",
                            "      <th>stem</th>\n",
                            "      <th>type</th>\n",
                            "    </tr>\n",
                            "  </thead>\n",
                            "  <tbody>\n",
                            "    <tr>\n",
                            "      <th>450</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>1</td>\n",
                            "      <td>1</td>\n",
                            "      <td>1</td>\n",
                            "      <td>Absecon City</td>\n",
                            "      <td>Absecon</td>\n",
                            "      <td>City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>0</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>2</td>\n",
                            "      <td>2</td>\n",
                            "      <td>2</td>\n",
                            "      <td>Atlantic City</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>451</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>3</td>\n",
                            "      <td>3</td>\n",
                            "      <td>3</td>\n",
                            "      <td>Brigantine City</td>\n",
                            "      <td>Brigantine</td>\n",
                            "      <td>City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>1</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>4</td>\n",
                            "      <td>4</td>\n",
                            "      <td>4</td>\n",
                            "      <td>Buena Boro</td>\n",
                            "      <td>Buena</td>\n",
                            "      <td>Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>2</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>5</td>\n",
                            "      <td>5</td>\n",
                            "      <td>5</td>\n",
                            "      <td>Buena Vista Twp</td>\n",
                            "      <td>Buena Vista</td>\n",
                            "      <td>Twp</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",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>519</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>19</td>\n",
                            "      <td>19</td>\n",
                            "      <td>19</td>\n",
                            "      <td>Phillipsburg Town</td>\n",
                            "      <td>Phillipsburg</td>\n",
                            "      <td>Town</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>446</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>20</td>\n",
                            "      <td>20</td>\n",
                            "      <td>20</td>\n",
                            "      <td>Pohatcong Twp</td>\n",
                            "      <td>Pohatcong</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>447</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>21</td>\n",
                            "      <td>21</td>\n",
                            "      <td>21</td>\n",
                            "      <td>Washington Boro</td>\n",
                            "      <td>Washington</td>\n",
                            "      <td>Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>448</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>22</td>\n",
                            "      <td>22</td>\n",
                            "      <td>22</td>\n",
                            "      <td>Washington Twp</td>\n",
                            "      <td>Washington</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>449</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>23</td>\n",
                            "      <td>23</td>\n",
                            "      <td>23</td>\n",
                            "      <td>White Twp</td>\n",
                            "      <td>White</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "  </tbody>\n",
                            "</table>\n",
                            "<p>568 rows \u00d7 8 columns</p>\n",
                            "</div>"
                        ],
                        "text/plain": [
                            "     cc        cn  mc_gin  mc_dot  mc_sp                 mn          stem  \\\n",
                            "450   1  Atlantic       1       1      1       Absecon City       Absecon   \n",
                            "0     1  Atlantic       2       2      2      Atlantic City      Atlantic   \n",
                            "451   1  Atlantic       3       3      3    Brigantine City    Brigantine   \n",
                            "1     1  Atlantic       4       4      4         Buena Boro         Buena   \n",
                            "2     1  Atlantic       5       5      5    Buena Vista Twp   Buena Vista   \n",
                            "..   ..       ...     ...     ...    ...                ...           ...   \n",
                            "519  21    Warren      19      19     19  Phillipsburg Town  Phillipsburg   \n",
                            "446  21    Warren      20      20     20      Pohatcong Twp     Pohatcong   \n",
                            "447  21    Warren      21      21     21    Washington Boro    Washington   \n",
                            "448  21    Warren      22      22     22     Washington Twp    Washington   \n",
                            "449  21    Warren      23      23     23          White Twp         White   \n",
                            "\n",
                            "     type  \n",
                            "450  City  \n",
                            "0    City  \n",
                            "451  City  \n",
                            "1    Boro  \n",
                            "2     Twp  \n",
                            "..    ...  \n",
                            "519  Town  \n",
                            "446   Twp  \n",
                            "447  Boro  \n",
                            "448   Twp  \n",
                            "449   Twp  \n",
                            "\n",
                            "[568 rows x 8 columns]"
                        ]
                    },
                    "execution_count": 22,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "m = (\n",
                "    m12\n",
                "    .merge(\n",
                "        m02,\n",
                "        on=['cc', 'mc_gin'],\n",
                "        how='outer',\n",
                "        suffixes=['_sp', '_dot']\n",
                "    )\n",
                "    .sort_values(['cc', 'mc_gin'])\n",
                "    .astype({\n",
                "        'mc_sp': 'Int8',\n",
                "        'mc_dot': 'Int8',\n",
                "    })\n",
                ")\n",
                "assert ((m.stem_sp == m.stem_dot) | m.stem_sp.isna() | m.stem_dot.isna()).all()\n",
                "assert ((m.type_sp == m.type_dot) | m.type_sp.isna() | m.type_dot.isna()).all()\n",
                "\n",
                "m['stem'] = m['stem_sp']\n",
                "m.loc[m.stem.isna() & ~m.stem_dot.isna(), 'stem'] = m.stem_dot\n",
                "m['type'] = m['type_sp']\n",
                "m.loc[m.type.isna() & ~m.type_dot.isna(), 'type'] = m.type_dot\n",
                "m = m.drop(columns=[\n",
                "    f'{c}_{t}'\n",
                "    for c in [ 'stem', 'type', 'mn', ]\n",
                "    for t in [ 'dot', 'sp', ]\n",
                "])\n",
                "m['mn'] = m.apply(lambda r: r.stem + (f' {r[\"type\"]}' if r[\"type\"] else ''), axis=1)\n",
                "m = m.merge(cc2cn, left_on='cc', right_index=True, how='left', validate='m:1')\n",
                "m = m[['cc', 'cn', 'mc_gin', 'mc_dot', 'mc_sp', 'mn', 'stem', 'type']]\n",
                "m"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 23,
            "metadata": {
                "tags": []
            },
            "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>cc</th>\n",
                            "      <th>cn</th>\n",
                            "      <th>mc_gin</th>\n",
                            "      <th>mc_dot</th>\n",
                            "      <th>mc_sp</th>\n",
                            "      <th>mn</th>\n",
                            "      <th>stem</th>\n",
                            "      <th>type</th>\n",
                            "    </tr>\n",
                            "  </thead>\n",
                            "  <tbody>\n",
                            "    <tr>\n",
                            "      <th>229</th>\n",
                            "      <td>11</td>\n",
                            "      <td>Mercer</td>\n",
                            "      <td>14</td>\n",
                            "      <td>9</td>\n",
                            "      <td>10</td>\n",
                            "      <td>Princeton</td>\n",
                            "      <td>Princeton</td>\n",
                            "      <td>None</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>230</th>\n",
                            "      <td>11</td>\n",
                            "      <td>Mercer</td>\n",
                            "      <td>14</td>\n",
                            "      <td>10</td>\n",
                            "      <td>10</td>\n",
                            "      <td>Princeton</td>\n",
                            "      <td>Princeton</td>\n",
                            "      <td>None</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>231</th>\n",
                            "      <td>11</td>\n",
                            "      <td>Mercer</td>\n",
                            "      <td>14</td>\n",
                            "      <td>14</td>\n",
                            "      <td>10</td>\n",
                            "      <td>Princeton</td>\n",
                            "      <td>Princeton</td>\n",
                            "      <td>None</td>\n",
                            "    </tr>\n",
                            "  </tbody>\n",
                            "</table>\n",
                            "</div>"
                        ],
                        "text/plain": [
                            "     cc      cn  mc_gin  mc_dot  mc_sp         mn       stem  type\n",
                            "229  11  Mercer      14       9     10  Princeton  Princeton  None\n",
                            "230  11  Mercer      14      10     10  Princeton  Princeton  None\n",
                            "231  11  Mercer      14      14     10  Princeton  Princeton  None"
                        ]
                    },
                    "execution_count": 23,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "m[m.type.isna()]"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 24,
            "metadata": {
                "tags": []
            },
            "outputs": [
                {
                    "data": {
                        "text/plain": [
                            "type\n",
                            "Boro       253\n",
                            "Twp        242\n",
                            "City        52\n",
                            "Town        15\n",
                            "Village      3\n",
                            "None         3\n",
                            "Name: count, dtype: int64"
                        ]
                    },
                    "execution_count": 24,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "m.type.value_counts(dropna=False)"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 25,
            "metadata": {
                "tags": []
            },
            "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>dot == gin</th>\n",
                            "      <th>False</th>\n",
                            "      <th>True</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>sp == gin</th>\n",
                            "      <th></th>\n",
                            "      <th></th>\n",
                            "    </tr>\n",
                            "  </thead>\n",
                            "  <tbody>\n",
                            "    <tr>\n",
                            "      <th>False</th>\n",
                            "      <td>76</td>\n",
                            "      <td>31</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>True</th>\n",
                            "      <td>10</td>\n",
                            "      <td>403</td>\n",
                            "    </tr>\n",
                            "  </tbody>\n",
                            "</table>\n",
                            "</div>"
                        ],
                        "text/plain": [
                            "dot == gin  False  True \n",
                            "sp == gin               \n",
                            "False          76     31\n",
                            "True           10    403"
                        ]
                    },
                    "execution_count": 25,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "pd.crosstab((m.mc_sp == m.mc_gin).rename('sp == gin'), (m.mc_dot == m.mc_gin).rename('dot == gin'))"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {
                "tags": []
            },
            "source": [
                "## Drop \"Township\"/\"City\"/etc. suffixes\n",
                "Except in a few cases, e.g. \"Jersey City\""
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 26,
            "metadata": {
                "tags": []
            },
            "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>cc</th>\n",
                            "      <th>cn</th>\n",
                            "      <th>mc</th>\n",
                            "      <th>mn</th>\n",
                            "      <th>stem</th>\n",
                            "      <th>type</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>gin</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>294</th>\n",
                            "      <td>11</td>\n",
                            "      <td>Mercer</td>\n",
                            "      <td>14</td>\n",
                            "      <td>Princeton</td>\n",
                            "      <td>Princeton</td>\n",
                            "      <td>None</td>\n",
                            "    </tr>\n",
                            "  </tbody>\n",
                            "</table>\n",
                            "</div>"
                        ],
                        "text/plain": [
                            "     cc      cn  mc         mn       stem  type\n",
                            "gin                                            \n",
                            "294  11  Mercer  14  Princeton  Princeton  None"
                        ]
                    },
                    "execution_count": 26,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "df2[df2.mn == 'Princeton']"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {
                "tags": []
            },
            "source": [
                "Preserve these cities' full names"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 27,
            "metadata": {
                "tags": []
            },
            "outputs": [
                {
                    "data": {
                        "text/plain": [
                            "['Atlantic City', 'Jersey City', 'Ocean City', 'Union City']"
                        ]
                    },
                    "execution_count": 27,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "city_stems = [ 'Atlantic', 'Jersey', 'Ocean', 'Union', ]\n",
                "cities = [ f'{stem} City' for stem in city_stems ]\n",
                "cities"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 28,
            "metadata": {
                "tags": []
            },
            "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>cc</th>\n",
                            "      <th>cn</th>\n",
                            "      <th>mc</th>\n",
                            "      <th>mn</th>\n",
                            "      <th>stem</th>\n",
                            "      <th>type</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>gin</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>1</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>2</td>\n",
                            "      <td>Atlantic City</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>176</th>\n",
                            "      <td>5</td>\n",
                            "      <td>Cape May</td>\n",
                            "      <td>8</td>\n",
                            "      <td>Ocean City</td>\n",
                            "      <td>Ocean</td>\n",
                            "      <td>City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>250</th>\n",
                            "      <td>9</td>\n",
                            "      <td>Hudson</td>\n",
                            "      <td>6</td>\n",
                            "      <td>Jersey City</td>\n",
                            "      <td>Jersey</td>\n",
                            "      <td>City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>254</th>\n",
                            "      <td>9</td>\n",
                            "      <td>Hudson</td>\n",
                            "      <td>10</td>\n",
                            "      <td>Union City</td>\n",
                            "      <td>Union</td>\n",
                            "      <td>City</td>\n",
                            "    </tr>\n",
                            "  </tbody>\n",
                            "</table>\n",
                            "</div>"
                        ],
                        "text/plain": [
                            "     cc        cn  mc             mn      stem  type\n",
                            "gin                                                 \n",
                            "1     1  Atlantic   2  Atlantic City  Atlantic  City\n",
                            "176   5  Cape May   8     Ocean City     Ocean  City\n",
                            "250   9    Hudson   6    Jersey City    Jersey  City\n",
                            "254   9    Hudson  10     Union City     Union  City"
                        ]
                    },
                    "execution_count": 28,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "city_full_mask = df2.mn.isin(cities)\n",
                "df2[city_full_mask]"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 29,
            "metadata": {
                "scrolled": true,
                "tags": []
            },
            "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>cc</th>\n",
                            "      <th>cn</th>\n",
                            "      <th>mc</th>\n",
                            "      <th>mn</th>\n",
                            "      <th>stem</th>\n",
                            "      <th>type</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>gin</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>6</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>7</td>\n",
                            "      <td>Egg Harbor City</td>\n",
                            "      <td>Egg Harbor</td>\n",
                            "      <td>City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>7</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>8</td>\n",
                            "      <td>Egg Harbor Twp</td>\n",
                            "      <td>Egg Harbor</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>95</th>\n",
                            "      <td>3</td>\n",
                            "      <td>Burlington</td>\n",
                            "      <td>3</td>\n",
                            "      <td>Bordentown</td>\n",
                            "      <td>Bordentown</td>\n",
                            "      <td>None</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>96</th>\n",
                            "      <td>3</td>\n",
                            "      <td>Burlington</td>\n",
                            "      <td>4</td>\n",
                            "      <td>Bordentown Twp</td>\n",
                            "      <td>Bordentown</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>97</th>\n",
                            "      <td>3</td>\n",
                            "      <td>Burlington</td>\n",
                            "      <td>5</td>\n",
                            "      <td>Burlington</td>\n",
                            "      <td>Burlington</td>\n",
                            "      <td>None</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>98</th>\n",
                            "      <td>3</td>\n",
                            "      <td>Burlington</td>\n",
                            "      <td>6</td>\n",
                            "      <td>Burlington Twp</td>\n",
                            "      <td>Burlington</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>120</th>\n",
                            "      <td>3</td>\n",
                            "      <td>Burlington</td>\n",
                            "      <td>28</td>\n",
                            "      <td>Pemberton Boro</td>\n",
                            "      <td>Pemberton</td>\n",
                            "      <td>Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>121</th>\n",
                            "      <td>3</td>\n",
                            "      <td>Burlington</td>\n",
                            "      <td>29</td>\n",
                            "      <td>Pemberton Twp</td>\n",
                            "      <td>Pemberton</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>137</th>\n",
                            "      <td>4</td>\n",
                            "      <td>Camden</td>\n",
                            "      <td>5</td>\n",
                            "      <td>Berlin Boro</td>\n",
                            "      <td>Berlin</td>\n",
                            "      <td>Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>138</th>\n",
                            "      <td>4</td>\n",
                            "      <td>Camden</td>\n",
                            "      <td>6</td>\n",
                            "      <td>Berlin Twp</td>\n",
                            "      <td>Berlin</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>146</th>\n",
                            "      <td>4</td>\n",
                            "      <td>Camden</td>\n",
                            "      <td>14</td>\n",
                            "      <td>Gloucester City</td>\n",
                            "      <td>Gloucester</td>\n",
                            "      <td>City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>147</th>\n",
                            "      <td>4</td>\n",
                            "      <td>Camden</td>\n",
                            "      <td>15</td>\n",
                            "      <td>Gloucester Twp</td>\n",
                            "      <td>Gloucester</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>261</th>\n",
                            "      <td>10</td>\n",
                            "      <td>Hunterdon</td>\n",
                            "      <td>5</td>\n",
                            "      <td>Clinton</td>\n",
                            "      <td>Clinton</td>\n",
                            "      <td>None</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>262</th>\n",
                            "      <td>10</td>\n",
                            "      <td>Hunterdon</td>\n",
                            "      <td>6</td>\n",
                            "      <td>Clinton Twp</td>\n",
                            "      <td>Clinton</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>274</th>\n",
                            "      <td>10</td>\n",
                            "      <td>Hunterdon</td>\n",
                            "      <td>18</td>\n",
                            "      <td>Lebanon Boro</td>\n",
                            "      <td>Lebanon</td>\n",
                            "      <td>Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>275</th>\n",
                            "      <td>10</td>\n",
                            "      <td>Hunterdon</td>\n",
                            "      <td>19</td>\n",
                            "      <td>Lebanon Twp</td>\n",
                            "      <td>Lebanon</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>287</th>\n",
                            "      <td>11</td>\n",
                            "      <td>Mercer</td>\n",
                            "      <td>5</td>\n",
                            "      <td>Hopewell Boro</td>\n",
                            "      <td>Hopewell</td>\n",
                            "      <td>Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>288</th>\n",
                            "      <td>11</td>\n",
                            "      <td>Mercer</td>\n",
                            "      <td>6</td>\n",
                            "      <td>Hopewell Twp</td>\n",
                            "      <td>Hopewell</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>335</th>\n",
                            "      <td>13</td>\n",
                            "      <td>Monmouth</td>\n",
                            "      <td>16</td>\n",
                            "      <td>Freehold Boro</td>\n",
                            "      <td>Freehold</td>\n",
                            "      <td>Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>336</th>\n",
                            "      <td>13</td>\n",
                            "      <td>Monmouth</td>\n",
                            "      <td>17</td>\n",
                            "      <td>Freehold Twp</td>\n",
                            "      <td>Freehold</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>363</th>\n",
                            "      <td>13</td>\n",
                            "      <td>Monmouth</td>\n",
                            "      <td>44</td>\n",
                            "      <td>Shrewsbury Boro</td>\n",
                            "      <td>Shrewsbury</td>\n",
                            "      <td>Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>364</th>\n",
                            "      <td>13</td>\n",
                            "      <td>Monmouth</td>\n",
                            "      <td>45</td>\n",
                            "      <td>Shrewsbury Twp</td>\n",
                            "      <td>Shrewsbury</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>373</th>\n",
                            "      <td>14</td>\n",
                            "      <td>Morris</td>\n",
                            "      <td>1</td>\n",
                            "      <td>Boonton Town</td>\n",
                            "      <td>Boonton</td>\n",
                            "      <td>Town</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>374</th>\n",
                            "      <td>14</td>\n",
                            "      <td>Morris</td>\n",
                            "      <td>2</td>\n",
                            "      <td>Boonton Twp</td>\n",
                            "      <td>Boonton</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>376</th>\n",
                            "      <td>14</td>\n",
                            "      <td>Morris</td>\n",
                            "      <td>4</td>\n",
                            "      <td>Chatham Boro</td>\n",
                            "      <td>Chatham</td>\n",
                            "      <td>Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>377</th>\n",
                            "      <td>14</td>\n",
                            "      <td>Morris</td>\n",
                            "      <td>5</td>\n",
                            "      <td>Chatham Twp</td>\n",
                            "      <td>Chatham</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>378</th>\n",
                            "      <td>14</td>\n",
                            "      <td>Morris</td>\n",
                            "      <td>6</td>\n",
                            "      <td>Chester Boro</td>\n",
                            "      <td>Chester</td>\n",
                            "      <td>Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>379</th>\n",
                            "      <td>14</td>\n",
                            "      <td>Morris</td>\n",
                            "      <td>7</td>\n",
                            "      <td>Chester Twp</td>\n",
                            "      <td>Chester</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>390</th>\n",
                            "      <td>14</td>\n",
                            "      <td>Morris</td>\n",
                            "      <td>18</td>\n",
                            "      <td>Mendham Boro</td>\n",
                            "      <td>Mendham</td>\n",
                            "      <td>Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>391</th>\n",
                            "      <td>14</td>\n",
                            "      <td>Morris</td>\n",
                            "      <td>19</td>\n",
                            "      <td>Mendham Twp</td>\n",
                            "      <td>Mendham</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>406</th>\n",
                            "      <td>14</td>\n",
                            "      <td>Morris</td>\n",
                            "      <td>34</td>\n",
                            "      <td>Rockaway Boro</td>\n",
                            "      <td>Rockaway</td>\n",
                            "      <td>Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>407</th>\n",
                            "      <td>14</td>\n",
                            "      <td>Morris</td>\n",
                            "      <td>35</td>\n",
                            "      <td>Rockaway Twp</td>\n",
                            "      <td>Rockaway</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>497</th>\n",
                            "      <td>19</td>\n",
                            "      <td>Sussex</td>\n",
                            "      <td>1</td>\n",
                            "      <td>Andover Boro</td>\n",
                            "      <td>Andover</td>\n",
                            "      <td>Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>498</th>\n",
                            "      <td>19</td>\n",
                            "      <td>Sussex</td>\n",
                            "      <td>2</td>\n",
                            "      <td>Andover Twp</td>\n",
                            "      <td>Andover</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>561</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>21</td>\n",
                            "      <td>Washington Boro</td>\n",
                            "      <td>Washington</td>\n",
                            "      <td>Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>562</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>22</td>\n",
                            "      <td>Washington Twp</td>\n",
                            "      <td>Washington</td>\n",
                            "      <td>Twp</td>\n",
                            "    </tr>\n",
                            "  </tbody>\n",
                            "</table>\n",
                            "</div>"
                        ],
                        "text/plain": [
                            "     cc          cn  mc               mn        stem  type\n",
                            "gin                                                       \n",
                            "6     1    Atlantic   7  Egg Harbor City  Egg Harbor  City\n",
                            "7     1    Atlantic   8   Egg Harbor Twp  Egg Harbor   Twp\n",
                            "95    3  Burlington   3       Bordentown  Bordentown  None\n",
                            "96    3  Burlington   4   Bordentown Twp  Bordentown   Twp\n",
                            "97    3  Burlington   5       Burlington  Burlington  None\n",
                            "98    3  Burlington   6   Burlington Twp  Burlington   Twp\n",
                            "120   3  Burlington  28   Pemberton Boro   Pemberton  Boro\n",
                            "121   3  Burlington  29    Pemberton Twp   Pemberton   Twp\n",
                            "137   4      Camden   5      Berlin Boro      Berlin  Boro\n",
                            "138   4      Camden   6       Berlin Twp      Berlin   Twp\n",
                            "146   4      Camden  14  Gloucester City  Gloucester  City\n",
                            "147   4      Camden  15   Gloucester Twp  Gloucester   Twp\n",
                            "261  10   Hunterdon   5          Clinton     Clinton  None\n",
                            "262  10   Hunterdon   6      Clinton Twp     Clinton   Twp\n",
                            "274  10   Hunterdon  18     Lebanon Boro     Lebanon  Boro\n",
                            "275  10   Hunterdon  19      Lebanon Twp     Lebanon   Twp\n",
                            "287  11      Mercer   5    Hopewell Boro    Hopewell  Boro\n",
                            "288  11      Mercer   6     Hopewell Twp    Hopewell   Twp\n",
                            "335  13    Monmouth  16    Freehold Boro    Freehold  Boro\n",
                            "336  13    Monmouth  17     Freehold Twp    Freehold   Twp\n",
                            "363  13    Monmouth  44  Shrewsbury Boro  Shrewsbury  Boro\n",
                            "364  13    Monmouth  45   Shrewsbury Twp  Shrewsbury   Twp\n",
                            "373  14      Morris   1     Boonton Town     Boonton  Town\n",
                            "374  14      Morris   2      Boonton Twp     Boonton   Twp\n",
                            "376  14      Morris   4     Chatham Boro     Chatham  Boro\n",
                            "377  14      Morris   5      Chatham Twp     Chatham   Twp\n",
                            "378  14      Morris   6     Chester Boro     Chester  Boro\n",
                            "379  14      Morris   7      Chester Twp     Chester   Twp\n",
                            "390  14      Morris  18     Mendham Boro     Mendham  Boro\n",
                            "391  14      Morris  19      Mendham Twp     Mendham   Twp\n",
                            "406  14      Morris  34    Rockaway Boro    Rockaway  Boro\n",
                            "407  14      Morris  35     Rockaway Twp    Rockaway   Twp\n",
                            "497  19      Sussex   1     Andover Boro     Andover  Boro\n",
                            "498  19      Sussex   2      Andover Twp     Andover   Twp\n",
                            "561  21      Warren  21  Washington Boro  Washington  Boro\n",
                            "562  21      Warren  22   Washington Twp  Washington   Twp"
                        ]
                    },
                    "execution_count": 29,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "cnn_dupe_mask = df2.duplicated(['cc', 'stem'], keep=False)\n",
                "cnn_dupes = df2[cnn_dupe_mask]\n",
                "cnn_dupes"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 30,
            "metadata": {
                "tags": []
            },
            "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>cc</th>\n",
                            "      <th>cn</th>\n",
                            "      <th>mc</th>\n",
                            "      <th>stem</th>\n",
                            "      <th>type</th>\n",
                            "      <th>name</th>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>gin</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>0</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>1</td>\n",
                            "      <td>Absecon</td>\n",
                            "      <td>None</td>\n",
                            "      <td>Absecon</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>1</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>2</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>City</td>\n",
                            "      <td>Atlantic City</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>2</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>3</td>\n",
                            "      <td>Brigantine</td>\n",
                            "      <td>None</td>\n",
                            "      <td>Brigantine</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>3</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>4</td>\n",
                            "      <td>Buena</td>\n",
                            "      <td>Boro</td>\n",
                            "      <td>Buena</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>4</th>\n",
                            "      <td>1</td>\n",
                            "      <td>Atlantic</td>\n",
                            "      <td>5</td>\n",
                            "      <td>Buena Vista</td>\n",
                            "      <td>Twp</td>\n",
                            "      <td>Buena Vista</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",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>559</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>19</td>\n",
                            "      <td>Phillipsburg</td>\n",
                            "      <td>None</td>\n",
                            "      <td>Phillipsburg</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>560</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>20</td>\n",
                            "      <td>Pohatcong</td>\n",
                            "      <td>Twp</td>\n",
                            "      <td>Pohatcong</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>561</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>21</td>\n",
                            "      <td>Washington</td>\n",
                            "      <td>Boro</td>\n",
                            "      <td>Washington Boro</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>562</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>22</td>\n",
                            "      <td>Washington</td>\n",
                            "      <td>Twp</td>\n",
                            "      <td>Washington Twp</td>\n",
                            "    </tr>\n",
                            "    <tr>\n",
                            "      <th>563</th>\n",
                            "      <td>21</td>\n",
                            "      <td>Warren</td>\n",
                            "      <td>23</td>\n",
                            "      <td>White</td>\n",
                            "      <td>Twp</td>\n",
                            "      <td>White</td>\n",
                            "    </tr>\n",
                            "  </tbody>\n",
                            "</table>\n",
                            "<p>564 rows \u00d7 6 columns</p>\n",
                            "</div>"
                        ],
                        "text/plain": [
                            "     cc        cn  mc          stem  type             name\n",
                            "gin                                                       \n",
                            "0     1  Atlantic   1       Absecon  None          Absecon\n",
                            "1     1  Atlantic   2      Atlantic  City    Atlantic City\n",
                            "2     1  Atlantic   3    Brigantine  None       Brigantine\n",
                            "3     1  Atlantic   4         Buena  Boro            Buena\n",
                            "4     1  Atlantic   5   Buena Vista   Twp      Buena Vista\n",
                            "..   ..       ...  ..           ...   ...              ...\n",
                            "559  21    Warren  19  Phillipsburg  None     Phillipsburg\n",
                            "560  21    Warren  20     Pohatcong   Twp        Pohatcong\n",
                            "561  21    Warren  21    Washington  Boro  Washington Boro\n",
                            "562  21    Warren  22    Washington   Twp   Washington Twp\n",
                            "563  21    Warren  23         White   Twp            White\n",
                            "\n",
                            "[564 rows x 6 columns]"
                        ]
                    },
                    "execution_count": 30,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "full_name_mask = city_full_mask | cnn_dupe_mask\n",
                "names = df2.copy()\n",
                "names['name'] = names.stem\n",
                "names.loc[full_name_mask, 'name'] = names.loc[full_name_mask, 'mn']\n",
                "names = names.drop(columns='mn')\n",
                "names"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 31,
            "metadata": {
                "tags": []
            },
            "outputs": [],
            "source": [
                "names.to_parquet(COUNTY_CITY_CODES_PQT)"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {
                "tags": []
            },
            "source": [
                "## Build cc2mc2mn dict, write as JSON"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 32,
            "metadata": {
                "tags": []
            },
            "outputs": [
                {
                    "data": {
                        "text/plain": [
                            "{1: {'cn': 'Atlantic',\n",
                            "  'mc2mn': {1: 'Absecon',\n",
                            "   2: 'Atlantic City',\n",
                            "   3: 'Brigantine',\n",
                            "   4: 'Buena',\n",
                            "   5: 'Buena Vista',\n",
                            "   6: 'Corbin',\n",
                            "   7: 'Egg Harbor City',\n",
                            "   8: 'Egg Harbor Twp',\n",
                            "   9: 'Estell Manor',\n",
                            "   10: 'Folsom',\n",
                            "   11: 'Galloway',\n",
                            "   12: 'Hamilton',\n",
                            "   13: 'Hammonton',\n",
                            "   14: 'Linwood',\n",
                            "   15: 'Longport',\n",
                            "   16: 'Margate',\n",
                            "   17: 'Mullica',\n",
                            "   18: 'Northfield',\n",
                            "   19: 'Pleasantville',\n",
                            "   20: 'Port Republic',\n",
                            "   21: 'Somers Point',\n",
                            "   22: 'Ventnor',\n",
                            "   23: 'Weymouth'}},\n",
                            " 2: {'cn': 'Bergen',\n",
                            "  'mc2mn': {1: 'Allendale',\n",
                            "   2: 'Alpine',\n",
                            "   3: 'Bergenfield',\n",
                            "   4: 'Bogota',\n",
                            "   5: 'Carlstadt',\n",
                            "   6: 'Cliffside Park',\n",
                            "   7: 'Closter',\n",
                            "   8: 'Cresskill',\n",
                            "   9: 'Demarest',\n",
                            "   10: 'Dumont',\n",
                            "   11: 'Elmwood Park',\n",
                            "   12: 'East Rutherford',\n",
                            "   13: 'Edgewater',\n",
                            "   14: 'Emerson',\n",
                            "   15: 'Englewood',\n",
                            "   16: 'Englewood Cliffs',\n",
                            "   17: 'Fair Lawn',\n",
                            "   18: 'Fairview',\n",
                            "   19: 'Fort Lee',\n",
                            "   20: 'Franklin Lakes',\n",
                            "   21: 'Garfield',\n",
                            "   22: 'Glen Rock',\n",
                            "   23: 'Hackensack',\n",
                            "   24: 'Harrington Park',\n",
                            "   25: 'Hasbrouck Heights',\n",
                            "   26: 'Haworth',\n",
                            "   27: 'Hillsdale',\n",
                            "   28: 'Ho-Ho-Kus',\n",
                            "   29: 'Leonia',\n",
                            "   30: 'Little Ferry',\n",
                            "   31: 'Lodi',\n",
                            "   32: 'Lyndhurst',\n",
                            "   33: 'Mahwah',\n",
                            "   34: 'Maywood',\n",
                            "   35: 'Midland Park',\n",
                            "   36: 'Montvale',\n",
                            "   37: 'Moonachie',\n",
                            "   38: 'New Milford',\n",
                            "   39: 'North Arlington',\n",
                            "   40: 'Northvale',\n",
                            "   41: 'Norwood',\n",
                            "   42: 'Oakland',\n",
                            "   43: 'Old Tappan',\n",
                            "   44: 'Oradell',\n",
                            "   45: 'Palisades Park',\n",
                            "   46: 'Paramus',\n",
                            "   47: 'Park Ridge',\n",
                            "   48: 'Ramsey',\n",
                            "   49: 'Ridgefield',\n",
                            "   50: 'Ridgefield Park',\n",
                            "   51: 'Ridgewood',\n",
                            "   52: 'River Edge',\n",
                            "   53: 'River Vale',\n",
                            "   54: 'Rochelle Park',\n",
                            "   55: 'Rockleigh',\n",
                            "   56: 'Rutherford',\n",
                            "   57: 'Saddle Brook',\n",
                            "   58: 'Saddle River',\n",
                            "   59: 'South Hackensack',\n",
                            "   60: 'Teaneck',\n",
                            "   61: 'Tenafly',\n",
                            "   62: 'Teterboro',\n",
                            "   63: 'Upper Saddle River',\n",
                            "   64: 'Waldwick',\n",
                            "   65: 'Wallington',\n",
                            "   66: 'Washington',\n",
                            "   67: 'Westwood',\n",
                            "   68: 'Woodcliff Lake',\n",
                            "   69: 'Wood-Ridge',\n",
                            "   70: 'Wyckoff'}},\n",
                            " 3: {'cn': 'Burlington',\n",
                            "  'mc2mn': {1: 'Bass River',\n",
                            "   2: 'Beverly',\n",
                            "   3: 'Bordentown',\n",
                            "   4: 'Bordentown Twp',\n",
                            "   5: 'Burlington',\n",
                            "   6: 'Burlington Twp',\n",
                            "   7: 'Chesterfield',\n",
                            "   8: 'Cinnaminson',\n",
                            "   9: 'Delanco',\n",
                            "   10: 'Delran',\n",
                            "   11: 'Eastampton',\n",
                            "   12: 'Edgewater Park',\n",
                            "   13: 'Evesham',\n",
                            "   14: 'Fieldsboro',\n",
                            "   15: 'Florence',\n",
                            "   16: 'Hainesport',\n",
                            "   17: 'Lumberton',\n",
                            "   18: 'Mansfield',\n",
                            "   19: 'Maple Shade',\n",
                            "   20: 'Medford',\n",
                            "   21: 'Medford Lakes',\n",
                            "   22: 'Moorestown',\n",
                            "   23: 'Mount Holly',\n",
                            "   24: 'Mount Laurel',\n",
                            "   25: 'New Hanover',\n",
                            "   26: 'North Hanover',\n",
                            "   27: 'Palmyra',\n",
                            "   28: 'Pemberton Boro',\n",
                            "   29: 'Pemberton Twp',\n",
                            "   30: 'Riverside',\n",
                            "   31: 'Riverton',\n",
                            "   32: 'Shamong',\n",
                            "   33: 'Southampton',\n",
                            "   34: 'Springfield',\n",
                            "   35: 'Tabernacle',\n",
                            "   36: 'Washington',\n",
                            "   37: 'Westampton',\n",
                            "   38: 'Willingboro',\n",
                            "   39: 'Woodland',\n",
                            "   40: 'Wrightstown'}},\n",
                            " 4: {'cn': 'Camden',\n",
                            "  'mc2mn': {1: 'Audubon',\n",
                            "   2: 'Audubon Park',\n",
                            "   3: 'Barrington',\n",
                            "   4: 'Bellmawr',\n",
                            "   5: 'Berlin Boro',\n",
                            "   6: 'Berlin Twp',\n",
                            "   7: 'Brooklawn',\n",
                            "   8: 'Camden',\n",
                            "   9: 'Cherry Hill',\n",
                            "   10: 'Chesilhurst',\n",
                            "   11: 'Clementon',\n",
                            "   12: 'Collingswood',\n",
                            "   13: 'Gibbsboro',\n",
                            "   14: 'Gloucester City',\n",
                            "   15: 'Gloucester Twp',\n",
                            "   16: 'Haddon',\n",
                            "   17: 'Haddonfield',\n",
                            "   18: 'Haddon Heights',\n",
                            "   19: 'Hi-Nella',\n",
                            "   20: 'Laurel Springs',\n",
                            "   21: 'Lawnside',\n",
                            "   22: 'Lindenwold',\n",
                            "   23: 'Magnolia',\n",
                            "   24: 'Merchantville',\n",
                            "   25: 'Mount Ephraim',\n",
                            "   26: 'Oaklyn',\n",
                            "   27: 'Pennsauken',\n",
                            "   28: 'Pine Hill',\n",
                            "   30: 'Runnemede',\n",
                            "   31: 'Somerdale',\n",
                            "   32: 'Stratford',\n",
                            "   33: 'Tavistock',\n",
                            "   34: 'Voorhees',\n",
                            "   35: 'Waterford',\n",
                            "   36: 'Winslow',\n",
                            "   37: 'Woodlynne'}},\n",
                            " 5: {'cn': 'Cape May',\n",
                            "  'mc2mn': {1: 'Avalon',\n",
                            "   2: 'Cape May',\n",
                            "   3: 'Cape May Point',\n",
                            "   4: 'Dennis',\n",
                            "   5: 'Lower',\n",
                            "   6: 'Middle',\n",
                            "   7: 'North Wildwood',\n",
                            "   8: 'Ocean City',\n",
                            "   9: 'Sea Isle',\n",
                            "   10: 'Stone Harbor',\n",
                            "   11: 'Upper',\n",
                            "   12: 'West Cape May',\n",
                            "   13: 'West Wildwood',\n",
                            "   14: 'Wildwood',\n",
                            "   15: 'Wildwood Crest',\n",
                            "   16: 'Woodbine'}},\n",
                            " 6: {'cn': 'Cumberland',\n",
                            "  'mc2mn': {1: 'Bridgeton',\n",
                            "   2: 'Commercial',\n",
                            "   3: 'Deerfield',\n",
                            "   4: 'Downe',\n",
                            "   5: 'Fairfield',\n",
                            "   6: 'Greenwich',\n",
                            "   7: 'Hopewell',\n",
                            "   8: 'Lawrence',\n",
                            "   9: 'Maurice River',\n",
                            "   10: 'Millville',\n",
                            "   11: 'Shiloh',\n",
                            "   12: 'Stow Creek',\n",
                            "   13: 'Upper Deerfield',\n",
                            "   14: 'Vineland'}},\n",
                            " 7: {'cn': 'Essex',\n",
                            "  'mc2mn': {1: 'Belleville',\n",
                            "   2: 'Bloomfield',\n",
                            "   3: 'Caldwell',\n",
                            "   4: 'Cedar Grove',\n",
                            "   5: 'East Orange',\n",
                            "   6: 'Essex Fells',\n",
                            "   7: 'Fairfield',\n",
                            "   8: 'Glen Ridge',\n",
                            "   9: 'Irvington',\n",
                            "   10: 'Livingston',\n",
                            "   11: 'Maplewood',\n",
                            "   12: 'Millburn',\n",
                            "   13: 'Montclair',\n",
                            "   14: 'Newark',\n",
                            "   15: 'North Caldwell',\n",
                            "   16: 'Nutley',\n",
                            "   17: 'Orange',\n",
                            "   18: 'Roseland',\n",
                            "   19: 'South Orange',\n",
                            "   20: 'Verona',\n",
                            "   21: 'West Caldwell',\n",
                            "   22: 'West Orange'}},\n",
                            " 8: {'cn': 'Gloucester',\n",
                            "  'mc2mn': {1: 'Clayton',\n",
                            "   2: 'Deptford',\n",
                            "   3: 'East Greenwich',\n",
                            "   4: 'Elk',\n",
                            "   5: 'Franklin',\n",
                            "   6: 'Glassboro',\n",
                            "   7: 'Greenwich',\n",
                            "   8: 'Harrison',\n",
                            "   9: 'Logan',\n",
                            "   10: 'Mantua',\n",
                            "   11: 'Monroe',\n",
                            "   12: 'National Park',\n",
                            "   13: 'Newfield',\n",
                            "   14: 'Paulsboro',\n",
                            "   15: 'Pitman',\n",
                            "   16: 'South Harrison',\n",
                            "   17: 'Swedesboro',\n",
                            "   18: 'Washington',\n",
                            "   19: 'Wenonah',\n",
                            "   20: 'West Deptford',\n",
                            "   21: 'Westville',\n",
                            "   22: 'Woodbury',\n",
                            "   23: 'Woodbury Heights',\n",
                            "   24: 'Woolwich'}},\n",
                            " 9: {'cn': 'Hudson',\n",
                            "  'mc2mn': {1: 'Bayonne',\n",
                            "   2: 'East Newark',\n",
                            "   3: 'Guttenberg',\n",
                            "   4: 'Harrison',\n",
                            "   5: 'Hoboken',\n",
                            "   6: 'Jersey City',\n",
                            "   7: 'Kearny',\n",
                            "   8: 'North Bergen',\n",
                            "   9: 'Secaucus',\n",
                            "   10: 'Union City',\n",
                            "   11: 'Weehawken',\n",
                            "   12: 'West New York'}},\n",
                            " 10: {'cn': 'Hunterdon',\n",
                            "  'mc2mn': {1: 'Alexandria',\n",
                            "   2: 'Bethlehem',\n",
                            "   3: 'Bloomsbury',\n",
                            "   4: 'Califon',\n",
                            "   5: 'Clinton',\n",
                            "   6: 'Clinton Twp',\n",
                            "   7: 'Delaware',\n",
                            "   8: 'East Amwell',\n",
                            "   9: 'Flemington',\n",
                            "   10: 'Franklin',\n",
                            "   11: 'Frenchtown',\n",
                            "   12: 'Glen Gardner',\n",
                            "   13: 'Hampton',\n",
                            "   14: 'High Bridge',\n",
                            "   15: 'Holland',\n",
                            "   16: 'Kingwood',\n",
                            "   17: 'Lambertville',\n",
                            "   18: 'Lebanon Boro',\n",
                            "   19: 'Lebanon Twp',\n",
                            "   20: 'Milford',\n",
                            "   21: 'Raritan',\n",
                            "   22: 'Readington',\n",
                            "   23: 'Stockton',\n",
                            "   24: 'Tewksbury',\n",
                            "   25: 'Union',\n",
                            "   26: 'West Amwell'}},\n",
                            " 11: {'cn': 'Mercer',\n",
                            "  'mc2mn': {1: 'East Windsor',\n",
                            "   2: 'Ewing',\n",
                            "   3: 'Hamilton',\n",
                            "   4: 'Hightstown',\n",
                            "   5: 'Hopewell Boro',\n",
                            "   6: 'Hopewell Twp',\n",
                            "   7: 'Lawrence',\n",
                            "   8: 'Pennington',\n",
                            "   11: 'Trenton',\n",
                            "   12: 'Robbinsville',\n",
                            "   13: 'West Windsor',\n",
                            "   14: 'Princeton'}},\n",
                            " 12: {'cn': 'Middlesex',\n",
                            "  'mc2mn': {1: 'Carteret',\n",
                            "   2: 'Cranbury',\n",
                            "   3: 'Dunellen',\n",
                            "   4: 'East Brunswick',\n",
                            "   5: 'Edison',\n",
                            "   6: 'Helmetta',\n",
                            "   7: 'Highland Park',\n",
                            "   8: 'Jamesburg',\n",
                            "   9: 'Metuchen',\n",
                            "   10: 'Middlesex',\n",
                            "   11: 'Milltown',\n",
                            "   12: 'Monroe',\n",
                            "   13: 'New Brunswick',\n",
                            "   14: 'North Brunswick',\n",
                            "   15: 'Old Bridge',\n",
                            "   16: 'Perth Amboy',\n",
                            "   17: 'Piscataway',\n",
                            "   18: 'Plainsboro',\n",
                            "   19: 'Sayreville',\n",
                            "   20: 'South Amboy',\n",
                            "   21: 'South Brunswick',\n",
                            "   22: 'South Plainfield',\n",
                            "   23: 'South River',\n",
                            "   24: 'Spotswood',\n",
                            "   25: 'Woodbridge'}},\n",
                            " 13: {'cn': 'Monmouth',\n",
                            "  'mc2mn': {1: 'Aberdeen',\n",
                            "   2: 'Allenhurst',\n",
                            "   3: 'Allentown',\n",
                            "   4: 'Asbury Park',\n",
                            "   5: 'Atlantic Highlands',\n",
                            "   6: 'Avon-by-the-Sea',\n",
                            "   7: 'Belmar',\n",
                            "   8: 'Bradley Beach',\n",
                            "   9: 'Brielle',\n",
                            "   10: 'Colts Neck',\n",
                            "   11: 'Deal',\n",
                            "   12: 'Eatontown',\n",
                            "   13: 'Englishtown',\n",
                            "   14: 'Fair Haven',\n",
                            "   15: 'Farmingdale',\n",
                            "   16: 'Freehold Boro',\n",
                            "   17: 'Freehold Twp',\n",
                            "   18: 'Hazlet',\n",
                            "   19: 'Highlands',\n",
                            "   20: 'Holmdel',\n",
                            "   21: 'Howell',\n",
                            "   22: 'Interlaken',\n",
                            "   23: 'Keansburg',\n",
                            "   24: 'Keyport',\n",
                            "   25: 'Little Silver',\n",
                            "   26: 'Loch Arbour',\n",
                            "   27: 'Long Branch',\n",
                            "   28: 'Manalapan',\n",
                            "   29: 'Manasquan',\n",
                            "   30: 'Marlboro',\n",
                            "   31: 'Matawan',\n",
                            "   32: 'Middletown',\n",
                            "   33: 'Millstone',\n",
                            "   34: 'Monmouth Beach',\n",
                            "   35: 'Neptune',\n",
                            "   36: 'Neptune City',\n",
                            "   37: 'Ocean',\n",
                            "   38: 'Oceanport',\n",
                            "   39: 'Red Bank',\n",
                            "   40: 'Roosevelt',\n",
                            "   41: 'Rumson',\n",
                            "   42: 'Sea Bright',\n",
                            "   43: 'Sea Girt',\n",
                            "   44: 'Shrewsbury Boro',\n",
                            "   45: 'Shrewsbury Twp',\n",
                            "   46: 'Lake Como',\n",
                            "   47: 'Spring Lake',\n",
                            "   48: 'Spring Lake Heights',\n",
                            "   49: 'Tinton Falls',\n",
                            "   50: 'Union Beach',\n",
                            "   51: 'Upper Freehold',\n",
                            "   52: 'Wall',\n",
                            "   53: 'West Long Branch'}},\n",
                            " 14: {'cn': 'Morris',\n",
                            "  'mc2mn': {1: 'Boonton Town',\n",
                            "   2: 'Boonton Twp',\n",
                            "   3: 'Butler',\n",
                            "   4: 'Chatham Boro',\n",
                            "   5: 'Chatham Twp',\n",
                            "   6: 'Chester Boro',\n",
                            "   7: 'Chester Twp',\n",
                            "   8: 'Denville',\n",
                            "   9: 'Dover',\n",
                            "   10: 'East Hanover',\n",
                            "   11: 'Florham Park',\n",
                            "   12: 'Hanover',\n",
                            "   13: 'Harding',\n",
                            "   14: 'Jefferson',\n",
                            "   15: 'Kinnelon',\n",
                            "   16: 'Lincoln Park',\n",
                            "   17: 'Madison',\n",
                            "   18: 'Mendham Boro',\n",
                            "   19: 'Mendham Twp',\n",
                            "   20: 'Mine Hill',\n",
                            "   21: 'Montville',\n",
                            "   22: 'Morris',\n",
                            "   23: 'Morris Plains',\n",
                            "   24: 'Morristown',\n",
                            "   25: 'Mountain Lakes',\n",
                            "   26: 'Mount Arlington',\n",
                            "   27: 'Mount Olive',\n",
                            "   28: 'Netcong',\n",
                            "   29: 'Parsippany-Troy Hills',\n",
                            "   30: 'Long Hill',\n",
                            "   31: 'Pequannock',\n",
                            "   32: 'Randolph',\n",
                            "   33: 'Riverdale',\n",
                            "   34: 'Rockaway Boro',\n",
                            "   35: 'Rockaway Twp',\n",
                            "   36: 'Roxbury',\n",
                            "   37: 'Victory Gardens',\n",
                            "   38: 'Washington',\n",
                            "   39: 'Wharton'}},\n",
                            " 15: {'cn': 'Ocean',\n",
                            "  'mc2mn': {1: 'Barnegat',\n",
                            "   2: 'Barnegat Light',\n",
                            "   3: 'Bay Head',\n",
                            "   4: 'Beach Haven',\n",
                            "   5: 'Beachwood',\n",
                            "   6: 'Berkeley',\n",
                            "   7: 'Brick',\n",
                            "   8: 'Toms River',\n",
                            "   9: 'Eagleswood',\n",
                            "   10: 'Harvey Cedars',\n",
                            "   11: 'Island Heights',\n",
                            "   12: 'Jackson',\n",
                            "   13: 'Lacey',\n",
                            "   14: 'Lakehurst',\n",
                            "   15: 'Lakewood',\n",
                            "   16: 'Lavallette',\n",
                            "   17: 'Little Egg Harbor',\n",
                            "   18: 'Long Beach',\n",
                            "   19: 'Manchester',\n",
                            "   20: 'Mantoloking',\n",
                            "   21: 'Ocean',\n",
                            "   22: 'Ocean Gate',\n",
                            "   23: 'Pine Beach',\n",
                            "   24: 'Plumsted',\n",
                            "   25: 'Point Pleasant',\n",
                            "   26: 'Point Pleasant Beach',\n",
                            "   27: 'Seaside Heights',\n",
                            "   28: 'Seaside Park',\n",
                            "   29: 'Ship Bottom',\n",
                            "   30: 'South Toms River',\n",
                            "   31: 'Stafford',\n",
                            "   32: 'Surf City',\n",
                            "   33: 'Tuckerton'}},\n",
                            " 16: {'cn': 'Passaic',\n",
                            "  'mc2mn': {1: 'Bloomingdale',\n",
                            "   2: 'Clifton',\n",
                            "   3: 'Haledon',\n",
                            "   4: 'Hawthorne',\n",
                            "   5: 'Little Falls',\n",
                            "   6: 'North Haledon',\n",
                            "   7: 'Passaic',\n",
                            "   8: 'Paterson',\n",
                            "   9: 'Pompton Lakes',\n",
                            "   10: 'Prospect Park',\n",
                            "   11: 'Ringwood',\n",
                            "   12: 'Totowa',\n",
                            "   13: 'Wanaque',\n",
                            "   14: 'Wayne',\n",
                            "   15: 'West Milford',\n",
                            "   16: 'Woodland Park'}},\n",
                            " 17: {'cn': 'Salem',\n",
                            "  'mc2mn': {1: 'Alloway',\n",
                            "   2: 'Carneys Point',\n",
                            "   3: 'Elmer',\n",
                            "   4: 'Elsinboro',\n",
                            "   5: 'Lower Alloways Creek',\n",
                            "   6: 'Mannington',\n",
                            "   7: 'Oldmans',\n",
                            "   8: 'Penns Grove',\n",
                            "   9: 'Pennsville',\n",
                            "   10: 'Pilesgrove',\n",
                            "   11: 'Pittsgrove',\n",
                            "   12: 'Quinton',\n",
                            "   13: 'Salem',\n",
                            "   14: 'Upper Pittsgrove',\n",
                            "   15: 'Woodstown'}},\n",
                            " 18: {'cn': 'Somerset',\n",
                            "  'mc2mn': {1: 'Bedminster',\n",
                            "   2: 'Bernards',\n",
                            "   3: 'Bernardsville',\n",
                            "   4: 'Bound Brook',\n",
                            "   5: 'Branchburg',\n",
                            "   6: 'Bridgewater',\n",
                            "   7: 'Far Hills',\n",
                            "   8: 'Franklin',\n",
                            "   9: 'Green Brook',\n",
                            "   10: 'Hillsborough',\n",
                            "   11: 'Manville',\n",
                            "   12: 'Millstone',\n",
                            "   13: 'Montgomery',\n",
                            "   14: 'North Plainfield',\n",
                            "   15: 'Peapack-Gladstone',\n",
                            "   16: 'Raritan',\n",
                            "   17: 'Rocky Hill',\n",
                            "   18: 'Somerville',\n",
                            "   19: 'South Bound Brook',\n",
                            "   20: 'Warren',\n",
                            "   21: 'Watchung'}},\n",
                            " 19: {'cn': 'Sussex',\n",
                            "  'mc2mn': {1: 'Andover Boro',\n",
                            "   2: 'Andover Twp',\n",
                            "   3: 'Branchville',\n",
                            "   4: 'Byram',\n",
                            "   5: 'Frankford',\n",
                            "   6: 'Franklin',\n",
                            "   7: 'Fredon',\n",
                            "   8: 'Green',\n",
                            "   9: 'Hamburg',\n",
                            "   10: 'Hampton',\n",
                            "   11: 'Hardyston',\n",
                            "   12: 'Hopatcong',\n",
                            "   13: 'Lafayette',\n",
                            "   14: 'Montague',\n",
                            "   15: 'Newton',\n",
                            "   16: 'Ogdensburg',\n",
                            "   17: 'Sandyston',\n",
                            "   18: 'Sparta',\n",
                            "   19: 'Stanhope',\n",
                            "   20: 'Stillwater',\n",
                            "   21: 'Sussex',\n",
                            "   22: 'Vernon',\n",
                            "   23: 'Walpack',\n",
                            "   24: 'Wantage'}},\n",
                            " 20: {'cn': 'Union',\n",
                            "  'mc2mn': {1: 'Berkeley Heights',\n",
                            "   2: 'Clark',\n",
                            "   3: 'Cranford',\n",
                            "   4: 'Elizabeth',\n",
                            "   5: 'Fanwood',\n",
                            "   6: 'Garwood',\n",
                            "   7: 'Hillside',\n",
                            "   8: 'Kenilworth',\n",
                            "   9: 'Linden',\n",
                            "   10: 'Mountainside',\n",
                            "   11: 'New Providence',\n",
                            "   12: 'Plainfield',\n",
                            "   13: 'Rahway',\n",
                            "   14: 'Roselle',\n",
                            "   15: 'Roselle Park',\n",
                            "   16: 'Scotch Plains',\n",
                            "   17: 'Springfield',\n",
                            "   18: 'Summit',\n",
                            "   19: 'Union',\n",
                            "   20: 'Westfield',\n",
                            "   21: 'Winfield'}},\n",
                            " 21: {'cn': 'Warren',\n",
                            "  'mc2mn': {1: 'Allamuchy',\n",
                            "   2: 'Alpha',\n",
                            "   3: 'Belvidere',\n",
                            "   4: 'Blairstown',\n",
                            "   5: 'Franklin',\n",
                            "   6: 'Frelinghuysen',\n",
                            "   7: 'Greenwich',\n",
                            "   8: 'Hackettstown',\n",
                            "   9: 'Hardwick',\n",
                            "   10: 'Harmony',\n",
                            "   11: 'Hope',\n",
                            "   12: 'Independence',\n",
                            "   13: 'Knowlton',\n",
                            "   14: 'Liberty',\n",
                            "   15: 'Lopatcong',\n",
                            "   16: 'Mansfield',\n",
                            "   17: 'Oxford',\n",
                            "   19: 'Phillipsburg',\n",
                            "   20: 'Pohatcong',\n",
                            "   21: 'Washington Boro',\n",
                            "   22: 'Washington Twp',\n",
                            "   23: 'White'}}}"
                        ]
                    },
                    "execution_count": 32,
                    "metadata": {},
                    "output_type": "execute_result"
                }
            ],
            "source": [
                "def county_obj(df):\n",
                "    return dict(\n",
                "        cn=singleton(df.cn.tolist()),\n",
                "        mc2mn=df.set_index('mc')['name'].to_dict(),\n",
                "    )\n",
                "    \n",
                "cc2mc2mn = (\n",
                "    names\n",
                "    .groupby('cc')\n",
                "    .apply(county_obj)\n",
                "    .to_dict()\n",
                ")\n",
                "cc2mc2mn"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": 33,
            "metadata": {
                "tags": []
            },
            "outputs": [],
            "source": [
                "with open(CC2MC2MN, 'w') as f:\n",
                "    json.dump(cc2mc2mn, f, indent=2)"
            ]
        }
    ],
    "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
}