master.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import pickle as pkl\n",
    "import warnings\n",
    "import time as time\n",
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "import urllib3\n",
    "import numpy as np\n",
    "from tqdm import tqdm\n",
    "import asyncio\n",
    "urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)\n",
    "from requests_html import HTMLSession,AsyncHTMLSession"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv('/home/bh_parijat/data/main_dataset_update1.2.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4791859, 21)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11207.0    98484\n",
       "10035.0    94918\n",
       "11212.0    92070\n",
       "10457.0    87027\n",
       "10001.0    86859\n",
       "           ...  \n",
       "10152.0        4\n",
       "10704.0        3\n",
       "11020.0        2\n",
       "10045.0        1\n",
       "11559.0        1\n",
       "Name: zipcode, Length: 214, dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.zipcode.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "asession = HTMLSession()\n",
    "url = \"http://www.zipcodestogo.com/New%20York/\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    " def get_html_data(delay=5000):\n",
    "    \n",
    "\n",
    "    response =  asession.get(url)\n",
    "    \n",
    "    \n",
    "    \n",
    "    soup = BeautifulSoup(response.content,'html.parser')\n",
    "    \n",
    "    table_rows = soup.find_all('tr')\n",
    "    \n",
    "    return table_rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "table_rows =get_html_data()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2217\n"
     ]
    }
   ],
   "source": [
    "print(len(table_rows))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<td align=\"center\"><a href=\"//www.zipcodestogo.com/Elmira/NY/14925/\">14925</a></td>,\n",
       " <td>Elmira</td>,\n",
       " <td><a href=\"/Chemung/NY/\">Chemung</a></td>,\n",
       " <td><div align=\"center\"><a href=\"//maps.google.com/maps?oi=map&amp;q=14925\">View Map</a></div></td>]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table_rows[-10].find_all('td')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_map(rows = None):\n",
    "    \n",
    "    \"\"\"\n",
    "    Used for creating a map for zipcode to city and vice-versa\n",
    "    \n",
    "    \"\"\"\n",
    "    \n",
    "    if not rows:\n",
    "        return\n",
    "    map_zipcode_city = {}\n",
    "    \n",
    "    map_city_zipcode = {}\n",
    "    for row in rows:\n",
    "        \n",
    "        trs = row.find_all('td')\n",
    "        \n",
    "        if len(trs)!=4:\n",
    "            continue\n",
    "        zipcode = row.find_all('td')[0].text\n",
    "        city = row.find_all('td')[1].text\n",
    "        \n",
    "        map_zipcode_city[zipcode] = city\n",
    "        \n",
    "        codes = map_city_zipcode.get(city,[])\n",
    "        \n",
    "        codes.append(zipcode)\n",
    "        \n",
    "        \n",
    "    return map_city_zipcode,map_zipcode_city"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "map_city_zipcode,map_zipcode_city = create_map(rows=table_rows[3:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2205"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(map_zipcode_city.keys())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_dataframe(map_zipcode_city=None):\n",
    "    \n",
    "    if map_zipcode_city is None:\n",
    "        return None\n",
    "    \n",
    "    zipcode,city = [],[]\n",
    "    for key in map_zipcode_city:\n",
    "        zipcode.append(key)\n",
    "        city.append(map_zipcode_city[key])\n",
    "    zipcode_city_df = pd.DataFrame(data={'zipcode':zipcode,'city':city})\n",
    "    \n",
    "    return zipcode_city_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>zipcode</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00501</td>\n",
       "      <td>Holtsville</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00544</td>\n",
       "      <td>Holtsville</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>06390</td>\n",
       "      <td>Fishers Island</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10001</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10002</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  zipcode            city\n",
       "0   00501      Holtsville\n",
       "1   00544      Holtsville\n",
       "2   06390  Fishers Island\n",
       "3   10001        New York\n",
       "4   10002        New York"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zipcode_city_df = create_dataframe(map_zipcode_city=map_zipcode_city)\n",
    "zipcode_city_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "zipcode_city_df.to_csv('~/data/zipcode_city_map.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4791859, 21)"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['zipcode'] = data.zipcode.apply(lambda x: np.int16(x))\n",
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2205, 2)"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zipcode_city_df['zipcode'] = zipcode_city_df.zipcode.apply(lambda x:np.int16(x))\n",
    "zipcode_city_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_mapped = pd.merge(data,zipcode_city_df, left_on=['zipcode'] , right_on=['zipcode'], how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4791859, 22)"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_mapped.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>index</th>\n",
       "      <th>ARREST_KEY</th>\n",
       "      <th>ARREST_DATE</th>\n",
       "      <th>PD_CD</th>\n",
       "      <th>PD_DESC</th>\n",
       "      <th>KY_CD</th>\n",
       "      <th>OFNS_DESC</th>\n",
       "      <th>LAW_CODE</th>\n",
       "      <th>LAW_CAT_CD</th>\n",
       "      <th>ARREST_BORO</th>\n",
       "      <th>...</th>\n",
       "      <th>AGE_GROUP</th>\n",
       "      <th>PERP_SEX</th>\n",
       "      <th>PERP_RACE</th>\n",
       "      <th>X_COORD_CD</th>\n",
       "      <th>Y_COORD_CD</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>org_index</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>173130602</td>\n",
       "      <td>12/31/2017</td>\n",
       "      <td>566.0</td>\n",
       "      <td>MARIJUANA, POSSESSION</td>\n",
       "      <td>678.0</td>\n",
       "      <td>MISCELLANEOUS PENAL LAW</td>\n",
       "      <td>PL 2210500</td>\n",
       "      <td>V</td>\n",
       "      <td>Q</td>\n",
       "      <td>...</td>\n",
       "      <td>25-44</td>\n",
       "      <td>M</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>1063056.0</td>\n",
       "      <td>207463.0</td>\n",
       "      <td>40.735772</td>\n",
       "      <td>-73.715638</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11426</td>\n",
       "      <td>Bellerose</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>173114463</td>\n",
       "      <td>12/31/2017</td>\n",
       "      <td>478.0</td>\n",
       "      <td>THEFT OF SERVICES, UNCLASSIFIED</td>\n",
       "      <td>343.0</td>\n",
       "      <td>OTHER OFFENSES RELATED TO THEFT</td>\n",
       "      <td>PL 1651503</td>\n",
       "      <td>M</td>\n",
       "      <td>Q</td>\n",
       "      <td>...</td>\n",
       "      <td>25-44</td>\n",
       "      <td>M</td>\n",
       "      <td>ASIAN / PACIFIC ISLANDER</td>\n",
       "      <td>1009113.0</td>\n",
       "      <td>219613.0</td>\n",
       "      <td>40.769437</td>\n",
       "      <td>-73.910241</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11105</td>\n",
       "      <td>Astoria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>173113513</td>\n",
       "      <td>12/31/2017</td>\n",
       "      <td>849.0</td>\n",
       "      <td>NY STATE LAWS,UNCLASSIFIED VIOLATION</td>\n",
       "      <td>677.0</td>\n",
       "      <td>OTHER STATE LAWS</td>\n",
       "      <td>LOC000000V</td>\n",
       "      <td>V</td>\n",
       "      <td>K</td>\n",
       "      <td>...</td>\n",
       "      <td>18-24</td>\n",
       "      <td>M</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>1010719.0</td>\n",
       "      <td>186857.0</td>\n",
       "      <td>40.679525</td>\n",
       "      <td>-73.904572</td>\n",
       "      <td>2.0</td>\n",
       "      <td>11207</td>\n",
       "      <td>Brooklyn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>173113423</td>\n",
       "      <td>12/31/2017</td>\n",
       "      <td>101.0</td>\n",
       "      <td>ASSAULT 3</td>\n",
       "      <td>344.0</td>\n",
       "      <td>ASSAULT 3 &amp; RELATED OFFENSES</td>\n",
       "      <td>PL 1200001</td>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>25-44</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>987831.0</td>\n",
       "      <td>217446.0</td>\n",
       "      <td>40.763523</td>\n",
       "      <td>-73.987074</td>\n",
       "      <td>3.0</td>\n",
       "      <td>10019</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>173113421</td>\n",
       "      <td>12/31/2017</td>\n",
       "      <td>101.0</td>\n",
       "      <td>ASSAULT 3</td>\n",
       "      <td>344.0</td>\n",
       "      <td>ASSAULT 3 &amp; RELATED OFFENSES</td>\n",
       "      <td>PL 1200001</td>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>45-64</td>\n",
       "      <td>M</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>987073.0</td>\n",
       "      <td>216078.0</td>\n",
       "      <td>40.759768</td>\n",
       "      <td>-73.989811</td>\n",
       "      <td>4.0</td>\n",
       "      <td>10036</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  ARREST_KEY ARREST_DATE  PD_CD                               PD_DESC  \\\n",
       "0      0   173130602  12/31/2017  566.0                 MARIJUANA, POSSESSION   \n",
       "1      1   173114463  12/31/2017  478.0       THEFT OF SERVICES, UNCLASSIFIED   \n",
       "2      2   173113513  12/31/2017  849.0  NY STATE LAWS,UNCLASSIFIED VIOLATION   \n",
       "3      3   173113423  12/31/2017  101.0                             ASSAULT 3   \n",
       "4      4   173113421  12/31/2017  101.0                             ASSAULT 3   \n",
       "\n",
       "   KY_CD                        OFNS_DESC    LAW_CODE LAW_CAT_CD ARREST_BORO  \\\n",
       "0  678.0          MISCELLANEOUS PENAL LAW  PL 2210500          V           Q   \n",
       "1  343.0  OTHER OFFENSES RELATED TO THEFT  PL 1651503          M           Q   \n",
       "2  677.0                 OTHER STATE LAWS  LOC000000V          V           K   \n",
       "3  344.0     ASSAULT 3 & RELATED OFFENSES  PL 1200001          M           M   \n",
       "4  344.0     ASSAULT 3 & RELATED OFFENSES  PL 1200001          M           M   \n",
       "\n",
       "   ...  AGE_GROUP  PERP_SEX                 PERP_RACE X_COORD_CD Y_COORD_CD  \\\n",
       "0  ...      25-44         M                     BLACK  1063056.0   207463.0   \n",
       "1  ...      25-44         M  ASIAN / PACIFIC ISLANDER  1009113.0   219613.0   \n",
       "2  ...      18-24         M                     BLACK  1010719.0   186857.0   \n",
       "3  ...      25-44         M                     WHITE   987831.0   217446.0   \n",
       "4  ...      45-64         M                     BLACK   987073.0   216078.0   \n",
       "\n",
       "    Latitude  Longitude  org_index  zipcode       city  \n",
       "0  40.735772 -73.715638        0.0    11426  Bellerose  \n",
       "1  40.769437 -73.910241        1.0    11105    Astoria  \n",
       "2  40.679525 -73.904572        2.0    11207   Brooklyn  \n",
       "3  40.763523 -73.987074        3.0    10019   New York  \n",
       "4  40.759768 -73.989811        4.0    10036   New York  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_mapped.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "cities_not_found = data_mapped[data_mapped.city.isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(22568, 22)"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities_not_found.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>index</th>\n",
       "      <th>ARREST_KEY</th>\n",
       "      <th>ARREST_DATE</th>\n",
       "      <th>PD_CD</th>\n",
       "      <th>PD_DESC</th>\n",
       "      <th>KY_CD</th>\n",
       "      <th>OFNS_DESC</th>\n",
       "      <th>LAW_CODE</th>\n",
       "      <th>LAW_CAT_CD</th>\n",
       "      <th>ARREST_BORO</th>\n",
       "      <th>...</th>\n",
       "      <th>AGE_GROUP</th>\n",
       "      <th>PERP_SEX</th>\n",
       "      <th>PERP_RACE</th>\n",
       "      <th>X_COORD_CD</th>\n",
       "      <th>Y_COORD_CD</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>org_index</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>48</td>\n",
       "      <td>173113521</td>\n",
       "      <td>12/31/2017</td>\n",
       "      <td>478.0</td>\n",
       "      <td>THEFT OF SERVICES, UNCLASSIFIED</td>\n",
       "      <td>343.0</td>\n",
       "      <td>OTHER OFFENSES RELATED TO THEFT</td>\n",
       "      <td>PL 1651503</td>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>25-44</td>\n",
       "      <td>F</td>\n",
       "      <td>WHITE HISPANIC</td>\n",
       "      <td>995369.0</td>\n",
       "      <td>221138.0</td>\n",
       "      <td>40.773650</td>\n",
       "      <td>-73.959857</td>\n",
       "      <td>48.0</td>\n",
       "      <td>10075</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>157</td>\n",
       "      <td>173117304</td>\n",
       "      <td>12/31/2017</td>\n",
       "      <td>109.0</td>\n",
       "      <td>ASSAULT 2,1,UNCLASSIFIED</td>\n",
       "      <td>106.0</td>\n",
       "      <td>FELONY ASSAULT</td>\n",
       "      <td>PL 1200502</td>\n",
       "      <td>F</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>25-44</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>994643.0</td>\n",
       "      <td>216998.0</td>\n",
       "      <td>40.762288</td>\n",
       "      <td>-73.962485</td>\n",
       "      <td>157.0</td>\n",
       "      <td>10065</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>197</td>\n",
       "      <td>173117789</td>\n",
       "      <td>12/31/2017</td>\n",
       "      <td>109.0</td>\n",
       "      <td>ASSAULT 2,1,UNCLASSIFIED</td>\n",
       "      <td>106.0</td>\n",
       "      <td>FELONY ASSAULT</td>\n",
       "      <td>PL 1200502</td>\n",
       "      <td>F</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>25-44</td>\n",
       "      <td>F</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>994643.0</td>\n",
       "      <td>216998.0</td>\n",
       "      <td>40.762288</td>\n",
       "      <td>-73.962485</td>\n",
       "      <td>197.0</td>\n",
       "      <td>10065</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268</th>\n",
       "      <td>268</td>\n",
       "      <td>173120426</td>\n",
       "      <td>12/31/2017</td>\n",
       "      <td>439.0</td>\n",
       "      <td>LARCENY,GRAND FROM OPEN AREAS,UNCLASSIFIED</td>\n",
       "      <td>109.0</td>\n",
       "      <td>GRAND LARCENY</td>\n",
       "      <td>PL 1553001</td>\n",
       "      <td>F</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>18-24</td>\n",
       "      <td>M</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>992359.0</td>\n",
       "      <td>217816.0</td>\n",
       "      <td>40.764536</td>\n",
       "      <td>-73.970728</td>\n",
       "      <td>268.0</td>\n",
       "      <td>10065</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>939</th>\n",
       "      <td>939</td>\n",
       "      <td>173085784</td>\n",
       "      <td>12/29/2017</td>\n",
       "      <td>205.0</td>\n",
       "      <td>TRESPASS 2, CRIMINAL</td>\n",
       "      <td>352.0</td>\n",
       "      <td>CRIMINAL TRESPASS</td>\n",
       "      <td>PL 1401500</td>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>25-44</td>\n",
       "      <td>F</td>\n",
       "      <td>ASIAN / PACIFIC ISLANDER</td>\n",
       "      <td>996768.0</td>\n",
       "      <td>221152.0</td>\n",
       "      <td>40.773687</td>\n",
       "      <td>-73.954806</td>\n",
       "      <td>939.0</td>\n",
       "      <td>10075</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     index  ARREST_KEY ARREST_DATE  PD_CD  \\\n",
       "48      48   173113521  12/31/2017  478.0   \n",
       "157    157   173117304  12/31/2017  109.0   \n",
       "197    197   173117789  12/31/2017  109.0   \n",
       "268    268   173120426  12/31/2017  439.0   \n",
       "939    939   173085784  12/29/2017  205.0   \n",
       "\n",
       "                                        PD_DESC  KY_CD  \\\n",
       "48              THEFT OF SERVICES, UNCLASSIFIED  343.0   \n",
       "157                    ASSAULT 2,1,UNCLASSIFIED  106.0   \n",
       "197                    ASSAULT 2,1,UNCLASSIFIED  106.0   \n",
       "268  LARCENY,GRAND FROM OPEN AREAS,UNCLASSIFIED  109.0   \n",
       "939                        TRESPASS 2, CRIMINAL  352.0   \n",
       "\n",
       "                           OFNS_DESC    LAW_CODE LAW_CAT_CD ARREST_BORO  ...  \\\n",
       "48   OTHER OFFENSES RELATED TO THEFT  PL 1651503          M           M  ...   \n",
       "157                   FELONY ASSAULT  PL 1200502          F           M  ...   \n",
       "197                   FELONY ASSAULT  PL 1200502          F           M  ...   \n",
       "268                    GRAND LARCENY  PL 1553001          F           M  ...   \n",
       "939                CRIMINAL TRESPASS  PL 1401500          M           M  ...   \n",
       "\n",
       "     AGE_GROUP  PERP_SEX                 PERP_RACE X_COORD_CD Y_COORD_CD  \\\n",
       "48       25-44         F            WHITE HISPANIC   995369.0   221138.0   \n",
       "157      25-44         M                     WHITE   994643.0   216998.0   \n",
       "197      25-44         F                     WHITE   994643.0   216998.0   \n",
       "268      18-24         M                     BLACK   992359.0   217816.0   \n",
       "939      25-44         F  ASIAN / PACIFIC ISLANDER   996768.0   221152.0   \n",
       "\n",
       "      Latitude  Longitude  org_index  zipcode  city  \n",
       "48   40.773650 -73.959857       48.0    10075   NaN  \n",
       "157  40.762288 -73.962485      157.0    10065   NaN  \n",
       "197  40.762288 -73.962485      197.0    10065   NaN  \n",
       "268  40.764536 -73.970728      268.0    10065   NaN  \n",
       "939  40.773687 -73.954806      939.0    10075   NaN  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities_not_found.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10065    15430\n",
       "83        4558\n",
       "10075     2476\n",
       "10097      104\n",
       "Name: zipcode, dtype: int64"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities_not_found.zipcode.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "d10097 = data[data.zipcode == 10097]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(104, 21)"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d10097.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>index</th>\n",
       "      <th>ARREST_KEY</th>\n",
       "      <th>ARREST_DATE</th>\n",
       "      <th>PD_CD</th>\n",
       "      <th>PD_DESC</th>\n",
       "      <th>KY_CD</th>\n",
       "      <th>OFNS_DESC</th>\n",
       "      <th>LAW_CODE</th>\n",
       "      <th>LAW_CAT_CD</th>\n",
       "      <th>ARREST_BORO</th>\n",
       "      <th>...</th>\n",
       "      <th>JURISDICTION_CODE</th>\n",
       "      <th>AGE_GROUP</th>\n",
       "      <th>PERP_SEX</th>\n",
       "      <th>PERP_RACE</th>\n",
       "      <th>X_COORD_CD</th>\n",
       "      <th>Y_COORD_CD</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>org_index</th>\n",
       "      <th>zipcode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>44615</th>\n",
       "      <td>44615</td>\n",
       "      <td>170952625</td>\n",
       "      <td>10/29/2017</td>\n",
       "      <td>203.0</td>\n",
       "      <td>TRESPASS 3, CRIMINAL</td>\n",
       "      <td>352.0</td>\n",
       "      <td>CRIMINAL TRESPASS</td>\n",
       "      <td>PL 1401000</td>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>45-64</td>\n",
       "      <td>M</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>988506.0</td>\n",
       "      <td>217073.0</td>\n",
       "      <td>40.762499</td>\n",
       "      <td>-73.984638</td>\n",
       "      <td>44615.0</td>\n",
       "      <td>10097</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63548</th>\n",
       "      <td>63548</td>\n",
       "      <td>170132910</td>\n",
       "      <td>10/06/2017</td>\n",
       "      <td>515.0</td>\n",
       "      <td>CONTROLLED SUBSTANCE,SALE 3</td>\n",
       "      <td>117.0</td>\n",
       "      <td>DANGEROUS DRUGS</td>\n",
       "      <td>PL 2203901</td>\n",
       "      <td>F</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25-44</td>\n",
       "      <td>M</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>988506.0</td>\n",
       "      <td>217073.0</td>\n",
       "      <td>40.762499</td>\n",
       "      <td>-73.984638</td>\n",
       "      <td>63548.0</td>\n",
       "      <td>10097</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143163</th>\n",
       "      <td>143163</td>\n",
       "      <td>166396250</td>\n",
       "      <td>06/24/2017</td>\n",
       "      <td>729.0</td>\n",
       "      <td>FORGERY,ETC.,UNCLASSIFIED-FELONY</td>\n",
       "      <td>113.0</td>\n",
       "      <td>FORGERY</td>\n",
       "      <td>PL 1657200</td>\n",
       "      <td>F</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>45-64</td>\n",
       "      <td>M</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>988506.0</td>\n",
       "      <td>217073.0</td>\n",
       "      <td>40.762499</td>\n",
       "      <td>-73.984638</td>\n",
       "      <td>143163.0</td>\n",
       "      <td>10097</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163748</th>\n",
       "      <td>163748</td>\n",
       "      <td>165289147</td>\n",
       "      <td>05/29/2017</td>\n",
       "      <td>269.0</td>\n",
       "      <td>MISCHIEF,CRIMINAL,    UNCL 2ND</td>\n",
       "      <td>121.0</td>\n",
       "      <td>CRIMINAL MISCHIEF &amp; RELATED OFFENSES</td>\n",
       "      <td>PL 145052H</td>\n",
       "      <td>F</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25-44</td>\n",
       "      <td>M</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>988506.0</td>\n",
       "      <td>217073.0</td>\n",
       "      <td>40.762499</td>\n",
       "      <td>-73.984638</td>\n",
       "      <td>163748.0</td>\n",
       "      <td>10097</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>227623</th>\n",
       "      <td>247553</td>\n",
       "      <td>162004919</td>\n",
       "      <td>02/16/2017</td>\n",
       "      <td>707.0</td>\n",
       "      <td>IMPERSONATION 2, PUBLIC SERVANT</td>\n",
       "      <td>340.0</td>\n",
       "      <td>FRAUDS</td>\n",
       "      <td>PL 1902300</td>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25-44</td>\n",
       "      <td>M</td>\n",
       "      <td>ASIAN / PACIFIC ISLANDER</td>\n",
       "      <td>988506.0</td>\n",
       "      <td>217073.0</td>\n",
       "      <td>40.762499</td>\n",
       "      <td>-73.984638</td>\n",
       "      <td>247553.0</td>\n",
       "      <td>10097</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         index  ARREST_KEY ARREST_DATE  PD_CD  \\\n",
       "44615    44615   170952625  10/29/2017  203.0   \n",
       "63548    63548   170132910  10/06/2017  515.0   \n",
       "143163  143163   166396250  06/24/2017  729.0   \n",
       "163748  163748   165289147  05/29/2017  269.0   \n",
       "227623  247553   162004919  02/16/2017  707.0   \n",
       "\n",
       "                                 PD_DESC  KY_CD  \\\n",
       "44615               TRESPASS 3, CRIMINAL  352.0   \n",
       "63548        CONTROLLED SUBSTANCE,SALE 3  117.0   \n",
       "143163  FORGERY,ETC.,UNCLASSIFIED-FELONY  113.0   \n",
       "163748    MISCHIEF,CRIMINAL,    UNCL 2ND  121.0   \n",
       "227623   IMPERSONATION 2, PUBLIC SERVANT  340.0   \n",
       "\n",
       "                                   OFNS_DESC    LAW_CODE LAW_CAT_CD  \\\n",
       "44615                      CRIMINAL TRESPASS  PL 1401000          M   \n",
       "63548                        DANGEROUS DRUGS  PL 2203901          F   \n",
       "143163                               FORGERY  PL 1657200          F   \n",
       "163748  CRIMINAL MISCHIEF & RELATED OFFENSES  PL 145052H          F   \n",
       "227623                                FRAUDS  PL 1902300          M   \n",
       "\n",
       "       ARREST_BORO  ...  JURISDICTION_CODE  AGE_GROUP PERP_SEX  \\\n",
       "44615            M  ...                0.0      45-64        M   \n",
       "63548            M  ...                0.0      25-44        M   \n",
       "143163           M  ...                0.0      45-64        M   \n",
       "163748           M  ...                0.0      25-44        M   \n",
       "227623           M  ...                0.0      25-44        M   \n",
       "\n",
       "                       PERP_RACE X_COORD_CD  Y_COORD_CD   Latitude  Longitude  \\\n",
       "44615                      BLACK   988506.0    217073.0  40.762499 -73.984638   \n",
       "63548                      BLACK   988506.0    217073.0  40.762499 -73.984638   \n",
       "143163                     BLACK   988506.0    217073.0  40.762499 -73.984638   \n",
       "163748                     BLACK   988506.0    217073.0  40.762499 -73.984638   \n",
       "227623  ASIAN / PACIFIC ISLANDER   988506.0    217073.0  40.762499 -73.984638   \n",
       "\n",
       "        org_index  zipcode  \n",
       "44615     44615.0    10097  \n",
       "63548     63548.0    10097  \n",
       "143163   143163.0    10097  \n",
       "163748   163748.0    10097  \n",
       "227623   247553.0    10097  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d10097.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "22568it [13:00, 28.92it/s]\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "latitudes,longitudes, zipcodes,indices = [],[],[],[]\n",
    "\n",
    "for i,row in tqdm(cities_not_found.iterrows()):\n",
    "    \n",
    "    lat = row['Latitude']\n",
    "    \n",
    "    lon = row['Longitude']\n",
    "    \n",
    "    index = row['org_index']\n",
    "    \n",
    "    base_url = \"https://maps.googleapis.com/maps/api/geocode/json?latlng=\"\n",
    "    \n",
    "    query_params = \"&key=AIzaSyDwnQnzv_HtSlzkKfXfcg_v0pbXBRBui1U\" \n",
    "    \n",
    "    url = base_url + str(lat) + \",\" + str(lon) + query_params\n",
    "    \n",
    "    res = requests.request(\"GET\",url)\n",
    "    \n",
    "    res = res.json()\n",
    "    \n",
    "    features = res['results']\n",
    "    \n",
    "    zipcode = None\n",
    "    for feature in features:\n",
    "        \n",
    "        address = feature['address_components']\n",
    "        \n",
    "        for info in address:\n",
    "            \n",
    "            if \"postal_code\" in info[\"types\"]:\n",
    "                zipcode = info[\"long_name\"]\n",
    "                \n",
    "                \n",
    "                \n",
    "    if zipcode is not None:\n",
    "        latitudes.append(lat)\n",
    "        longitudes.append(lon)\n",
    "        zipcodes.append(zipcode)\n",
    "        indices.append(index)\n",
    "        \n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(22568, 22568)"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(indices),len(zipcodes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df = pd.DataFrame(data={\"org_index\":indices,\"Latitude\":latitudes,\"Longitude\":longitudes,\"zipcode\":zipcodes})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>org_index</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>zipcode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>48.0</td>\n",
       "      <td>40.773650</td>\n",
       "      <td>-73.959857</td>\n",
       "      <td>10075</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>157.0</td>\n",
       "      <td>40.762288</td>\n",
       "      <td>-73.962485</td>\n",
       "      <td>10065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>197.0</td>\n",
       "      <td>40.762288</td>\n",
       "      <td>-73.962485</td>\n",
       "      <td>10065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>268.0</td>\n",
       "      <td>40.764536</td>\n",
       "      <td>-73.970728</td>\n",
       "      <td>10065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>939.0</td>\n",
       "      <td>40.773687</td>\n",
       "      <td>-73.954806</td>\n",
       "      <td>10075</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   org_index   Latitude  Longitude zipcode\n",
       "0       48.0  40.773650 -73.959857   10075\n",
       "1      157.0  40.762288 -73.962485   10065\n",
       "2      197.0  40.762288 -73.962485   10065\n",
       "3      268.0  40.764536 -73.970728   10065\n",
       "4      939.0  40.773687 -73.954806   10075"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(22568, 4)"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10065    13490\n",
       "10024     3269\n",
       "10075     2215\n",
       "10022     1094\n",
       "10019      827\n",
       "10021      755\n",
       "10128      536\n",
       "10028      382\n",
       "Name: zipcode, dtype: int64"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df.zipcode.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "missing_zipcode_in_map = {10065:\"New York\",10075:\"New York\",\n",
    "                          10024:\"New york\",10022:\"New york\",\n",
    "                          10019:\"New york\",10021:\"New york\",\n",
    "                          10128:\"New york\",10028:\"New York\"}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df['city'] = new_df.zipcode.apply(lambda x:missing_zipcode_in_map[np.int32(x)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "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>org_index</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>48.0</td>\n",
       "      <td>40.773650</td>\n",
       "      <td>-73.959857</td>\n",
       "      <td>10075</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>157.0</td>\n",
       "      <td>40.762288</td>\n",
       "      <td>-73.962485</td>\n",
       "      <td>10065</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>197.0</td>\n",
       "      <td>40.762288</td>\n",
       "      <td>-73.962485</td>\n",
       "      <td>10065</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>268.0</td>\n",
       "      <td>40.764536</td>\n",
       "      <td>-73.970728</td>\n",
       "      <td>10065</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>939.0</td>\n",
       "      <td>40.773687</td>\n",
       "      <td>-73.954806</td>\n",
       "      <td>10075</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   org_index   Latitude  Longitude zipcode      city\n",
       "0       48.0  40.773650 -73.959857   10075  New York\n",
       "1      157.0  40.762288 -73.962485   10065  New York\n",
       "2      197.0  40.762288 -73.962485   10065  New York\n",
       "3      268.0  40.764536 -73.970728   10065  New York\n",
       "4      939.0  40.773687 -73.954806   10075  New York"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "cities_not_found_1 = cities_not_found.drop(columns=['zipcode','city','org_index'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "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>index</th>\n",
       "      <th>ARREST_KEY</th>\n",
       "      <th>ARREST_DATE</th>\n",
       "      <th>PD_CD</th>\n",
       "      <th>PD_DESC</th>\n",
       "      <th>KY_CD</th>\n",
       "      <th>OFNS_DESC</th>\n",
       "      <th>LAW_CODE</th>\n",
       "      <th>LAW_CAT_CD</th>\n",
       "      <th>ARREST_BORO</th>\n",
       "      <th>ARREST_PRECINCT</th>\n",
       "      <th>JURISDICTION_CODE</th>\n",
       "      <th>AGE_GROUP</th>\n",
       "      <th>PERP_SEX</th>\n",
       "      <th>PERP_RACE</th>\n",
       "      <th>X_COORD_CD</th>\n",
       "      <th>Y_COORD_CD</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>48</td>\n",
       "      <td>173113521</td>\n",
       "      <td>12/31/2017</td>\n",
       "      <td>478.0</td>\n",
       "      <td>THEFT OF SERVICES, UNCLASSIFIED</td>\n",
       "      <td>343.0</td>\n",
       "      <td>OTHER OFFENSES RELATED TO THEFT</td>\n",
       "      <td>PL 1651503</td>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "      <td>19</td>\n",
       "      <td>1.0</td>\n",
       "      <td>25-44</td>\n",
       "      <td>F</td>\n",
       "      <td>WHITE HISPANIC</td>\n",
       "      <td>995369.0</td>\n",
       "      <td>221138.0</td>\n",
       "      <td>40.773650</td>\n",
       "      <td>-73.959857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>157</td>\n",
       "      <td>173117304</td>\n",
       "      <td>12/31/2017</td>\n",
       "      <td>109.0</td>\n",
       "      <td>ASSAULT 2,1,UNCLASSIFIED</td>\n",
       "      <td>106.0</td>\n",
       "      <td>FELONY ASSAULT</td>\n",
       "      <td>PL 1200502</td>\n",
       "      <td>F</td>\n",
       "      <td>M</td>\n",
       "      <td>19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25-44</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>994643.0</td>\n",
       "      <td>216998.0</td>\n",
       "      <td>40.762288</td>\n",
       "      <td>-73.962485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>197</td>\n",
       "      <td>173117789</td>\n",
       "      <td>12/31/2017</td>\n",
       "      <td>109.0</td>\n",
       "      <td>ASSAULT 2,1,UNCLASSIFIED</td>\n",
       "      <td>106.0</td>\n",
       "      <td>FELONY ASSAULT</td>\n",
       "      <td>PL 1200502</td>\n",
       "      <td>F</td>\n",
       "      <td>M</td>\n",
       "      <td>19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25-44</td>\n",
       "      <td>F</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>994643.0</td>\n",
       "      <td>216998.0</td>\n",
       "      <td>40.762288</td>\n",
       "      <td>-73.962485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268</th>\n",
       "      <td>268</td>\n",
       "      <td>173120426</td>\n",
       "      <td>12/31/2017</td>\n",
       "      <td>439.0</td>\n",
       "      <td>LARCENY,GRAND FROM OPEN AREAS,UNCLASSIFIED</td>\n",
       "      <td>109.0</td>\n",
       "      <td>GRAND LARCENY</td>\n",
       "      <td>PL 1553001</td>\n",
       "      <td>F</td>\n",
       "      <td>M</td>\n",
       "      <td>19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18-24</td>\n",
       "      <td>M</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>992359.0</td>\n",
       "      <td>217816.0</td>\n",
       "      <td>40.764536</td>\n",
       "      <td>-73.970728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>939</th>\n",
       "      <td>939</td>\n",
       "      <td>173085784</td>\n",
       "      <td>12/29/2017</td>\n",
       "      <td>205.0</td>\n",
       "      <td>TRESPASS 2, CRIMINAL</td>\n",
       "      <td>352.0</td>\n",
       "      <td>CRIMINAL TRESPASS</td>\n",
       "      <td>PL 1401500</td>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "      <td>19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25-44</td>\n",
       "      <td>F</td>\n",
       "      <td>ASIAN / PACIFIC ISLANDER</td>\n",
       "      <td>996768.0</td>\n",
       "      <td>221152.0</td>\n",
       "      <td>40.773687</td>\n",
       "      <td>-73.954806</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     index  ARREST_KEY ARREST_DATE  PD_CD  \\\n",
       "48      48   173113521  12/31/2017  478.0   \n",
       "157    157   173117304  12/31/2017  109.0   \n",
       "197    197   173117789  12/31/2017  109.0   \n",
       "268    268   173120426  12/31/2017  439.0   \n",
       "939    939   173085784  12/29/2017  205.0   \n",
       "\n",
       "                                        PD_DESC  KY_CD  \\\n",
       "48              THEFT OF SERVICES, UNCLASSIFIED  343.0   \n",
       "157                    ASSAULT 2,1,UNCLASSIFIED  106.0   \n",
       "197                    ASSAULT 2,1,UNCLASSIFIED  106.0   \n",
       "268  LARCENY,GRAND FROM OPEN AREAS,UNCLASSIFIED  109.0   \n",
       "939                        TRESPASS 2, CRIMINAL  352.0   \n",
       "\n",
       "                           OFNS_DESC    LAW_CODE LAW_CAT_CD ARREST_BORO  \\\n",
       "48   OTHER OFFENSES RELATED TO THEFT  PL 1651503          M           M   \n",
       "157                   FELONY ASSAULT  PL 1200502          F           M   \n",
       "197                   FELONY ASSAULT  PL 1200502          F           M   \n",
       "268                    GRAND LARCENY  PL 1553001          F           M   \n",
       "939                CRIMINAL TRESPASS  PL 1401500          M           M   \n",
       "\n",
       "     ARREST_PRECINCT  JURISDICTION_CODE AGE_GROUP PERP_SEX  \\\n",
       "48                19                1.0     25-44        F   \n",
       "157               19                0.0     25-44        M   \n",
       "197               19                0.0     25-44        F   \n",
       "268               19                0.0     18-24        M   \n",
       "939               19                0.0     25-44        F   \n",
       "\n",
       "                    PERP_RACE  X_COORD_CD  Y_COORD_CD   Latitude  Longitude  \n",
       "48             WHITE HISPANIC    995369.0    221138.0  40.773650 -73.959857  \n",
       "157                     WHITE    994643.0    216998.0  40.762288 -73.962485  \n",
       "197                     WHITE    994643.0    216998.0  40.762288 -73.962485  \n",
       "268                     BLACK    992359.0    217816.0  40.764536 -73.970728  \n",
       "939  ASIAN / PACIFIC ISLANDER    996768.0    221152.0  40.773687 -73.954806  "
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities_not_found_1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(22568, 19)"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities_not_found_1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "cities_not_found_1 = pd.merge(cities_not_found_1,new_df, left_on=['index','Latitude','Longitude'],right_on=['org_index','Latitude','Longitude'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(22568, 22)"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities_not_found_1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['index', 'ARREST_KEY', 'ARREST_DATE', 'PD_CD', 'PD_DESC', 'KY_CD',\n",
       "       'OFNS_DESC', 'LAW_CODE', 'LAW_CAT_CD', 'ARREST_BORO', 'ARREST_PRECINCT',\n",
       "       'JURISDICTION_CODE', 'AGE_GROUP', 'PERP_SEX', 'PERP_RACE', 'X_COORD_CD',\n",
       "       'Y_COORD_CD', 'Latitude', 'Longitude', 'org_index', 'zipcode', 'city'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities_not_found_1.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "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>index</th>\n",
       "      <th>ARREST_KEY</th>\n",
       "      <th>ARREST_DATE</th>\n",
       "      <th>PD_CD</th>\n",
       "      <th>PD_DESC</th>\n",
       "      <th>KY_CD</th>\n",
       "      <th>OFNS_DESC</th>\n",
       "      <th>LAW_CODE</th>\n",
       "      <th>LAW_CAT_CD</th>\n",
       "      <th>ARREST_BORO</th>\n",
       "      <th>...</th>\n",
       "      <th>AGE_GROUP</th>\n",
       "      <th>PERP_SEX</th>\n",
       "      <th>PERP_RACE</th>\n",
       "      <th>X_COORD_CD</th>\n",
       "      <th>Y_COORD_CD</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>org_index</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>48</td>\n",
       "      <td>173113521</td>\n",
       "      <td>12/31/2017</td>\n",
       "      <td>478.0</td>\n",
       "      <td>THEFT OF SERVICES, UNCLASSIFIED</td>\n",
       "      <td>343.0</td>\n",
       "      <td>OTHER OFFENSES RELATED TO THEFT</td>\n",
       "      <td>PL 1651503</td>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>25-44</td>\n",
       "      <td>F</td>\n",
       "      <td>WHITE HISPANIC</td>\n",
       "      <td>995369.0</td>\n",
       "      <td>221138.0</td>\n",
       "      <td>40.773650</td>\n",
       "      <td>-73.959857</td>\n",
       "      <td>48.0</td>\n",
       "      <td>10075</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>157</td>\n",
       "      <td>173117304</td>\n",
       "      <td>12/31/2017</td>\n",
       "      <td>109.0</td>\n",
       "      <td>ASSAULT 2,1,UNCLASSIFIED</td>\n",
       "      <td>106.0</td>\n",
       "      <td>FELONY ASSAULT</td>\n",
       "      <td>PL 1200502</td>\n",
       "      <td>F</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>25-44</td>\n",
       "      <td>M</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>994643.0</td>\n",
       "      <td>216998.0</td>\n",
       "      <td>40.762288</td>\n",
       "      <td>-73.962485</td>\n",
       "      <td>157.0</td>\n",
       "      <td>10065</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>197</td>\n",
       "      <td>173117789</td>\n",
       "      <td>12/31/2017</td>\n",
       "      <td>109.0</td>\n",
       "      <td>ASSAULT 2,1,UNCLASSIFIED</td>\n",
       "      <td>106.0</td>\n",
       "      <td>FELONY ASSAULT</td>\n",
       "      <td>PL 1200502</td>\n",
       "      <td>F</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>25-44</td>\n",
       "      <td>F</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>994643.0</td>\n",
       "      <td>216998.0</td>\n",
       "      <td>40.762288</td>\n",
       "      <td>-73.962485</td>\n",
       "      <td>197.0</td>\n",
       "      <td>10065</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>268</td>\n",
       "      <td>173120426</td>\n",
       "      <td>12/31/2017</td>\n",
       "      <td>439.0</td>\n",
       "      <td>LARCENY,GRAND FROM OPEN AREAS,UNCLASSIFIED</td>\n",
       "      <td>109.0</td>\n",
       "      <td>GRAND LARCENY</td>\n",
       "      <td>PL 1553001</td>\n",
       "      <td>F</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>18-24</td>\n",
       "      <td>M</td>\n",
       "      <td>BLACK</td>\n",
       "      <td>992359.0</td>\n",
       "      <td>217816.0</td>\n",
       "      <td>40.764536</td>\n",
       "      <td>-73.970728</td>\n",
       "      <td>268.0</td>\n",
       "      <td>10065</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>939</td>\n",
       "      <td>173085784</td>\n",
       "      <td>12/29/2017</td>\n",
       "      <td>205.0</td>\n",
       "      <td>TRESPASS 2, CRIMINAL</td>\n",
       "      <td>352.0</td>\n",
       "      <td>CRIMINAL TRESPASS</td>\n",
       "      <td>PL 1401500</td>\n",
       "      <td>M</td>\n",
       "      <td>M</td>\n",
       "      <td>...</td>\n",
       "      <td>25-44</td>\n",
       "      <td>F</td>\n",
       "      <td>ASIAN / PACIFIC ISLANDER</td>\n",
       "      <td>996768.0</td>\n",
       "      <td>221152.0</td>\n",
       "      <td>40.773687</td>\n",
       "      <td>-73.954806</td>\n",
       "      <td>939.0</td>\n",
       "      <td>10075</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  ARREST_KEY ARREST_DATE  PD_CD  \\\n",
       "0     48   173113521  12/31/2017  478.0   \n",
       "1    157   173117304  12/31/2017  109.0   \n",
       "2    197   173117789  12/31/2017  109.0   \n",
       "3    268   173120426  12/31/2017  439.0   \n",
       "4    939   173085784  12/29/2017  205.0   \n",
       "\n",
       "                                      PD_DESC  KY_CD  \\\n",
       "0             THEFT OF SERVICES, UNCLASSIFIED  343.0   \n",
       "1                    ASSAULT 2,1,UNCLASSIFIED  106.0   \n",
       "2                    ASSAULT 2,1,UNCLASSIFIED  106.0   \n",
       "3  LARCENY,GRAND FROM OPEN AREAS,UNCLASSIFIED  109.0   \n",
       "4                        TRESPASS 2, CRIMINAL  352.0   \n",
       "\n",
       "                         OFNS_DESC    LAW_CODE LAW_CAT_CD ARREST_BORO  ...  \\\n",
       "0  OTHER OFFENSES RELATED TO THEFT  PL 1651503          M           M  ...   \n",
       "1                   FELONY ASSAULT  PL 1200502          F           M  ...   \n",
       "2                   FELONY ASSAULT  PL 1200502          F           M  ...   \n",
       "3                    GRAND LARCENY  PL 1553001          F           M  ...   \n",
       "4                CRIMINAL TRESPASS  PL 1401500          M           M  ...   \n",
       "\n",
       "   AGE_GROUP  PERP_SEX                 PERP_RACE X_COORD_CD Y_COORD_CD  \\\n",
       "0      25-44         F            WHITE HISPANIC   995369.0   221138.0   \n",
       "1      25-44         M                     WHITE   994643.0   216998.0   \n",
       "2      25-44         F                     WHITE   994643.0   216998.0   \n",
       "3      18-24         M                     BLACK   992359.0   217816.0   \n",
       "4      25-44         F  ASIAN / PACIFIC ISLANDER   996768.0   221152.0   \n",
       "\n",
       "    Latitude  Longitude  org_index  zipcode      city  \n",
       "0  40.773650 -73.959857       48.0    10075  New York  \n",
       "1  40.762288 -73.962485      157.0    10065  New York  \n",
       "2  40.762288 -73.962485      197.0    10065  New York  \n",
       "3  40.764536 -73.970728      268.0    10065  New York  \n",
       "4  40.773687 -73.954806      939.0    10075  New York  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities_not_found_1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4791859, 22)"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_mapped.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "cities_found = data_mapped[~data_mapped.city.isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4769291, 22)"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities_found.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4791859"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities_found.shape[0] + cities_not_found_1.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_mapped_final = pd.concat([cities_found,cities_not_found_1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((0, 22), (4791859, 22))"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_mapped_final[data_mapped_final.zipcode.isnull()].shape,data_mapped_final[~data_mapped_final.zipcode.isnull()].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((0, 22), (4791859, 22))"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_mapped_final[data_mapped_final.city.isnull()].shape,data_mapped_final[~data_mapped_final.city.isnull()].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_mapped_final.to_csv('~/data/master_mapped_data.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}