Skip to content

Instantly share code, notes, and snippets.

@HariSan1

HariSan1/folium-nj-2017-crashes-gist.ipynb

Created December 27, 2018 02:28
Show Gist options
  • Save HariSan1/0245dca9ba3b32caf9b59ff81a4bd9b5 to your computer and use it in GitHub Desktop.
Save HariSan1/0245dca9ba3b32caf9b59ff81a4bd9b5 to your computer and use it in GitHub Desktop.
Download ZIP
folium-nj-2017-crashes-gist.ipynb
Display the source blob
Display the rendered blob
Raw
folium-nj-2017-crashes-gist.ipynb
{
"nbformat": 4,
"nbformat_minor": 0,
"metadata": {
"colab": {
"name": "folium-nj-2017-crashes-gist.ipynb",
"version": "0.3.2",
"provenance": [],
"collapsed_sections": [],
"include_colab_link": true
},
"kernelspec": {
"name": "python3",
"display_name": "Python 3"
},
"accelerator": "GPU"
},
"cells": [
{
"cell_type": "markdown",
"metadata": {
"id": "view-in-github",
"colab_type": "text"
},
"source": [
"<a href=\"https://colab.research.google.com/gist/HariSan1/0245dca9ba3b32caf9b59ff81a4bd9b5/folium-nj-2017-crashes-gist.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
]
},
{
"metadata": {
"id": "_l2fdXsXe-xO",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"# **NJ 2017 Traffic Accidents visualization program using Python and Folium**\n",
"\n",
"I collected traffic accident data from the State of New Jersey (NJ), USA for the year 2017, the latest year for which there is data on the NJ state government web site. I am glad for the open policy of sharing data with the citizens, though the data organization is somewhat quirky (my opinion!), and it is not, as might be expected, straight forward.\n",
"**I collected, cleaned, analyzed the data, then created a heat map with time that shows accidents by location, for the year, over a map of NJ, for visualization.** The article will be published in medium.com.\n",
"\n",
"Before going forward, to those who may not be familiar, the state of New Jersey is located to the west of New York City and extends west and south (and a little north). After all, what is the point of a located-based analysis, without, er, some location background? Basically, anyone going from New York City to the rest of the US (except North to the New England region and Canada) by automobile has to pass through NJ, and the state does have an excellent connection of highways; it also has a dense population of suburbs and once you get past many kilometers of tightly knit older towns, there are suburban, pastoral and even rural areas. Because automobile use is so high, for daily commuting as well as everyday tasks and through traffic from Washington DC and Philadelphia to New York and Boston, the accident rate is high, especially during snow storms and adverse weather. Many people use the highways often to go to the shore in the summer, skiing in winter, and to visit friends and family all over, in addition to work and education.\n",
"Data prep and cleansing\n",
"Search [and research] data. I obtained the data from the New Jersey State government site. Latitude and Longitude (Lat, Long) are geographic coordinate points that can map locations on any point on Earth. Read here for more information about them.\n",
"Clean and format the data. Read below:\n",
"\n",
"I used the summary data for the whole state for 2017, the latest year for which there is data. There is a separate file that lists the headers and their descriptions. I copied the header file into Excel, parsed it, then pasted that using a text editor.\n",
"The header file - I copied from the pdf into Excel then parsed it.2nd step: Deleted some items, saved as csv file.Out of approximately 277k+ rows, only 70k+ had Latitude and Longitude coordinates (26%). Since the objective of the program is a visual representation overlay of accident data across NJ, this became a challenge. Here's what I did:\n",
"Separated the rows with no Lat, Long coordinates into a new pandas dataframe.\n",
"Since these rows had a town name (but no lat, long coordinates for the exact accident location in that town), I decided to do a heatmap for the towns for this data separately from the ones with the Lat, Long coordinates.\n",
"So there are two sets of heat maps, one for the dataset with precise Latitude and Longitude coordinates, the other for the other data with only town information, for which I used code to get the town Lat, Long coordinates - this dataset is far larger, comprising 74% of the total data! This is real-life data - often incomplete and in need of cleansing and \"prepping\" (preparing). \n",
"I converted Lat and Long fields from string to numeric.\n",
"For the first dataset with Lat and Long coordinates, I found that the negative sign for the Longitude wasn't there, so I multiplied all of them by -1 to get that.\n",
"The first column wasn't that useful to me, so I labeled it \"mumbo-jumbo\". It is a concatenation of some information.\n",
"Check the data for missing Lat and Long field info and split the file into two dataframes - one with good data and one with missing data. More on that after the initialization code snippet below."
]
},
{
"metadata": {
"id": "RYfzvI12TWNE",
"colab_type": "code",
"colab": {}
},
"cell_type": "code",
"source": [
"#above steps are ONLY for Colab users, to copy the data file from My Drive\n",
"#from google.colab import drive\n",
"#drive.mount('/content/drive/')"
],
"execution_count": 0,
"outputs": []
},
{
"metadata": {
"id": "AG9EkhC5muiC",
"colab_type": "code",
"outputId": "e332ddc8-b04f-4605-9299-9fca57b34d2b",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 51
}
},
"cell_type": "code",
"source": [
"#take the following step ONLY if HeatMapWithTime doesn't work properly way down below. If it does, then ignore this. If you un-install it, you need to re-install it :)\n",
"!pip uninstall folium -y"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"Uninstalling folium-0.7.0:\n",
" Successfully uninstalled folium-0.7.0\n"
],
"name": "stdout"
}
]
},
{
"metadata": {
"id": "HGpgghUwnBWz",
"colab_type": "code",
"outputId": "1e3e7e4d-2f15-4833-9747-670075858882",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 272
}
},
"cell_type": "code",
"source": [
"!pip install folium"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"Collecting folium\n",
" Using cached https://files.pythonhosted.org/packages/55/e2/7e523df8558b7f4b2ab4c62014fd378ccecce3fdc14c9928b272a88ae4cc/folium-0.7.0-py3-none-any.whl\n",
"Requirement already satisfied: branca>=0.3.0 in /usr/local/lib/python3.6/dist-packages (from folium) (0.3.1)\n",
"Requirement already satisfied: requests in /usr/local/lib/python3.6/dist-packages (from folium) (2.18.4)\n",
"Requirement already satisfied: jinja2 in /usr/local/lib/python3.6/dist-packages (from folium) (2.10)\n",
"Requirement already satisfied: numpy in /usr/local/lib/python3.6/dist-packages (from folium) (1.14.6)\n",
"Requirement already satisfied: six in /usr/local/lib/python3.6/dist-packages (from folium) (1.11.0)\n",
"Requirement already satisfied: urllib3<1.23,>=1.21.1 in /usr/local/lib/python3.6/dist-packages (from requests->folium) (1.22)\n",
"Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.6/dist-packages (from requests->folium) (2018.11.29)\n",
"Requirement already satisfied: idna<2.7,>=2.5 in /usr/local/lib/python3.6/dist-packages (from requests->folium) (2.6)\n",
"Requirement already satisfied: chardet<3.1.0,>=3.0.2 in /usr/local/lib/python3.6/dist-packages (from requests->folium) (3.0.4)\n",
"Requirement already satisfied: MarkupSafe>=0.23 in /usr/local/lib/python3.6/dist-packages (from jinja2->folium) (1.1.0)\n",
"\u001b[31mdatascience 0.10.6 has requirement folium==0.2.1, but you'll have folium 0.7.0 which is incompatible.\u001b[0m\n",
"Installing collected packages: folium\n",
"Successfully installed folium-0.7.0\n"
],
"name": "stdout"
}
]
},
{
"metadata": {
"id": "EpLXXLBCnVn4",
"colab_type": "code",
"colab": {}
},
"cell_type": "code",
"source": [
"import folium\n",
"import folium.plugins as plugins\n",
"from folium.plugins import HeatMapWithTime"
],
"execution_count": 0,
"outputs": []
},
{
"metadata": {
"id": "4XHaHj7cSyB1",
"colab_type": "code",
"outputId": "c3f3127c-33f0-421e-b4de-9378ca877dd0",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 51
}
},
"cell_type": "code",
"source": [
"import pandas as pd\n",
"import folium\n",
"column_names =['Mumbo-jumbo','County Name','Municipality Name','Crash Date','Crash Day Of Week','Crash Time','Police Dept Code','Police Department','Police Station','Total Killed','Total Injured',\n",
" 'Pedestrians Killed','Pedestrians Injured','Severity','Intersection','Alcohol Involved','HazMat Involved','Crash Type Code','Total Vehicles Involved','Crash Location','Location Direction',\n",
" 'Route','Route Suffix','SRI (Std Rte Identifier)','MilePost','Road System','Road Character','Road Horizontal Alignment','Road Grade','Road Surface Type','Surface Condition','Light Condition',\n",
" 'Environmental Condition','Road Divided By','Temporary Traffic Control Zone','Distance To Cross Street','Unit Of Measurement','Directn From Cross Street','Cross Street Name',\n",
" 'Is Ramp','Ramp To/From Route Name','Ramp To/From Route Direction','Posted Speed','Posted Speed Cross Street','First Harmful Event','Latitude','Longitude',\n",
" 'Cell Phone In Use Flag','Other Property Damage','Reporting Badge No']\n",
"#df1 = pd.read_csv('/content/drive/My Drive/Colab/Somerset2017Accidents.txt', header=None)\n",
"df1 = pd.read_csv('/content/drive/My Drive/Colab/NewJersey2017Accidents.txt', header=None)\n",
"df1.columns = column_names"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"/usr/local/lib/python3.6/dist-packages/IPython/core/interactiveshell.py:2718: DtypeWarning: Columns (25) have mixed types. Specify dtype option on import or set low_memory=False.\n",
" interactivity=interactivity, compiler=compiler, result=result)\n"
],
"name": "stderr"
}
]
},
{
"metadata": {
"id": "61BFjJkwUayt",
"colab_type": "code",
"outputId": "6cf69e9c-a0ee-40fa-8068-ce8edb41ae3c",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 34
}
},
"cell_type": "code",
"source": [
"df1.shape"
],
"execution_count": 0,
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": [
"(277508, 50)"
]
},
"metadata": {
"tags": []
},
"execution_count": 3
}
]
},
{
"metadata": {
"id": "FtK1oDzmUfAi",
"colab_type": "code",
"outputId": "e41ab0ce-a419-4c9f-9471-ecca81bba4c4",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 406
}
},
"cell_type": "code",
"source": [
"df1.head()"
],
"execution_count": 0,
"outputs": [
{
"output_type": "execute_result",
"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>Mumbo-jumbo</th>\n",
" <th>County Name</th>\n",
" <th>Municipality Name</th>\n",
" <th>Crash Date</th>\n",
" <th>Crash Day Of Week</th>\n",
" <th>Crash Time</th>\n",
" <th>Police Dept Code</th>\n",
" <th>Police Department</th>\n",
" <th>Police Station</th>\n",
" <th>Total Killed</th>\n",
" <th>...</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>First Harmful Event</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",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>201701011708-0494</td>\n",
" <td>ATLANTIC</td>\n",
" <td>ABSECON CITY</td>\n",
" <td>08/10/2017</td>\n",
" <td>TH</td>\n",
" <td>1735</td>\n",
" <td>1.0</td>\n",
" <td>ATLANTIC CITY PD</td>\n",
" <td></td>\n",
" <td>0.0</td>\n",
" <td>...</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>50</td>\n",
" <td>35</td>\n",
" <td>26</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>N</td>\n",
" <td>...</td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>201701011708-0497</td>\n",
" <td>ATLANTIC</td>\n",
" <td>ABSECON CITY</td>\n",
" <td>08/10/2017</td>\n",
" <td>TH</td>\n",
" <td>1736</td>\n",
" <td>1.0</td>\n",
" <td>ATLANTIC CITY PD</td>\n",
" <td></td>\n",
" <td>0.0</td>\n",
" <td>...</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>50</td>\n",
" <td>35</td>\n",
" <td>26</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>N</td>\n",
" <td>...</td>\n",
" <td>530</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>201701011709-1131</td>\n",
" <td>ATLANTIC</td>\n",
" <td>ABSECON CITY</td>\n",
" <td>09/27/2017</td>\n",
" <td>WE</td>\n",
" <td>2127</td>\n",
" <td>1.0</td>\n",
" <td>ATLANTIC CITY PD</td>\n",
" <td></td>\n",
" <td>0.0</td>\n",
" <td>...</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>50</td>\n",
" <td>35</td>\n",
" <td>48</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>N</td>\n",
" <td>...</td>\n",
" <td>530</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>201701011709-1132</td>\n",
" <td>ATLANTIC</td>\n",
" <td>ABSECON CITY</td>\n",
" <td>09/27/2017</td>\n",
" <td>WE</td>\n",
" <td>2128</td>\n",
" <td>1.0</td>\n",
" <td>ATLANTIC CITY PD</td>\n",
" <td></td>\n",
" <td>0.0</td>\n",
" <td>...</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>50</td>\n",
" <td>35</td>\n",
" <td>26</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>N</td>\n",
" <td>...</td>\n",
" <td>530</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>201701011712-0555</td>\n",
" <td>ATLANTIC</td>\n",
" <td>ABSECON CITY</td>\n",
" <td>12/18/2017</td>\n",
" <td>MO</td>\n",
" <td>1321</td>\n",
" <td>1.0</td>\n",
" <td>ATLANTIC CITY PD</td>\n",
" <td>TRAFFIC</td>\n",
" <td>0.0</td>\n",
" <td>...</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>50</td>\n",
" <td></td>\n",
" <td>26</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>N</td>\n",
" <td>...</td>\n",
" <td>630</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>5 rows × 50 columns</p>\n",
"</div>"
],
"text/plain": [
" Mumbo-jumbo County Name Municipality Name \\\n",
"0 201701011708-0494 ATLANTIC ABSECON CITY \n",
"1 201701011708-0497 ATLANTIC ABSECON CITY \n",
"2 201701011709-1131 ATLANTIC ABSECON CITY \n",
"3 201701011709-1132 ATLANTIC ABSECON CITY \n",
"4 201701011712-0555 ATLANTIC ABSECON CITY \n",
"\n",
" Crash Date Crash Day Of Week Crash Time Police Dept Code \\\n",
"0 08/10/2017 TH 1735 1.0 \n",
"1 08/10/2017 TH 1736 1.0 \n",
"2 09/27/2017 WE 2127 1.0 \n",
"3 09/27/2017 WE 2128 1.0 \n",
"4 12/18/2017 MO 1321 1.0 \n",
"\n",
" Police Department Police Station Total Killed \\\n",
"0 ATLANTIC CITY PD 0.0 \n",
"1 ATLANTIC CITY PD 0.0 \n",
"2 ATLANTIC CITY PD 0.0 \n",
"3 ATLANTIC CITY PD 0.0 \n",
"4 ATLANTIC CITY PD TRAFFIC 0.0 \n",
"\n",
" ... Ramp To/From Route Name Ramp To/From Route Direction \\\n",
"0 ... \n",
"1 ... \n",
"2 ... \n",
"3 ... \n",
"4 ... \n",
"\n",
" Posted Speed Posted Speed Cross Street First Harmful Event Latitude \\\n",
"0 50 35 26 \n",
"1 50 35 26 \n",
"2 50 35 48 \n",
"3 50 35 26 \n",
"4 50 26 \n",
"\n",
" Longitude Cell Phone In Use Flag \\\n",
"0 N \n",
"1 N \n",
"2 N \n",
"3 N \n",
"4 N \n",
"\n",
" Other Property Damage Reporting Badge No \n",
"0 ... \n",
"1 ... 530 \n",
"2 ... 530 \n",
"3 ... 530 \n",
"4 ... 630 \n",
"\n",
"[5 rows x 50 columns]"
]
},
"metadata": {
"tags": []
},
"execution_count": 8
}
]
},
{
"metadata": {
"id": "qxxSHZyV9UFz",
"colab_type": "code",
"colab": {}
},
"cell_type": "code",
"source": [
"#some queries on our data, peek and see some content\n",
"#print(df1.loc[(df1['Cell Phone In Use Flag'] == 'Y'),['Posted Speed','Police Station','Latitude','Longitude']])\n",
"#print(df1.loc[(df1['Crash Day Of Week'] == 'FR'),['Municipality Name','Posted Speed','Police Station','Latitude','Longitude']])\n",
"#print(df1.loc[(df1['Municipality Name'] == 'WATCHUNG BORO'), ['Municipality Name','Posted Speed','Police Station','Latitude','Longitude']])"
],
"execution_count": 0,
"outputs": []
},
{
"metadata": {
"id": "3ADuLa1XlZMI",
"colab_type": "code",
"colab": {}
},
"cell_type": "code",
"source": [
"#convert 'Crash Date' field to python pandas readable month/ day/ year format \n",
"df1['Crash Date'] = pd.to_datetime(df1['Crash Date'], format = '%m/%d/%Y')"
],
"execution_count": 0,
"outputs": []
},
{
"metadata": {
"id": "E2N3wg-qAKn7",
"colab_type": "code",
"colab": {}
},
"cell_type": "code",
"source": [
"#convert Latitude, Longitude columns from string to numeric\n",
"cols_to_convert = ['Latitude', 'Longitude']\n",
"for col in cols_to_convert:\n",
" df1[col] = pd.to_numeric(df1[col], errors='coerce')"
],
"execution_count": 0,
"outputs": []
},
{
"metadata": {
"id": "hWG3d7bqoAv6",
"colab_type": "code",
"outputId": "9a4e3339-c61d-4d40-8c9b-a0f6432a5e46",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 884
}
},
"cell_type": "code",
"source": [
"print(df1.dtypes)"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"Mumbo-jumbo object\n",
"County Name object\n",
"Municipality Name object\n",
"Crash Date datetime64[ns]\n",
"Crash Day Of Week object\n",
"Crash Time object\n",
"Police Dept Code float64\n",
"Police Department object\n",
"Police Station object\n",
"Total Killed float64\n",
"Total Injured float64\n",
"Pedestrians Killed float64\n",
"Pedestrians Injured float64\n",
"Severity object\n",
"Intersection object\n",
"Alcohol Involved object\n",
"HazMat Involved object\n",
"Crash Type Code object\n",
"Total Vehicles Involved float64\n",
"Crash Location object\n",
"Location Direction object\n",
"Route object\n",
"Route Suffix object\n",
"SRI (Std Rte Identifier) object\n",
"MilePost object\n",
"Road System object\n",
"Road Character object\n",
"Road Horizontal Alignment object\n",
"Road Grade object\n",
"Road Surface Type object\n",
"Surface Condition object\n",
"Light Condition object\n",
"Environmental Condition object\n",
"Road Divided By object\n",
"Temporary Traffic Control Zone object\n",
"Distance To Cross Street object\n",
"Unit Of Measurement object\n",
"Directn From Cross Street object\n",
"Cross Street Name object\n",
"Is Ramp object\n",
"Ramp To/From Route Name object\n",
"Ramp To/From Route Direction object\n",
"Posted Speed object\n",
"Posted Speed Cross Street object\n",
"First Harmful Event object\n",
"Latitude float64\n",
"Longitude float64\n",
"Cell Phone In Use Flag object\n",
"Other Property Damage object\n",
"Reporting Badge No object\n",
"dtype: object\n"
],
"name": "stdout"
}
]
},
{
"metadata": {
"id": "pA67Aale0c2I",
"colab_type": "code",
"outputId": "61bc5018-061b-4ada-856d-ae850297cf8c",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 884
}
},
"cell_type": "code",
"source": [
"df1.isna().sum(axis = 0)"
],
"execution_count": 0,
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": [
"Mumbo-jumbo 0\n",
"County Name 0\n",
"Municipality Name 0\n",
"Crash Date 0\n",
"Crash Day Of Week 0\n",
"Crash Time 0\n",
"Police Dept Code 0\n",
"Police Department 0\n",
"Police Station 0\n",
"Total Killed 0\n",
"Total Injured 0\n",
"Pedestrians Killed 0\n",
"Pedestrians Injured 0\n",
"Severity 0\n",
"Intersection 0\n",
"Alcohol Involved 0\n",
"HazMat Involved 0\n",
"Crash Type Code 0\n",
"Total Vehicles Involved 0\n",
"Crash Location 0\n",
"Location Direction 0\n",
"Route 0\n",
"Route Suffix 0\n",
"SRI (Std Rte Identifier) 0\n",
"MilePost 0\n",
"Road System 0\n",
"Road Character 0\n",
"Road Horizontal Alignment 0\n",
"Road Grade 0\n",
"Road Surface Type 0\n",
"Surface Condition 0\n",
"Light Condition 0\n",
"Environmental Condition 0\n",
"Road Divided By 0\n",
"Temporary Traffic Control Zone 0\n",
"Distance To Cross Street 0\n",
"Unit Of Measurement 0\n",
"Directn From Cross Street 0\n",
"Cross Street Name 0\n",
"Is Ramp 0\n",
"Ramp To/From Route Name 0\n",
"Ramp To/From Route Direction 0\n",
"Posted Speed 0\n",
"Posted Speed Cross Street 0\n",
"First Harmful Event 0\n",
"Latitude 0\n",
"Longitude 0\n",
"Cell Phone In Use Flag 0\n",
"Other Property Damage 6\n",
"Reporting Badge No 802\n",
"dtype: int64"
]
},
"metadata": {
"tags": []
},
"execution_count": 40
}
]
},
{
"metadata": {
"id": "UEFCaCFAHq-U",
"colab_type": "code",
"outputId": "20961448-1a1b-4f38-b751-08ae34202c7b",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 34
}
},
"cell_type": "code",
"source": [
"\t\n",
"#print(df1.isnull().sum())\n",
"#put all records with no data(NaN) for Lat and Long in separate dataframe (df2)\n",
"df2 = df1.loc[df1.Latitude.isnull()]\n",
"#df2 = df1.loc[df1.Latitude.isnull()] & df1.loc[df1.Longitude.isnull()]\n",
"df2.shape\n",
"#df2.head()\n",
"\n",
"#drop records with NaN in Lat and Long from df1 (they are saved above in df2)\n",
"df1 = df1.dropna(subset=['Latitude','Longitude'])\n",
"df1.shape\n"
],
"execution_count": 0,
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": [
"(73325, 50)"
]
},
"metadata": {
"tags": []
},
"execution_count": 6
}
]
},
{
"metadata": {
"id": "5sgeCOdrt36i",
"colab_type": "code",
"outputId": "5283fc67-77f8-43b8-8e06-31635c7774d3",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 34
}
},
"cell_type": "code",
"source": [
"df2.shape"
],
"execution_count": 0,
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": [
"(204183, 50)"
]
},
"metadata": {
"tags": []
},
"execution_count": 7
}
]
},
{
"metadata": {
"id": "fF0tb7KEsX8l",
"colab_type": "code",
"colab": {}
},
"cell_type": "code",
"source": [
"#Longitude values in the original data didn't have the negative (-) sign - this code below fixes that by replacing all Lat values with Lat * -1.\n",
"#df1.replace(df1.loc[(df1['Longitude']), df1 * -1.0])\n",
"df1['Longitude']=df1['Longitude']* -1"
],
"execution_count": 0,
"outputs": []
},
{
"metadata": {
"id": "vE0c7zFInY1l",
"colab_type": "code",
"outputId": "b73e58cb-2566-48ef-f0d6-a24a39cc12be",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 1105
}
},
"cell_type": "code",
"source": [
"#list accidents where one or more person killed - very serious ones\n",
"print(df1.loc[(df1['Total Killed'] >= 1.0), ['Municipality Name','Latitude','Longitude', 'Total Killed']])"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
" Municipality Name Latitude Longitude Total Killed\n",
"2202 BUENA BORO 39.51293 -74.97905 1.0\n",
"2670 EGG HARBOR TWP 39.40520 -74.56310 1.0\n",
"2700 EGG HARBOR TWP 39.35784 -74.65009 1.0\n",
"2858 EGG HARBOR TWP 39.37008 -74.61428 1.0\n",
"2958 EGG HARBOR TWP 39.39997 -74.55230 1.0\n",
"3161 EGG HARBOR TWP 39.43721 -74.61458 1.0\n",
"3383 EGG HARBOR TWP 39.37940 -74.49257 1.0\n",
"3492 EGG HARBOR TWP 39.39670 -74.54873 1.0\n",
"3589 EGG HARBOR TWP 39.34932 -74.67085 1.0\n",
"3785 EGG HARBOR TWP 39.39763 -74.55200 1.0\n",
"4262 FOLSOM BORO 39.59821 -74.86389 1.0\n",
"4289 FOLSOM BORO 39.60346 -74.88128 1.0\n",
"5189 HAMILTON TWP 39.52619 -74.81947 1.0\n",
"5309 HAMILTON TWP 39.43148 -74.69208 1.0\n",
"5338 HAMILTON TWP 39.44247 -74.79372 2.0\n",
"5353 HAMILTON TWP 39.44676 -74.74565 1.0\n",
"5475 HAMILTON TWP 39.42728 -74.64285 1.0\n",
"5813 HAMILTON TWP 39.44657 -74.68614 1.0\n",
"5859 HAMILTON TWP 39.55212 -74.81070 1.0\n",
"5959 HAMILTON TWP 39.44795 -74.62213 1.0\n",
"5992 HAMILTON TWP 39.48982 -74.65333 1.0\n",
"6021 HAMILTON TWP 39.54839 -74.72929 1.0\n",
"6051 HAMILTON TWP 39.57983 -74.77720 1.0\n",
"7687 PORT REPUBLIC CITY 39.53573 -74.49304 1.0\n",
"8312 ALPINE BORO 40.94445 -73.92194 1.0\n",
"12381 ENGLEWOOD CITY 40.87503 -73.98452 1.0\n",
"19543 HACKENSACK CITY 40.86360 -74.03470 1.0\n",
"30643 RIDGEFIELD PARK VILLAGE 40.85694 -74.02194 2.0\n",
"33129 SADDLE BROOK TWP 40.90393 -74.10446 1.0\n",
"37758 BASS RIVER TWP 39.59096 -74.46003 1.0\n",
"... ... ... ... ...\n",
"241089 BEDMINSTER TWP 40.64299 -74.63875 1.0\n",
"241180 BERNARDS TWP 40.64346 -74.60506 2.0\n",
"244153 BRIDGEWATER TWP 40.60197 -74.62751 1.0\n",
"244242 BRIDGEWATER TWP 40.58063 -74.58287 1.0\n",
"250266 WARREN TWP 40.64711 -74.50981 1.0\n",
"251055 WATCHUNG BORO 40.65775 -74.44044 1.0\n",
"251069 WATCHUNG BORO 40.70603 -74.25135 1.0\n",
"251805 GREEN TWP 40.99723 -74.82394 1.0\n",
"251983 HAMPTON TWP 41.09759 -74.74867 1.0\n",
"252504 LAFAYETTE TWP 41.10785 -74.68070 1.0\n",
"252540 LAFAYETTE TWP 41.08652 -74.67340 1.0\n",
"252677 MONTAGUE TWP 41.33970 -74.67690 1.0\n",
"254536 BERKELEY HEIGHTS TWP 40.68840 -74.38231 1.0\n",
"255387 CLARK TWP 40.63814 -74.29146 2.0\n",
"256348 CRANFORD TWP 40.64471 -74.28760 1.0\n",
"261412 ELIZABETH CITY 40.65448 -74.19150 1.0\n",
"262603 HILLSIDE TWP 40.70715 -74.25365 1.0\n",
"264894 LINDEN CITY 40.63137 -74.21141 1.0\n",
"264905 LINDEN CITY 40.62744 -74.21396 1.0\n",
"264990 LINDEN CITY 40.62588 -74.21563 1.0\n",
"270851 UNION TWP 40.71286 -74.27199 1.0\n",
"270879 UNION TWP 40.71464 -74.28914 1.0\n",
"274402 ALLAMUCHY TWP 40.92434 -74.78701 1.0\n",
"275197 GREENWICH TWP 40.66251 -75.08329 1.0\n",
"275625 HOPE TWP 40.92416 -74.95600 1.0\n",
"275783 KNOWLTON TWP 40.92325 -75.06410 1.0\n",
"275934 KNOWLTON TWP 40.92372 -75.05185 1.0\n",
"277031 POHATCONG TWP 40.65324 -75.15042 1.0\n",
"277032 POHATCONG TWP 40.66780 -75.13821 1.0\n",
"277262 WASHINGTON TWP 40.75400 -74.99770 1.0\n",
"\n",
"[276 rows x 4 columns]\n"
],
"name": "stdout"
}
]
},
{
"metadata": {
"id": "SmAY4nxT5pQy",
"colab_type": "code",
"colab": {}
},
"cell_type": "code",
"source": [
"df3 = df1[df1['Total Killed']< 1.0]\n"
],
"execution_count": 0,
"outputs": []
},
{
"metadata": {
"id": "oXRkO0oTfJu6",
"colab_type": "code",
"outputId": "8f2d1512-e1e6-4247-90aa-568116bf2c92",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 34
}
},
"cell_type": "code",
"source": [
"df3.shape\n"
],
"execution_count": 0,
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": [
"(73049, 50)"
]
},
"metadata": {
"tags": []
},
"execution_count": 20
}
]
},
{
"metadata": {
"id": "1rVUtTUZFsYc",
"colab_type": "code",
"outputId": "824fab0a-fec3-4772-e773-f541ad52bd96",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 34
}
},
"cell_type": "code",
"source": [
"from folium.plugins import HeatMap\n",
"#first, copy all data [all 2017 county accidents] to our map dataframe\n",
"df_map = df1.copy()\n",
"#df_map = df1[df1['Total Killed']>= 1.0].copy()\n",
"df_map['count']=1\n",
"df_map[['Latitude', 'Longitude', 'count']].groupby(['Latitude', 'Longitude']).sum().sort_values('count', ascending=False).head(10)\n",
"\n",
"df_map.shape"
],
"execution_count": 0,
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": [
"(73325, 51)"
]
},
"metadata": {
"tags": []
},
"execution_count": 37
}
]
},
{
"metadata": {
"id": "X22S7oSUXuEC",
"colab_type": "code",
"outputId": "6a66e2e6-70c3-4c16-f68f-5df539883a5f",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 34
}
},
"cell_type": "code",
"source": [
"print(df1['Severity'].unique())"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"['P' 'I' 'F']\n"
],
"name": "stdout"
}
]
},
{
"metadata": {
"id": "xl7vlaKls_kA",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
""
]
},
{
"metadata": {
"id": "iEArEGvds_nW",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
""
]
},
{
"metadata": {
"id": "6g4Y4mhLs_xQ",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
""
]
},
{
"metadata": {
"id": "FYSEVZVh_8uC",
"colab_type": "code",
"colab": {}
},
"cell_type": "code",
"source": [
"def generateBaseMap(default_location=[40.5397293,-74.6273494], default_zoom_start=12):\n",
" base_map = folium.Map(location=default_location, control_scale=True, zoom_start=default_zoom_start)\n",
" return base_map\n",
"\n",
"base_map = generateBaseMap()\n",
"base_map\n",
"m = HeatMap(data=df_map[['Latitude', 'Longitude', 'count']].groupby(['Latitude','Longitude']).sum().reset_index().values.tolist(), radius=7, max_zoom=10).add_to(base_map)\n",
"m.save('/content/drive/My Drive/Colab/heatmap.html')"
],
"execution_count": 0,
"outputs": []
},
{
"metadata": {
"id": "oZsKrLhTCHlG",
"colab_type": "code",
"colab": {}
},
"cell_type": "code",
"source": [
"!pip -q install folium"
],
"execution_count": 0,
"outputs": []
},
{
"metadata": {
"id": "0t1HHsPJDNwt",
"colab_type": "code",
"colab": {}
},
"cell_type": "code",
"source": [
"#define a base map generator function-note - if folium doesn't work #properly(it didn't, at first, for me :) - try notes in next box - I #saved to file as pressing run didn't output results\n",
"\n",
"\n",
"def generateBaseMap(default_location=[40.5397293,-74.6273494], default_zoom_start=12):\n",
" base_map = folium.Map(location=default_location, control_scale=True, zoom_start=default_zoom_start)\n",
" return base_map\n",
"base_map = generateBaseMap()\n",
"base_map\n",
"m = HeatMap(data=df_map[['Latitude', 'Longitude', 'count']].groupby(['Latitude','Longitude']).sum().reset_index().values.tolist(), radius=7, max_zoom=10).add_to(base_map)\n",
"m.save('/content/drive/My Drive/Colab/heatmap.html')"
],
"execution_count": 0,
"outputs": []
},
{
"metadata": {
"id": "8ZkoFnU7_-rt",
"colab_type": "code",
"outputId": "776d63d7-0b56-460f-b446-c3675ee626d5",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 573
}
},
"cell_type": "code",
"source": [
"\n",
"df_map.head()"
],
"execution_count": 0,
"outputs": [
{
"output_type": "execute_result",
"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>Mumbo-jumbo</th>\n",
" <th>County Name</th>\n",
" <th>Municipality Name</th>\n",
" <th>Crash Date</th>\n",
" <th>Crash Day Of Week</th>\n",
" <th>Crash Time</th>\n",
" <th>Police Dept Code</th>\n",
" <th>Police Department</th>\n",
" <th>Police Station</th>\n",
" <th>Total Killed</th>\n",
" <th>...</th>\n",
" <th>Ramp To/From Route Direction</th>\n",
" <th>Posted Speed</th>\n",
" <th>Posted Speed Cross Street</th>\n",
" <th>First Harmful Event</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>count</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>1917</th>\n",
" <td>20170102A160-2017-00001A</td>\n",
" <td>ATLANTIC</td>\n",
" <td>ATLANTIC CITY</td>\n",
" <td>2017-01-01</td>\n",
" <td>SU</td>\n",
" <td>0131</td>\n",
" <td>2.0</td>\n",
" <td>NEW JERSEY STATE POLICE</td>\n",
" <td>ATLANTIC CITY E</td>\n",
" <td>0.0</td>\n",
" <td>...</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>26</td>\n",
" <td>39.36445</td>\n",
" <td>-74.44420</td>\n",
" <td>N</td>\n",
" <td>...</td>\n",
" <td>7527</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1918</th>\n",
" <td>20170102A160-2017-00004A</td>\n",
" <td>ATLANTIC</td>\n",
" <td>ATLANTIC CITY</td>\n",
" <td>2017-01-03</td>\n",
" <td>TU</td>\n",
" <td>1040</td>\n",
" <td>2.0</td>\n",
" <td>NEW JERSEY STATE POLICE</td>\n",
" <td>ATLANTIC CITY E</td>\n",
" <td>0.0</td>\n",
" <td>...</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>48</td>\n",
" <td>39.37452</td>\n",
" <td>-74.44194</td>\n",
" <td>N</td>\n",
" <td>...</td>\n",
" <td>7412</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1919</th>\n",
" <td>20170102A160-2017-00006A</td>\n",
" <td>ATLANTIC</td>\n",
" <td>ATLANTIC CITY</td>\n",
" <td>2017-01-04</td>\n",
" <td>WE</td>\n",
" <td>0744</td>\n",
" <td>2.0</td>\n",
" <td>NEW JERSEY STATE POLICE</td>\n",
" <td>ATLANTIC CITY E</td>\n",
" <td>0.0</td>\n",
" <td>...</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>47</td>\n",
" <td>39.37815</td>\n",
" <td>-74.48359</td>\n",
" <td>N</td>\n",
" <td>...</td>\n",
" <td>7498</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1920</th>\n",
" <td>20170102A160-2017-00007A</td>\n",
" <td>ATLANTIC</td>\n",
" <td>ATLANTIC CITY</td>\n",
" <td>2017-01-05</td>\n",
" <td>TH</td>\n",
" <td>2253</td>\n",
" <td>2.0</td>\n",
" <td>NEW JERSEY STATE POLICE</td>\n",
" <td>ATLANTIC CITY E</td>\n",
" <td>0.0</td>\n",
" <td>...</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>26</td>\n",
" <td>39.38119</td>\n",
" <td>-74.42842</td>\n",
" <td>N</td>\n",
" <td>...</td>\n",
" <td>7673</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1921</th>\n",
" <td>20170102A160-2017-00013A</td>\n",
" <td>ATLANTIC</td>\n",
" <td>ATLANTIC CITY</td>\n",
" <td>2017-01-07</td>\n",
" <td>SA</td>\n",
" <td>1159</td>\n",
" <td>2.0</td>\n",
" <td>NEW JERSEY STATE POLICE</td>\n",
" <td>ATLANTIC CITY E</td>\n",
" <td>0.0</td>\n",
" <td>...</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>48</td>\n",
" <td>39.36445</td>\n",
" <td>-74.44420</td>\n",
" <td>N</td>\n",
" <td>PAINT TRANSFER TO BARRIER WALL. ...</td>\n",
" <td>7482</td>\n",
" <td>1</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>5 rows × 51 columns</p>\n",
"</div>"
],
"text/plain": [
" Mumbo-jumbo County Name Municipality Name \\\n",
"1917 20170102A160-2017-00001A ATLANTIC ATLANTIC CITY \n",
"1918 20170102A160-2017-00004A ATLANTIC ATLANTIC CITY \n",
"1919 20170102A160-2017-00006A ATLANTIC ATLANTIC CITY \n",
"1920 20170102A160-2017-00007A ATLANTIC ATLANTIC CITY \n",
"1921 20170102A160-2017-00013A ATLANTIC ATLANTIC CITY \n",
"\n",
" Crash Date Crash Day Of Week Crash Time Police Dept Code \\\n",
"1917 2017-01-01 SU 0131 2.0 \n",
"1918 2017-01-03 TU 1040 2.0 \n",
"1919 2017-01-04 WE 0744 2.0 \n",
"1920 2017-01-05 TH 2253 2.0 \n",
"1921 2017-01-07 SA 1159 2.0 \n",
"\n",
" Police Department Police Station Total Killed ... \\\n",
"1917 NEW JERSEY STATE POLICE ATLANTIC CITY E 0.0 ... \n",
"1918 NEW JERSEY STATE POLICE ATLANTIC CITY E 0.0 ... \n",
"1919 NEW JERSEY STATE POLICE ATLANTIC CITY E 0.0 ... \n",
"1920 NEW JERSEY STATE POLICE ATLANTIC CITY E 0.0 ... \n",
"1921 NEW JERSEY STATE POLICE ATLANTIC CITY E 0.0 ... \n",
"\n",
" Ramp To/From Route Direction Posted Speed Posted Speed Cross Street \\\n",
"1917 \n",
"1918 \n",
"1919 \n",
"1920 \n",
"1921 \n",
"\n",
" First Harmful Event Latitude Longitude Cell Phone In Use Flag \\\n",
"1917 26 39.36445 -74.44420 N \n",
"1918 48 39.37452 -74.44194 N \n",
"1919 47 39.37815 -74.48359 N \n",
"1920 26 39.38119 -74.42842 N \n",
"1921 48 39.36445 -74.44420 N \n",
"\n",
" Other Property Damage Reporting Badge No \\\n",
"1917 ... 7527 \n",
"1918 ... 7412 \n",
"1919 ... 7498 \n",
"1920 ... 7673 \n",
"1921 PAINT TRANSFER TO BARRIER WALL. ... 7482 \n",
"\n",
" count \n",
"1917 1 \n",
"1918 1 \n",
"1919 1 \n",
"1920 1 \n",
"1921 1 \n",
"\n",
"[5 rows x 51 columns]"
]
},
"metadata": {
"tags": []
},
"execution_count": 26
}
]
},
{
"metadata": {
"id": "aW6sx7pZs8-G",
"colab_type": "code",
"outputId": "8511f12a-90ba-4c75-9ac1-72e896f52161",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 136
}
},
"cell_type": "code",
"source": [
"#print the unique listing of 'Other Property Damage' - might be useful for State Road Planning and Repair, Insurance Companies, Road safety planning and testing etc.\n",
"print(df1['Other Property Damage'].unique())"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"[' '\n",
" 'PAINT TRANSFER TO BARRIER WALL. '\n",
" 'CONCRETE BARRIER - SJTA P.O. BOX 389 HAMMONTON NJ '\n",
" ...\n",
" 'USPS MAILBOX LOCATED IN FRONT OF 37 COLBY CT WHITE TWP WARREN COUNTY. '\n",
" 'Utility pole (A2755114)'\n",
" 'JCP&L UTILITY POLE NJ405WH ']\n"
],
"name": "stdout"
}
]
},
{
"metadata": {
"id": "HRuc7xEFtkpQ",
"colab_type": "code",
"outputId": "44f6d149-bef8-49c8-e86b-8b0d8cb5a30c",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 2017
}
},
"cell_type": "code",
"source": [
"#group environmental conditions, dates and count them.\n",
"#gives us an idea of which environmental conditions had the highest crashes\n",
"#the state did not list which environmental condition # was labeled as which (1=snow, etc.)\n",
"#I am guessing that #03 had something to do with snow based on 'Crash Date'; I was caught in a few snow storms myself on some of those dates :)\n",
"print(df1['Environmental Condition'].unique())\n",
"df_map[['Environmental Condition','Crash Date','count']].groupby(['Environmental Condition','Crash Date']).sum().sort_values('count', ascending=False)"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"['01' '02' '03' '04' '05' '10' '00' '07' '06' '99' '08' ' ']\n"
],
"name": "stdout"
},
{
"output_type": "execute_result",
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th></th>\n",
" <th>count</th>\n",
" </tr>\n",
" <tr>\n",
" <th>Environmental Condition</th>\n",
" <th>Crash Date</th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>02</th>\n",
" <th>2017-10-29</th>\n",
" <td>328</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"3\" valign=\"top\">01</th>\n",
" <th>2017-11-17</th>\n",
" <td>314</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-11-21</th>\n",
" <td>300</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-10-20</th>\n",
" <td>293</td>\n",
" </tr>\n",
" <tr>\n",
" <th>03</th>\n",
" <th>2017-01-07</th>\n",
" <td>291</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"7\" valign=\"top\">01</th>\n",
" <th>2017-10-27</th>\n",
" <td>291</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-06-13</th>\n",
" <td>290</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-10-04</th>\n",
" <td>287</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-10-19</th>\n",
" <td>287</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-05-19</th>\n",
" <td>283</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-12-18</th>\n",
" <td>281</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-12-01</th>\n",
" <td>279</td>\n",
" </tr>\n",
" <tr>\n",
" <th>03</th>\n",
" <th>2017-12-09</th>\n",
" <td>277</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"11\" valign=\"top\">01</th>\n",
" <th>2017-11-20</th>\n",
" <td>275</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-06-20</th>\n",
" <td>273</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-10-17</th>\n",
" <td>271</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-10-06</th>\n",
" <td>269</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-12-07</th>\n",
" <td>269</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-05-17</th>\n",
" <td>265</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-11-02</th>\n",
" <td>264</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-10-02</th>\n",
" <td>263</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-11-29</th>\n",
" <td>261</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-03-16</th>\n",
" <td>261</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-11-14</th>\n",
" <td>260</td>\n",
" </tr>\n",
" <tr>\n",
" <th>03</th>\n",
" <th>2017-12-15</th>\n",
" <td>260</td>\n",
" </tr>\n",
" <tr>\n",
" <th>02</th>\n",
" <th>2017-05-13</th>\n",
" <td>258</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"3\" valign=\"top\">01</th>\n",
" <th>2017-06-15</th>\n",
" <td>256</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-09-28</th>\n",
" <td>255</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-09-07</th>\n",
" <td>254</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"4\" valign=\"top\">02</th>\n",
" <th>2017-03-31</th>\n",
" <td>254</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-06-22</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-06-28</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>05</th>\n",
" <th>2017-03-12</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"21\" valign=\"top\">02</th>\n",
" <th>2017-06-29</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-07-09</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-07-10</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-07-18</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-04-18</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-04-05</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-01-09</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-02-23</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-01-13</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-01-19</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-01-25</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-01-28</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-01-30</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-02-03</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-02-14</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-02-17</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-02-26</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-03-24</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-03-03</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-03-06</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-03-11</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>05</th>\n",
" <th>2017-04-18</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">02</th>\n",
" <th>2017-03-19</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-03-22</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>05</th>\n",
" <th>2017-04-11</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>02</th>\n",
" <th>2017-03-23</th>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th></th>\n",
" <th>2017-01-03</th>\n",
" <td>1</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>1261 rows × 1 columns</p>\n",
"</div>"
],
"text/plain": [
" count\n",
"Environmental Condition Crash Date \n",
"02 2017-10-29 328\n",
"01 2017-11-17 314\n",
" 2017-11-21 300\n",
" 2017-10-20 293\n",
"03 2017-01-07 291\n",
"01 2017-10-27 291\n",
" 2017-06-13 290\n",
" 2017-10-04 287\n",
" 2017-10-19 287\n",
" 2017-05-19 283\n",
" 2017-12-18 281\n",
" 2017-12-01 279\n",
"03 2017-12-09 277\n",
"01 2017-11-20 275\n",
" 2017-06-20 273\n",
" 2017-10-17 271\n",
" 2017-10-06 269\n",
" 2017-12-07 269\n",
" 2017-05-17 265\n",
" 2017-11-02 264\n",
" 2017-10-02 263\n",
" 2017-11-29 261\n",
" 2017-03-16 261\n",
" 2017-11-14 260\n",
"03 2017-12-15 260\n",
"02 2017-05-13 258\n",
"01 2017-06-15 256\n",
" 2017-09-28 255\n",
" 2017-09-07 254\n",
"02 2017-03-31 254\n",
"... ...\n",
" 2017-06-22 1\n",
" 2017-06-28 1\n",
"05 2017-03-12 1\n",
"02 2017-06-29 1\n",
" 2017-07-09 1\n",
" 2017-07-10 1\n",
" 2017-07-18 1\n",
" 2017-04-18 1\n",
" 2017-04-05 1\n",
" 2017-01-09 1\n",
" 2017-02-23 1\n",
" 2017-01-13 1\n",
" 2017-01-19 1\n",
" 2017-01-25 1\n",
" 2017-01-28 1\n",
" 2017-01-30 1\n",
" 2017-02-03 1\n",
" 2017-02-14 1\n",
" 2017-02-17 1\n",
" 2017-02-26 1\n",
" 2017-03-24 1\n",
" 2017-03-03 1\n",
" 2017-03-06 1\n",
" 2017-03-11 1\n",
"05 2017-04-18 1\n",
"02 2017-03-19 1\n",
" 2017-03-22 1\n",
"05 2017-04-11 1\n",
"02 2017-03-23 1\n",
" 2017-01-03 1\n",
"\n",
"[1261 rows x 1 columns]"
]
},
"metadata": {
"tags": []
},
"execution_count": 28
}
]
},
{
"metadata": {
"id": "_c0Q2lheeXeT",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"# **Part II: Create Heat Maps of the 2nd dataset **\n",
"# with town names only, not specific address within towns **bold text** **bold text**\n",
"\n",
"Earlier, we split the dataset and created a 'df2' for those records without specific Latitude and Longitude coordinates. The reasons for this could be many - the data was corrupted at the time of capture, or the police officers were too busy with first responder duties to input precise information, or perhaps the data capture tablets/notebooks/laptops didn't work properly. Whatever the reason, let's get location data for the towns, which turned out to be more complicated and cumbersome than I had imagined. It could simply be that I didn't think of an optimal way. This is the way it ended up. In real work situations, many a time you will have to go for the 'most viable solution', which means the best that is possible given the time constraints and incomplete data."
]
},
{
"metadata": {
"id": "CKeY0Wu0jwai",
"colab_type": "code",
"colab": {}
},
"cell_type": "code",
"source": [
"#print(df2['Municipality Name'].unique())\n",
"\n",
"#group and count by city - since there are no Lat, Long markers, it would be good to know where the accidents were, grouped by town\n",
"df2['count']=1\n",
"df5 = df2[['Municipality Name', 'count']].groupby(['Municipality Name']).sum().sort_values('count', ascending=False)\n",
"df5.shape\n",
"df5.head\n",
"#df2[['Municipality Name', 'count']].groupby(['Municipality Name', 'count']).sum().sort_values('count', ascending=False)\n",
"pd.DataFrame(df5).to_csv('/content/drive/My Drive/Colab/town_list.csv')"
],
"execution_count": 0,
"outputs": []
},
{
"metadata": {
"id": "9iNZ5HD5Ua9Q",
"colab_type": "code",
"outputId": "0bfa7750-29ba-4802-f576-7a4b872df95b",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 34
}
},
"cell_type": "code",
"source": [
"#function to see if variable is a dataframe\n",
"def f(var):\n",
" if isinstance(var, pd.DataFrame):\n",
" print(\"dataframe\")\n",
" else:\n",
" print(\"not a dataframe, prob a list\")\n",
"\n",
"f(df5)"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"dataframe\n"
],
"name": "stdout"
}
]
},
{
"metadata": {
"id": "cxGNcidW5hf9",
"colab_type": "code",
"outputId": "70344c77-77ab-4de1-b392-48fd9e952380",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 4335
}
},
"cell_type": "code",
"source": [
"#create a list of unique town names from df2 for use later to make a call to function to get Lat, Long - easy to read each value from a list\n",
"town_names=[]\n",
"df2.dropna(subset=['Municipality Name'])\n",
"town_names = df2['Municipality Name'].unique()\n",
"print(town_names)"
],
"execution_count": 0,
"outputs": [
{
"output_type": "stream",
"text": [
"['ABSECON CITY ' 'ATLANTIC CITY ' nan\n",
" 'BRIGANTINE CITY ' 'BUENA BORO '\n",
" 'BUENA VISTA TWP ' 'EGG HARBOR CITY '\n",
" 'EGG HARBOR TWP ' 'ESTELL MANOR CITY '\n",
" 'GALLOWAY TWP ' 'HAMILTON TWP '\n",
" 'HAMMONTON TOWN ' 'LINWOOD CITY '\n",
" 'LONGPORT BORO ' 'MARGATE CITY '\n",
" 'MULLICA TWP ' 'NORTHFIELD CITY '\n",
" 'PLEASANTVILLE CITY ' 'PORT REPUBLIC CITY '\n",
" 'SOMERS POINT CITY ' 'VENTNOR CITY '\n",
" 'ALLENDALE BORO ' 'ALPINE BORO '\n",
" 'BERGENFIELD BORO ' 'BOGOTA BORO '\n",
" 'CARLSTADT BORO ' 'CLIFFSIDE PARK BORO '\n",
" 'CLOSTER BORO ' 'DEMAREST BORO '\n",
" 'DUMONT BORO ' 'ELMWOOD PARK BORO '\n",
" 'EAST RUTHERFORD BORO ' 'EDGEWATER BORO '\n",
" 'EMERSON BORO ' 'ENGLEWOOD CITY '\n",
" 'ENGLEWOOD CLIFFS BORO ' 'FAIR LAWN BORO '\n",
" 'FAIRVIEW BORO ' 'FORT LEE BORO '\n",
" 'FRANKLIN LAKES BORO ' 'GARFIELD CITY '\n",
" 'GLEN ROCK BORO ' 'HACKENSACK CITY '\n",
" 'HARRINGTON PARK BORO ' 'HASBROUCK HEIGHTS BORO '\n",
" 'HAWORTH BORO ' 'HILLSDALE BORO '\n",
" 'HO HO KUS BORO ' 'LEONIA BORO '\n",
" 'LITTLE FERRY BORO ' 'LODI BORO '\n",
" 'LYNDHURST TWP ' 'MAHWAH TWP '\n",
" 'MAYWOOD BORO ' 'MIDLAND PARK BORO '\n",
" 'MONTVALE BORO ' 'MOONACHIE BORO '\n",
" 'NEW MILFORD BORO ' 'NORTH ARLINGTON BORO '\n",
" 'NORTHVALE BORO ' 'NORWOOD BORO '\n",
" 'OAKLAND BORO ' 'OLD TAPPAN BORO '\n",
" 'ORADELL BORO ' 'PALISADES PARK BORO '\n",
" 'PARAMUS BORO ' 'PARK RIDGE BORO '\n",
" 'RAMSEY BORO ' 'RIDGEFIELD BORO '\n",
" 'RIDGEFIELD PARK VILLAGE ' 'RIDGEWOOD VILLAGE '\n",
" 'RIVER EDGE BORO ' 'RIVER VALE TWP '\n",
" 'ROCHELLE PARK TWP ' 'ROCKLEIGH BORO '\n",
" 'RUTHERFORD BORO ' 'SADDLE BROOK TWP '\n",
" 'SADDLE RIVER BORO ' 'SOUTH HACKENSACK TWP '\n",
" 'TEANECK TWP ' 'TENAFLY BORO '\n",
" 'TETERBORO BORO ' 'UPPER SADDLE RIVER BORO '\n",
" 'WALDWICK BORO ' 'WALLINGTON BORO '\n",
" 'WASHINGTON TWP ' 'WESTWOOD BORO '\n",
" 'WOODCLIFF LAKE BORO ' 'WOOD-RIDGE BORO '\n",
" 'WYCKOFF TWP ' 'BASS RIVER TWP '\n",
" 'BORDENTOWN CITY ' 'BORDENTOWN TWP '\n",
" 'BURLINGTON CITY ' 'BURLINGTON TWP '\n",
" 'CHESTERFIELD TWP ' 'CINNAMINSON TWP '\n",
" 'DELANCO TWP ' 'DELRAN TWP '\n",
" 'EASTAMPTON TWP ' 'EDGEWATER PARK TWP '\n",
" 'EVESHAM TWP ' 'FIELDSBORO BORO '\n",
" 'FLORENCE TWP ' 'HAINESPORT TWP '\n",
" 'LUMBERTON TWP ' 'MANSFIELD TWP '\n",
" 'MAPLE SHADE TWP ' 'MEDFORD TWP '\n",
" 'MEDFORD LAKES BORO ' 'MOORESTOWN TWP '\n",
" 'MOUNT HOLLY TWP ' 'MOUNT LAUREL TWP '\n",
" 'NEW HANOVER TWP ' 'NORTH HANOVER TWP '\n",
" 'PALMYRA BORO ' 'PEMBERTON BORO '\n",
" 'PEMBERTON TWP ' 'RIVERSIDE TWP '\n",
" 'RIVERTON BORO ' 'SHAMONG TWP '\n",
" 'SOUTHAMPTON TWP ' 'SPRINGFIELD TWP '\n",
" 'TABERNACLE TWP ' 'WESTAMPTON TWP '\n",
" 'WILLINGBORO TWP ' 'WOODLAND TWP '\n",
" 'AUDUBON PARK BORO ' 'BARRINGTON BORO '\n",
" 'BELLMAWR BORO ' 'BERLIN BORO '\n",
" 'BERLIN TWP ' 'BROOKLAWN BORO '\n",
" 'CAMDEN CITY ' 'CHERRY HILL TWP '\n",
" 'CHESILHURST BORO ' 'CLEMENTON BORO '\n",
" 'COLLINGSWOOD BORO ' 'GIBBSBORO BORO '\n",
" 'GLOUCESTER CITY ' 'GLOUCESTER TWP '\n",
" 'HADDON TWP ' 'HADDONFIELD BORO '\n",
" 'HADDON HEIGHTS BORO ' 'LAUREL SPRINGS BORO '\n",
" 'LAWNSIDE BORO ' 'LINDENWOLD BORO '\n",
" 'MAGNOLIA BORO ' 'MERCHANTVILLE BORO '\n",
" 'MOUNT EPHRIAM BORO ' 'OAKLYN BORO '\n",
" 'PENNSAUKEN TWP ' 'PINE HILL BORO '\n",
" 'RUNNEMEDE BORO ' 'SOMERDALE BORO '\n",
" 'STRATFORD BORO ' 'VOORHEES TWP '\n",
" 'WATERFORD TWP ' 'WINSLOW TWP '\n",
" 'WOODLYNNE BORO ' 'AVALON BORO '\n",
" 'CAPE MAY CITY ' 'DENNIS TWP '\n",
" 'LOWER TWP ' 'MIDDLE TWP '\n",
" 'NORTH WILDWOOD CITY ' 'OCEAN CITY '\n",
" 'SEA ISLE CITY ' 'STONE HARBOR BORO '\n",
" 'UPPER TWP ' 'WILDWOOD CITY '\n",
" 'WILDWOOD CREST BORO ' 'WOODBINE BORO '\n",
" 'BRIDGETON CITY ' 'DEERFIELD TWP '\n",
" 'LAWRENCE TWP ' 'MAURICE RIVER TWP '\n",
" 'MILLVILLE CITY ' 'UPPER DEERFIELD TWP '\n",
" 'VINELAND CITY ' 'BELLEVILLE TWP '\n",
" 'BLOOMFIELD TWP ' 'CALDWELL BORO '\n",
" 'CEDAR GROVE TWP ' 'EAST ORANGE CITY '\n",
" 'ESSEX FELLS BORO ' 'FAIRFIELD BORO '\n",
" 'GLEN RIDGE BORO ' 'IRVINGTON TWP '\n",
" 'LIVINGSTON TWP ' 'MAPLEWOOD TWP '\n",
" 'MILLBURN TWP ' 'MONTCLAIR TWP '\n",
" 'NEWARK CITY ' 'NORTH CALDWELL BORO '\n",
" 'NUTLEY TWP ' 'ORANGE CITY '\n",
" 'ROSELAND BORO ' 'SOUTH ORANGE VILLAGE TWP'\n",
" 'VERONA TWP ' 'WEST CALDWELL TWP '\n",
" 'WEST ORANGE TWP ' 'CLAYTON BORO '\n",
" 'DEPTFORD TWP ' 'EAST GREENWICH TWP '\n",
" 'ELK TWP ' 'FRANKLIN TWP '\n",
" 'GLASSBORO BORO ' 'GREENWICH TWP '\n",
" 'HARRISON TWP ' 'LOGAN TWP '\n",
" 'MANTUA TWP ' 'MONROE TWP '\n",
" 'NATIONAL PARK BORO ' 'NEWFIELD BORO '\n",
" 'PAULSBORO BORO ' 'PITMAN BORO '\n",
" 'SOUTH HARRISON TWP ' 'SWEDESBORO BORO '\n",
" 'WENONAH BORO ' 'WEST DEPTFORD TWP '\n",
" 'WOODBURY CITY ' 'WOODBURY HEIGHTS BORO '\n",
" 'WOOLWICH TWP ' 'BAYONNE CITY '\n",
" 'EAST NEWARK BORO ' 'GUTTENBERG TOWN '\n",
" 'HARRISON TOWN ' 'HOBOKEN CITY '\n",
" 'JERSEY CITY ' 'KEARNY TOWN '\n",
" 'NORTH BERGEN TWP ' 'SECAUCUS TOWN '\n",
" 'UNION CITY ' 'WEEHAWKEN TWP '\n",
" 'WEST NEW YORK TOWN ' 'ALEXANDRIA TWP '\n",
" 'BETHLEHEM TWP ' 'BLOOMSBURY BORO '\n",
" 'CALIFON BORO ' 'CLINTON TOWN '\n",
" 'CLINTON TWP ' 'DELAWARE TWP '\n",
" 'EAST AMWELL TWP ' 'FLEMINGTON BORO '\n",
" 'FRENCHTOWN BORO ' 'GLEN GARDNER BORO '\n",
" 'HIGH BRIDGE BORO ' 'HOLLAND TWP '\n",
" 'LAMBERTVILLE CITY ' 'LEBANON BORO '\n",
" 'LEBANON TWP ' 'MILFORD TWP '\n",
" 'RARITAN TWP ' 'READINGTON TWP '\n",
" 'STOCKTON BORO ' 'TEWKSBURY TWP '\n",
" 'UNION TWP ' 'WEST AMWELL TWP '\n",
" 'EAST WINDSOR TWP ' 'EWING TWP '\n",
" 'HIGHTSTOWN BORO ' 'HOPEWELL BORO '\n",
" 'HOPEWELL TWP ' 'PENNINGTON BORO '\n",
" 'PRINCETON TWP ' 'TRENTON CITY '\n",
" 'ROBBINSVILLE TWP ' 'WEST WINDSOR TWP '\n",
" 'PRINCETON ' 'CARTERET BORO '\n",
" 'CRANBURY TWP ' 'DUNELLEN BORO '\n",
" 'EAST BRUNSWICK TWP ' 'EDISON TWP '\n",
" 'HELMETTA BORO ' 'HIGHLAND PARK BORO '\n",
" 'JAMESBURG BORO ' 'OLD BRIDGE TWP '\n",
" 'METUCHEN BORO ' 'MIDDLESEX BORO '\n",
" 'MILLTOWN BORO ' 'NEW BRUNSWICK CITY '\n",
" 'NORTH BRUNSWICK TWP ' 'PERTH AMBOY CITY '\n",
" 'PISCATAWAY TWP ' 'PLAINSBORO TWP '\n",
" 'SAYREVILLE BORO ' 'SOUTH AMBOY CITY '\n",
" 'SOUTH BRUNSWICK TWP ' 'SOUTH PLAINFIELD BORO '\n",
" 'SOUTH RIVER BORO ' 'SPOTSWOOD BORO '\n",
" 'WOODBRIDGE TWP ' 'ALLENHURST BORO '\n",
" 'ALLENTOWN BORO ' 'ASBURY PARK CITY '\n",
" 'ATLANTIC HIGHLANDS BORO ' 'AVON-BY-THE-SEA BORO '\n",
" 'BELMAR BORO ' 'BRADLEY BEACH BORO '\n",
" 'BRIELLE BORO ' 'COLTS NECK TWP '\n",
" 'DEAL BORO ' 'EATONTOWN BORO '\n",
" 'FAIR HAVEN BORO ' 'FREEHOLD BORO '\n",
" 'FREEHOLD TWP ' 'HIGHLANDS BORO '\n",
" 'HOLMDEL TWP ' 'HOWELL TWP '\n",
" 'INTERLAKEN BORO ' 'KEANSBURG BORO '\n",
" 'KEYPORT BORO ' 'LITTLE SILVER BORO '\n",
" 'LOCH ARBOUR VILLAGE ' 'LONG BRANCH CITY '\n",
" 'MANALAPAN TWP ' 'MANASQUAN BORO '\n",
" 'MARLBORO TWP ' 'MATAWAN BORO '\n",
" 'ABERDEEN TWP ' 'MIDDLETOWN TWP '\n",
" 'MILLSTONE TWP ' 'MONMOUTH BEACH BORO '\n",
" 'NEPTUNE TWP ' 'NEPTUNE CITY BORO '\n",
" 'TINTON FALLS BORO ' 'OCEAN TWP '\n",
" 'OCEANPORT BORO ' 'HAZLET TWP '\n",
" 'RED BANK BORO ' 'RUMSON BORO '\n",
" 'SEA BRIGHT BORO ' 'SEA GIRT BORO '\n",
" 'SHREWSBURY BORO ' 'LAKE COMO BORO '\n",
" 'SPRING LAKE BORO ' 'SPRING LAKE HEIGHTS BORO'\n",
" 'UNION BEACH BORO ' 'WALL TWP '\n",
" 'WEST LONG BRANCH BORO ' 'BOONTON TOWN '\n",
" 'BOONTON TWP ' 'BUTLER BORO '\n",
" 'CHATHAM BORO ' 'CHATHAM TWP '\n",
" 'CHESTER BORO ' 'CHESTER TWP '\n",
" 'DENVILLE TWP ' 'DOVER TOWN '\n",
" 'EAST HANOVER TWP ' 'FLORHAM PARK BORO '\n",
" 'HANOVER TWP ' 'HARDING TWP '\n",
" 'JEFFERSON TWP ' 'KINNELON BORO '\n",
" 'LINCOLN PARK BORO ' 'MADISON BORO '\n",
" 'MENDHAM BORO ' 'MENDHAM TWP '\n",
" 'MINE HILL TWP ' 'MONTVILLE TWP '\n",
" 'MORRIS TWP ' 'MORRIS PLAINS BORO '\n",
" 'MORRISTOWN TOWN ' 'MOUNTAIN LAKES BORO '\n",
" 'MOUNT ARLINGTON BORO ' 'MOUNT OLIVE TWP '\n",
" 'NETCONG BORO ' 'PARSIPPANY-TROY HILLS '\n",
" 'PASSAIC TWP ' 'PEQUANNOCK TWP '\n",
" 'RANDOLPH TWP ' 'RIVERDALE BORO '\n",
" 'ROCKAWAY BORO ' 'ROCKAWAY TWP '\n",
" 'ROXBURY TWP ' 'VICTORY GARDENS BORO '\n",
" 'WHARTON BORO ' 'BARNEGAT LIGHT BORO '\n",
" 'BAY HEAD BORO ' 'BEACH HAVEN BORO '\n",
" 'BEACHWOOD BORO ' 'BERKELEY TWP '\n",
" 'BRICK TWP ' 'TOMS RIVER TWP '\n",
" 'HARVEY CEDARS BORO ' 'JACKSON TWP '\n",
" 'LACEY TWP ' 'LAKEHURST BORO '\n",
" 'LAKEWOOD TWP ' 'LAVALLETTE BORO '\n",
" 'LITTLE EGG HARBOR TWP ' 'LONG BEACH TWP '\n",
" 'MANCHESTER TWP ' 'OCEAN GATE BORO '\n",
" 'PINE BEACH BORO ' 'PLUMSTED TWP '\n",
" 'POINT PLEASANT BORO ' 'PT PLEASANT BEACH BORO '\n",
" 'SEASIDE HEIGHTS BORO ' 'SEASIDE PARK BORO '\n",
" 'SHIP BOTTOM BORO ' 'SOUTH TOMS RIVER BORO '\n",
" 'STAFFORD TWP ' 'SURF CITY BORO '\n",
" 'TUCKERTON BORO ' 'BARNEGAT TWP '\n",
" 'BLOOMINGDALE BORO ' 'CLIFTON CITY '\n",
" 'HALEDON BORO ' 'HAWTHORNE BORO '\n",
" 'LITTLE FALLS TWP ' 'NORTH HALEDON BORO '\n",
" 'PASSAIC CITY ' 'PATERSON CITY '\n",
" 'POMPTON LAKES BORO ' 'PROSPECT PARK BORO '\n",
" 'RINGWOOD BORO ' 'TOTOWA BORO '\n",
" 'WANAQUE BORO ' 'WAYNE TWP '\n",
" 'WEST MILFORD TWP ' 'WOODLAND PARK BORO '\n",
" 'ELSINBORO TWP ' 'LOWER ALLOWAYS CRK TWP '\n",
" 'MANNINGTON TWP ' 'OLDMANS TWP '\n",
" 'PENNS GROVE BORO ' 'PENNSVILLE TWP '\n",
" 'SALEM CITY ' 'CARNEYS POINT TWP '\n",
" 'UPPER PITTSGROVE TWP ' 'WOODSTOWN BORO '\n",
" 'BEDMINSTER TWP ' 'BERNARDS TWP '\n",
" 'BERNARDSVILLE BORO ' 'BOUND BROOK BORO '\n",
" 'BRANCHBURG TWP ' 'BRIDGEWATER TWP '\n",
" 'FAR HILLS BORO ' 'GREEN BROOK TWP '\n",
" 'HILLSBOROUGH TWP ' 'MANVILLE BORO '\n",
" 'MONTGOMERY TWP ' 'NORTH PLAINFIELD BORO '\n",
" 'PEAPACK-GLADSTONE BORO ' 'RARITAN BORO '\n",
" 'ROCKY HILL BORO ' 'SOMERVILLE BORO '\n",
" 'SOUTH BOUND BROOK BORO ' 'WARREN TWP '\n",
" 'WATCHUNG BORO ' 'ANDOVER BORO '\n",
" 'ANDOVER TWP ' 'BYRAM TWP '\n",
" 'FRANKFORD TWP ' 'FRANKLIN BORO '\n",
" 'HAMBURG BORO ' 'HAMPTON TWP '\n",
" 'HARDYSTON TWP ' 'HOPATCONG BORO '\n",
" 'LAFAYETTE TWP ' 'MONTAGUE TWP '\n",
" 'NEWTON TOWN ' 'OGDENSBURG BORO '\n",
" 'SANDVSTON TWP ' 'SPARTA TWP '\n",
" 'STANHOPE BORO ' 'STILLWATER TWP '\n",
" 'SUSSEX BORO ' 'VERNON TWP '\n",
" 'WANTAGE TWP ' 'BERKELEY HEIGHTS TWP '\n",
" 'CLARK TWP ' 'CRANFORD TWP '\n",
" 'ELIZABETH CITY ' 'FANWOOD BORO '\n",
" 'GARWOOD BORO ' 'HILLSIDE TWP '\n",
" 'KENILWORTH BORO ' 'LINDEN CITY '\n",
" 'MOUNTAINSIDE BORO ' 'NEW PROVIDENCE BORO '\n",
" 'PLAINFIELD CITY ' 'RAHWAY CITY '\n",
" 'ROSELLE BORO ' 'ROSELLE PARK BORO '\n",
" 'SCOTCH PLAINS TWP ' 'SUMMIT CITY '\n",
" 'WESTFIELD TOWN ' 'WINFIELD TWP '\n",
" 'ALLAMUCHY TWP ' 'ALPHA BORO '\n",
" 'BELVIDERE TOWN ' 'BLAIRSTOWN TWP '\n",
" 'FRELINGHUYSEN TWP ' 'HACKETTSTOWN TOWN '\n",
" 'HARMONY TWP ' 'HOPE TWP '\n",
" 'KNOWLTON TWP ' 'LOPATCONG TWP '\n",
" 'OXFORD TWP ' 'PHILLIPSBURG TOWN '\n",
" 'POHATCONG TWP ' 'WASHINGTON BORO '\n",
" 'WHITE TWP ']\n"
],
"name": "stdout"
}
]
},
{
"metadata": {
"id": "Uud-GdINunxB",
"colab_type": "code",
"colab": {}
},
"cell_type": "code",
"source": [
"#function and steps to get town Latitude and Longitude coordinates (for town_list above) to eventually plot on map\n",
"import os\n",
"import requests\n",
"import logging\n",
"import time\n",
"\n",
"logger = logging.getLogger(\"root\")\n",
"logger.setLevel(logging.DEBUG)\n",
"ch = logging.StreamHandler() #console handler\n",
"ch.setLevel(logging.DEBUG)\n",
"logger.addHandler(ch)\n",
"\n",
"RETURN_FULL_RESULTS = False\n",
"BACKOFF_TIME = 30\n",
"API_KEY = 'Enter Your API Key Here'\n",
"output_filename = '/content/drive/My Drive/Colab/town_with_Lat_Long.csv'\n",
"#print(addresses)\n",
"#adapted from Shane Lynn - thanks\n",
"def get_google_results(address, api_key=None, return_full_response=False):\n",
" geocode_url = \"https://maps.googleapis.com/maps/api/geocode/json?address={}\".format(address)\n",
" if api_key is not None:\n",
" geocode_url = geocode_url + \"&key={}\".format(api_key)\n",
" \n",
" #ping google for the results:\n",
" results = requests.get(geocode_url)\n",
" results = results.json() \n",
" \n",
" if len(results['results']) == 0:\n",
" output = {\n",
" \"latitude\": None,\n",
" \"longitude\": None,\n",
" }\n",
" else:\n",
" answer = results['results'][0]\n",
" output = {\n",
" \"latitude\": answer.get('geometry').get('location').get('lat'),\n",
" \"longitude\": answer.get('geometry').get('location').get('lng'),\n",
" }\n",
" \n",
" #append some other details\n",
" output['input_string'] = address\n",
" output['number_of_results'] = len(results['results'])\n",
" output['status'] = results.get('status')\n",
" if return_full_response is True:\n",
" output['response'] = results\n",
" \n",
" return output"
],
"execution_count": 0,
"outputs": []
},
{
"metadata": {
"id": "TuE9u0KvuT01",
"colab_type": "code",
"colab": {}
},
"cell_type": "code",
"source": [
"#call the function from the previous step, then store google geo coordinates in a csv file.\n",
"Lat_Long=[]\n",
"API_KEY = 'AIzaSyDdzQm4-CeG-hTgelQDjnr554Dzf4B0S3c'\n",
"for address in town_names:\n",
" geocode_result = get_google_results(address, API_KEY, return_full_response=RETURN_FULL_RESULTS)\n",
" Lat_Long.append(geocode_result)\n",
"#now, convert the list with our geo coordinates into a csv file that will be called by another program to overlay on a map.\n",
"pd.DataFrame(Lat_Long).to_csv('/content/drive/My Drive/Colab/town_with_Lat_Long_output.csv', encoding='utf8')"
],
"execution_count": 0,
"outputs": []
},
{
"metadata": {
"id": "aB6QirnD6Y2o",
"colab_type": "code",
"outputId": "eed2ae48-9ea6-4ac6-846e-b2e194781642",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 2176
}
},
"cell_type": "code",
"source": [
"df6.head"
],
"execution_count": 0,
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": [
"<bound method NDFrame.head of Unnamed: 0 input_string latitude longitude \\\n",
"0 0 ABSECON CITY 39.428450 -74.495708 \n",
"1 1 ATLANTIC CITY 39.364283 -74.422927 \n",
"2 2 NaN 33.791638 -84.389488 \n",
"3 3 BRIGANTINE CITY 39.410117 -74.364591 \n",
"4 4 BUENA BORO 39.521646 -74.948709 \n",
"5 5 BUENA VISTA TWP 39.528110 -74.896267 \n",
"6 6 EGG HARBOR CITY 39.528728 -74.647936 \n",
"7 7 EGG HARBOR TWP 39.382254 -74.616619 \n",
"8 8 ESTELL MANOR CITY 39.412060 -74.742385 \n",
"9 9 GALLOWAY TWP 39.492824 -74.559688 \n",
"10 10 HAMILTON TWP 40.211511 -74.679665 \n",
"11 11 HAMMONTON TOWN 39.636506 -74.802385 \n",
"12 12 LINWOOD CITY 39.339838 -74.575156 \n",
"13 13 LONGPORT BORO 39.312021 -74.527573 \n",
"14 14 MARGATE CITY 39.327894 -74.503486 \n",
"15 15 MULLICA TWP 39.590785 -74.663464 \n",
"16 16 NORTHFIELD CITY 44.458298 -93.161604 \n",
"17 17 PLEASANTVILLE CITY 39.389839 -74.524042 \n",
"18 18 PORT REPUBLIC CITY 39.520673 -74.485707 \n",
"19 19 SOMERS POINT CITY 39.316064 -74.605305 \n",
"20 20 VENTNOR CITY 39.340394 -74.477373 \n",
"21 21 ALLENDALE BORO 41.034445 -74.134677 \n",
"22 22 ALPINE BORO 40.955932 -73.931248 \n",
"23 23 BERGENFIELD BORO 40.927599 -73.997361 \n",
"24 24 BOGOTA BORO 40.877322 -74.027074 \n",
"25 25 CARLSTADT BORO 40.841634 -74.094772 \n",
"26 26 CLIFFSIDE PARK BORO 40.821489 -73.987639 \n",
"27 27 CLOSTER BORO 40.971384 -73.959277 \n",
"28 28 DEMAREST BORO 40.953102 -73.953616 \n",
"29 29 DUMONT BORO 40.940479 -73.998458 \n",
".. ... ... ... ... \n",
"478 478 FANWOOD BORO 40.640936 -74.383484 \n",
"479 479 GARWOOD BORO 40.651081 -74.325777 \n",
"480 480 HILLSIDE TWP 40.695504 -74.228733 \n",
"481 481 KENILWORTH BORO 40.676491 -74.290703 \n",
"482 482 LINDEN CITY 42.814473 -83.782453 \n",
"483 483 MOUNTAINSIDE BORO 40.671780 -74.355952 \n",
"484 484 NEW PROVIDENCE BORO 40.699699 -74.399798 \n",
"485 485 PLAINFIELD CITY 40.633714 -74.407374 \n",
"486 486 RAHWAY CITY 40.608159 -74.277647 \n",
"487 487 ROSELLE BORO 40.652098 -74.270751 \n",
"488 488 ROSELLE PARK BORO 40.661717 -74.260220 \n",
"489 489 SCOTCH PLAINS TWP 40.640040 -74.369018 \n",
"490 490 SUMMIT CITY 41.075642 -85.209284 \n",
"491 491 WESTFIELD TOWN 40.658991 -74.347372 \n",
"492 492 WINFIELD TWP 40.772336 -79.728770 \n",
"493 493 ALLAMUCHY TWP 40.921765 -74.810166 \n",
"494 494 ALPHA BORO 40.667044 -75.157397 \n",
"495 495 BELVIDERE TOWN 40.829820 -75.077675 \n",
"496 496 BLAIRSTOWN TWP 40.983102 -74.958940 \n",
"497 497 FRELINGHUYSEN TWP 40.968410 -74.885799 \n",
"498 498 HACKETTSTOWN TOWN 40.853988 -74.829056 \n",
"499 499 HARMONY TWP 40.746765 -75.137397 \n",
"500 500 HOPE TWP 40.911118 -74.967363 \n",
"501 501 KNOWLTON TWP 40.930895 -75.027358 \n",
"502 502 LOPATCONG TWP 40.712792 -75.160375 \n",
"503 503 OXFORD TWP 42.826953 -83.297170 \n",
"504 504 PHILLIPSBURG TOWN 40.693710 -75.190176 \n",
"505 505 POHATCONG TWP 40.633355 -75.160375 \n",
"506 506 WASHINGTON BORO 40.758433 -74.979338 \n",
"507 507 WHITE TWP 40.812195 -75.049433 \n",
"\n",
" number_of_results status \n",
"0 1 OK \n",
"1 1 OK \n",
"2 1 OK \n",
"3 1 OK \n",
"4 1 OK \n",
"5 1 OK \n",
"6 1 OK \n",
"7 1 OK \n",
"8 1 OK \n",
"9 1 OK \n",
"10 1 OK \n",
"11 1 OK \n",
"12 1 OK \n",
"13 2 OK \n",
"14 1 OK \n",
"15 1 OK \n",
"16 1 OK \n",
"17 1 OK \n",
"18 1 OK \n",
"19 1 OK \n",
"20 1 OK \n",
"21 1 OK \n",
"22 1 OK \n",
"23 1 OK \n",
"24 1 OK \n",
"25 2 OK \n",
"26 1 OK \n",
"27 6 OK \n",
"28 1 OK \n",
"29 1 OK \n",
".. ... ... \n",
"478 1 OK \n",
"479 1 OK \n",
"480 1 OK \n",
"481 1 OK \n",
"482 1 OK \n",
"483 3 OK \n",
"484 1 OK \n",
"485 1 OK \n",
"486 1 OK \n",
"487 1 OK \n",
"488 1 OK \n",
"489 1 OK \n",
"490 1 OK \n",
"491 1 OK \n",
"492 1 OK \n",
"493 1 OK \n",
"494 2 OK \n",
"495 1 OK \n",
"496 1 OK \n",
"497 1 OK \n",
"498 1 OK \n",
"499 1 OK \n",
"500 1 OK \n",
"501 1 OK \n",
"502 1 OK \n",
"503 10 OK \n",
"504 1 OK \n",
"505 1 OK \n",
"506 1 OK \n",
"507 1 OK \n",
"\n",
"[508 rows x 6 columns]>"
]
},
"metadata": {
"tags": []
},
"execution_count": 25
}
]
},
{
"metadata": {
"id": "WEroeo421YP1",
"colab_type": "code",
"colab": {}
},
"cell_type": "code",
"source": [
"#read the csv file that has the latitude and longitude for the records in df2, which originally did NOT have latitude and longitude\n",
"df6 = pd.read_csv('/content/drive/My Drive/Colab/town_with_Lat_Long_output.csv')\n",
"df6.shape\n",
"\n",
"#pd.merge(df6, df7, on=\"Municipality Name\")\n",
"dfinal = df6.merge(df7, on=\"Municipality Name\", how = 'inner')"
],
"execution_count": 0,
"outputs": []
},
{
"metadata": {
"id": "JkaL81T0AbKx",
"colab_type": "code",
"outputId": "303729aa-ef75-4522-dd5d-4a9d7446e11a",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 2176
}
},
"cell_type": "code",
"source": [
"dfinal.head"
],
"execution_count": 0,
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": [
"<bound method NDFrame.head of Num Municipality Name latitude longitude number_of_results \\\n",
"0 0 ABSECON CITY 39.428450 -74.495708 1 \n",
"1 1 ATLANTIC CITY 39.364283 -74.422927 1 \n",
"2 3 BRIGANTINE CITY 39.410117 -74.364591 1 \n",
"3 4 BUENA BORO 39.521646 -74.948709 1 \n",
"4 5 BUENA VISTA TWP 39.528110 -74.896267 1 \n",
"5 6 EGG HARBOR CITY 39.528728 -74.647936 1 \n",
"6 7 EGG HARBOR TWP 39.382254 -74.616619 1 \n",
"7 8 ESTELL MANOR CITY 39.412060 -74.742385 1 \n",
"8 9 GALLOWAY TWP 39.492824 -74.559688 1 \n",
"9 10 HAMILTON TWP 40.211511 -74.679665 1 \n",
"10 11 HAMMONTON TOWN 39.636506 -74.802385 1 \n",
"11 12 LINWOOD CITY 39.339838 -74.575156 1 \n",
"12 13 LONGPORT BORO 39.312021 -74.527573 2 \n",
"13 14 MARGATE CITY 39.327894 -74.503486 1 \n",
"14 15 MULLICA TWP 39.590785 -74.663464 1 \n",
"15 16 NORTHFIELD CITY 44.458298 -93.161604 1 \n",
"16 17 PLEASANTVILLE CITY 39.389839 -74.524042 1 \n",
"17 18 PORT REPUBLIC CITY 39.520673 -74.485707 1 \n",
"18 19 SOMERS POINT CITY 39.316064 -74.605305 1 \n",
"19 20 VENTNOR CITY 39.340394 -74.477373 1 \n",
"20 21 ALLENDALE BORO 41.034445 -74.134677 1 \n",
"21 22 ALPINE BORO 40.955932 -73.931248 1 \n",
"22 23 BERGENFIELD BORO 40.927599 -73.997361 1 \n",
"23 24 BOGOTA BORO 40.877322 -74.027074 1 \n",
"24 25 CARLSTADT BORO 40.841634 -74.094772 2 \n",
"25 26 CLIFFSIDE PARK BORO 40.821489 -73.987639 1 \n",
"26 27 CLOSTER BORO 40.971384 -73.959277 6 \n",
"27 28 DEMAREST BORO 40.953102 -73.953616 1 \n",
"28 29 DUMONT BORO 40.940479 -73.998458 1 \n",
"29 30 ELMWOOD PARK BORO 40.900682 -74.124068 1 \n",
".. ... ... ... ... ... \n",
"477 478 FANWOOD BORO 40.640936 -74.383484 1 \n",
"478 479 GARWOOD BORO 40.651081 -74.325777 1 \n",
"479 480 HILLSIDE TWP 40.695504 -74.228733 1 \n",
"480 481 KENILWORTH BORO 40.676491 -74.290703 1 \n",
"481 482 LINDEN CITY 42.814473 -83.782453 1 \n",
"482 483 MOUNTAINSIDE BORO 40.671780 -74.355952 3 \n",
"483 484 NEW PROVIDENCE BORO 40.699699 -74.399798 1 \n",
"484 485 PLAINFIELD CITY 40.633714 -74.407374 1 \n",
"485 486 RAHWAY CITY 40.608159 -74.277647 1 \n",
"486 487 ROSELLE BORO 40.652098 -74.270751 1 \n",
"487 488 ROSELLE PARK BORO 40.661717 -74.260220 1 \n",
"488 489 SCOTCH PLAINS TWP 40.640040 -74.369018 1 \n",
"489 490 SUMMIT CITY 41.075642 -85.209284 1 \n",
"490 491 WESTFIELD TOWN 40.658991 -74.347372 1 \n",
"491 492 WINFIELD TWP 40.772336 -79.728770 1 \n",
"492 493 ALLAMUCHY TWP 40.921765 -74.810166 1 \n",
"493 494 ALPHA BORO 40.667044 -75.157397 2 \n",
"494 495 BELVIDERE TOWN 40.829820 -75.077675 1 \n",
"495 496 BLAIRSTOWN TWP 40.983102 -74.958940 1 \n",
"496 497 FRELINGHUYSEN TWP 40.968410 -74.885799 1 \n",
"497 498 HACKETTSTOWN TOWN 40.853988 -74.829056 1 \n",
"498 499 HARMONY TWP 40.746765 -75.137397 1 \n",
"499 500 HOPE TWP 40.911118 -74.967363 1 \n",
"500 501 KNOWLTON TWP 40.930895 -75.027358 1 \n",
"501 502 LOPATCONG TWP 40.712792 -75.160375 1 \n",
"502 503 OXFORD TWP 42.826953 -83.297170 10 \n",
"503 504 PHILLIPSBURG TOWN 40.693710 -75.190176 1 \n",
"504 505 POHATCONG TWP 40.633355 -75.160375 1 \n",
"505 506 WASHINGTON BORO 40.758433 -74.979338 1 \n",
"506 507 WHITE TWP 40.812195 -75.049433 1 \n",
"\n",
" status count \n",
"0 OK 284 \n",
"1 OK 1634 \n",
"2 OK 124 \n",
"3 OK 23 \n",
"4 OK 1 \n",
"5 OK 13 \n",
"6 OK 1123 \n",
"7 OK 1 \n",
"8 OK 730 \n",
"9 OK 2417 \n",
"10 OK 399 \n",
"11 OK 118 \n",
"12 OK 26 \n",
"13 OK 170 \n",
"14 OK 142 \n",
"15 OK 74 \n",
"16 OK 358 \n",
"17 OK 1 \n",
"18 OK 241 \n",
"19 OK 179 \n",
"20 OK 57 \n",
"21 OK 70 \n",
"22 OK 443 \n",
"23 OK 104 \n",
"24 OK 335 \n",
"25 OK 468 \n",
"26 OK 92 \n",
"27 OK 63 \n",
"28 OK 3 \n",
"29 OK 598 \n",
".. ... ... \n",
"477 OK 137 \n",
"478 OK 97 \n",
"479 OK 821 \n",
"480 OK 99 \n",
"481 OK 1570 \n",
"482 OK 192 \n",
"483 OK 242 \n",
"484 OK 992 \n",
"485 OK 1107 \n",
"486 OK 246 \n",
"487 OK 324 \n",
"488 OK 523 \n",
"489 OK 542 \n",
"490 OK 584 \n",
"491 OK 2 \n",
"492 OK 6 \n",
"493 OK 4 \n",
"494 OK 13 \n",
"495 OK 50 \n",
"496 OK 4 \n",
"497 OK 210 \n",
"498 OK 3 \n",
"499 OK 3 \n",
"500 OK 4 \n",
"501 OK 111 \n",
"502 OK 26 \n",
"503 OK 330 \n",
"504 OK 163 \n",
"505 OK 101 \n",
"506 OK 11 \n",
"\n",
"[507 rows x 7 columns]>"
]
},
"metadata": {
"tags": []
},
"execution_count": 40
}
]
},
{
"metadata": {
"id": "7wYQ4JSO2vck",
"colab_type": "code",
"outputId": "50a3089a-6bd2-4817-fbf5-40d4bf94265c",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 1105
}
},
"cell_type": "code",
"source": [
"#df5.head\n",
"#pd.DataFrame(df5).to_csv('/content/drive/My Drive/Colab/df5_output.csv', encoding='utf8')\n",
"df7 = pd.read_csv('/content/drive/My Drive/Colab/df5_output.csv')\n",
"df7.head"
],
"execution_count": 0,
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": [
"<bound method NDFrame.head of Municipality Name count\n",
"0 NEWARK CITY 9919\n",
"1 JERSEY CITY 7738\n",
"2 PATERSON CITY 5761\n",
"3 ELIZABETH CITY 4119\n",
"4 EDISON TWP 3340\n",
"5 WOODBRIDGE TWP 2949\n",
"6 NORTH BERGEN TWP 2554\n",
"7 UNION TWP 2537\n",
"8 PARAMUS BORO 2527\n",
"9 HAMILTON TWP 2417\n",
"10 FRANKLIN TWP 2301\n",
"11 CHERRY HILL TWP 2206\n",
"12 HACKENSACK CITY 2189\n",
"13 IRVINGTON TWP 2080\n",
"14 EAST ORANGE CITY 1976\n",
"15 PASSAIC CITY 1960\n",
"16 OLD BRIDGE TWP 1865\n",
"17 SOUTH BRUNSWICK TWP 1831\n",
"18 NEW BRUNSWICK CITY 1803\n",
"19 BRICK TWP 1740\n",
"20 CAMDEN CITY 1672\n",
"21 ATLANTIC CITY 1634\n",
"22 BRIDGEWATER TWP 1627\n",
"23 LINDEN CITY 1570\n",
"24 EAST BRUNSWICK TWP 1513\n",
"25 TEANECK TWP 1511\n",
"26 PERTH AMBOY CITY 1430\n",
"27 GLOUCESTER TWP 1403\n",
"28 MONTCLAIR TWP 1389\n",
"29 TRENTON CITY 1379\n",
".. ... ...\n",
"477 FRANKFORD TWP 1\n",
"478 NEWFIELD BORO 1\n",
"479 ESTELL MANOR CITY 1\n",
"480 OLDMANS TWP 1\n",
"481 SOUTHAMPTON TWP 1\n",
"482 BASS RIVER TWP 1\n",
"483 LAWNSIDE BORO 1\n",
"484 PORT REPUBLIC CITY 1\n",
"485 STOCKTON BORO 1\n",
"486 HELMETTA BORO 1\n",
"487 BUENA VISTA TWP 1\n",
"488 SUSSEX BORO 1\n",
"489 SWEDESBORO BORO 1\n",
"490 BLOOMSBURY BORO 1\n",
"491 HAWORTH BORO 1\n",
"492 SHAMONG TWP 1\n",
"493 BYRAM TWP 1\n",
"494 SANDVSTON TWP 1\n",
"495 MONTAGUE TWP 1\n",
"496 UPPER DEERFIELD TWP 1\n",
"497 MOONACHIE BORO 1\n",
"498 HAMPTON TWP 1\n",
"499 HAINESPORT TWP 1\n",
"500 ROCKY HILL BORO 1\n",
"501 STILLWATER TWP 1\n",
"502 DEERFIELD TWP 1\n",
"503 BARNEGAT LIGHT BORO 1\n",
"504 WATERFORD TWP 1\n",
"505 GLEN GARDNER BORO 1\n",
"506 MILFORD TWP 1\n",
"\n",
"[507 rows x 2 columns]>"
]
},
"metadata": {
"tags": []
},
"execution_count": 33
}
]
},
{
"metadata": {
"id": "Y5udJZm63Tdg",
"colab_type": "code",
"colab": {}
},
"cell_type": "code",
"source": [
"from folium.plugins import HeatMap\n",
"def generateBaseMap(default_location=[40.5397293,-74.6273494], default_zoom_start=12):\n",
" base_map = folium.Map(location=default_location, control_scale=True, zoom_start=default_zoom_start)\n",
" return base_map\n",
"\n",
"base_map = generateBaseMap()\n",
"base_map\n",
"m = HeatMap(data=dfinal[['latitude', 'longitude', 'count']].groupby(['latitude','longitude']).sum().reset_index().values.tolist(), radius=7, max_zoom=10).add_to(base_map)\n",
"m.save('/content/drive/My Drive/Colab/heatmap_town_total_accidents_2017.html')"
],
"execution_count": 0,
"outputs": []
}
]
}
@HariSan1
Copy link
Author

TSB - version 1.

@ashieldtech
Copy link

Thanks for the details code. Where can I get the dataset?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment