address_to_lat_long.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Converting address to latitude and longitude"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests\n",
    "import geocoder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(30347, 8)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>RecordID</th>\n",
       "      <th>Offense</th>\n",
       "      <th>IncidentID</th>\n",
       "      <th>BlockNumber</th>\n",
       "      <th>StreetName</th>\n",
       "      <th>Agency</th>\n",
       "      <th>DateReported</th>\n",
       "      <th>HourReported</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Burglary</td>\n",
       "      <td>201000073238</td>\n",
       "      <td>1700.0</td>\n",
       "      <td>ALLIED LN</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2017-09-22T16:00:00.000Z</td>\n",
       "      <td>1200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Drug/Narcotics Violation</td>\n",
       "      <td>201300002276</td>\n",
       "      <td>1200.0</td>\n",
       "      <td>LANDONIA CIR</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2013-12-13T16:03:00.000Z</td>\n",
       "      <td>1103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Drug/Narcotics Violation</td>\n",
       "      <td>201300002481</td>\n",
       "      <td>500.0</td>\n",
       "      <td>WATER ST</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2014-01-02T19:00:00.000Z</td>\n",
       "      <td>1400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Drug/Narcotics Violation</td>\n",
       "      <td>201300003848</td>\n",
       "      <td>100.0</td>\n",
       "      <td>DICE ST</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2014-02-18T23:30:00.000Z</td>\n",
       "      <td>1830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Drug/Narcotics Violation</td>\n",
       "      <td>201300004323</td>\n",
       "      <td>1800.0</td>\n",
       "      <td>HYDRAULIC RD</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2013-12-27T19:00:00.000Z</td>\n",
       "      <td>1400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   RecordID                   Offense    IncidentID  BlockNumber  \\\n",
       "0         1                  Burglary  201000073238       1700.0   \n",
       "1         2  Drug/Narcotics Violation  201300002276       1200.0   \n",
       "2         3  Drug/Narcotics Violation  201300002481        500.0   \n",
       "3         4  Drug/Narcotics Violation  201300003848        100.0   \n",
       "4         5  Drug/Narcotics Violation  201300004323       1800.0   \n",
       "\n",
       "     StreetName Agency              DateReported  HourReported  \n",
       "0     ALLIED LN    CPD  2017-09-22T16:00:00.000Z          1200  \n",
       "1  LANDONIA CIR    CPD  2013-12-13T16:03:00.000Z          1103  \n",
       "2      WATER ST    CPD  2014-01-02T19:00:00.000Z          1400  \n",
       "3       DICE ST    CPD  2014-02-18T23:30:00.000Z          1830  \n",
       "4  HYDRAULIC RD    CPD  2013-12-27T19:00:00.000Z          1400  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# import dataset\n",
    "crime = pd.read_csv(r'C:\\Users\\Elena\\Desktop\\Spring 19\\Capstone\\Crime_Data.csv')\n",
    "print(crime.shape)\n",
    "crime.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RecordID          0\n",
       "Offense           0\n",
       "IncidentID        0\n",
       "BlockNumber     406\n",
       "StreetName        2\n",
       "Agency            0\n",
       "DateReported      0\n",
       "HourReported      0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# count number of missing values\n",
    "crime.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1700., 1200.,  500.,  100., 1800., 2100., 1100.,  700.,  600.,\n",
       "         nan,  800., 1400.,  400.,  900., 2300., 2000., 1000., 1500.,\n",
       "        300.,  200., 1600., 2400., 1300., 2500., 1900., 2200., 2700.,\n",
       "       2600., 4000., 6200., 3000., 2800., 9100., 5300., 3100., 8000.,\n",
       "       3400., 7200., 2900., 4400., 3300.,    0., 3800.])"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# distribution of block numbers\n",
    "crime.BlockNumber.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>StreetName</th>\n",
       "      <th>BlockNumber</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10 1/2 ST NW</td>\n",
       "      <td>300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10TH ST</td>\n",
       "      <td>400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10TH ST NE</td>\n",
       "      <td>300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10TH ST NW</td>\n",
       "      <td>300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10TH ST NW / ANDERSON ST</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 StreetName  BlockNumber\n",
       "0              10 1/2 ST NW        300.0\n",
       "1                   10TH ST        400.0\n",
       "2                10TH ST NE        300.0\n",
       "3                10TH ST NW        300.0\n",
       "4  10TH ST NW / ANDERSON ST          NaN"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crime_med = crime.groupby('StreetName', as_index = False)['BlockNumber'].median()\n",
    "crime_med.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "StreetName\n",
       "10 1/2 ST NW                 50\n",
       "10TH ST                       4\n",
       "10TH ST NE                   14\n",
       "10TH ST NW                  167\n",
       "10TH ST NW / ANDERSON ST      1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crime_count = crime.groupby('StreetName', as_index = False)['BlockNumber'].size()\n",
    "crime_count.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11          6TH ST SE\n",
       "173       WERTLAND ST\n",
       "357    UNIVERSITY CIR\n",
       "404     UNIVERSITY CT\n",
       "538     UNIVERSITY CT\n",
       "Name: StreetName, dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crime_na = crime.StreetName[crime['BlockNumber'].isnull()]\n",
    "crime_na.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# if values are missing, fill them with empty 1\n",
    "crime['BlockNumber'] = crime['BlockNumber'].fillna(0)\n",
    "crime['BlockNumber'].isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     1700\n",
       "1     1200\n",
       "2      500\n",
       "3      100\n",
       "4     1800\n",
       "5      100\n",
       "6     2100\n",
       "7      100\n",
       "8     1100\n",
       "9      700\n",
       "10     600\n",
       "11       0\n",
       "12     700\n",
       "13     700\n",
       "14     800\n",
       "Name: BlockNumber, dtype: int32"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crime['BlockNumber'] = pd.to_numeric(crime['BlockNumber']).astype(int)\n",
    "crime['BlockNumber'].head(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>RecordID</th>\n",
       "      <th>Offense</th>\n",
       "      <th>IncidentID</th>\n",
       "      <th>BlockNumber</th>\n",
       "      <th>StreetName</th>\n",
       "      <th>Agency</th>\n",
       "      <th>DateReported</th>\n",
       "      <th>HourReported</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Burglary</td>\n",
       "      <td>201000073238</td>\n",
       "      <td>1700</td>\n",
       "      <td>ALLIED LN</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2017-09-22T16:00:00.000Z</td>\n",
       "      <td>1200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Drug/Narcotics Violation</td>\n",
       "      <td>201300002276</td>\n",
       "      <td>1200</td>\n",
       "      <td>LANDONIA CIR</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2013-12-13T16:03:00.000Z</td>\n",
       "      <td>1103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Drug/Narcotics Violation</td>\n",
       "      <td>201300002481</td>\n",
       "      <td>500</td>\n",
       "      <td>WATER ST</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2014-01-02T19:00:00.000Z</td>\n",
       "      <td>1400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Drug/Narcotics Violation</td>\n",
       "      <td>201300003848</td>\n",
       "      <td>100</td>\n",
       "      <td>DICE ST</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2014-02-18T23:30:00.000Z</td>\n",
       "      <td>1830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Drug/Narcotics Violation</td>\n",
       "      <td>201300004323</td>\n",
       "      <td>1800</td>\n",
       "      <td>HYDRAULIC RD</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2013-12-27T19:00:00.000Z</td>\n",
       "      <td>1400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   RecordID                   Offense    IncidentID  BlockNumber  \\\n",
       "0         1                  Burglary  201000073238         1700   \n",
       "1         2  Drug/Narcotics Violation  201300002276         1200   \n",
       "2         3  Drug/Narcotics Violation  201300002481          500   \n",
       "3         4  Drug/Narcotics Violation  201300003848          100   \n",
       "4         5  Drug/Narcotics Violation  201300004323         1800   \n",
       "\n",
       "     StreetName Agency              DateReported  HourReported  \n",
       "0     ALLIED LN    CPD  2017-09-22T16:00:00.000Z          1200  \n",
       "1  LANDONIA CIR    CPD  2013-12-13T16:03:00.000Z          1103  \n",
       "2      WATER ST    CPD  2014-01-02T19:00:00.000Z          1400  \n",
       "3       DICE ST    CPD  2014-02-18T23:30:00.000Z          1830  \n",
       "4  HYDRAULIC RD    CPD  2013-12-27T19:00:00.000Z          1400  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crime.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "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>RecordID</th>\n",
       "      <th>Offense</th>\n",
       "      <th>IncidentID</th>\n",
       "      <th>BlockNumber</th>\n",
       "      <th>StreetName</th>\n",
       "      <th>Agency</th>\n",
       "      <th>DateReported</th>\n",
       "      <th>HourReported</th>\n",
       "      <th>Address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Burglary</td>\n",
       "      <td>201000073238</td>\n",
       "      <td>1700</td>\n",
       "      <td>ALLIED LN</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2017-09-22T16:00:00.000Z</td>\n",
       "      <td>1200</td>\n",
       "      <td>1700 ALLIED LN, Charlottesville VA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Drug/Narcotics Violation</td>\n",
       "      <td>201300002276</td>\n",
       "      <td>1200</td>\n",
       "      <td>LANDONIA CIR</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2013-12-13T16:03:00.000Z</td>\n",
       "      <td>1103</td>\n",
       "      <td>1200 LANDONIA CIR, Charlottesville VA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Drug/Narcotics Violation</td>\n",
       "      <td>201300002481</td>\n",
       "      <td>500</td>\n",
       "      <td>WATER ST</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2014-01-02T19:00:00.000Z</td>\n",
       "      <td>1400</td>\n",
       "      <td>500 WATER ST, Charlottesville VA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Drug/Narcotics Violation</td>\n",
       "      <td>201300003848</td>\n",
       "      <td>100</td>\n",
       "      <td>DICE ST</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2014-02-18T23:30:00.000Z</td>\n",
       "      <td>1830</td>\n",
       "      <td>100 DICE ST, Charlottesville VA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Drug/Narcotics Violation</td>\n",
       "      <td>201300004323</td>\n",
       "      <td>1800</td>\n",
       "      <td>HYDRAULIC RD</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2013-12-27T19:00:00.000Z</td>\n",
       "      <td>1400</td>\n",
       "      <td>1800 HYDRAULIC RD, Charlottesville VA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   RecordID                   Offense    IncidentID  BlockNumber  \\\n",
       "0         1                  Burglary  201000073238         1700   \n",
       "1         2  Drug/Narcotics Violation  201300002276         1200   \n",
       "2         3  Drug/Narcotics Violation  201300002481          500   \n",
       "3         4  Drug/Narcotics Violation  201300003848          100   \n",
       "4         5  Drug/Narcotics Violation  201300004323         1800   \n",
       "\n",
       "     StreetName Agency              DateReported  HourReported  \\\n",
       "0     ALLIED LN    CPD  2017-09-22T16:00:00.000Z          1200   \n",
       "1  LANDONIA CIR    CPD  2013-12-13T16:03:00.000Z          1103   \n",
       "2      WATER ST    CPD  2014-01-02T19:00:00.000Z          1400   \n",
       "3       DICE ST    CPD  2014-02-18T23:30:00.000Z          1830   \n",
       "4  HYDRAULIC RD    CPD  2013-12-27T19:00:00.000Z          1400   \n",
       "\n",
       "                                 Address  \n",
       "0     1700 ALLIED LN, Charlottesville VA  \n",
       "1  1200 LANDONIA CIR, Charlottesville VA  \n",
       "2       500 WATER ST, Charlottesville VA  \n",
       "3        100 DICE ST, Charlottesville VA  \n",
       "4  1800 HYDRAULIC RD, Charlottesville VA  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crime['Address'] = crime.BlockNumber.map(str) + \" \" + crime.StreetName + \", Charlottesville VA\"\n",
    "crime.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 276,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'http://www.datasciencetoolkit.org/street2coordinates/1200 LANDONIA CIR, Charlottesville VA'"
      ]
     },
     "execution_count": 276,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "url = pd.Series([])\n",
    "for i in range(len(crime)):\n",
    "    url[i] = \"http://www.datasciencetoolkit.org/street2coordinates/\" + str(crime['Address'].iloc[i])\n",
    "url[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 277,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "b'{\\n  \"1200 LANDONIA CIR, Charlottesville VA\": {\\n    \"country_code3\": \"USA\",\\n    \"latitude\": 38.036315,\\n    \"country_name\": \"United States\",\\n    \"longitude\": -78.462432,\\n    \"street_address\": \"1200 Landonia Cir\",\\n    \"region\": \"VA\",\\n    \"confidence\": 0.902,\\n    \"street_number\": \"1200\",\\n    \"locality\": \"Charlottesville\",\\n    \"street_name\": \"Landonia Cir\",\\n    \"fips_county\": \"51540\",\\n    \"country_code\": \"US\"\\n  }\\n}'"
      ]
     },
     "execution_count": 277,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r = pd.Series([])\n",
    "for i in range(len(url)):\n",
    "    r[i] = requests.get(url[i])\n",
    "r[1].content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 282,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'38.034974'"
      ]
     },
     "execution_count": 282,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "re.search(r\".*latitude\\\": *(.*?) *,\\n*\", str(r[10].content)).group(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 284,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "38.036315\n",
      "-78.462432\n"
     ]
    }
   ],
   "source": [
    "lat = pd.Series([])\n",
    "lng = pd.Series([])\n",
    "\n",
    "for i in range(len(r)):\n",
    "    x = re.search(r\".*latitude\\\": *(.*?) *,\\n*\", str(r[i].content))\n",
    "    if x is not None:\n",
    "        lat[i] = x.group(1)\n",
    "    else:\n",
    "        lat[i] = 0\n",
    "    \n",
    "    y = re.search(r\".*longitude\\\": *(.*?) *,\\n*\", str(r[i].content))\n",
    "    if y is not None:\n",
    "        lng[i] = y.group(1)\n",
    "    else:\n",
    "        lng[i] = 0 \n",
    "      \n",
    "print(lat[1])\n",
    "print(lng[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 287,
   "metadata": {
    "scrolled": true
   },
   "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>RecordID</th>\n",
       "      <th>Offense</th>\n",
       "      <th>IncidentID</th>\n",
       "      <th>BlockNumber</th>\n",
       "      <th>StreetName</th>\n",
       "      <th>Agency</th>\n",
       "      <th>DateReported</th>\n",
       "      <th>HourReported</th>\n",
       "      <th>Address</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Burglary</td>\n",
       "      <td>201000073238</td>\n",
       "      <td>1700</td>\n",
       "      <td>ALLIED LN</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2017-09-22T16:00:00.000Z</td>\n",
       "      <td>1200</td>\n",
       "      <td>1700 ALLIED LN, Charlottesville VA</td>\n",
       "      <td>38.041381</td>\n",
       "      <td>-78.479806</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Drug/Narcotics Violation</td>\n",
       "      <td>201300002276</td>\n",
       "      <td>1200</td>\n",
       "      <td>LANDONIA CIR</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2013-12-13T16:03:00.000Z</td>\n",
       "      <td>1103</td>\n",
       "      <td>1200 LANDONIA CIR, Charlottesville VA</td>\n",
       "      <td>38.036315</td>\n",
       "      <td>-78.462432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Drug/Narcotics Violation</td>\n",
       "      <td>201300002481</td>\n",
       "      <td>500</td>\n",
       "      <td>WATER ST</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2014-01-02T19:00:00.000Z</td>\n",
       "      <td>1400</td>\n",
       "      <td>500 WATER ST, Charlottesville VA</td>\n",
       "      <td>38.029081</td>\n",
       "      <td>-78.478864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Drug/Narcotics Violation</td>\n",
       "      <td>201300003848</td>\n",
       "      <td>100</td>\n",
       "      <td>DICE ST</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2014-02-18T23:30:00.000Z</td>\n",
       "      <td>1830</td>\n",
       "      <td>100 DICE ST, Charlottesville VA</td>\n",
       "      <td>38.027426</td>\n",
       "      <td>-78.483989</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Drug/Narcotics Violation</td>\n",
       "      <td>201300004323</td>\n",
       "      <td>1800</td>\n",
       "      <td>HYDRAULIC RD</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2013-12-27T19:00:00.000Z</td>\n",
       "      <td>1400</td>\n",
       "      <td>1800 HYDRAULIC RD, Charlottesville VA</td>\n",
       "      <td>38.060175</td>\n",
       "      <td>-78.490386</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   RecordID                   Offense    IncidentID  BlockNumber  \\\n",
       "0         1                  Burglary  201000073238         1700   \n",
       "1         2  Drug/Narcotics Violation  201300002276         1200   \n",
       "2         3  Drug/Narcotics Violation  201300002481          500   \n",
       "3         4  Drug/Narcotics Violation  201300003848          100   \n",
       "4         5  Drug/Narcotics Violation  201300004323         1800   \n",
       "\n",
       "     StreetName Agency              DateReported  HourReported  \\\n",
       "0     ALLIED LN    CPD  2017-09-22T16:00:00.000Z          1200   \n",
       "1  LANDONIA CIR    CPD  2013-12-13T16:03:00.000Z          1103   \n",
       "2      WATER ST    CPD  2014-01-02T19:00:00.000Z          1400   \n",
       "3       DICE ST    CPD  2014-02-18T23:30:00.000Z          1830   \n",
       "4  HYDRAULIC RD    CPD  2013-12-27T19:00:00.000Z          1400   \n",
       "\n",
       "                                 Address   Latitude   Longitude  \n",
       "0     1700 ALLIED LN, Charlottesville VA  38.041381  -78.479806  \n",
       "1  1200 LANDONIA CIR, Charlottesville VA  38.036315  -78.462432  \n",
       "2       500 WATER ST, Charlottesville VA  38.029081  -78.478864  \n",
       "3        100 DICE ST, Charlottesville VA  38.027426  -78.483989  \n",
       "4  1800 HYDRAULIC RD, Charlottesville VA  38.060175  -78.490386  "
      ]
     },
     "execution_count": 287,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crime['Latitude'] = lat\n",
    "crime['Longitude'] = lng\n",
    "crime.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 289,
   "metadata": {},
   "outputs": [],
   "source": [
    "crime.to_csv('crime_ll.csv', sep=',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import sys\n",
    "# import time\n",
    "# import urllib.request\n",
    "# import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "metadata": {},
   "outputs": [],
   "source": [
    "# def addresstoll(df):\n",
    "    \n",
    "#     # Create empty lists for lats and lngs\n",
    "#     lat_list = pd.Series([])\n",
    "#     lng_list = pd.Series([])\n",
    "   \n",
    "#     # Create opener\n",
    "#     opener = urllib.request.FancyURLopener({})\n",
    "    \n",
    "#     # Iterate over all rows of dataframe\n",
    "#     for i in range(len(df)):\n",
    "        \n",
    "#         # Get latitude and longitude\n",
    "#         block = df['BlockNumber'][i]\n",
    "#         street = df['StreetName'][i]\n",
    "        \n",
    "#         # Create url from lat/long\n",
    "#         url = 'http://www.datasciencetoolkit.org/street2coordinates/'\\\n",
    "#         + str(block) + '+' + str(street) + '+' + 'Charlottesville+VA'\n",
    "        \n",
    "#         # Store webpage contents\n",
    "#         f = opener.open(url)\n",
    "#         content = f.read()\n",
    "        \n",
    "#         # Get lat and long out of content string\n",
    "#         lat = re.search(r\".*latitude\\\": *(.*?) *,\\n*\", str(content)).group(1)\n",
    "#         lng = re.search(r\".*longitude\\\": *(.*?) *,\\n*\", str(content)).group(1)\n",
    "        \n",
    "#         # Add to the lists\n",
    "#         lat_list[i] = lat\n",
    "#         lng_list[i] = lng\n",
    "               \n",
    "#     return lat_list, lng_list\n",
    "\n",
    "# #       sys.stdout.write('\\r' + str(lat) +', '+ str(lng))\n",
    "# #       time.sleep(0.1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Elena\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:8: DeprecationWarning: FancyURLopener style of invoking requests is deprecated. Use newer urlopen functions/methods\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "# crime_lats, crime_lngs = addresstoll(crime[:5])\n",
    "# crime['Lats'] = crime_lats\n",
    "# crime['Lngs'] = crime_lngs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# crime_lats, crime_lngs = addresstoll(crime[:5])\n",
    "# crime['Lats'] = crime_lats\n",
    "# crime['Lngs'] = crime_lngs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 274,
   "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>RecordID</th>\n",
       "      <th>Offense</th>\n",
       "      <th>IncidentID</th>\n",
       "      <th>BlockNumber</th>\n",
       "      <th>StreetName</th>\n",
       "      <th>Agency</th>\n",
       "      <th>DateReported</th>\n",
       "      <th>HourReported</th>\n",
       "      <th>Address</th>\n",
       "      <th>Lats</th>\n",
       "      <th>Lngs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Burglary</td>\n",
       "      <td>201000073238</td>\n",
       "      <td>1700</td>\n",
       "      <td>ALLIED LN</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2017-09-22T16:00:00.000Z</td>\n",
       "      <td>1200</td>\n",
       "      <td>1700 ALLIED LN, Charlottesville VA</td>\n",
       "      <td>38.041381</td>\n",
       "      <td>-78.479806</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Drug/Narcotics Violation</td>\n",
       "      <td>201300002276</td>\n",
       "      <td>1200</td>\n",
       "      <td>LANDONIA CIR</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2013-12-13T16:03:00.000Z</td>\n",
       "      <td>1103</td>\n",
       "      <td>1200 LANDONIA CIR, Charlottesville VA</td>\n",
       "      <td>38.036315</td>\n",
       "      <td>-78.462432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Drug/Narcotics Violation</td>\n",
       "      <td>201300002481</td>\n",
       "      <td>500</td>\n",
       "      <td>WATER ST</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2014-01-02T19:00:00.000Z</td>\n",
       "      <td>1400</td>\n",
       "      <td>500 WATER ST, Charlottesville VA</td>\n",
       "      <td>38.029081</td>\n",
       "      <td>-78.478864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Drug/Narcotics Violation</td>\n",
       "      <td>201300003848</td>\n",
       "      <td>100</td>\n",
       "      <td>DICE ST</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2014-02-18T23:30:00.000Z</td>\n",
       "      <td>1830</td>\n",
       "      <td>100 DICE ST, Charlottesville VA</td>\n",
       "      <td>38.027426</td>\n",
       "      <td>-78.483989</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Drug/Narcotics Violation</td>\n",
       "      <td>201300004323</td>\n",
       "      <td>1800</td>\n",
       "      <td>HYDRAULIC RD</td>\n",
       "      <td>CPD</td>\n",
       "      <td>2013-12-27T19:00:00.000Z</td>\n",
       "      <td>1400</td>\n",
       "      <td>1800 HYDRAULIC RD, Charlottesville VA</td>\n",
       "      <td>38.060175</td>\n",
       "      <td>-78.490386</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   RecordID                   Offense    IncidentID  BlockNumber  \\\n",
       "0         1                  Burglary  201000073238         1700   \n",
       "1         2  Drug/Narcotics Violation  201300002276         1200   \n",
       "2         3  Drug/Narcotics Violation  201300002481          500   \n",
       "3         4  Drug/Narcotics Violation  201300003848          100   \n",
       "4         5  Drug/Narcotics Violation  201300004323         1800   \n",
       "\n",
       "     StreetName Agency              DateReported  HourReported  \\\n",
       "0     ALLIED LN    CPD  2017-09-22T16:00:00.000Z          1200   \n",
       "1  LANDONIA CIR    CPD  2013-12-13T16:03:00.000Z          1103   \n",
       "2      WATER ST    CPD  2014-01-02T19:00:00.000Z          1400   \n",
       "3       DICE ST    CPD  2014-02-18T23:30:00.000Z          1830   \n",
       "4  HYDRAULIC RD    CPD  2013-12-27T19:00:00.000Z          1400   \n",
       "\n",
       "                                 Address       Lats        Lngs  \n",
       "0     1700 ALLIED LN, Charlottesville VA  38.041381  -78.479806  \n",
       "1  1200 LANDONIA CIR, Charlottesville VA  38.036315  -78.462432  \n",
       "2       500 WATER ST, Charlottesville VA  38.029081  -78.478864  \n",
       "3        100 DICE ST, Charlottesville VA  38.027426  -78.483989  \n",
       "4  1800 HYDRAULIC RD, Charlottesville VA  38.060175  -78.490386  "
      ]
     },
     "execution_count": 274,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# crime.head()"
   ]
  }
 ],
 "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}