njdot/geocode-mps.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "600a3f94",
   "metadata": {},
   "outputs": [],
   "source": [
    "from utz import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "50b40791",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "34479"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from nj_crashes.sri.sri import get_sris\n",
    "sris = get_sris()\n",
    "len(sris)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "e93ae5c6",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 9.98 s, sys: 5.01 s, total: 15 s\n",
      "Wall time: 14 s\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>County Code</th>\n",
       "      <th>Municipality Code</th>\n",
       "      <th>Department Case Number</th>\n",
       "      <th>County Name</th>\n",
       "      <th>Municipality Name</th>\n",
       "      <th>Police Department Code</th>\n",
       "      <th>Police Department</th>\n",
       "      <th>Police Station</th>\n",
       "      <th>Total Killed</th>\n",
       "      <th>Total Injured</th>\n",
       "      <th>Pedestrians Killed</th>\n",
       "      <th>Pedestrians Injured</th>\n",
       "      <th>Severity</th>\n",
       "      <th>Intersection</th>\n",
       "      <th>Alcohol Involved</th>\n",
       "      <th>HazMat Involved</th>\n",
       "      <th>Crash Type Code</th>\n",
       "      <th>Total Vehicles Involved</th>\n",
       "      <th>Crash Location</th>\n",
       "      <th>Location Direction</th>\n",
       "      <th>Route</th>\n",
       "      <th>Route Suffix</th>\n",
       "      <th>SRI (Standard Route Identifier)</th>\n",
       "      <th>Mile Post</th>\n",
       "      <th>Road System</th>\n",
       "      <th>Road Character</th>\n",
       "      <th>Road Surface Type</th>\n",
       "      <th>Surface Condition</th>\n",
       "      <th>Light Condition</th>\n",
       "      <th>Environmental Condition</th>\n",
       "      <th>Road Divided By</th>\n",
       "      <th>Temporary Traffic Control Zone</th>\n",
       "      <th>Distance To Cross Street</th>\n",
       "      <th>Unit Of Measurement</th>\n",
       "      <th>Direction From Cross Street</th>\n",
       "      <th>Cross Street Name</th>\n",
       "      <th>Is Ramp</th>\n",
       "      <th>Ramp To/From Route Name</th>\n",
       "      <th>Ramp To/From Route Direction</th>\n",
       "      <th>Posted Speed</th>\n",
       "      <th>Posted Speed Cross Street</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Cell Phone In Use Flag</th>\n",
       "      <th>Other Property Damage</th>\n",
       "      <th>Reporting Badge No.</th>\n",
       "      <th>Date</th>\n",
       "      <th>Road Horizontal Alignment</th>\n",
       "      <th>Road Grade</th>\n",
       "      <th>First Harmful Event</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>#2001-17846</td>\n",
       "      <td>ATLANTIC</td>\n",
       "      <td>ABSECON CITY</td>\n",
       "      <td>01</td>\n",
       "      <td>ABSECON CITY</td>\n",
       "      <td>MUNICIPAL COMP?</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>03</td>\n",
       "      <td>2</td>\n",
       "      <td>CALDERON AVENUE</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>07</td>\n",
       "      <td>02</td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>06</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td>100</td>\n",
       "      <td>FE</td>\n",
       "      <td>N</td>\n",
       "      <td>RT 30</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>25</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>NEVER SAW V-1 MINOR DAMAGE - NO INJURIES REPOR...</td>\n",
       "      <td>830</td>\n",
       "      <td>2001-12-21 18:34:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01-00029</td>\n",
       "      <td>ATLANTIC</td>\n",
       "      <td>ABSECON CITY</td>\n",
       "      <td>1</td>\n",
       "      <td>ABSECON</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>06</td>\n",
       "      <td>2</td>\n",
       "      <td>RITZ DRIVE</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>07</td>\n",
       "      <td>01</td>\n",
       "      <td>02</td>\n",
       "      <td>03</td>\n",
       "      <td>06</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>25</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>836</td>\n",
       "      <td>2001-01-01 09:30:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01-004615</td>\n",
       "      <td>ATLANTIC</td>\n",
       "      <td>ABSECON CITY</td>\n",
       "      <td>1</td>\n",
       "      <td>ABSECON</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>I</td>\n",
       "      <td>I</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>03</td>\n",
       "      <td>2</td>\n",
       "      <td>MORTON AVENUE</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>07</td>\n",
       "      <td>01</td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td>AT</td>\n",
       "      <td></td>\n",
       "      <td>NEW YORK AVENUE</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>25</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>836</td>\n",
       "      <td>2001-04-10 14:44:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01-004880</td>\n",
       "      <td>ATLANTIC</td>\n",
       "      <td>ABSECON CITY</td>\n",
       "      <td>1</td>\n",
       "      <td>ABSECON</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>I</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>01</td>\n",
       "      <td>2</td>\n",
       "      <td>RT 30</td>\n",
       "      <td></td>\n",
       "      <td>30</td>\n",
       "      <td></td>\n",
       "      <td>00000030__</td>\n",
       "      <td>51.10</td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>45</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>WITNESS-PETRIA GIBSON AND ANGELO HERSH</td>\n",
       "      <td>886</td>\n",
       "      <td>2001-04-15 13:56:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01-004912</td>\n",
       "      <td>ATLANTIC</td>\n",
       "      <td>ABSECON CITY</td>\n",
       "      <td>1</td>\n",
       "      <td>ABSECON</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>I</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>01</td>\n",
       "      <td>2</td>\n",
       "      <td>CR 651</td>\n",
       "      <td></td>\n",
       "      <td>651</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>05</td>\n",
       "      <td>02</td>\n",
       "      <td>02</td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>02</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td>AT</td>\n",
       "      <td></td>\n",
       "      <td>CR 630</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>35</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>*BETH VEHICLES-RELIANCE INSURANCE CO.(215)864-...</td>\n",
       "      <td>836</td>\n",
       "      <td>2001-04-16 10:29:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <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",
       "      <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",
       "      <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>6077298</th>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>B150-2021-00117A</td>\n",
       "      <td>WARREN</td>\n",
       "      <td>WHITE TWP</td>\n",
       "      <td>02</td>\n",
       "      <td>NEW JERSEY STATE POLICE</td>\n",
       "      <td>WASHINGTON - SQ</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>I</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>WARREN COUNTY 620</td>\n",
       "      <td>N</td>\n",
       "      <td>620</td>\n",
       "      <td></td>\n",
       "      <td>21000620__</td>\n",
       "      <td>0.00</td>\n",
       "      <td>05</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>06</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td>AT</td>\n",
       "      <td></td>\n",
       "      <td>ROUTE 519 / S BRIDGEVILLE RD</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50</td>\n",
       "      <td>50</td>\n",
       "      <td>40.80707</td>\n",
       "      <td>75.07559</td>\n",
       "      <td>N</td>\n",
       "      <td>Damage to 10ft of Country road guardrail.</td>\n",
       "      <td>7837</td>\n",
       "      <td>2021-05-26 21:08:00</td>\n",
       "      <td>02</td>\n",
       "      <td>04</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077299</th>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>B150-2021-00118A</td>\n",
       "      <td>WARREN</td>\n",
       "      <td>WHITE TWP</td>\n",
       "      <td>02</td>\n",
       "      <td>NEW JERSEY STATE POLICE</td>\n",
       "      <td>WASHINGTON - SQ</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>I</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>01</td>\n",
       "      <td>2</td>\n",
       "      <td>MANUNKA CHUNK RD / UPPER SEREPTA RD **</td>\n",
       "      <td>W</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>21231034__</td>\n",
       "      <td>1.73</td>\n",
       "      <td>07</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td>AT</td>\n",
       "      <td></td>\n",
       "      <td>MASSENATTS RD</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>40</td>\n",
       "      <td>50</td>\n",
       "      <td>40.84466</td>\n",
       "      <td>75.05549</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>8610</td>\n",
       "      <td>2021-05-25 07:26:00</td>\n",
       "      <td>01</td>\n",
       "      <td>04</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077300</th>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>B150-2021-00128A</td>\n",
       "      <td>WARREN</td>\n",
       "      <td>WHITE TWP</td>\n",
       "      <td>02</td>\n",
       "      <td>NEW JERSEY STATE POLICE</td>\n",
       "      <td>WASHINGTON - SQ</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>ROUTE 519</td>\n",
       "      <td>S</td>\n",
       "      <td>519</td>\n",
       "      <td></td>\n",
       "      <td>00000519__</td>\n",
       "      <td>44.00</td>\n",
       "      <td>05</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>02</td>\n",
       "      <td>05</td>\n",
       "      <td>04</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50</td>\n",
       "      <td></td>\n",
       "      <td>40.80564</td>\n",
       "      <td>75.07627</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>8514</td>\n",
       "      <td>2021-06-03 20:17:00</td>\n",
       "      <td>03</td>\n",
       "      <td>04</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077301</th>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>B150-2021-00130A</td>\n",
       "      <td>WARREN</td>\n",
       "      <td>WHITE TWP</td>\n",
       "      <td>02</td>\n",
       "      <td>NEW JERSEY STATE POLICE</td>\n",
       "      <td>WASHINGTON - SQ</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>US 46</td>\n",
       "      <td>E</td>\n",
       "      <td>46</td>\n",
       "      <td></td>\n",
       "      <td>00000046__</td>\n",
       "      <td>7.50</td>\n",
       "      <td>02</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>07</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50</td>\n",
       "      <td></td>\n",
       "      <td>40.84149</td>\n",
       "      <td>75.04968</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>8365</td>\n",
       "      <td>2021-06-06 00:00:00</td>\n",
       "      <td>01</td>\n",
       "      <td>04</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077302</th>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>B150-2021-00132A</td>\n",
       "      <td>WARREN</td>\n",
       "      <td>WHITE TWP</td>\n",
       "      <td>02</td>\n",
       "      <td>NEW JERSEY STATE POLICE</td>\n",
       "      <td>WASHINGTON - SQ</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>03</td>\n",
       "      <td>2</td>\n",
       "      <td>FIELDSTONE CT</td>\n",
       "      <td>W</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>07</td>\n",
       "      <td></td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>130 COUNTY ROAD 519</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>40.81220</td>\n",
       "      <td>75.04940</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>8395</td>\n",
       "      <td>2021-06-06 19:33:00</td>\n",
       "      <td>01</td>\n",
       "      <td>04</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6077303 rows × 50 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        County Code Municipality Code Department Case Number County Name  \\\n",
       "0                01                01            #2001-17846    ATLANTIC   \n",
       "1                01                01               01-00029    ATLANTIC   \n",
       "2                01                01              01-004615    ATLANTIC   \n",
       "3                01                01              01-004880    ATLANTIC   \n",
       "4                01                01              01-004912    ATLANTIC   \n",
       "...             ...               ...                    ...         ...   \n",
       "6077298          21                23       B150-2021-00117A      WARREN   \n",
       "6077299          21                23       B150-2021-00118A      WARREN   \n",
       "6077300          21                23       B150-2021-00128A      WARREN   \n",
       "6077301          21                23       B150-2021-00130A      WARREN   \n",
       "6077302          21                23       B150-2021-00132A      WARREN   \n",
       "\n",
       "        Municipality Name Police Department Code        Police Department  \\\n",
       "0            ABSECON CITY                     01             ABSECON CITY   \n",
       "1            ABSECON CITY                      1                  ABSECON   \n",
       "2            ABSECON CITY                      1                  ABSECON   \n",
       "3            ABSECON CITY                      1                  ABSECON   \n",
       "4            ABSECON CITY                      1                  ABSECON   \n",
       "...                   ...                    ...                      ...   \n",
       "6077298         WHITE TWP                     02  NEW JERSEY STATE POLICE   \n",
       "6077299         WHITE TWP                     02  NEW JERSEY STATE POLICE   \n",
       "6077300         WHITE TWP                     02  NEW JERSEY STATE POLICE   \n",
       "6077301         WHITE TWP                     02  NEW JERSEY STATE POLICE   \n",
       "6077302         WHITE TWP                     02  NEW JERSEY STATE POLICE   \n",
       "\n",
       "          Police Station  Total Killed  Total Injured  Pedestrians Killed  \\\n",
       "0        MUNICIPAL COMP?             0              0                   0   \n",
       "1                                    0              0                   0   \n",
       "2                                    0              4                   0   \n",
       "3                                    0              1                   0   \n",
       "4                                    0              0                   0   \n",
       "...                  ...           ...            ...                 ...   \n",
       "6077298  WASHINGTON - SQ             0              0                   0   \n",
       "6077299  WASHINGTON - SQ             0              0                   0   \n",
       "6077300  WASHINGTON - SQ             0              0                   0   \n",
       "6077301  WASHINGTON - SQ             0              0                   0   \n",
       "6077302  WASHINGTON - SQ             0              0                   0   \n",
       "\n",
       "         Pedestrians Injured Severity Intersection  Alcohol Involved  \\\n",
       "0                          0        P            B             False   \n",
       "1                          0        P            B             False   \n",
       "2                          0        I            I             False   \n",
       "3                          0        I            B             False   \n",
       "4                          0        P            I             False   \n",
       "...                      ...      ...          ...               ...   \n",
       "6077298                    0        P            I             False   \n",
       "6077299                    0        P            I             False   \n",
       "6077300                    0        P            B             False   \n",
       "6077301                    0        P            B             False   \n",
       "6077302                    0        P            B             False   \n",
       "\n",
       "         HazMat Involved Crash Type Code  Total Vehicles Involved  \\\n",
       "0                  False              03                        2   \n",
       "1                  False              06                        2   \n",
       "2                  False              03                        2   \n",
       "3                  False              01                        2   \n",
       "4                  False              01                        2   \n",
       "...                  ...             ...                      ...   \n",
       "6077298            False              11                        1   \n",
       "6077299            False              01                        2   \n",
       "6077300            False              12                        1   \n",
       "6077301            False              12                        1   \n",
       "6077302            False              03                        2   \n",
       "\n",
       "                                 Crash Location Location Direction Route  \\\n",
       "0                               CALDERON AVENUE                            \n",
       "1                                    RITZ DRIVE                            \n",
       "2                                 MORTON AVENUE                            \n",
       "3                                         RT 30                       30   \n",
       "4                                        CR 651                      651   \n",
       "...                                         ...                ...   ...   \n",
       "6077298                       WARREN COUNTY 620                  N   620   \n",
       "6077299  MANUNKA CHUNK RD / UPPER SEREPTA RD **                  W         \n",
       "6077300                               ROUTE 519                  S   519   \n",
       "6077301                                   US 46                  E    46   \n",
       "6077302                           FIELDSTONE CT                  W         \n",
       "\n",
       "        Route Suffix SRI (Standard Route Identifier)  Mile Post Road System  \\\n",
       "0                                                           NaN          07   \n",
       "1                                                           NaN          07   \n",
       "2                                                           NaN          07   \n",
       "3                                         00000030__      51.10          02   \n",
       "4                                                           NaN          05   \n",
       "...              ...                             ...        ...         ...   \n",
       "6077298                                   21000620__       0.00          05   \n",
       "6077299                                   21231034__       1.73          07   \n",
       "6077300                                   00000519__      44.00          05   \n",
       "6077301                                   00000046__       7.50          02   \n",
       "6077302                                                     NaN          07   \n",
       "\n",
       "        Road Character Road Surface Type Surface Condition Light Condition  \\\n",
       "0                   02                02                01              06   \n",
       "1                   01                02                03              06   \n",
       "2                   01                02                01              01   \n",
       "3                   01                02                01              01   \n",
       "4                   02                02                02              01   \n",
       "...                ...               ...               ...             ...   \n",
       "6077298                               02                01              06   \n",
       "6077299                               02                01              01   \n",
       "6077300                               02                02              05   \n",
       "6077301                               02                01              07   \n",
       "6077302                               05                01              01   \n",
       "\n",
       "        Environmental Condition Road Divided By  \\\n",
       "0                            01              05   \n",
       "1                            01              05   \n",
       "2                            01              05   \n",
       "3                            01              05   \n",
       "4                            02              05   \n",
       "...                         ...             ...   \n",
       "6077298                      01              05   \n",
       "6077299                      01              05   \n",
       "6077300                      04              05   \n",
       "6077301                      01              05   \n",
       "6077302                      01              05   \n",
       "\n",
       "        Temporary Traffic Control Zone Distance To Cross Street  \\\n",
       "0                                   01                      100   \n",
       "1                                   01                            \n",
       "2                                   01                            \n",
       "3                                   01                            \n",
       "4                                   01                            \n",
       "...                                ...                      ...   \n",
       "6077298                             01                            \n",
       "6077299                             01                            \n",
       "6077300                             01                            \n",
       "6077301                             01                            \n",
       "6077302                             01                            \n",
       "\n",
       "        Unit Of Measurement Direction From Cross Street  \\\n",
       "0                        FE                           N   \n",
       "1                                                         \n",
       "2                        AT                               \n",
       "3                                                         \n",
       "4                        AT                               \n",
       "...                     ...                         ...   \n",
       "6077298                  AT                               \n",
       "6077299                  AT                               \n",
       "6077300                                                   \n",
       "6077301                                                   \n",
       "6077302                                                   \n",
       "\n",
       "                    Cross Street Name Is Ramp Ramp To/From Route Name  \\\n",
       "0                               RT 30                                   \n",
       "1                                                                       \n",
       "2                     NEW YORK AVENUE                                   \n",
       "3                                                                       \n",
       "4                              CR 630                                   \n",
       "...                               ...     ...                     ...   \n",
       "6077298  ROUTE 519 / S BRIDGEVILLE RD                                   \n",
       "6077299                 MASSENATTS RD                                   \n",
       "6077300                                                                 \n",
       "6077301                                                                 \n",
       "6077302           130 COUNTY ROAD 519                                   \n",
       "\n",
       "        Ramp To/From Route Direction Posted Speed Posted Speed Cross Street  \\\n",
       "0                                              25                             \n",
       "1                                              25                             \n",
       "2                                              25                             \n",
       "3                                              45                             \n",
       "4                                              35                             \n",
       "...                              ...          ...                       ...   \n",
       "6077298                                        50                        50   \n",
       "6077299                                        40                        50   \n",
       "6077300                                        50                             \n",
       "6077301                                        50                             \n",
       "6077302                                        15                         5   \n",
       "\n",
       "         Latitude  Longitude Cell Phone In Use Flag  \\\n",
       "0             NaN        NaN                      N   \n",
       "1             NaN        NaN                      N   \n",
       "2             NaN        NaN                      N   \n",
       "3             NaN        NaN                      N   \n",
       "4             NaN        NaN                      N   \n",
       "...           ...        ...                    ...   \n",
       "6077298  40.80707   75.07559                      N   \n",
       "6077299  40.84466   75.05549                      N   \n",
       "6077300  40.80564   75.07627                      N   \n",
       "6077301  40.84149   75.04968                      N   \n",
       "6077302  40.81220   75.04940                      N   \n",
       "\n",
       "                                     Other Property Damage  \\\n",
       "0        NEVER SAW V-1 MINOR DAMAGE - NO INJURIES REPOR...   \n",
       "1                                                            \n",
       "2                                                            \n",
       "3                   WITNESS-PETRIA GIBSON AND ANGELO HERSH   \n",
       "4        *BETH VEHICLES-RELIANCE INSURANCE CO.(215)864-...   \n",
       "...                                                    ...   \n",
       "6077298          Damage to 10ft of Country road guardrail.   \n",
       "6077299                                                      \n",
       "6077300                                                      \n",
       "6077301                                                      \n",
       "6077302                                                      \n",
       "\n",
       "        Reporting Badge No.                Date Road Horizontal Alignment  \\\n",
       "0                       830 2001-12-21 18:34:00                       NaN   \n",
       "1                       836 2001-01-01 09:30:00                       NaN   \n",
       "2                       836 2001-04-10 14:44:00                       NaN   \n",
       "3                       886 2001-04-15 13:56:00                       NaN   \n",
       "4                       836 2001-04-16 10:29:00                       NaN   \n",
       "...                     ...                 ...                       ...   \n",
       "6077298                7837 2021-05-26 21:08:00                        02   \n",
       "6077299                8610 2021-05-25 07:26:00                        01   \n",
       "6077300                8514 2021-06-03 20:17:00                        03   \n",
       "6077301                8365 2021-06-06 00:00:00                        01   \n",
       "6077302                8395 2021-06-06 19:33:00                        01   \n",
       "\n",
       "        Road Grade First Harmful Event  \n",
       "0              NaN                 NaN  \n",
       "1              NaN                 NaN  \n",
       "2              NaN                 NaN  \n",
       "3              NaN                 NaN  \n",
       "4              NaN                 NaN  \n",
       "...            ...                 ...  \n",
       "6077298         04                  46  \n",
       "6077299         04                  26  \n",
       "6077300         04                  24  \n",
       "6077301         04                  24  \n",
       "6077302         04                  26  \n",
       "\n",
       "[6077303 rows x 50 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "from njdot.data import START_YEAR, END_YEAR\n",
    "c = concat([\n",
    "    read_parquet(f'data/{year}/NewJersey{year}Accidents.pqt')\n",
    "    for year in range(START_YEAR, END_YEAR)\n",
    "]).reset_index(drop=True)\n",
    "c"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "04ba7cd8",
   "metadata": {},
   "outputs": [],
   "source": [
    "sri = c['SRI (Standard Route Identifier)'].rename('sri')\n",
    "mp = c['Mile Post'].rename('mp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c7306368",
   "metadata": {},
   "outputs": [],
   "source": [
    "from nj_crashes.sri.sri import LL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "b5b371a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "from dataclasses import asdict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "ed3540d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "def geocode_mp(r):\n",
    "    sri = r.sri\n",
    "    mp = r.mp\n",
    "    if isna(sri):\n",
    "        return dict(reason=\"No SRI\")\n",
    "    if isna(mp):\n",
    "        return dict(reason=\"No MP\")\n",
    "    if sri not in sris:\n",
    "        return dict(reason='SRI not found')\n",
    "    mp_lls = sris[sri]\n",
    "    ll = mp_lls.get(mp)\n",
    "    if ll:\n",
    "        return asdict(ll)\n",
    "    else:\n",
    "        return dict(reason=\"MP didn't geocode\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "8a085066",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 59.6 s, sys: 1.19 s, total: 1min\n",
      "Wall time: 1min 1s\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>reason</th>\n",
       "      <th>lon</th>\n",
       "      <th>lat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>No MP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>No MP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>No MP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-74.512308</td>\n",
       "      <td>39.432705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>No MP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077298</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-75.075607</td>\n",
       "      <td>40.807156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077299</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-75.056122</td>\n",
       "      <td>40.844345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077300</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-75.015149</td>\n",
       "      <td>40.863108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077301</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-75.046404</td>\n",
       "      <td>40.840421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077302</th>\n",
       "      <td>No MP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6077303 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        reason        lon        lat\n",
       "0        No MP        NaN        NaN\n",
       "1        No MP        NaN        NaN\n",
       "2        No MP        NaN        NaN\n",
       "3          NaN -74.512308  39.432705\n",
       "4        No MP        NaN        NaN\n",
       "...        ...        ...        ...\n",
       "6077298    NaN -75.075607  40.807156\n",
       "6077299    NaN -75.056122  40.844345\n",
       "6077300    NaN -75.015149  40.863108\n",
       "6077301    NaN -75.046404  40.840421\n",
       "6077302  No MP        NaN        NaN\n",
       "\n",
       "[6077303 rows x 3 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "geocoded_mps = DF(sxs(sri, mp).apply(geocode_mp, axis=1).tolist())\n",
    "geocoded_mps"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "180e6570",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "NaN                  3479827\n",
       "No MP                2367547\n",
       "MP didn't geocode     141019\n",
       "SRI not found          88910\n",
       "Name: reason, dtype: int64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geocoded_mps.reason.value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "3690d139",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "reason    3479827\n",
       "lon       2597476\n",
       "lat       2597476\n",
       "dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geocoded_mps.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "98dbefb8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>reason</th>\n",
       "      <th>lon</th>\n",
       "      <th>lat</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Latitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>No MP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>No MP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>No MP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-74.512308</td>\n",
       "      <td>39.432705</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>No MP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077298</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-75.075607</td>\n",
       "      <td>40.807156</td>\n",
       "      <td>75.07559</td>\n",
       "      <td>40.80707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077299</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-75.056122</td>\n",
       "      <td>40.844345</td>\n",
       "      <td>75.05549</td>\n",
       "      <td>40.84466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077300</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-75.015149</td>\n",
       "      <td>40.863108</td>\n",
       "      <td>75.07627</td>\n",
       "      <td>40.80564</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077301</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-75.046404</td>\n",
       "      <td>40.840421</td>\n",
       "      <td>75.04968</td>\n",
       "      <td>40.84149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077302</th>\n",
       "      <td>No MP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75.04940</td>\n",
       "      <td>40.81220</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6077303 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        reason        lon        lat  Longitude  Latitude\n",
       "0        No MP        NaN        NaN        NaN       NaN\n",
       "1        No MP        NaN        NaN        NaN       NaN\n",
       "2        No MP        NaN        NaN        NaN       NaN\n",
       "3          NaN -74.512308  39.432705        NaN       NaN\n",
       "4        No MP        NaN        NaN        NaN       NaN\n",
       "...        ...        ...        ...        ...       ...\n",
       "6077298    NaN -75.075607  40.807156   75.07559  40.80707\n",
       "6077299    NaN -75.056122  40.844345   75.05549  40.84466\n",
       "6077300    NaN -75.015149  40.863108   75.07627  40.80564\n",
       "6077301    NaN -75.046404  40.840421   75.04968  40.84149\n",
       "6077302  No MP        NaN        NaN   75.04940  40.81220\n",
       "\n",
       "[6077303 rows x 5 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lat = c.Latitude\n",
    "lon = c.Longitude\n",
    "#ll = sxs(lon, lat)\n",
    "ll = sxs(geocoded_mps, lon, lat)\n",
    "ll"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "97bac380",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>has Longitude</th>\n",
       "      <th>False</th>\n",
       "      <th>True</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>has lon</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>False</th>\n",
       "      <td>2504641</td>\n",
       "      <td>92835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>True</th>\n",
       "      <td>2317307</td>\n",
       "      <td>1162520</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "has Longitude    False    True \n",
       "has lon                        \n",
       "False          2504641    92835\n",
       "True           2317307  1162520"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(\n",
    "    (~ll.lon.isna()).rename('has lon'),\n",
    "    (~ll.Longitude.isna()).rename('has Longitude'),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "9f486ac4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4914783"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "has_rsn = ~ll.reason.isna()\n",
    "has_ll = ~ll.Latitude.isna()\n",
    "print(f'{has_rsn.sum()} crashes failed to geocode based on SRI / Mile Post')\n",
    "print(f'{has_ll.sum()} crashes have Latitude/Longitude')\n",
    "print(f'{(has_rsn & has_ll).sum()} crashes have only have Latitude/Longitude')\n",
    "print\n",
    "(~(ll.reason.isna()) | ll.Latitude.isna()).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "91b9b3fc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>County Code</th>\n",
       "      <th>Municipality Code</th>\n",
       "      <th>Department Case Number</th>\n",
       "      <th>County Name</th>\n",
       "      <th>Municipality Name</th>\n",
       "      <th>Police Department Code</th>\n",
       "      <th>Police Department</th>\n",
       "      <th>Police Station</th>\n",
       "      <th>Total Killed</th>\n",
       "      <th>Total Injured</th>\n",
       "      <th>Pedestrians Killed</th>\n",
       "      <th>Pedestrians Injured</th>\n",
       "      <th>Severity</th>\n",
       "      <th>Intersection</th>\n",
       "      <th>Alcohol Involved</th>\n",
       "      <th>HazMat Involved</th>\n",
       "      <th>Crash Type Code</th>\n",
       "      <th>Total Vehicles Involved</th>\n",
       "      <th>Crash Location</th>\n",
       "      <th>Location Direction</th>\n",
       "      <th>Route</th>\n",
       "      <th>Route Suffix</th>\n",
       "      <th>SRI (Standard Route Identifier)</th>\n",
       "      <th>Mile Post</th>\n",
       "      <th>Road System</th>\n",
       "      <th>Road Character</th>\n",
       "      <th>Road Surface Type</th>\n",
       "      <th>Surface Condition</th>\n",
       "      <th>Light Condition</th>\n",
       "      <th>Environmental Condition</th>\n",
       "      <th>Road Divided By</th>\n",
       "      <th>Temporary Traffic Control Zone</th>\n",
       "      <th>Distance To Cross Street</th>\n",
       "      <th>Unit Of Measurement</th>\n",
       "      <th>Direction From Cross Street</th>\n",
       "      <th>Cross Street Name</th>\n",
       "      <th>Is Ramp</th>\n",
       "      <th>Ramp To/From Route Name</th>\n",
       "      <th>Ramp To/From Route Direction</th>\n",
       "      <th>Posted Speed</th>\n",
       "      <th>Posted Speed Cross Street</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Cell Phone In Use Flag</th>\n",
       "      <th>Other Property Damage</th>\n",
       "      <th>Reporting Badge No.</th>\n",
       "      <th>Date</th>\n",
       "      <th>Road Horizontal Alignment</th>\n",
       "      <th>Road Grade</th>\n",
       "      <th>First Harmful Event</th>\n",
       "      <th>reason</th>\n",
       "      <th>lon</th>\n",
       "      <th>lat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>#2001-17846</td>\n",
       "      <td>ATLANTIC</td>\n",
       "      <td>ABSECON CITY</td>\n",
       "      <td>01</td>\n",
       "      <td>ABSECON CITY</td>\n",
       "      <td>MUNICIPAL COMP?</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>03</td>\n",
       "      <td>2</td>\n",
       "      <td>CALDERON AVENUE</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>07</td>\n",
       "      <td>02</td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>06</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td>100</td>\n",
       "      <td>FE</td>\n",
       "      <td>N</td>\n",
       "      <td>RT 30</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>25</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>NEVER SAW V-1 MINOR DAMAGE - NO INJURIES REPOR...</td>\n",
       "      <td>830</td>\n",
       "      <td>2001-12-21 18:34:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No MP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01-00029</td>\n",
       "      <td>ATLANTIC</td>\n",
       "      <td>ABSECON CITY</td>\n",
       "      <td>1</td>\n",
       "      <td>ABSECON</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>06</td>\n",
       "      <td>2</td>\n",
       "      <td>RITZ DRIVE</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>07</td>\n",
       "      <td>01</td>\n",
       "      <td>02</td>\n",
       "      <td>03</td>\n",
       "      <td>06</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>25</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>836</td>\n",
       "      <td>2001-01-01 09:30:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No MP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01-004615</td>\n",
       "      <td>ATLANTIC</td>\n",
       "      <td>ABSECON CITY</td>\n",
       "      <td>1</td>\n",
       "      <td>ABSECON</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>I</td>\n",
       "      <td>I</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>03</td>\n",
       "      <td>2</td>\n",
       "      <td>MORTON AVENUE</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>07</td>\n",
       "      <td>01</td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td>AT</td>\n",
       "      <td></td>\n",
       "      <td>NEW YORK AVENUE</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>25</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>836</td>\n",
       "      <td>2001-04-10 14:44:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No MP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01-004880</td>\n",
       "      <td>ATLANTIC</td>\n",
       "      <td>ABSECON CITY</td>\n",
       "      <td>1</td>\n",
       "      <td>ABSECON</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>I</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>01</td>\n",
       "      <td>2</td>\n",
       "      <td>RT 30</td>\n",
       "      <td></td>\n",
       "      <td>30</td>\n",
       "      <td></td>\n",
       "      <td>00000030__</td>\n",
       "      <td>51.10</td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>45</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>WITNESS-PETRIA GIBSON AND ANGELO HERSH</td>\n",
       "      <td>886</td>\n",
       "      <td>2001-04-15 13:56:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-74.512308</td>\n",
       "      <td>39.432705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01-004912</td>\n",
       "      <td>ATLANTIC</td>\n",
       "      <td>ABSECON CITY</td>\n",
       "      <td>1</td>\n",
       "      <td>ABSECON</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>I</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>01</td>\n",
       "      <td>2</td>\n",
       "      <td>CR 651</td>\n",
       "      <td></td>\n",
       "      <td>651</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>05</td>\n",
       "      <td>02</td>\n",
       "      <td>02</td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>02</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td>AT</td>\n",
       "      <td></td>\n",
       "      <td>CR 630</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>35</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>*BETH VEHICLES-RELIANCE INSURANCE CO.(215)864-...</td>\n",
       "      <td>836</td>\n",
       "      <td>2001-04-16 10:29:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No MP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <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",
       "      <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",
       "      <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>6077298</th>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>B150-2021-00117A</td>\n",
       "      <td>WARREN</td>\n",
       "      <td>WHITE TWP</td>\n",
       "      <td>02</td>\n",
       "      <td>NEW JERSEY STATE POLICE</td>\n",
       "      <td>WASHINGTON - SQ</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>I</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>WARREN COUNTY 620</td>\n",
       "      <td>N</td>\n",
       "      <td>620</td>\n",
       "      <td></td>\n",
       "      <td>21000620__</td>\n",
       "      <td>0.00</td>\n",
       "      <td>05</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>06</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td>AT</td>\n",
       "      <td></td>\n",
       "      <td>ROUTE 519 / S BRIDGEVILLE RD</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50</td>\n",
       "      <td>50</td>\n",
       "      <td>40.80707</td>\n",
       "      <td>75.07559</td>\n",
       "      <td>N</td>\n",
       "      <td>Damage to 10ft of Country road guardrail.</td>\n",
       "      <td>7837</td>\n",
       "      <td>2021-05-26 21:08:00</td>\n",
       "      <td>02</td>\n",
       "      <td>04</td>\n",
       "      <td>46</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-75.075607</td>\n",
       "      <td>40.807156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077299</th>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>B150-2021-00118A</td>\n",
       "      <td>WARREN</td>\n",
       "      <td>WHITE TWP</td>\n",
       "      <td>02</td>\n",
       "      <td>NEW JERSEY STATE POLICE</td>\n",
       "      <td>WASHINGTON - SQ</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>I</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>01</td>\n",
       "      <td>2</td>\n",
       "      <td>MANUNKA CHUNK RD / UPPER SEREPTA RD **</td>\n",
       "      <td>W</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>21231034__</td>\n",
       "      <td>1.73</td>\n",
       "      <td>07</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td>AT</td>\n",
       "      <td></td>\n",
       "      <td>MASSENATTS RD</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>40</td>\n",
       "      <td>50</td>\n",
       "      <td>40.84466</td>\n",
       "      <td>75.05549</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>8610</td>\n",
       "      <td>2021-05-25 07:26:00</td>\n",
       "      <td>01</td>\n",
       "      <td>04</td>\n",
       "      <td>26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-75.056122</td>\n",
       "      <td>40.844345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077300</th>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>B150-2021-00128A</td>\n",
       "      <td>WARREN</td>\n",
       "      <td>WHITE TWP</td>\n",
       "      <td>02</td>\n",
       "      <td>NEW JERSEY STATE POLICE</td>\n",
       "      <td>WASHINGTON - SQ</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>ROUTE 519</td>\n",
       "      <td>S</td>\n",
       "      <td>519</td>\n",
       "      <td></td>\n",
       "      <td>00000519__</td>\n",
       "      <td>44.00</td>\n",
       "      <td>05</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>02</td>\n",
       "      <td>05</td>\n",
       "      <td>04</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50</td>\n",
       "      <td></td>\n",
       "      <td>40.80564</td>\n",
       "      <td>75.07627</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>8514</td>\n",
       "      <td>2021-06-03 20:17:00</td>\n",
       "      <td>03</td>\n",
       "      <td>04</td>\n",
       "      <td>24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-75.015149</td>\n",
       "      <td>40.863108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077301</th>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>B150-2021-00130A</td>\n",
       "      <td>WARREN</td>\n",
       "      <td>WHITE TWP</td>\n",
       "      <td>02</td>\n",
       "      <td>NEW JERSEY STATE POLICE</td>\n",
       "      <td>WASHINGTON - SQ</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>US 46</td>\n",
       "      <td>E</td>\n",
       "      <td>46</td>\n",
       "      <td></td>\n",
       "      <td>00000046__</td>\n",
       "      <td>7.50</td>\n",
       "      <td>02</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>07</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50</td>\n",
       "      <td></td>\n",
       "      <td>40.84149</td>\n",
       "      <td>75.04968</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>8365</td>\n",
       "      <td>2021-06-06 00:00:00</td>\n",
       "      <td>01</td>\n",
       "      <td>04</td>\n",
       "      <td>24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-75.046404</td>\n",
       "      <td>40.840421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077302</th>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>B150-2021-00132A</td>\n",
       "      <td>WARREN</td>\n",
       "      <td>WHITE TWP</td>\n",
       "      <td>02</td>\n",
       "      <td>NEW JERSEY STATE POLICE</td>\n",
       "      <td>WASHINGTON - SQ</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>03</td>\n",
       "      <td>2</td>\n",
       "      <td>FIELDSTONE CT</td>\n",
       "      <td>W</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>07</td>\n",
       "      <td></td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>130 COUNTY ROAD 519</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>40.81220</td>\n",
       "      <td>75.04940</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>8395</td>\n",
       "      <td>2021-06-06 19:33:00</td>\n",
       "      <td>01</td>\n",
       "      <td>04</td>\n",
       "      <td>26</td>\n",
       "      <td>No MP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6077303 rows × 53 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        County Code Municipality Code Department Case Number County Name  \\\n",
       "0                01                01            #2001-17846    ATLANTIC   \n",
       "1                01                01               01-00029    ATLANTIC   \n",
       "2                01                01              01-004615    ATLANTIC   \n",
       "3                01                01              01-004880    ATLANTIC   \n",
       "4                01                01              01-004912    ATLANTIC   \n",
       "...             ...               ...                    ...         ...   \n",
       "6077298          21                23       B150-2021-00117A      WARREN   \n",
       "6077299          21                23       B150-2021-00118A      WARREN   \n",
       "6077300          21                23       B150-2021-00128A      WARREN   \n",
       "6077301          21                23       B150-2021-00130A      WARREN   \n",
       "6077302          21                23       B150-2021-00132A      WARREN   \n",
       "\n",
       "        Municipality Name Police Department Code        Police Department  \\\n",
       "0            ABSECON CITY                     01             ABSECON CITY   \n",
       "1            ABSECON CITY                      1                  ABSECON   \n",
       "2            ABSECON CITY                      1                  ABSECON   \n",
       "3            ABSECON CITY                      1                  ABSECON   \n",
       "4            ABSECON CITY                      1                  ABSECON   \n",
       "...                   ...                    ...                      ...   \n",
       "6077298         WHITE TWP                     02  NEW JERSEY STATE POLICE   \n",
       "6077299         WHITE TWP                     02  NEW JERSEY STATE POLICE   \n",
       "6077300         WHITE TWP                     02  NEW JERSEY STATE POLICE   \n",
       "6077301         WHITE TWP                     02  NEW JERSEY STATE POLICE   \n",
       "6077302         WHITE TWP                     02  NEW JERSEY STATE POLICE   \n",
       "\n",
       "          Police Station  Total Killed  Total Injured  Pedestrians Killed  \\\n",
       "0        MUNICIPAL COMP?             0              0                   0   \n",
       "1                                    0              0                   0   \n",
       "2                                    0              4                   0   \n",
       "3                                    0              1                   0   \n",
       "4                                    0              0                   0   \n",
       "...                  ...           ...            ...                 ...   \n",
       "6077298  WASHINGTON - SQ             0              0                   0   \n",
       "6077299  WASHINGTON - SQ             0              0                   0   \n",
       "6077300  WASHINGTON - SQ             0              0                   0   \n",
       "6077301  WASHINGTON - SQ             0              0                   0   \n",
       "6077302  WASHINGTON - SQ             0              0                   0   \n",
       "\n",
       "         Pedestrians Injured Severity Intersection  Alcohol Involved  \\\n",
       "0                          0        P            B             False   \n",
       "1                          0        P            B             False   \n",
       "2                          0        I            I             False   \n",
       "3                          0        I            B             False   \n",
       "4                          0        P            I             False   \n",
       "...                      ...      ...          ...               ...   \n",
       "6077298                    0        P            I             False   \n",
       "6077299                    0        P            I             False   \n",
       "6077300                    0        P            B             False   \n",
       "6077301                    0        P            B             False   \n",
       "6077302                    0        P            B             False   \n",
       "\n",
       "         HazMat Involved Crash Type Code  Total Vehicles Involved  \\\n",
       "0                  False              03                        2   \n",
       "1                  False              06                        2   \n",
       "2                  False              03                        2   \n",
       "3                  False              01                        2   \n",
       "4                  False              01                        2   \n",
       "...                  ...             ...                      ...   \n",
       "6077298            False              11                        1   \n",
       "6077299            False              01                        2   \n",
       "6077300            False              12                        1   \n",
       "6077301            False              12                        1   \n",
       "6077302            False              03                        2   \n",
       "\n",
       "                                 Crash Location Location Direction Route  \\\n",
       "0                               CALDERON AVENUE                            \n",
       "1                                    RITZ DRIVE                            \n",
       "2                                 MORTON AVENUE                            \n",
       "3                                         RT 30                       30   \n",
       "4                                        CR 651                      651   \n",
       "...                                         ...                ...   ...   \n",
       "6077298                       WARREN COUNTY 620                  N   620   \n",
       "6077299  MANUNKA CHUNK RD / UPPER SEREPTA RD **                  W         \n",
       "6077300                               ROUTE 519                  S   519   \n",
       "6077301                                   US 46                  E    46   \n",
       "6077302                           FIELDSTONE CT                  W         \n",
       "\n",
       "        Route Suffix SRI (Standard Route Identifier)  Mile Post Road System  \\\n",
       "0                                                           NaN          07   \n",
       "1                                                           NaN          07   \n",
       "2                                                           NaN          07   \n",
       "3                                         00000030__      51.10          02   \n",
       "4                                                           NaN          05   \n",
       "...              ...                             ...        ...         ...   \n",
       "6077298                                   21000620__       0.00          05   \n",
       "6077299                                   21231034__       1.73          07   \n",
       "6077300                                   00000519__      44.00          05   \n",
       "6077301                                   00000046__       7.50          02   \n",
       "6077302                                                     NaN          07   \n",
       "\n",
       "        Road Character Road Surface Type Surface Condition Light Condition  \\\n",
       "0                   02                02                01              06   \n",
       "1                   01                02                03              06   \n",
       "2                   01                02                01              01   \n",
       "3                   01                02                01              01   \n",
       "4                   02                02                02              01   \n",
       "...                ...               ...               ...             ...   \n",
       "6077298                               02                01              06   \n",
       "6077299                               02                01              01   \n",
       "6077300                               02                02              05   \n",
       "6077301                               02                01              07   \n",
       "6077302                               05                01              01   \n",
       "\n",
       "        Environmental Condition Road Divided By  \\\n",
       "0                            01              05   \n",
       "1                            01              05   \n",
       "2                            01              05   \n",
       "3                            01              05   \n",
       "4                            02              05   \n",
       "...                         ...             ...   \n",
       "6077298                      01              05   \n",
       "6077299                      01              05   \n",
       "6077300                      04              05   \n",
       "6077301                      01              05   \n",
       "6077302                      01              05   \n",
       "\n",
       "        Temporary Traffic Control Zone Distance To Cross Street  \\\n",
       "0                                   01                      100   \n",
       "1                                   01                            \n",
       "2                                   01                            \n",
       "3                                   01                            \n",
       "4                                   01                            \n",
       "...                                ...                      ...   \n",
       "6077298                             01                            \n",
       "6077299                             01                            \n",
       "6077300                             01                            \n",
       "6077301                             01                            \n",
       "6077302                             01                            \n",
       "\n",
       "        Unit Of Measurement Direction From Cross Street  \\\n",
       "0                        FE                           N   \n",
       "1                                                         \n",
       "2                        AT                               \n",
       "3                                                         \n",
       "4                        AT                               \n",
       "...                     ...                         ...   \n",
       "6077298                  AT                               \n",
       "6077299                  AT                               \n",
       "6077300                                                   \n",
       "6077301                                                   \n",
       "6077302                                                   \n",
       "\n",
       "                    Cross Street Name Is Ramp Ramp To/From Route Name  \\\n",
       "0                               RT 30                                   \n",
       "1                                                                       \n",
       "2                     NEW YORK AVENUE                                   \n",
       "3                                                                       \n",
       "4                              CR 630                                   \n",
       "...                               ...     ...                     ...   \n",
       "6077298  ROUTE 519 / S BRIDGEVILLE RD                                   \n",
       "6077299                 MASSENATTS RD                                   \n",
       "6077300                                                                 \n",
       "6077301                                                                 \n",
       "6077302           130 COUNTY ROAD 519                                   \n",
       "\n",
       "        Ramp To/From Route Direction Posted Speed Posted Speed Cross Street  \\\n",
       "0                                              25                             \n",
       "1                                              25                             \n",
       "2                                              25                             \n",
       "3                                              45                             \n",
       "4                                              35                             \n",
       "...                              ...          ...                       ...   \n",
       "6077298                                        50                        50   \n",
       "6077299                                        40                        50   \n",
       "6077300                                        50                             \n",
       "6077301                                        50                             \n",
       "6077302                                        15                         5   \n",
       "\n",
       "         Latitude  Longitude Cell Phone In Use Flag  \\\n",
       "0             NaN        NaN                      N   \n",
       "1             NaN        NaN                      N   \n",
       "2             NaN        NaN                      N   \n",
       "3             NaN        NaN                      N   \n",
       "4             NaN        NaN                      N   \n",
       "...           ...        ...                    ...   \n",
       "6077298  40.80707   75.07559                      N   \n",
       "6077299  40.84466   75.05549                      N   \n",
       "6077300  40.80564   75.07627                      N   \n",
       "6077301  40.84149   75.04968                      N   \n",
       "6077302  40.81220   75.04940                      N   \n",
       "\n",
       "                                     Other Property Damage  \\\n",
       "0        NEVER SAW V-1 MINOR DAMAGE - NO INJURIES REPOR...   \n",
       "1                                                            \n",
       "2                                                            \n",
       "3                   WITNESS-PETRIA GIBSON AND ANGELO HERSH   \n",
       "4        *BETH VEHICLES-RELIANCE INSURANCE CO.(215)864-...   \n",
       "...                                                    ...   \n",
       "6077298          Damage to 10ft of Country road guardrail.   \n",
       "6077299                                                      \n",
       "6077300                                                      \n",
       "6077301                                                      \n",
       "6077302                                                      \n",
       "\n",
       "        Reporting Badge No.                Date Road Horizontal Alignment  \\\n",
       "0                       830 2001-12-21 18:34:00                       NaN   \n",
       "1                       836 2001-01-01 09:30:00                       NaN   \n",
       "2                       836 2001-04-10 14:44:00                       NaN   \n",
       "3                       886 2001-04-15 13:56:00                       NaN   \n",
       "4                       836 2001-04-16 10:29:00                       NaN   \n",
       "...                     ...                 ...                       ...   \n",
       "6077298                7837 2021-05-26 21:08:00                        02   \n",
       "6077299                8610 2021-05-25 07:26:00                        01   \n",
       "6077300                8514 2021-06-03 20:17:00                        03   \n",
       "6077301                8365 2021-06-06 00:00:00                        01   \n",
       "6077302                8395 2021-06-06 19:33:00                        01   \n",
       "\n",
       "        Road Grade First Harmful Event reason        lon        lat  \n",
       "0              NaN                 NaN  No MP        NaN        NaN  \n",
       "1              NaN                 NaN  No MP        NaN        NaN  \n",
       "2              NaN                 NaN  No MP        NaN        NaN  \n",
       "3              NaN                 NaN    NaN -74.512308  39.432705  \n",
       "4              NaN                 NaN  No MP        NaN        NaN  \n",
       "...            ...                 ...    ...        ...        ...  \n",
       "6077298         04                  46    NaN -75.075607  40.807156  \n",
       "6077299         04                  26    NaN -75.056122  40.844345  \n",
       "6077300         04                  24    NaN -75.015149  40.863108  \n",
       "6077301         04                  24    NaN -75.046404  40.840421  \n",
       "6077302         04                  26  No MP        NaN        NaN  \n",
       "\n",
       "[6077303 rows x 53 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cg = sxs(c, geocoded_mps)\n",
    "cg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "e3c6339c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>County Code</th>\n",
       "      <th>Municipality Code</th>\n",
       "      <th>Department Case Number</th>\n",
       "      <th>County Name</th>\n",
       "      <th>Municipality Name</th>\n",
       "      <th>Police Department Code</th>\n",
       "      <th>Police Department</th>\n",
       "      <th>Police Station</th>\n",
       "      <th>Total Killed</th>\n",
       "      <th>Total Injured</th>\n",
       "      <th>Pedestrians Killed</th>\n",
       "      <th>Pedestrians Injured</th>\n",
       "      <th>Severity</th>\n",
       "      <th>Intersection</th>\n",
       "      <th>Alcohol Involved</th>\n",
       "      <th>HazMat Involved</th>\n",
       "      <th>Crash Type Code</th>\n",
       "      <th>Total Vehicles Involved</th>\n",
       "      <th>Crash Location</th>\n",
       "      <th>Location Direction</th>\n",
       "      <th>Route</th>\n",
       "      <th>Route Suffix</th>\n",
       "      <th>SRI (Standard Route Identifier)</th>\n",
       "      <th>Mile Post</th>\n",
       "      <th>Road System</th>\n",
       "      <th>Road Character</th>\n",
       "      <th>Road Surface Type</th>\n",
       "      <th>Surface Condition</th>\n",
       "      <th>Light Condition</th>\n",
       "      <th>Environmental Condition</th>\n",
       "      <th>Road Divided By</th>\n",
       "      <th>Temporary Traffic Control Zone</th>\n",
       "      <th>Distance To Cross Street</th>\n",
       "      <th>Unit Of Measurement</th>\n",
       "      <th>Direction From Cross Street</th>\n",
       "      <th>Cross Street Name</th>\n",
       "      <th>Is Ramp</th>\n",
       "      <th>Ramp To/From Route Name</th>\n",
       "      <th>Ramp To/From Route Direction</th>\n",
       "      <th>Posted Speed</th>\n",
       "      <th>Posted Speed Cross Street</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Cell Phone In Use Flag</th>\n",
       "      <th>Other Property Damage</th>\n",
       "      <th>Reporting Badge No.</th>\n",
       "      <th>Date</th>\n",
       "      <th>Road Horizontal Alignment</th>\n",
       "      <th>Road Grade</th>\n",
       "      <th>First Harmful Event</th>\n",
       "      <th>reason</th>\n",
       "      <th>lon</th>\n",
       "      <th>lat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4809915</th>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>1708-0494</td>\n",
       "      <td>ATLANTIC</td>\n",
       "      <td>ABSECON CITY</td>\n",
       "      <td>01</td>\n",
       "      <td>ATLANTIC CITY PD</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>01</td>\n",
       "      <td>3</td>\n",
       "      <td>US 30</td>\n",
       "      <td></td>\n",
       "      <td>30</td>\n",
       "      <td></td>\n",
       "      <td>00000030__</td>\n",
       "      <td>54.29</td>\n",
       "      <td>02</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>1056</td>\n",
       "      <td>FE</td>\n",
       "      <td>E</td>\n",
       "      <td>CR 646 / DELILIAH RD</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50</td>\n",
       "      <td>35</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2017-08-10 17:35:00</td>\n",
       "      <td>03</td>\n",
       "      <td>04</td>\n",
       "      <td>26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-74.490831</td>\n",
       "      <td>39.393658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4809916</th>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>1708-0497</td>\n",
       "      <td>ATLANTIC</td>\n",
       "      <td>ABSECON CITY</td>\n",
       "      <td>01</td>\n",
       "      <td>ATLANTIC CITY PD</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>02</td>\n",
       "      <td>2</td>\n",
       "      <td>US 30</td>\n",
       "      <td>W</td>\n",
       "      <td>30</td>\n",
       "      <td></td>\n",
       "      <td>00000030__</td>\n",
       "      <td>54.29</td>\n",
       "      <td>02</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>1056</td>\n",
       "      <td>FE</td>\n",
       "      <td>E</td>\n",
       "      <td>CR 646 / DELILIAH RD</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50</td>\n",
       "      <td>35</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>530</td>\n",
       "      <td>2017-08-10 17:36:00</td>\n",
       "      <td>03</td>\n",
       "      <td>04</td>\n",
       "      <td>26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-74.490831</td>\n",
       "      <td>39.393658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4809917</th>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>1709-1131</td>\n",
       "      <td>ATLANTIC</td>\n",
       "      <td>ABSECON CITY</td>\n",
       "      <td>01</td>\n",
       "      <td>ATLANTIC CITY PD</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>US 30</td>\n",
       "      <td>W</td>\n",
       "      <td>30</td>\n",
       "      <td></td>\n",
       "      <td>00000030__</td>\n",
       "      <td>54.29</td>\n",
       "      <td>02</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>06</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>1056</td>\n",
       "      <td>FE</td>\n",
       "      <td>E</td>\n",
       "      <td>CR 646 / DELILIAH RD</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50</td>\n",
       "      <td>35</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>530</td>\n",
       "      <td>2017-09-27 21:27:00</td>\n",
       "      <td>03</td>\n",
       "      <td>04</td>\n",
       "      <td>48</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-74.490831</td>\n",
       "      <td>39.393658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4809918</th>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>1709-1132</td>\n",
       "      <td>ATLANTIC</td>\n",
       "      <td>ABSECON CITY</td>\n",
       "      <td>01</td>\n",
       "      <td>ATLANTIC CITY PD</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>I</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>01</td>\n",
       "      <td>2</td>\n",
       "      <td>US 30</td>\n",
       "      <td>W</td>\n",
       "      <td>30</td>\n",
       "      <td></td>\n",
       "      <td>00000030__</td>\n",
       "      <td>54.29</td>\n",
       "      <td>02</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>06</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>1056</td>\n",
       "      <td>FE</td>\n",
       "      <td>E</td>\n",
       "      <td>CR 646 / DELILIAH RD</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50</td>\n",
       "      <td>35</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>530</td>\n",
       "      <td>2017-09-27 21:28:00</td>\n",
       "      <td>03</td>\n",
       "      <td>04</td>\n",
       "      <td>26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-74.490831</td>\n",
       "      <td>39.393658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4809919</th>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>1712-0555</td>\n",
       "      <td>ATLANTIC</td>\n",
       "      <td>ABSECON CITY</td>\n",
       "      <td>01</td>\n",
       "      <td>ATLANTIC CITY PD</td>\n",
       "      <td>TRAFFIC</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>02</td>\n",
       "      <td>2</td>\n",
       "      <td>US 30</td>\n",
       "      <td>W</td>\n",
       "      <td>30</td>\n",
       "      <td></td>\n",
       "      <td>00000030__</td>\n",
       "      <td>54.40</td>\n",
       "      <td>02</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>630</td>\n",
       "      <td>2017-12-18 13:21:00</td>\n",
       "      <td>01</td>\n",
       "      <td>04</td>\n",
       "      <td>26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-74.489583</td>\n",
       "      <td>39.392377</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",
       "      <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",
       "      <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",
       "      <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>6077298</th>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>B150-2021-00117A</td>\n",
       "      <td>WARREN</td>\n",
       "      <td>WHITE TWP</td>\n",
       "      <td>02</td>\n",
       "      <td>NEW JERSEY STATE POLICE</td>\n",
       "      <td>WASHINGTON - SQ</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>I</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>WARREN COUNTY 620</td>\n",
       "      <td>N</td>\n",
       "      <td>620</td>\n",
       "      <td></td>\n",
       "      <td>21000620__</td>\n",
       "      <td>0.00</td>\n",
       "      <td>05</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>06</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td>AT</td>\n",
       "      <td></td>\n",
       "      <td>ROUTE 519 / S BRIDGEVILLE RD</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50</td>\n",
       "      <td>50</td>\n",
       "      <td>40.80707</td>\n",
       "      <td>75.07559</td>\n",
       "      <td>N</td>\n",
       "      <td>Damage to 10ft of Country road guardrail.</td>\n",
       "      <td>7837</td>\n",
       "      <td>2021-05-26 21:08:00</td>\n",
       "      <td>02</td>\n",
       "      <td>04</td>\n",
       "      <td>46</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-75.075607</td>\n",
       "      <td>40.807156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077299</th>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>B150-2021-00118A</td>\n",
       "      <td>WARREN</td>\n",
       "      <td>WHITE TWP</td>\n",
       "      <td>02</td>\n",
       "      <td>NEW JERSEY STATE POLICE</td>\n",
       "      <td>WASHINGTON - SQ</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>I</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>01</td>\n",
       "      <td>2</td>\n",
       "      <td>MANUNKA CHUNK RD / UPPER SEREPTA RD **</td>\n",
       "      <td>W</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>21231034__</td>\n",
       "      <td>1.73</td>\n",
       "      <td>07</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td>AT</td>\n",
       "      <td></td>\n",
       "      <td>MASSENATTS RD</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>40</td>\n",
       "      <td>50</td>\n",
       "      <td>40.84466</td>\n",
       "      <td>75.05549</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>8610</td>\n",
       "      <td>2021-05-25 07:26:00</td>\n",
       "      <td>01</td>\n",
       "      <td>04</td>\n",
       "      <td>26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-75.056122</td>\n",
       "      <td>40.844345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077300</th>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>B150-2021-00128A</td>\n",
       "      <td>WARREN</td>\n",
       "      <td>WHITE TWP</td>\n",
       "      <td>02</td>\n",
       "      <td>NEW JERSEY STATE POLICE</td>\n",
       "      <td>WASHINGTON - SQ</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>ROUTE 519</td>\n",
       "      <td>S</td>\n",
       "      <td>519</td>\n",
       "      <td></td>\n",
       "      <td>00000519__</td>\n",
       "      <td>44.00</td>\n",
       "      <td>05</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>02</td>\n",
       "      <td>05</td>\n",
       "      <td>04</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50</td>\n",
       "      <td></td>\n",
       "      <td>40.80564</td>\n",
       "      <td>75.07627</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>8514</td>\n",
       "      <td>2021-06-03 20:17:00</td>\n",
       "      <td>03</td>\n",
       "      <td>04</td>\n",
       "      <td>24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-75.015149</td>\n",
       "      <td>40.863108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077301</th>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>B150-2021-00130A</td>\n",
       "      <td>WARREN</td>\n",
       "      <td>WHITE TWP</td>\n",
       "      <td>02</td>\n",
       "      <td>NEW JERSEY STATE POLICE</td>\n",
       "      <td>WASHINGTON - SQ</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>US 46</td>\n",
       "      <td>E</td>\n",
       "      <td>46</td>\n",
       "      <td></td>\n",
       "      <td>00000046__</td>\n",
       "      <td>7.50</td>\n",
       "      <td>02</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>07</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50</td>\n",
       "      <td></td>\n",
       "      <td>40.84149</td>\n",
       "      <td>75.04968</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>8365</td>\n",
       "      <td>2021-06-06 00:00:00</td>\n",
       "      <td>01</td>\n",
       "      <td>04</td>\n",
       "      <td>24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-75.046404</td>\n",
       "      <td>40.840421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077302</th>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>B150-2021-00132A</td>\n",
       "      <td>WARREN</td>\n",
       "      <td>WHITE TWP</td>\n",
       "      <td>02</td>\n",
       "      <td>NEW JERSEY STATE POLICE</td>\n",
       "      <td>WASHINGTON - SQ</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>03</td>\n",
       "      <td>2</td>\n",
       "      <td>FIELDSTONE CT</td>\n",
       "      <td>W</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>07</td>\n",
       "      <td></td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>130 COUNTY ROAD 519</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>40.81220</td>\n",
       "      <td>75.04940</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>8395</td>\n",
       "      <td>2021-06-06 19:33:00</td>\n",
       "      <td>01</td>\n",
       "      <td>04</td>\n",
       "      <td>26</td>\n",
       "      <td>No MP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1267388 rows × 53 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        County Code Municipality Code Department Case Number County Name  \\\n",
       "4809915          01                01              1708-0494    ATLANTIC   \n",
       "4809916          01                01              1708-0497    ATLANTIC   \n",
       "4809917          01                01              1709-1131    ATLANTIC   \n",
       "4809918          01                01              1709-1132    ATLANTIC   \n",
       "4809919          01                01              1712-0555    ATLANTIC   \n",
       "...             ...               ...                    ...         ...   \n",
       "6077298          21                23       B150-2021-00117A      WARREN   \n",
       "6077299          21                23       B150-2021-00118A      WARREN   \n",
       "6077300          21                23       B150-2021-00128A      WARREN   \n",
       "6077301          21                23       B150-2021-00130A      WARREN   \n",
       "6077302          21                23       B150-2021-00132A      WARREN   \n",
       "\n",
       "        Municipality Name Police Department Code        Police Department  \\\n",
       "4809915      ABSECON CITY                     01         ATLANTIC CITY PD   \n",
       "4809916      ABSECON CITY                     01         ATLANTIC CITY PD   \n",
       "4809917      ABSECON CITY                     01         ATLANTIC CITY PD   \n",
       "4809918      ABSECON CITY                     01         ATLANTIC CITY PD   \n",
       "4809919      ABSECON CITY                     01         ATLANTIC CITY PD   \n",
       "...                   ...                    ...                      ...   \n",
       "6077298         WHITE TWP                     02  NEW JERSEY STATE POLICE   \n",
       "6077299         WHITE TWP                     02  NEW JERSEY STATE POLICE   \n",
       "6077300         WHITE TWP                     02  NEW JERSEY STATE POLICE   \n",
       "6077301         WHITE TWP                     02  NEW JERSEY STATE POLICE   \n",
       "6077302         WHITE TWP                     02  NEW JERSEY STATE POLICE   \n",
       "\n",
       "          Police Station  Total Killed  Total Injured  Pedestrians Killed  \\\n",
       "4809915                              0              0                   0   \n",
       "4809916                              0              0                   0   \n",
       "4809917                              0              0                   0   \n",
       "4809918                              0              1                   0   \n",
       "4809919          TRAFFIC             0              0                   0   \n",
       "...                  ...           ...            ...                 ...   \n",
       "6077298  WASHINGTON - SQ             0              0                   0   \n",
       "6077299  WASHINGTON - SQ             0              0                   0   \n",
       "6077300  WASHINGTON - SQ             0              0                   0   \n",
       "6077301  WASHINGTON - SQ             0              0                   0   \n",
       "6077302  WASHINGTON - SQ             0              0                   0   \n",
       "\n",
       "         Pedestrians Injured Severity Intersection  Alcohol Involved  \\\n",
       "4809915                    0        P            B             False   \n",
       "4809916                    0        P            B             False   \n",
       "4809917                    0        P            B             False   \n",
       "4809918                    0        I            B             False   \n",
       "4809919                    0        P            B             False   \n",
       "...                      ...      ...          ...               ...   \n",
       "6077298                    0        P            I             False   \n",
       "6077299                    0        P            I             False   \n",
       "6077300                    0        P            B             False   \n",
       "6077301                    0        P            B             False   \n",
       "6077302                    0        P            B             False   \n",
       "\n",
       "         HazMat Involved Crash Type Code  Total Vehicles Involved  \\\n",
       "4809915            False              01                        3   \n",
       "4809916            False              02                        2   \n",
       "4809917            False              11                        1   \n",
       "4809918            False              01                        2   \n",
       "4809919            False              02                        2   \n",
       "...                  ...             ...                      ...   \n",
       "6077298            False              11                        1   \n",
       "6077299            False              01                        2   \n",
       "6077300            False              12                        1   \n",
       "6077301            False              12                        1   \n",
       "6077302            False              03                        2   \n",
       "\n",
       "                                 Crash Location Location Direction Route  \\\n",
       "4809915                                   US 30                       30   \n",
       "4809916                                   US 30                  W    30   \n",
       "4809917                                   US 30                  W    30   \n",
       "4809918                                   US 30                  W    30   \n",
       "4809919                                   US 30                  W    30   \n",
       "...                                         ...                ...   ...   \n",
       "6077298                       WARREN COUNTY 620                  N   620   \n",
       "6077299  MANUNKA CHUNK RD / UPPER SEREPTA RD **                  W         \n",
       "6077300                               ROUTE 519                  S   519   \n",
       "6077301                                   US 46                  E    46   \n",
       "6077302                           FIELDSTONE CT                  W         \n",
       "\n",
       "        Route Suffix SRI (Standard Route Identifier)  Mile Post Road System  \\\n",
       "4809915                                   00000030__      54.29          02   \n",
       "4809916                                   00000030__      54.29          02   \n",
       "4809917                                   00000030__      54.29          02   \n",
       "4809918                                   00000030__      54.29          02   \n",
       "4809919                                   00000030__      54.40          02   \n",
       "...              ...                             ...        ...         ...   \n",
       "6077298                                   21000620__       0.00          05   \n",
       "6077299                                   21231034__       1.73          07   \n",
       "6077300                                   00000519__      44.00          05   \n",
       "6077301                                   00000046__       7.50          02   \n",
       "6077302                                                     NaN          07   \n",
       "\n",
       "        Road Character Road Surface Type Surface Condition Light Condition  \\\n",
       "4809915                               02                01              01   \n",
       "4809916                               02                01              01   \n",
       "4809917                               02                01              06   \n",
       "4809918                               02                01              06   \n",
       "4809919                               02                01              01   \n",
       "...                ...               ...               ...             ...   \n",
       "6077298                               02                01              06   \n",
       "6077299                               02                01              01   \n",
       "6077300                               02                02              05   \n",
       "6077301                               02                01              07   \n",
       "6077302                               05                01              01   \n",
       "\n",
       "        Environmental Condition Road Divided By  \\\n",
       "4809915                      01              01   \n",
       "4809916                      01              01   \n",
       "4809917                      01              01   \n",
       "4809918                      01              01   \n",
       "4809919                      01              05   \n",
       "...                         ...             ...   \n",
       "6077298                      01              05   \n",
       "6077299                      01              05   \n",
       "6077300                      04              05   \n",
       "6077301                      01              05   \n",
       "6077302                      01              05   \n",
       "\n",
       "        Temporary Traffic Control Zone Distance To Cross Street  \\\n",
       "4809915                             01                     1056   \n",
       "4809916                             05                     1056   \n",
       "4809917                             01                     1056   \n",
       "4809918                             05                     1056   \n",
       "4809919                             01                            \n",
       "...                                ...                      ...   \n",
       "6077298                             01                            \n",
       "6077299                             01                            \n",
       "6077300                             01                            \n",
       "6077301                             01                            \n",
       "6077302                             01                            \n",
       "\n",
       "        Unit Of Measurement Direction From Cross Street  \\\n",
       "4809915                  FE                           E   \n",
       "4809916                  FE                           E   \n",
       "4809917                  FE                           E   \n",
       "4809918                  FE                           E   \n",
       "4809919                                                   \n",
       "...                     ...                         ...   \n",
       "6077298                  AT                               \n",
       "6077299                  AT                               \n",
       "6077300                                                   \n",
       "6077301                                                   \n",
       "6077302                                                   \n",
       "\n",
       "                    Cross Street Name Is Ramp Ramp To/From Route Name  \\\n",
       "4809915          CR 646 / DELILIAH RD                                   \n",
       "4809916          CR 646 / DELILIAH RD                                   \n",
       "4809917          CR 646 / DELILIAH RD                                   \n",
       "4809918          CR 646 / DELILIAH RD                                   \n",
       "4809919                                                                 \n",
       "...                               ...     ...                     ...   \n",
       "6077298  ROUTE 519 / S BRIDGEVILLE RD                                   \n",
       "6077299                 MASSENATTS RD                                   \n",
       "6077300                                                                 \n",
       "6077301                                                                 \n",
       "6077302           130 COUNTY ROAD 519                                   \n",
       "\n",
       "        Ramp To/From Route Direction Posted Speed Posted Speed Cross Street  \\\n",
       "4809915                                        50                        35   \n",
       "4809916                                        50                        35   \n",
       "4809917                                        50                        35   \n",
       "4809918                                        50                        35   \n",
       "4809919                                        50                             \n",
       "...                              ...          ...                       ...   \n",
       "6077298                                        50                        50   \n",
       "6077299                                        40                        50   \n",
       "6077300                                        50                             \n",
       "6077301                                        50                             \n",
       "6077302                                        15                         5   \n",
       "\n",
       "         Latitude  Longitude Cell Phone In Use Flag  \\\n",
       "4809915       NaN        NaN                      N   \n",
       "4809916       NaN        NaN                      N   \n",
       "4809917       NaN        NaN                      N   \n",
       "4809918       NaN        NaN                      N   \n",
       "4809919       NaN        NaN                      N   \n",
       "...           ...        ...                    ...   \n",
       "6077298  40.80707   75.07559                      N   \n",
       "6077299  40.84466   75.05549                      N   \n",
       "6077300  40.80564   75.07627                      N   \n",
       "6077301  40.84149   75.04968                      N   \n",
       "6077302  40.81220   75.04940                      N   \n",
       "\n",
       "                             Other Property Damage Reporting Badge No.  \\\n",
       "4809915                                                                  \n",
       "4809916                                                            530   \n",
       "4809917                                                            530   \n",
       "4809918                                                            530   \n",
       "4809919                                                            630   \n",
       "...                                            ...                 ...   \n",
       "6077298  Damage to 10ft of Country road guardrail.                7837   \n",
       "6077299                                                           8610   \n",
       "6077300                                                           8514   \n",
       "6077301                                                           8365   \n",
       "6077302                                                           8395   \n",
       "\n",
       "                       Date Road Horizontal Alignment Road Grade  \\\n",
       "4809915 2017-08-10 17:35:00                        03         04   \n",
       "4809916 2017-08-10 17:36:00                        03         04   \n",
       "4809917 2017-09-27 21:27:00                        03         04   \n",
       "4809918 2017-09-27 21:28:00                        03         04   \n",
       "4809919 2017-12-18 13:21:00                        01         04   \n",
       "...                     ...                       ...        ...   \n",
       "6077298 2021-05-26 21:08:00                        02         04   \n",
       "6077299 2021-05-25 07:26:00                        01         04   \n",
       "6077300 2021-06-03 20:17:00                        03         04   \n",
       "6077301 2021-06-06 00:00:00                        01         04   \n",
       "6077302 2021-06-06 19:33:00                        01         04   \n",
       "\n",
       "        First Harmful Event reason        lon        lat  \n",
       "4809915                  26    NaN -74.490831  39.393658  \n",
       "4809916                  26    NaN -74.490831  39.393658  \n",
       "4809917                  48    NaN -74.490831  39.393658  \n",
       "4809918                  26    NaN -74.490831  39.393658  \n",
       "4809919                  26    NaN -74.489583  39.392377  \n",
       "...                     ...    ...        ...        ...  \n",
       "6077298                  46    NaN -75.075607  40.807156  \n",
       "6077299                  26    NaN -75.056122  40.844345  \n",
       "6077300                  24    NaN -75.015149  40.863108  \n",
       "6077301                  24    NaN -75.046404  40.840421  \n",
       "6077302                  26  No MP        NaN        NaN  \n",
       "\n",
       "[1267388 rows x 53 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cg17 = cg[cg.Date.dt.year >= 2017]\n",
    "cg17"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "5fbc6f24",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>County Code</th>\n",
       "      <th>Municipality Code</th>\n",
       "      <th>Department Case Number</th>\n",
       "      <th>County Name</th>\n",
       "      <th>Municipality Name</th>\n",
       "      <th>Police Department Code</th>\n",
       "      <th>Police Department</th>\n",
       "      <th>Police Station</th>\n",
       "      <th>Total Killed</th>\n",
       "      <th>Total Injured</th>\n",
       "      <th>Pedestrians Killed</th>\n",
       "      <th>Pedestrians Injured</th>\n",
       "      <th>Severity</th>\n",
       "      <th>Intersection</th>\n",
       "      <th>Alcohol Involved</th>\n",
       "      <th>HazMat Involved</th>\n",
       "      <th>Crash Type Code</th>\n",
       "      <th>Total Vehicles Involved</th>\n",
       "      <th>Crash Location</th>\n",
       "      <th>Location Direction</th>\n",
       "      <th>Route</th>\n",
       "      <th>Route Suffix</th>\n",
       "      <th>SRI (Standard Route Identifier)</th>\n",
       "      <th>Mile Post</th>\n",
       "      <th>Road System</th>\n",
       "      <th>Road Character</th>\n",
       "      <th>Road Surface Type</th>\n",
       "      <th>Surface Condition</th>\n",
       "      <th>Light Condition</th>\n",
       "      <th>Environmental Condition</th>\n",
       "      <th>Road Divided By</th>\n",
       "      <th>Temporary Traffic Control Zone</th>\n",
       "      <th>Distance To Cross Street</th>\n",
       "      <th>Unit Of Measurement</th>\n",
       "      <th>Direction From Cross Street</th>\n",
       "      <th>Cross Street Name</th>\n",
       "      <th>Is Ramp</th>\n",
       "      <th>Ramp To/From Route Name</th>\n",
       "      <th>Ramp To/From Route Direction</th>\n",
       "      <th>Posted Speed</th>\n",
       "      <th>Posted Speed Cross Street</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Cell Phone In Use Flag</th>\n",
       "      <th>Other Property Damage</th>\n",
       "      <th>Reporting Badge No.</th>\n",
       "      <th>Date</th>\n",
       "      <th>Road Horizontal Alignment</th>\n",
       "      <th>Road Grade</th>\n",
       "      <th>First Harmful Event</th>\n",
       "      <th>reason</th>\n",
       "      <th>lon</th>\n",
       "      <th>lat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4809920</th>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>I-2017-000179</td>\n",
       "      <td>ATLANTIC</td>\n",
       "      <td>ABSECON CITY</td>\n",
       "      <td>01</td>\n",
       "      <td>ABSECON CITY PD</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>06</td>\n",
       "      <td>2</td>\n",
       "      <td>720 S NEW ROAD</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>09</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>843</td>\n",
       "      <td>2017-01-03 11:57:00</td>\n",
       "      <td>01</td>\n",
       "      <td>04</td>\n",
       "      <td>28</td>\n",
       "      <td>No MP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4809939</th>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>I-2017-002241</td>\n",
       "      <td>ATLANTIC</td>\n",
       "      <td>ABSECON CITY</td>\n",
       "      <td>01</td>\n",
       "      <td>ABSECON CITY PD</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>I</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>03</td>\n",
       "      <td>2</td>\n",
       "      <td>HOBART AVE</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>07</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td>AT</td>\n",
       "      <td></td>\n",
       "      <td>OAKHURST AVE</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>25</td>\n",
       "      <td>25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>846</td>\n",
       "      <td>2017-01-25 16:41:00</td>\n",
       "      <td>01</td>\n",
       "      <td>04</td>\n",
       "      <td>26</td>\n",
       "      <td>No MP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4809940</th>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>I-2017-002303</td>\n",
       "      <td>ATLANTIC</td>\n",
       "      <td>ABSECON CITY</td>\n",
       "      <td>01</td>\n",
       "      <td>ABSECON CITY PD</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>I</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>05</td>\n",
       "      <td>2</td>\n",
       "      <td>CRESTVIEW AVE</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>07</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>02</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td>AT</td>\n",
       "      <td></td>\n",
       "      <td>ELM AVE</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>25</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>843</td>\n",
       "      <td>2017-01-26 10:05:00</td>\n",
       "      <td>01</td>\n",
       "      <td>04</td>\n",
       "      <td>26</td>\n",
       "      <td>No MP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4809948</th>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>I-2017-003718</td>\n",
       "      <td>ATLANTIC</td>\n",
       "      <td>ABSECON CITY</td>\n",
       "      <td>01</td>\n",
       "      <td>ABSECON CITY PD</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>I</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>06</td>\n",
       "      <td>2</td>\n",
       "      <td>221 NEW JERSEY AVE</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>09</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>OPI CRASHED V1 INTO THE FRONT WALL OF THE BANK...</td>\n",
       "      <td>857</td>\n",
       "      <td>2017-02-08 11:55:00</td>\n",
       "      <td>01</td>\n",
       "      <td>04</td>\n",
       "      <td>28</td>\n",
       "      <td>No MP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4809953</th>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>I-2017-004245</td>\n",
       "      <td>ATLANTIC</td>\n",
       "      <td>ABSECON CITY</td>\n",
       "      <td>01</td>\n",
       "      <td>ABSECON CITY PD</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>I</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>06</td>\n",
       "      <td>2</td>\n",
       "      <td>CONNECTICUT AVE</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>07</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td>100</td>\n",
       "      <td>FE</td>\n",
       "      <td>E</td>\n",
       "      <td>OAKHURST AVE</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>25</td>\n",
       "      <td>25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td></td>\n",
       "      <td>857</td>\n",
       "      <td>2017-02-13 08:43:00</td>\n",
       "      <td>01</td>\n",
       "      <td>06</td>\n",
       "      <td>26</td>\n",
       "      <td>No MP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <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",
       "      <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",
       "      <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>6077298</th>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>B150-2021-00117A</td>\n",
       "      <td>WARREN</td>\n",
       "      <td>WHITE TWP</td>\n",
       "      <td>02</td>\n",
       "      <td>NEW JERSEY STATE POLICE</td>\n",
       "      <td>WASHINGTON - SQ</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>I</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>WARREN COUNTY 620</td>\n",
       "      <td>N</td>\n",
       "      <td>620</td>\n",
       "      <td></td>\n",
       "      <td>21000620__</td>\n",
       "      <td>0.00</td>\n",
       "      <td>05</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>06</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td>AT</td>\n",
       "      <td></td>\n",
       "      <td>ROUTE 519 / S BRIDGEVILLE RD</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50</td>\n",
       "      <td>50</td>\n",
       "      <td>40.80707</td>\n",
       "      <td>75.07559</td>\n",
       "      <td>N</td>\n",
       "      <td>Damage to 10ft of Country road guardrail.</td>\n",
       "      <td>7837</td>\n",
       "      <td>2021-05-26 21:08:00</td>\n",
       "      <td>02</td>\n",
       "      <td>04</td>\n",
       "      <td>46</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-75.075607</td>\n",
       "      <td>40.807156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077299</th>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>B150-2021-00118A</td>\n",
       "      <td>WARREN</td>\n",
       "      <td>WHITE TWP</td>\n",
       "      <td>02</td>\n",
       "      <td>NEW JERSEY STATE POLICE</td>\n",
       "      <td>WASHINGTON - SQ</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>I</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>01</td>\n",
       "      <td>2</td>\n",
       "      <td>MANUNKA CHUNK RD / UPPER SEREPTA RD **</td>\n",
       "      <td>W</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>21231034__</td>\n",
       "      <td>1.73</td>\n",
       "      <td>07</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td>AT</td>\n",
       "      <td></td>\n",
       "      <td>MASSENATTS RD</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>40</td>\n",
       "      <td>50</td>\n",
       "      <td>40.84466</td>\n",
       "      <td>75.05549</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>8610</td>\n",
       "      <td>2021-05-25 07:26:00</td>\n",
       "      <td>01</td>\n",
       "      <td>04</td>\n",
       "      <td>26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-75.056122</td>\n",
       "      <td>40.844345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077300</th>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>B150-2021-00128A</td>\n",
       "      <td>WARREN</td>\n",
       "      <td>WHITE TWP</td>\n",
       "      <td>02</td>\n",
       "      <td>NEW JERSEY STATE POLICE</td>\n",
       "      <td>WASHINGTON - SQ</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>ROUTE 519</td>\n",
       "      <td>S</td>\n",
       "      <td>519</td>\n",
       "      <td></td>\n",
       "      <td>00000519__</td>\n",
       "      <td>44.00</td>\n",
       "      <td>05</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>02</td>\n",
       "      <td>05</td>\n",
       "      <td>04</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50</td>\n",
       "      <td></td>\n",
       "      <td>40.80564</td>\n",
       "      <td>75.07627</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>8514</td>\n",
       "      <td>2021-06-03 20:17:00</td>\n",
       "      <td>03</td>\n",
       "      <td>04</td>\n",
       "      <td>24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-75.015149</td>\n",
       "      <td>40.863108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077301</th>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>B150-2021-00130A</td>\n",
       "      <td>WARREN</td>\n",
       "      <td>WHITE TWP</td>\n",
       "      <td>02</td>\n",
       "      <td>NEW JERSEY STATE POLICE</td>\n",
       "      <td>WASHINGTON - SQ</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>US 46</td>\n",
       "      <td>E</td>\n",
       "      <td>46</td>\n",
       "      <td></td>\n",
       "      <td>00000046__</td>\n",
       "      <td>7.50</td>\n",
       "      <td>02</td>\n",
       "      <td></td>\n",
       "      <td>02</td>\n",
       "      <td>01</td>\n",
       "      <td>07</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>50</td>\n",
       "      <td></td>\n",
       "      <td>40.84149</td>\n",
       "      <td>75.04968</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>8365</td>\n",
       "      <td>2021-06-06 00:00:00</td>\n",
       "      <td>01</td>\n",
       "      <td>04</td>\n",
       "      <td>24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-75.046404</td>\n",
       "      <td>40.840421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6077302</th>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>B150-2021-00132A</td>\n",
       "      <td>WARREN</td>\n",
       "      <td>WHITE TWP</td>\n",
       "      <td>02</td>\n",
       "      <td>NEW JERSEY STATE POLICE</td>\n",
       "      <td>WASHINGTON - SQ</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>P</td>\n",
       "      <td>B</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>03</td>\n",
       "      <td>2</td>\n",
       "      <td>FIELDSTONE CT</td>\n",
       "      <td>W</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>07</td>\n",
       "      <td></td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>01</td>\n",
       "      <td>05</td>\n",
       "      <td>01</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>130 COUNTY ROAD 519</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>40.81220</td>\n",
       "      <td>75.04940</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>8395</td>\n",
       "      <td>2021-06-06 19:33:00</td>\n",
       "      <td>01</td>\n",
       "      <td>04</td>\n",
       "      <td>26</td>\n",
       "      <td>No MP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>627771 rows × 53 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        County Code Municipality Code Department Case Number County Name  \\\n",
       "4809920          01                01          I-2017-000179    ATLANTIC   \n",
       "4809939          01                01          I-2017-002241    ATLANTIC   \n",
       "4809940          01                01          I-2017-002303    ATLANTIC   \n",
       "4809948          01                01          I-2017-003718    ATLANTIC   \n",
       "4809953          01                01          I-2017-004245    ATLANTIC   \n",
       "...             ...               ...                    ...         ...   \n",
       "6077298          21                23       B150-2021-00117A      WARREN   \n",
       "6077299          21                23       B150-2021-00118A      WARREN   \n",
       "6077300          21                23       B150-2021-00128A      WARREN   \n",
       "6077301          21                23       B150-2021-00130A      WARREN   \n",
       "6077302          21                23       B150-2021-00132A      WARREN   \n",
       "\n",
       "        Municipality Name Police Department Code        Police Department  \\\n",
       "4809920      ABSECON CITY                     01          ABSECON CITY PD   \n",
       "4809939      ABSECON CITY                     01          ABSECON CITY PD   \n",
       "4809940      ABSECON CITY                     01          ABSECON CITY PD   \n",
       "4809948      ABSECON CITY                     01          ABSECON CITY PD   \n",
       "4809953      ABSECON CITY                     01          ABSECON CITY PD   \n",
       "...                   ...                    ...                      ...   \n",
       "6077298         WHITE TWP                     02  NEW JERSEY STATE POLICE   \n",
       "6077299         WHITE TWP                     02  NEW JERSEY STATE POLICE   \n",
       "6077300         WHITE TWP                     02  NEW JERSEY STATE POLICE   \n",
       "6077301         WHITE TWP                     02  NEW JERSEY STATE POLICE   \n",
       "6077302         WHITE TWP                     02  NEW JERSEY STATE POLICE   \n",
       "\n",
       "          Police Station  Total Killed  Total Injured  Pedestrians Killed  \\\n",
       "4809920                              0              0                   0   \n",
       "4809939                              0              0                   0   \n",
       "4809940                              0              0                   0   \n",
       "4809948                              0              1                   0   \n",
       "4809953                              0              1                   0   \n",
       "...                  ...           ...            ...                 ...   \n",
       "6077298  WASHINGTON - SQ             0              0                   0   \n",
       "6077299  WASHINGTON - SQ             0              0                   0   \n",
       "6077300  WASHINGTON - SQ             0              0                   0   \n",
       "6077301  WASHINGTON - SQ             0              0                   0   \n",
       "6077302  WASHINGTON - SQ             0              0                   0   \n",
       "\n",
       "         Pedestrians Injured Severity Intersection  Alcohol Involved  \\\n",
       "4809920                    0        P            B             False   \n",
       "4809939                    0        P            I             False   \n",
       "4809940                    0        P            I             False   \n",
       "4809948                    0        I            B             False   \n",
       "4809953                    0        I            B             False   \n",
       "...                      ...      ...          ...               ...   \n",
       "6077298                    0        P            I             False   \n",
       "6077299                    0        P            I             False   \n",
       "6077300                    0        P            B             False   \n",
       "6077301                    0        P            B             False   \n",
       "6077302                    0        P            B             False   \n",
       "\n",
       "         HazMat Involved Crash Type Code  Total Vehicles Involved  \\\n",
       "4809920            False              06                        2   \n",
       "4809939            False              03                        2   \n",
       "4809940            False              05                        2   \n",
       "4809948            False              06                        2   \n",
       "4809953            False              06                        2   \n",
       "...                  ...             ...                      ...   \n",
       "6077298            False              11                        1   \n",
       "6077299            False              01                        2   \n",
       "6077300            False              12                        1   \n",
       "6077301            False              12                        1   \n",
       "6077302            False              03                        2   \n",
       "\n",
       "                                 Crash Location Location Direction Route  \\\n",
       "4809920                          720 S NEW ROAD                            \n",
       "4809939                              HOBART AVE                            \n",
       "4809940                           CRESTVIEW AVE                            \n",
       "4809948                      221 NEW JERSEY AVE                            \n",
       "4809953                         CONNECTICUT AVE                            \n",
       "...                                         ...                ...   ...   \n",
       "6077298                       WARREN COUNTY 620                  N   620   \n",
       "6077299  MANUNKA CHUNK RD / UPPER SEREPTA RD **                  W         \n",
       "6077300                               ROUTE 519                  S   519   \n",
       "6077301                                   US 46                  E    46   \n",
       "6077302                           FIELDSTONE CT                  W         \n",
       "\n",
       "        Route Suffix SRI (Standard Route Identifier)  Mile Post Road System  \\\n",
       "4809920                                                     NaN          09   \n",
       "4809939                                                     NaN          07   \n",
       "4809940                                                     NaN          07   \n",
       "4809948                                                     NaN          09   \n",
       "4809953                                                     NaN          07   \n",
       "...              ...                             ...        ...         ...   \n",
       "6077298                                   21000620__       0.00          05   \n",
       "6077299                                   21231034__       1.73          07   \n",
       "6077300                                   00000519__      44.00          05   \n",
       "6077301                                   00000046__       7.50          02   \n",
       "6077302                                                     NaN          07   \n",
       "\n",
       "        Road Character Road Surface Type Surface Condition Light Condition  \\\n",
       "4809920                               02                01              01   \n",
       "4809939                               02                01              01   \n",
       "4809940                               02                02              01   \n",
       "4809948                               02                01              01   \n",
       "4809953                               02                01              01   \n",
       "...                ...               ...               ...             ...   \n",
       "6077298                               02                01              06   \n",
       "6077299                               02                01              01   \n",
       "6077300                               02                02              05   \n",
       "6077301                               02                01              07   \n",
       "6077302                               05                01              01   \n",
       "\n",
       "        Environmental Condition Road Divided By  \\\n",
       "4809920                      01              05   \n",
       "4809939                      01              05   \n",
       "4809940                      02              05   \n",
       "4809948                      01              05   \n",
       "4809953                      01              05   \n",
       "...                         ...             ...   \n",
       "6077298                      01              05   \n",
       "6077299                      01              05   \n",
       "6077300                      04              05   \n",
       "6077301                      01              05   \n",
       "6077302                      01              05   \n",
       "\n",
       "        Temporary Traffic Control Zone Distance To Cross Street  \\\n",
       "4809920                             01                            \n",
       "4809939                             01                            \n",
       "4809940                             01                            \n",
       "4809948                             01                            \n",
       "4809953                             01                      100   \n",
       "...                                ...                      ...   \n",
       "6077298                             01                            \n",
       "6077299                             01                            \n",
       "6077300                             01                            \n",
       "6077301                             01                            \n",
       "6077302                             01                            \n",
       "\n",
       "        Unit Of Measurement Direction From Cross Street  \\\n",
       "4809920                                                   \n",
       "4809939                  AT                               \n",
       "4809940                  AT                               \n",
       "4809948                                                   \n",
       "4809953                  FE                           E   \n",
       "...                     ...                         ...   \n",
       "6077298                  AT                               \n",
       "6077299                  AT                               \n",
       "6077300                                                   \n",
       "6077301                                                   \n",
       "6077302                                                   \n",
       "\n",
       "                    Cross Street Name Is Ramp Ramp To/From Route Name  \\\n",
       "4809920                                                                 \n",
       "4809939                  OAKHURST AVE                                   \n",
       "4809940                       ELM AVE                                   \n",
       "4809948                                                                 \n",
       "4809953                  OAKHURST AVE                                   \n",
       "...                               ...     ...                     ...   \n",
       "6077298  ROUTE 519 / S BRIDGEVILLE RD                                   \n",
       "6077299                 MASSENATTS RD                                   \n",
       "6077300                                                                 \n",
       "6077301                                                                 \n",
       "6077302           130 COUNTY ROAD 519                                   \n",
       "\n",
       "        Ramp To/From Route Direction Posted Speed Posted Speed Cross Street  \\\n",
       "4809920                                                                       \n",
       "4809939                                        25                        25   \n",
       "4809940                                        25                             \n",
       "4809948                                                                       \n",
       "4809953                                        25                        25   \n",
       "...                              ...          ...                       ...   \n",
       "6077298                                        50                        50   \n",
       "6077299                                        40                        50   \n",
       "6077300                                        50                             \n",
       "6077301                                        50                             \n",
       "6077302                                        15                         5   \n",
       "\n",
       "         Latitude  Longitude Cell Phone In Use Flag  \\\n",
       "4809920       NaN        NaN                      N   \n",
       "4809939       NaN        NaN                      N   \n",
       "4809940       NaN        NaN                      N   \n",
       "4809948       NaN        NaN                      N   \n",
       "4809953       NaN        NaN                      Y   \n",
       "...           ...        ...                    ...   \n",
       "6077298  40.80707   75.07559                      N   \n",
       "6077299  40.84466   75.05549                      N   \n",
       "6077300  40.80564   75.07627                      N   \n",
       "6077301  40.84149   75.04968                      N   \n",
       "6077302  40.81220   75.04940                      N   \n",
       "\n",
       "                                     Other Property Damage  \\\n",
       "4809920                                                      \n",
       "4809939                                                      \n",
       "4809940                                                      \n",
       "4809948  OPI CRASHED V1 INTO THE FRONT WALL OF THE BANK...   \n",
       "4809953                                                      \n",
       "...                                                    ...   \n",
       "6077298          Damage to 10ft of Country road guardrail.   \n",
       "6077299                                                      \n",
       "6077300                                                      \n",
       "6077301                                                      \n",
       "6077302                                                      \n",
       "\n",
       "        Reporting Badge No.                Date Road Horizontal Alignment  \\\n",
       "4809920                 843 2017-01-03 11:57:00                        01   \n",
       "4809939                 846 2017-01-25 16:41:00                        01   \n",
       "4809940                 843 2017-01-26 10:05:00                        01   \n",
       "4809948                 857 2017-02-08 11:55:00                        01   \n",
       "4809953                 857 2017-02-13 08:43:00                        01   \n",
       "...                     ...                 ...                       ...   \n",
       "6077298                7837 2021-05-26 21:08:00                        02   \n",
       "6077299                8610 2021-05-25 07:26:00                        01   \n",
       "6077300                8514 2021-06-03 20:17:00                        03   \n",
       "6077301                8365 2021-06-06 00:00:00                        01   \n",
       "6077302                8395 2021-06-06 19:33:00                        01   \n",
       "\n",
       "        Road Grade First Harmful Event reason        lon        lat  \n",
       "4809920         04                  28  No MP        NaN        NaN  \n",
       "4809939         04                  26  No MP        NaN        NaN  \n",
       "4809940         04                  26  No MP        NaN        NaN  \n",
       "4809948         04                  28  No MP        NaN        NaN  \n",
       "4809953         06                  26  No MP        NaN        NaN  \n",
       "...            ...                 ...    ...        ...        ...  \n",
       "6077298         04                  46    NaN -75.075607  40.807156  \n",
       "6077299         04                  26    NaN -75.056122  40.844345  \n",
       "6077300         04                  24    NaN -75.015149  40.863108  \n",
       "6077301         04                  24    NaN -75.046404  40.840421  \n",
       "6077302         04                  26  No MP        NaN        NaN  \n",
       "\n",
       "[627771 rows x 53 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "g17 = (~cg17.reason.isna()) | (~cg17.Latitude.isna())\n",
    "cgg17 = cg17[g17]\n",
    "cgg17"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}