spatial_merge.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "from shapely.geometry import Point"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "events = pd.read_csv('ny_events_all.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Unnamed: 0</th>\n",
       "      <th>id</th>\n",
       "      <th>datetime_local</th>\n",
       "      <th>title</th>\n",
       "      <th>type</th>\n",
       "      <th>venue.id</th>\n",
       "      <th>venue.location.lat</th>\n",
       "      <th>venue.location.lon</th>\n",
       "      <th>venue.name</th>\n",
       "      <th>venue.postal_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>4421121</td>\n",
       "      <td>2018-11-08T10:00:00</td>\n",
       "      <td>Hop On Hop Off Sightseeing - New York</td>\n",
       "      <td>theater</td>\n",
       "      <td>446159</td>\n",
       "      <td>40.7050</td>\n",
       "      <td>-74.0038</td>\n",
       "      <td>Hornblower Pier 78 New York</td>\n",
       "      <td>10018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>4442708</td>\n",
       "      <td>2018-11-08T11:00:00</td>\n",
       "      <td>Big Apple Circus - New York</td>\n",
       "      <td>family</td>\n",
       "      <td>3900</td>\n",
       "      <td>40.7726</td>\n",
       "      <td>-73.9841</td>\n",
       "      <td>Damrosch Park</td>\n",
       "      <td>10023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>4421118</td>\n",
       "      <td>2018-11-08T12:00:00</td>\n",
       "      <td>Hop On Hop Off Sightseeing - New York</td>\n",
       "      <td>theater</td>\n",
       "      <td>446159</td>\n",
       "      <td>40.7050</td>\n",
       "      <td>-74.0038</td>\n",
       "      <td>Hornblower Pier 78 New York</td>\n",
       "      <td>10018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>4575383</td>\n",
       "      <td>2018-11-08T13:00:00</td>\n",
       "      <td>Fiddler on the Roof - New York</td>\n",
       "      <td>broadway_tickets_national</td>\n",
       "      <td>57361</td>\n",
       "      <td>40.7060</td>\n",
       "      <td>-74.0186</td>\n",
       "      <td>Jewish Museum</td>\n",
       "      <td>10280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>4371937</td>\n",
       "      <td>2018-11-08T14:00:00</td>\n",
       "      <td>The Phantom of the Opera - New York</td>\n",
       "      <td>broadway_tickets_national</td>\n",
       "      <td>2930</td>\n",
       "      <td>40.7584</td>\n",
       "      <td>-73.9882</td>\n",
       "      <td>Majestic Theatre</td>\n",
       "      <td>10036</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0       id       datetime_local  \\\n",
       "0           0  4421121  2018-11-08T10:00:00   \n",
       "1           1  4442708  2018-11-08T11:00:00   \n",
       "2           2  4421118  2018-11-08T12:00:00   \n",
       "3           3  4575383  2018-11-08T13:00:00   \n",
       "4           4  4371937  2018-11-08T14:00:00   \n",
       "\n",
       "                                   title                       type  venue.id  \\\n",
       "0  Hop On Hop Off Sightseeing - New York                    theater    446159   \n",
       "1            Big Apple Circus - New York                     family      3900   \n",
       "2  Hop On Hop Off Sightseeing - New York                    theater    446159   \n",
       "3         Fiddler on the Roof - New York  broadway_tickets_national     57361   \n",
       "4    The Phantom of the Opera - New York  broadway_tickets_national      2930   \n",
       "\n",
       "   venue.location.lat  venue.location.lon                   venue.name  \\\n",
       "0             40.7050            -74.0038  Hornblower Pier 78 New York   \n",
       "1             40.7726            -73.9841                Damrosch Park   \n",
       "2             40.7050            -74.0038  Hornblower Pier 78 New York   \n",
       "3             40.7060            -74.0186                Jewish Museum   \n",
       "4             40.7584            -73.9882             Majestic Theatre   \n",
       "\n",
       "   venue.postal_code  \n",
       "0              10018  \n",
       "1              10023  \n",
       "2              10018  \n",
       "3              10280  \n",
       "4              10036  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "events.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi = pd.read_csv('nyc_taxi_agg.csv')"
   ]
  },
  {
   "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>Unnamed: 0</th>\n",
       "      <th>PULocationID</th>\n",
       "      <th>DOLocationID</th>\n",
       "      <th>pickup_hour</th>\n",
       "      <th>dropoff_hour</th>\n",
       "      <th>pickup_dow</th>\n",
       "      <th>dropoff_dow</th>\n",
       "      <th>pickup_week</th>\n",
       "      <th>dropoff_week</th>\n",
       "      <th>sum</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>25</td>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>22</td>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>23</td>\n",
       "      <td>23</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>22</td>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  PULocationID  DOLocationID  pickup_hour  dropoff_hour  \\\n",
       "0           0             1             1            0             0   \n",
       "1           1             1             1            0             0   \n",
       "2           2             1             1            0             0   \n",
       "3           3             1             1            0             0   \n",
       "4           4             1             1            1             1   \n",
       "\n",
       "   pickup_dow  dropoff_dow  pickup_week  dropoff_week  sum  count  \n",
       "0           2            2           25            25    1      1  \n",
       "1           4            4           20            20    1      1  \n",
       "2           5            5           22            22    1      1  \n",
       "3           5            5           23            23    1      1  \n",
       "4           4            4           22            22    1      1  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "taxi.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([10018, 10023, 10280, 10036, 10019, 10014, 10038, 10002, 10003,\n",
       "       10009, 10012, 10016, 10010, 10001, 10011, 10021, 10022, 10020,\n",
       "       10007, 10128, 11238, 11377, 10024, 10031, 10026, 10027, 10025,\n",
       "       10033, 10034, 10013, 10065, 10004, 10017])"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "events['venue.postal_code'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Archive:  taxi_zones.zip\n",
      "  inflating: taxi_zones.prj          \n",
      "  inflating: taxi_zones.dbf          \n",
      "  inflating: taxi_zones.shp          \n",
      "  inflating: taxi_zones.shp.xml      \n",
      "  inflating: taxi_zones.shx          \n",
      "  inflating: taxi_zones.sbn          \n",
      "  inflating: taxi_zones.sbx          \n"
     ]
    }
   ],
   "source": [
    "!unzip taxi_zones.zip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_zones = gpd.read_file('taxi_zones.shp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>OBJECTID</th>\n",
       "      <th>Shape_Leng</th>\n",
       "      <th>Shape_Area</th>\n",
       "      <th>zone</th>\n",
       "      <th>LocationID</th>\n",
       "      <th>borough</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.116357</td>\n",
       "      <td>0.000782</td>\n",
       "      <td>Newark Airport</td>\n",
       "      <td>1</td>\n",
       "      <td>EWR</td>\n",
       "      <td>POLYGON ((933100.9183527103 192536.0856972019,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>0.433470</td>\n",
       "      <td>0.004866</td>\n",
       "      <td>Jamaica Bay</td>\n",
       "      <td>2</td>\n",
       "      <td>Queens</td>\n",
       "      <td>(POLYGON ((1033269.243591294 172126.0078125, 1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>0.084341</td>\n",
       "      <td>0.000314</td>\n",
       "      <td>Allerton/Pelham Gardens</td>\n",
       "      <td>3</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>POLYGON ((1026308.769506663 256767.6975403726,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>0.043567</td>\n",
       "      <td>0.000112</td>\n",
       "      <td>Alphabet City</td>\n",
       "      <td>4</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>POLYGON ((992073.4667968601 203714.0759887695,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>0.092146</td>\n",
       "      <td>0.000498</td>\n",
       "      <td>Arden Heights</td>\n",
       "      <td>5</td>\n",
       "      <td>Staten Island</td>\n",
       "      <td>POLYGON ((935843.3104932606 144283.335850656, ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   OBJECTID  Shape_Leng  Shape_Area                     zone  LocationID  \\\n",
       "0         1    0.116357    0.000782           Newark Airport           1   \n",
       "1         2    0.433470    0.004866              Jamaica Bay           2   \n",
       "2         3    0.084341    0.000314  Allerton/Pelham Gardens           3   \n",
       "3         4    0.043567    0.000112            Alphabet City           4   \n",
       "4         5    0.092146    0.000498            Arden Heights           5   \n",
       "\n",
       "         borough                                           geometry  \n",
       "0            EWR  POLYGON ((933100.9183527103 192536.0856972019,...  \n",
       "1         Queens  (POLYGON ((1033269.243591294 172126.0078125, 1...  \n",
       "2          Bronx  POLYGON ((1026308.769506663 256767.6975403726,...  \n",
       "3      Manhattan  POLYGON ((992073.4667968601 203714.0759887695,...  \n",
       "4  Staten Island  POLYGON ((935843.3104932606 144283.335850656, ...  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "taxi_zones.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'proj': 'lcc',\n",
       " 'lat_1': 40.66666666666666,\n",
       " 'lat_2': 41.03333333333333,\n",
       " 'lat_0': 40.16666666666666,\n",
       " 'lon_0': -74,\n",
       " 'x_0': 300000,\n",
       " 'y_0': 0,\n",
       " 'datum': 'NAD83',\n",
       " 'units': 'us-ft',\n",
       " 'no_defs': True}"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "taxi_zones.crs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_zones.to_crs(epsg=4326,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "geometry = [Point(xy) for xy in zip(events['venue.location.lon'], events['venue.location.lat'])]\n",
    "crs = {'init': 'epsg:4326'}\n",
    "events_geo = gpd.GeoDataFrame(events, crs=crs, geometry=geometry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/nfshome/cb4102/.conda/envs/geoplot/lib/python3.7/site-packages/geopandas/tools/sjoin.py:44: UserWarning: CRS of frames being joined does not match!\n",
      "  warn('CRS of frames being joined does not match!')\n"
     ]
    }
   ],
   "source": [
    "events_geo_taxi = gpd.sjoin(events_geo,taxi_zones,op='intersects')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Manhattan', 'Brooklyn', 'Queens'], dtype=object)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "events_geo_taxi['borough'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "events_geo_taxi.drop(['index_right','OBJECTID','Shape_Leng','Shape_Area','zone'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "events_geo_taxi.to_csv('events_geo_taxi.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_zones_df = pd.DataFrame(taxi_zones)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_agg_geo = pd.merge(taxi,taxi_zones_df,how='left',left_on=['PULocationID'],right_on='LocationID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_agg_geo.drop(['OBJECTID','Shape_Leng','Shape_Area','zone','LocationID'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_agg_geo.rename(columns={'geometry':'pickup_geo'},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_agg_geo = pd.merge(taxi_agg_geo,taxi_zones_df,how='left',left_on='DOLocationID',right_on='LocationID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_agg_geo.drop(['OBJECTID','Shape_Leng','Shape_Area','zone','LocationID'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_agg_geo.rename(columns={'geometry':'dropoff_geo'},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_agg_geo.to_csv('taxi_agg_geo.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "geoplot",
   "language": "python",
   "name": "geoplot"
  },
  "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}