02_bart_caltrain_locations.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# BART / Caltrain Location Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime as dt\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy import inspect\n",
    "cnx = create_engine('postgresql://matthewmurray@52.53.245.4/policedata')\n",
    "import geopandas\n",
    "import geopandas.tools\n",
    "from shapely.geometry import Point\n",
    "import pickle\n",
    "import requests\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Collect & Process Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "api_key = ''\n",
    "bart_url = 'http://api.bart.gov/api/stn.aspx?cmd=stns&orig=ssan&key={}&json=y'.format(api_key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "r = requests.get(bart_url)\n",
    "json_doc = r.json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>station_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>37.779732</td>\n",
       "      <td>-122.414123</td>\n",
       "      <td>Civic Center/UN Plaza</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>37.733064</td>\n",
       "      <td>-122.433817</td>\n",
       "      <td>Glen Park</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>37.752470</td>\n",
       "      <td>-122.418143</td>\n",
       "      <td>24th St. Mission</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     latitude    longitude           station_name\n",
       "8   37.779732  -122.414123  Civic Center/UN Plaza\n",
       "20  37.733064  -122.433817              Glen Park\n",
       "3   37.752470  -122.418143       24th St. Mission"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bart_df = pd.DataFrame.from_dict(json_doc['root']['stations']['station'])\n",
    "bart_df = bart_df[bart_df['county'] == 'sanfrancisco']\n",
    "bart_df.drop(['abbr','address','city','county','state','zipcode'], axis=1, inplace=True)\n",
    "bart_df.rename(columns={'gtfs_latitude':'latitude','gtfs_longitude':'longitude','name':'station_name'}, inplace=True)\n",
    "bart_df.sample(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "There aren't many Caltrain stations so I just manually collected the latitude and longitude information from Wikipedia"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "caltrains = {\n",
    "    'South San Francisco':[37.655833, -122.405],\n",
    "    'Bayshore Station':[37.7075, -122.401944],\n",
    "    '22nd St. Station':[37.757222, -122.3925],\n",
    "    'San Francisco':[37.776389, -122.394444]\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "cal_df = pd.DataFrame.from_dict(caltrains, orient='index')\n",
    "cal_df = cal_df.reset_index()\n",
    "cal_df.columns = ['station_name','latitude','longitude']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>station_name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>South San Francisco</td>\n",
       "      <td>37.655833</td>\n",
       "      <td>-122.405000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bayshore Station</td>\n",
       "      <td>37.707500</td>\n",
       "      <td>-122.401944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>22nd St. Station</td>\n",
       "      <td>37.757222</td>\n",
       "      <td>-122.392500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>37.776389</td>\n",
       "      <td>-122.394444</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          station_name   latitude   longitude\n",
       "0  South San Francisco  37.655833 -122.405000\n",
       "1     Bayshore Station  37.707500 -122.401944\n",
       "2     22nd St. Station  37.757222 -122.392500\n",
       "3        San Francisco  37.776389 -122.394444"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cal_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>station_name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>South San Francisco</td>\n",
       "      <td>37.655833</td>\n",
       "      <td>-122.405000</td>\n",
       "      <td>POINT (-122.405 37.655833)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bayshore Station</td>\n",
       "      <td>37.707500</td>\n",
       "      <td>-122.401944</td>\n",
       "      <td>POINT (-122.401944 37.7075)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>22nd St. Station</td>\n",
       "      <td>37.757222</td>\n",
       "      <td>-122.392500</td>\n",
       "      <td>POINT (-122.3925 37.757222)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>37.776389</td>\n",
       "      <td>-122.394444</td>\n",
       "      <td>POINT (-122.394444 37.776389)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16th St. Mission</td>\n",
       "      <td>37.765062</td>\n",
       "      <td>-122.419694</td>\n",
       "      <td>POINT (-122.419694 37.765062)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>24th St. Mission</td>\n",
       "      <td>37.752470</td>\n",
       "      <td>-122.418143</td>\n",
       "      <td>POINT (-122.418143 37.75247)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Balboa Park</td>\n",
       "      <td>37.721585</td>\n",
       "      <td>-122.447506</td>\n",
       "      <td>POINT (-122.447506 37.721585)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Civic Center/UN Plaza</td>\n",
       "      <td>37.779732</td>\n",
       "      <td>-122.414123</td>\n",
       "      <td>POINT (-122.414123 37.779732)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Embarcadero</td>\n",
       "      <td>37.792874</td>\n",
       "      <td>-122.397020</td>\n",
       "      <td>POINT (-122.39702 37.792874)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Glen Park</td>\n",
       "      <td>37.733064</td>\n",
       "      <td>-122.433817</td>\n",
       "      <td>POINT (-122.433817 37.733064)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Montgomery St.</td>\n",
       "      <td>37.789405</td>\n",
       "      <td>-122.401066</td>\n",
       "      <td>POINT (-122.401066 37.789405)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Powell St.</td>\n",
       "      <td>37.784471</td>\n",
       "      <td>-122.407974</td>\n",
       "      <td>POINT (-122.407974 37.784471)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             station_name   latitude   longitude  \\\n",
       "0     South San Francisco  37.655833 -122.405000   \n",
       "1        Bayshore Station  37.707500 -122.401944   \n",
       "2        22nd St. Station  37.757222 -122.392500   \n",
       "3           San Francisco  37.776389 -122.394444   \n",
       "4        16th St. Mission  37.765062 -122.419694   \n",
       "5        24th St. Mission  37.752470 -122.418143   \n",
       "6             Balboa Park  37.721585 -122.447506   \n",
       "7   Civic Center/UN Plaza  37.779732 -122.414123   \n",
       "8             Embarcadero  37.792874 -122.397020   \n",
       "9               Glen Park  37.733064 -122.433817   \n",
       "10         Montgomery St.  37.789405 -122.401066   \n",
       "11             Powell St.  37.784471 -122.407974   \n",
       "\n",
       "                         geometry  \n",
       "0      POINT (-122.405 37.655833)  \n",
       "1     POINT (-122.401944 37.7075)  \n",
       "2     POINT (-122.3925 37.757222)  \n",
       "3   POINT (-122.394444 37.776389)  \n",
       "4   POINT (-122.419694 37.765062)  \n",
       "5    POINT (-122.418143 37.75247)  \n",
       "6   POINT (-122.447506 37.721585)  \n",
       "7   POINT (-122.414123 37.779732)  \n",
       "8    POINT (-122.39702 37.792874)  \n",
       "9   POINT (-122.433817 37.733064)  \n",
       "10  POINT (-122.401066 37.789405)  \n",
       "11  POINT (-122.407974 37.784471)  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transport_df = pd.concat([cal_df, bart_df])\n",
    "transport_df['latitude'] = transport_df['latitude'].apply(lambda x: float(x))\n",
    "transport_df['longitude'] = transport_df['longitude'].apply(lambda x: float(x))\n",
    "transport_df['geometry'] = transport_df.apply(lambda row: Point(row['longitude'], row['latitude']), axis=1)\n",
    "transport_df = transport_df.set_index('station_name').reset_index()\n",
    "transport_df = geopandas.GeoDataFrame(transport_df, geometry='geometry')\n",
    "transport_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "I intend to use these coordinates to calculate - for each crime incident - its distance from the nearest station."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "station_locations = transport_df['geometry']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "pickle.dump(station_locations, open('station_locations.pkl', 'wb'))"
   ]
  }
 ],
 "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}