ArcPrepEvents.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import geopandas as gpd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "events_agg = pd.read_csv('events_geo_taxi.csv') #load events data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Unnamed: 0.1</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",
       "      <th>geometry</th>\n",
       "      <th>LocationID</th>\n",
       "      <th>borough</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\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",
       "      <td>POINT (-73.9841 40.7726)</td>\n",
       "      <td>142</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>4547368</td>\n",
       "      <td>2018-11-08T19:00:00</td>\n",
       "      <td>Plot Points in Our Sexual Development - New York</td>\n",
       "      <td>broadway_tickets_national</td>\n",
       "      <td>8948</td>\n",
       "      <td>40.7736</td>\n",
       "      <td>-73.9837</td>\n",
       "      <td>Lincoln Center Theater - Claire Tow Theater</td>\n",
       "      <td>10023</td>\n",
       "      <td>POINT (-73.9837 40.7736)</td>\n",
       "      <td>142</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>3991302</td>\n",
       "      <td>2018-11-08T19:00:00</td>\n",
       "      <td>My Fair Lady - New York</td>\n",
       "      <td>broadway_tickets_national</td>\n",
       "      <td>2953</td>\n",
       "      <td>40.7731</td>\n",
       "      <td>-73.9822</td>\n",
       "      <td>Lincoln Center Theater-Vivian Beaumont</td>\n",
       "      <td>10023</td>\n",
       "      <td>POINT (-73.98220000000001 40.7731)</td>\n",
       "      <td>142</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>4442706</td>\n",
       "      <td>2018-11-08T19: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",
       "      <td>POINT (-73.9841 40.7726)</td>\n",
       "      <td>142</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>4596170</td>\n",
       "      <td>2018-11-08T19:00:00</td>\n",
       "      <td>OpenICE</td>\n",
       "      <td>concert</td>\n",
       "      <td>457975</td>\n",
       "      <td>40.7734</td>\n",
       "      <td>-73.9846</td>\n",
       "      <td>The Vincent Astor Gallery, The New York Public...</td>\n",
       "      <td>10023</td>\n",
       "      <td>POINT (-73.9846 40.7734)</td>\n",
       "      <td>142</td>\n",
       "      <td>Manhattan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  Unnamed: 0.1       id       datetime_local  \\\n",
       "0           1             1  4442708  2018-11-08T11:00:00   \n",
       "1          12            12  4547368  2018-11-08T19:00:00   \n",
       "2          14            14  3991302  2018-11-08T19:00:00   \n",
       "3          15            15  4442706  2018-11-08T19:00:00   \n",
       "4          30            30  4596170  2018-11-08T19:00:00   \n",
       "\n",
       "                                              title  \\\n",
       "0                       Big Apple Circus - New York   \n",
       "1  Plot Points in Our Sexual Development - New York   \n",
       "2                           My Fair Lady - New York   \n",
       "3                       Big Apple Circus - New York   \n",
       "4                                           OpenICE   \n",
       "\n",
       "                        type  venue.id  venue.location.lat  \\\n",
       "0                     family      3900             40.7726   \n",
       "1  broadway_tickets_national      8948             40.7736   \n",
       "2  broadway_tickets_national      2953             40.7731   \n",
       "3                     family      3900             40.7726   \n",
       "4                    concert    457975             40.7734   \n",
       "\n",
       "   venue.location.lon                                         venue.name  \\\n",
       "0            -73.9841                                      Damrosch Park   \n",
       "1            -73.9837        Lincoln Center Theater - Claire Tow Theater   \n",
       "2            -73.9822             Lincoln Center Theater-Vivian Beaumont   \n",
       "3            -73.9841                                      Damrosch Park   \n",
       "4            -73.9846  The Vincent Astor Gallery, The New York Public...   \n",
       "\n",
       "   venue.postal_code                            geometry  LocationID  \\\n",
       "0              10023            POINT (-73.9841 40.7726)         142   \n",
       "1              10023            POINT (-73.9837 40.7736)         142   \n",
       "2              10023  POINT (-73.98220000000001 40.7731)         142   \n",
       "3              10023            POINT (-73.9841 40.7726)         142   \n",
       "4              10023            POINT (-73.9846 40.7734)         142   \n",
       "\n",
       "     borough  \n",
       "0  Manhattan  \n",
       "1  Manhattan  \n",
       "2  Manhattan  \n",
       "3  Manhattan  \n",
       "4  Manhattan  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "events_agg.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "41"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "events_agg['LocationID'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "events_agg['datetime_local'] = pd.to_datetime(events_agg['datetime_local'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 14583 entries, 0 to 14582\n",
      "Data columns (total 14 columns):\n",
      "Unnamed: 0            14583 non-null int64\n",
      "Unnamed: 0.1          14583 non-null int64\n",
      "id                    14583 non-null int64\n",
      "datetime_local        14583 non-null datetime64[ns]\n",
      "title                 14583 non-null object\n",
      "type                  14583 non-null object\n",
      "venue.id              14583 non-null int64\n",
      "venue.location.lat    14583 non-null float64\n",
      "venue.location.lon    14583 non-null float64\n",
      "venue.name            14583 non-null object\n",
      "venue.postal_code     14583 non-null int64\n",
      "geometry              14583 non-null object\n",
      "LocationID            14583 non-null int64\n",
      "borough               14583 non-null object\n",
      "dtypes: datetime64[ns](1), float64(2), int64(6), object(5)\n",
      "memory usage: 1.6+ MB\n"
     ]
    }
   ],
   "source": [
    "events_agg.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "events_agg['hour'] = events_agg['datetime_local'].dt.hour"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "events_agg['nightevent'] = np.where(events_agg['hour'] >= 19, 1, 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Unnamed: 0.1</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",
       "      <th>geometry</th>\n",
       "      <th>LocationID</th>\n",
       "      <th>borough</th>\n",
       "      <th>hour</th>\n",
       "      <th>nightevent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4442708</td>\n",
       "      <td>2018-11-08 11: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",
       "      <td>POINT (-73.9841 40.7726)</td>\n",
       "      <td>142</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>4547368</td>\n",
       "      <td>2018-11-08 19:00:00</td>\n",
       "      <td>Plot Points in Our Sexual Development - New York</td>\n",
       "      <td>broadway_tickets_national</td>\n",
       "      <td>8948</td>\n",
       "      <td>40.7736</td>\n",
       "      <td>-73.9837</td>\n",
       "      <td>Lincoln Center Theater - Claire Tow Theater</td>\n",
       "      <td>10023</td>\n",
       "      <td>POINT (-73.9837 40.7736)</td>\n",
       "      <td>142</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>3991302</td>\n",
       "      <td>2018-11-08 19:00:00</td>\n",
       "      <td>My Fair Lady - New York</td>\n",
       "      <td>broadway_tickets_national</td>\n",
       "      <td>2953</td>\n",
       "      <td>40.7731</td>\n",
       "      <td>-73.9822</td>\n",
       "      <td>Lincoln Center Theater-Vivian Beaumont</td>\n",
       "      <td>10023</td>\n",
       "      <td>POINT (-73.98220000000001 40.7731)</td>\n",
       "      <td>142</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15</td>\n",
       "      <td>15</td>\n",
       "      <td>4442706</td>\n",
       "      <td>2018-11-08 19: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",
       "      <td>POINT (-73.9841 40.7726)</td>\n",
       "      <td>142</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>4596170</td>\n",
       "      <td>2018-11-08 19:00:00</td>\n",
       "      <td>OpenICE</td>\n",
       "      <td>concert</td>\n",
       "      <td>457975</td>\n",
       "      <td>40.7734</td>\n",
       "      <td>-73.9846</td>\n",
       "      <td>The Vincent Astor Gallery, The New York Public...</td>\n",
       "      <td>10023</td>\n",
       "      <td>POINT (-73.9846 40.7734)</td>\n",
       "      <td>142</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  Unnamed: 0.1       id      datetime_local  \\\n",
       "0           1             1  4442708 2018-11-08 11:00:00   \n",
       "1          12            12  4547368 2018-11-08 19:00:00   \n",
       "2          14            14  3991302 2018-11-08 19:00:00   \n",
       "3          15            15  4442706 2018-11-08 19:00:00   \n",
       "4          30            30  4596170 2018-11-08 19:00:00   \n",
       "\n",
       "                                              title  \\\n",
       "0                       Big Apple Circus - New York   \n",
       "1  Plot Points in Our Sexual Development - New York   \n",
       "2                           My Fair Lady - New York   \n",
       "3                       Big Apple Circus - New York   \n",
       "4                                           OpenICE   \n",
       "\n",
       "                        type  venue.id  venue.location.lat  \\\n",
       "0                     family      3900             40.7726   \n",
       "1  broadway_tickets_national      8948             40.7736   \n",
       "2  broadway_tickets_national      2953             40.7731   \n",
       "3                     family      3900             40.7726   \n",
       "4                    concert    457975             40.7734   \n",
       "\n",
       "   venue.location.lon                                         venue.name  \\\n",
       "0            -73.9841                                      Damrosch Park   \n",
       "1            -73.9837        Lincoln Center Theater - Claire Tow Theater   \n",
       "2            -73.9822             Lincoln Center Theater-Vivian Beaumont   \n",
       "3            -73.9841                                      Damrosch Park   \n",
       "4            -73.9846  The Vincent Astor Gallery, The New York Public...   \n",
       "\n",
       "   venue.postal_code                            geometry  LocationID  \\\n",
       "0              10023            POINT (-73.9841 40.7726)         142   \n",
       "1              10023            POINT (-73.9837 40.7736)         142   \n",
       "2              10023  POINT (-73.98220000000001 40.7731)         142   \n",
       "3              10023            POINT (-73.9841 40.7726)         142   \n",
       "4              10023            POINT (-73.9846 40.7734)         142   \n",
       "\n",
       "     borough  hour  nightevent  \n",
       "0  Manhattan    11           0  \n",
       "1  Manhattan    19           1  \n",
       "2  Manhattan    19           1  \n",
       "3  Manhattan    19           1  \n",
       "4  Manhattan    19           1  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "events_agg.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "events_agg = events_agg[['id','LocationID','hour','nightevent']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "tz_events_agg = events_agg.groupby(['LocationID'])['nightevent'].agg(['sum','count'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>LocationID</th>\n",
       "      <th>sum</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>54</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13</td>\n",
       "      <td>22</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>41</td>\n",
       "      <td>15</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>42</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>43</td>\n",
       "      <td>300</td>\n",
       "      <td>418</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   LocationID  sum  count\n",
       "0           4   54     54\n",
       "1          13   22     54\n",
       "2          41   15     21\n",
       "3          42    0      1\n",
       "4          43  300    418"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tz_events_agg.reset_index(inplace=True)\n",
    "tz_events_agg.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_shapes = gpd.read_file('taxi_zones.shp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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_shapes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "events_shp = pd.merge(taxi_shapes,tz_events_agg,how='left',left_on='LocationID',right_on='LocationID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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",
       "      <th>sum</th>\n",
       "      <th>count</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <td>54.0</td>\n",
       "      <td>54.0</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</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   sum  \\\n",
       "0            EWR  POLYGON ((933100.9183527103 192536.0856972019,...   NaN   \n",
       "1         Queens  (POLYGON ((1033269.243591294 172126.0078125, 1...   NaN   \n",
       "2          Bronx  POLYGON ((1026308.769506663 256767.6975403726,...   NaN   \n",
       "3      Manhattan  POLYGON ((992073.4667968601 203714.0759887695,...  54.0   \n",
       "4  Staten Island  POLYGON ((935843.3104932606 144283.335850656, ...   NaN   \n",
       "\n",
       "   count  \n",
       "0    NaN  \n",
       "1    NaN  \n",
       "2    NaN  \n",
       "3   54.0  \n",
       "4    NaN  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "events_shp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "events_shp.rename(columns={\"sum\": \"night_events\", \"count\": \"all_events\"},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "events_shp['night_events'].fillna(0,inplace=True)\n",
    "events_shp['all_events'].fillna(0,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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",
       "      <th>night_events</th>\n",
       "      <th>all_events</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",
       "      <td>0.0</td>\n",
       "      <td>0.0</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",
       "      <td>0.0</td>\n",
       "      <td>0.0</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",
       "      <td>0.0</td>\n",
       "      <td>0.0</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",
       "      <td>54.0</td>\n",
       "      <td>54.0</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",
       "      <td>0.0</td>\n",
       "      <td>0.0</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, ...   \n",
       "\n",
       "   night_events  all_events  \n",
       "0           0.0         0.0  \n",
       "1           0.0         0.0  \n",
       "2           0.0         0.0  \n",
       "3          54.0        54.0  \n",
       "4           0.0         0.0  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "events_shp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "events_shp.to_file(driver='ESRI Shapefile',filename='events_shp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "PUI2016_Python3",
   "language": "python",
   "name": "pui2016_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.5.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}