Merge_EventCitiBike.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Management: Merge Bike and Event Datasets\n",
    "\n",
    "## This notebook:\n",
    "1. Merge the two datasets\n",
    "2. Returns a csv with Citi Bike trips associating with the event happening on the same day ('merged_event_CB2016_800m.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Clean before merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Populating the interactive namespace from numpy and matplotlib\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/Yuwen/anaconda/lib/python3.6/site-packages/IPython/core/magics/pylab.py:161: UserWarning: pylab import has clobbered these variables: ['e']\n",
      "`%matplotlib` prevents importing * from pylab and numpy\n",
      "  \"\\n`%matplotlib` prevents importing * from pylab and numpy\"\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import pylab as pl\n",
    "%pylab inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>EventID</th>\n",
       "      <th>Event_type</th>\n",
       "      <th>Name</th>\n",
       "      <th>Start_Time</th>\n",
       "      <th>Start_Hour</th>\n",
       "      <th>End_Time</th>\n",
       "      <th>End_Hour</th>\n",
       "      <th>weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1.490000e+15</td>\n",
       "      <td>basketball</td>\n",
       "      <td>Harlem Globetrotters</td>\n",
       "      <td>2016-01-02 19:00:00</td>\n",
       "      <td>19</td>\n",
       "      <td>2016-01-02 21:00:00</td>\n",
       "      <td>21</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0       EventID  Event_type                  Name  \\\n",
       "0           1  1.490000e+15  basketball  Harlem Globetrotters   \n",
       "\n",
       "            Start_Time  Start_Hour             End_Time  End_Hour  weekday  \n",
       "0  2016-01-02 19:00:00          19  2016-01-02 21:00:00        21    False  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "event = pd.read_csv('events_2016_subset.csv')\n",
    "event.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>tripduration</th>\n",
       "      <th>starttime</th>\n",
       "      <th>stoptime</th>\n",
       "      <th>start station id</th>\n",
       "      <th>start station name</th>\n",
       "      <th>start station latitude</th>\n",
       "      <th>start station longitude</th>\n",
       "      <th>end station id</th>\n",
       "      <th>end station name</th>\n",
       "      <th>end station latitude</th>\n",
       "      <th>end station longitude</th>\n",
       "      <th>bikeid</th>\n",
       "      <th>usertype</th>\n",
       "      <th>birth year</th>\n",
       "      <th>gender</th>\n",
       "      <th>startdate</th>\n",
       "      <th>stopdate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>173</td>\n",
       "      <td>16:03:10</td>\n",
       "      <td>16:06:03</td>\n",
       "      <td>243</td>\n",
       "      <td>Fulton St &amp; Rockwell Pl</td>\n",
       "      <td>40.688226</td>\n",
       "      <td>-73.979382</td>\n",
       "      <td>241</td>\n",
       "      <td>DeKalb Ave &amp; S Portland Ave</td>\n",
       "      <td>40.68981</td>\n",
       "      <td>-73.974931</td>\n",
       "      <td>23976</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1971.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>2016-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  tripduration starttime  stoptime  start station id  \\\n",
       "0           0           173  16:03:10  16:06:03               243   \n",
       "\n",
       "        start station name  start station latitude  start station longitude  \\\n",
       "0  Fulton St & Rockwell Pl               40.688226               -73.979382   \n",
       "\n",
       "   end station id             end station name  end station latitude  \\\n",
       "0             241  DeKalb Ave & S Portland Ave              40.68981   \n",
       "\n",
       "   end station longitude  bikeid    usertype  birth year  gender   startdate  \\\n",
       "0             -73.974931   23976  Subscriber      1971.0       2  2016-01-01   \n",
       "\n",
       "     stopdate  \n",
       "0  2016-01-01  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bike = pd.read_csv('CB2016_subset_800m_16h.csv')\n",
    "bike.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(135, 60148)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(event), len(bike)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>EventID</th>\n",
       "      <th>Event_type</th>\n",
       "      <th>Name</th>\n",
       "      <th>Start_Time</th>\n",
       "      <th>Start_Hour</th>\n",
       "      <th>End_Time</th>\n",
       "      <th>End_Hour</th>\n",
       "      <th>weekday</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1.490000e+15</td>\n",
       "      <td>basketball</td>\n",
       "      <td>Harlem Globetrotters</td>\n",
       "      <td>2016-01-02 19:00:00</td>\n",
       "      <td>19</td>\n",
       "      <td>2016-01-02 21:00:00</td>\n",
       "      <td>21</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-01-02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0       EventID  Event_type                  Name  \\\n",
       "0           1  1.490000e+15  basketball  Harlem Globetrotters   \n",
       "\n",
       "            Start_Time  Start_Hour             End_Time  End_Hour  weekday  \\\n",
       "0  2016-01-02 19:00:00          19  2016-01-02 21:00:00        21    False   \n",
       "\n",
       "         Date  \n",
       "0  2016-01-02  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "event['Date'] = pd.to_datetime(event['Start_Time']).dt.date\n",
    "event.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Event_type</th>\n",
       "      <th>End_Time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>basketball</td>\n",
       "      <td>2016-01-02 21:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  Event_type             End_Time\n",
       "0  2016-01-02  basketball  2016-01-02 21:00:00"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "e = event[['Date','Event_type','End_Time']]\n",
    "e.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/Yuwen/anaconda/lib/python3.6/site-packages/pandas/core/generic.py:2999: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self[name] = value\n"
     ]
    }
   ],
   "source": [
    "e.Date = pd.to_datetime(e.Date)\n",
    "bike.startdate = pd.to_datetime(bike.startdate)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Examine end time distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>End_Time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>21.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>21.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   End_Time\n",
       "0      21.0\n",
       "1      21.5\n",
       "2      21.5\n",
       "3      21.5\n",
       "4      21.5"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = pd.to_datetime(event['End_Time']).dt.hour + pd.to_datetime(event['End_Time']).dt.minute / 60\n",
    "tt = pd.DataFrame(t)\n",
    "tt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXQAAAD8CAYAAABn919SAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAADF1JREFUeJzt3X+IZfdZx/H3p9n4A2NtQsZlbaLjH2sxCE3qEooVq0lT\nU1O6AWlo8cdKAktti0kRyrQVRIqwUSn+QCiLDS4YUiNtzdoUmmVNiEISO1tjk3QTt0qCtZvdaUpt\ni2iJefxjTmG62TtzZ+beuTvPvF+w3HPPPbPn+WaX95y9c+9NqgpJ0vb3ilkPIEmaDIMuSU0YdElq\nwqBLUhMGXZKaMOiS1IRBl6QmDLokNWHQJamJXVt5sssvv7zm5+e38pSStO2dOHHiq1U1t9ZxWxr0\n+fl5FhcXt/KUkrTtJXlunON8ykWSmjDoktSEQZekJgy6JDVh0CWpCYMuSU0YdElqwqBLUhMGXZKa\n2NJ3ikq6cMwv3D+zcz976KaZnbszr9AlqQmDLklNGHRJasKgS1ITBl2SmjDoktSEQZekJgy6JDVh\n0CWpCYMuSU0YdElqwqBLUhMGXZKaMOiS1MTYQU9yUZJ/TvLp4f5lSY4lOTXcXjq9MSVJa1nPFfrt\nwMkV9xeA41W1Fzg+3JckzchYQU9yBXAT8Bcrdu8HjgzbR4CbJzuaJGk9xr1C/2Pg/cBLK/btrqrT\nw/bzwO5JDiZJWp81g57krcDZqjox6piqKqBGfP3BJItJFpeWljY+qSRpVeNcob8BeFuSZ4GPA9cl\n+SvgTJI9AMPt2fN9cVUdrqp9VbVvbm5uQmNLks61ZtCr6gNVdUVVzQPvAP6+qn4VOAocGA47ANw3\ntSklSWvazOvQDwE3JDkFvGm4L0makV3rObiqHgIeGrZfAK6f/EiSpI3wnaKS1IRBl6QmDLokNWHQ\nJakJgy5JTRh0SWrCoEtSEwZdkpow6JLUhEGXpCYMuiQ1YdAlqQmDLklNGHRJasKgS1ITBl2SmjDo\nktSEQZekJgy6JDVh0CWpCYMuSU0YdElqwqBLUhMGXZKaMOiS1IRBl6QmDLokNWHQJakJgy5JTRh0\nSWrCoEtSEwZdkpow6JLUhEGXpCYMuiQ1YdAlqQmDLklN7Jr1ANJON79w/6xHUBNrXqEn+b4k/5Tk\nX5I8leT3hv2XJTmW5NRwe+n0x5UkjTLOUy7/C1xXVa8FrgZuTPJ6YAE4XlV7gePDfUnSjKwZ9Fr2\nreHuxcOvAvYDR4b9R4CbpzKhJGksY/1QNMlFSR4HzgLHquoxYHdVnR4OeR7YPaUZJUljGCvoVfV/\nVXU1cAVwbZKfOufxYvmq/WWSHEyymGRxaWlp0wNLks5vXS9brKqvAw8CNwJnkuwBGG7Pjviaw1W1\nr6r2zc3NbXZeSdII47zKZS7Jq4bt7wduAJ4GjgIHhsMOAPdNa0hJ0trGeR36HuBIkotY/gZwb1V9\nOskjwL1JbgOeA26Z4pySpDWsGfSq+gJwzXn2vwBcP42hJEnr51v/JakJgy5JTRh0SWrCoEtSEwZd\nkpow6JLUhEGXpCYMuiQ1YdAlqQmDLklNGHRJasKgS1ITBl2SmjDoktSEQZekJgy6JDVh0CWpCYMu\nSU2M8/8UlaQW5hfun9m5nz1009TP4RW6JDVh0CWpCYMuSU0YdElqwqBLUhMGXZKaMOiS1IRBl6Qm\nDLokNWHQJakJgy5JTRh0SWrCoEtSEwZdkpow6JLUhEGXpCYMuiQ1YdAlqQmDLklNrBn0JFcmeTDJ\nF5M8leT2Yf9lSY4lOTXcXjr9cSVJo4xzhf4i8NtVdRXweuA9Sa4CFoDjVbUXOD7clyTNyJpBr6rT\nVfX5YfubwEng1cB+4Mhw2BHg5mkNKUla27qeQ08yD1wDPAbsrqrTw0PPA7snOpkkaV3GDnqSS4BP\nAHdU1TdWPlZVBdSIrzuYZDHJ4tLS0qaGlSSNNlbQk1zMcszvrqpPDrvPJNkzPL4HOHu+r62qw1W1\nr6r2zc3NTWJmSdJ5jPMqlwAfA05W1UdWPHQUODBsHwDum/x4kqRx7RrjmDcAvwY8keTxYd8HgUPA\nvUluA54DbpnOiJKkcawZ9Kr6RyAjHr5+suNIkjbKd4pKUhMGXZKaMOiS1IRBl6QmDLokNWHQJakJ\ngy5JTRh0SWrCoEtSEwZdkpow6JLUhEGXpCYMuiQ1YdAlqQmDLklNGHRJasKgS1ITBl2SmjDoktSE\nQZekJgy6JDVh0CWpCYMuSU0YdElqwqBLUhMGXZKaMOiS1IRBl6Qmds16AEk7z/zC/bMeoSWv0CWp\nCYMuSU0YdElqwqBLUhMGXZKaMOiS1IRBl6QmDLokNWHQJamJNYOe5K4kZ5M8uWLfZUmOJTk13F46\n3TElSWsZ5wr9L4Ebz9m3AByvqr3A8eG+JGmG1gx6VT0MfO2c3fuBI8P2EeDmCc8lSVqnjT6Hvruq\nTg/bzwO7JzSPJGmDNv1D0aoqoEY9nuRgksUki0tLS5s9nSRphI0G/UySPQDD7dlRB1bV4araV1X7\n5ubmNng6SdJaNhr0o8CBYfsAcN9kxpEkbdQ4L1u8B3gEeE2SLye5DTgE3JDkFPCm4b4kaYbW/D8W\nVdU7Rzx0/YRnkSRtgu8UlaQmDLokNWHQJakJgy5JTaz5Q1FpK80v3D+T8z576KaZnFeaJK/QJakJ\ngy5JTRh0SWrCoEtSEwZdkpow6JLUhEGXpCYMuiQ1YdAlqQmDLklNGHRJasKgS1ITBl2SmvDTFi9g\nfvLg1pnVf2tpkrxCl6QmDLokNWHQJakJgy5JTRh0SWrCoEtSEwZdkpow6JLUhEGXpCYMuiQ1YdAl\nqQmDLklNGHRJasJPW9TL+MmD0vbkFbokNWHQJakJgy5JTRh0SWpi2/xQ1B/USdLqNnWFnuTGJM8k\n+VKShUkNJUlavw0HPclFwJ8DbwGuAt6Z5KpJDSZJWp/NXKFfC3ypqv69qr4NfBzYP5mxJEnrtZmg\nvxr4jxX3vzzskyTNwNR/KJrkIHBwuPutJM9s8Le6HPjqZKbaNlzzzuCad4Dcuak1/9g4B20m6P8J\nXLni/hXDvu9SVYeBw5s4DwBJFqtq32Z/n+3ENe8Mrnln2Io1b+Ypl88Be5P8eJLvAd4BHJ3MWJKk\n9drwFXpVvZjkvcBngYuAu6rqqYlNJklal009h15VnwE+M6FZ1rLpp222Ide8M7jmnWHqa05VTfsc\nkqQt4Ge5SFITF2TQk9yV5GySJ1fsuzrJo0keT7KY5NpZzjhpI9b82iSPJHkiyd8leeUsZ5ykJFcm\neTDJF5M8leT2Yf9lSY4lOTXcXjrrWSdllTW/fbj/UpJWr/xYZc1/mOTpJF9I8qkkr5r1rJOyypo/\nPKz38SQPJPmRiZ+8qi64X8DPAa8Dnlyx7wHgLcP2LwEPzXrOLVjz54A3Dtu3Ah+e9ZwTXO8e4HXD\n9g8C/8ryR0j8AbAw7F8A7pz1rFuw5p8EXgM8BOyb9ZxbtOY3A7uG/XfukD/nV6445reAj0763Bfk\nFXpVPQx87dzdwHeuUH8I+MqWDjVlI9b8E8DDw/Yx4Je3dKgpqqrTVfX5YfubwEmW32m8HzgyHHYE\nuHk2E07eqDVX1cmq2ugb7i5oq6z5gap6cTjsUZbfx9LCKmv+xorDfoDlpk3Utvn4XOAO4LNJ/ojl\np4p+ZsbzbIWnWA7c3wJv57vfyNVGknngGuAxYHdVnR4eeh7YPaOxpuqcNe8Iq6z5VuCvt3qerXDu\nmpP8PvDrwH8BvzDp812QV+gj/Cbwvqq6Engf8LEZz7MVbgXeneQEy/90+/aM55m4JJcAnwDuOOcK\nhlr+t2m7l2GttuauRq05yYeAF4G7ZzXbtJxvzVX1oaFhdwPvnfQ5t1PQDwCfHLb/huVPe2ytqp6u\nqjdX1U8D9wD/NuuZJinJxSz/hb+7qr7zZ3smyZ7h8T3A2VnNNw0j1tzaqDUn+Q3grcCvDN+82xjj\nz/lupvAU6nYK+leANw7b1wGnZjjLlkjyw8PtK4DfAT4624kmJ0lY/lfWyar6yIqHjrL8zZvh9r6t\nnm1aVllzW6PWnORG4P3A26rqv2c13zSssua9Kw7bDzw98XNfiN8Yk9wD/DzLn8h2Bvhd4BngT1h+\n3v9/gHdX1YlZzThpI9Z8CfCe4ZBPAh/ociWT5GeBfwCeAF4adn+Q5eca7wV+FHgOuKWqzv1h8ba0\nypq/F/gzYA74OvB4Vf3iTIacsFXW/Kcsr/uFYd+jVfWurZ9w8lZZ820sv5rpJZb/br+rql72gYab\nOneTPkjSjrednnKRJK3CoEtSEwZdkpow6JLUhEGXpCYMuiQ1YdAlqQmDLklN/D+3qruyT/MceQAA\nAABJRU5ErkJggg==\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x116a31518>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "pl.hist(tt.End_Time)\n",
    "pl.savefig('fig_EndTimeDistribution.png', dpi=300)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>tripduration</th>\n",
       "      <th>starttime</th>\n",
       "      <th>stoptime</th>\n",
       "      <th>start station id</th>\n",
       "      <th>start station name</th>\n",
       "      <th>start station latitude</th>\n",
       "      <th>start station longitude</th>\n",
       "      <th>end station id</th>\n",
       "      <th>end station name</th>\n",
       "      <th>...</th>\n",
       "      <th>end station longitude</th>\n",
       "      <th>bikeid</th>\n",
       "      <th>usertype</th>\n",
       "      <th>birth year</th>\n",
       "      <th>gender</th>\n",
       "      <th>startdate</th>\n",
       "      <th>stopdate</th>\n",
       "      <th>Date</th>\n",
       "      <th>Event_type</th>\n",
       "      <th>End_Time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>173</td>\n",
       "      <td>16:03:10</td>\n",
       "      <td>16:06:03</td>\n",
       "      <td>243</td>\n",
       "      <td>Fulton St &amp; Rockwell Pl</td>\n",
       "      <td>40.688226</td>\n",
       "      <td>-73.979382</td>\n",
       "      <td>241</td>\n",
       "      <td>DeKalb Ave &amp; S Portland Ave</td>\n",
       "      <td>...</td>\n",
       "      <td>-73.974931</td>\n",
       "      <td>23976</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1971.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  tripduration starttime  stoptime  start station id  \\\n",
       "0           0           173  16:03:10  16:06:03               243   \n",
       "\n",
       "        start station name  start station latitude  start station longitude  \\\n",
       "0  Fulton St & Rockwell Pl               40.688226               -73.979382   \n",
       "\n",
       "   end station id             end station name   ...     \\\n",
       "0             241  DeKalb Ave & S Portland Ave   ...      \n",
       "\n",
       "   end station longitude  bikeid    usertype birth year  gender  startdate  \\\n",
       "0             -73.974931   23976  Subscriber     1971.0       2 2016-01-01   \n",
       "\n",
       "     stopdate Date Event_type End_Time  \n",
       "0  2016-01-01  NaT        NaN      NaN  \n",
       "\n",
       "[1 rows x 21 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined = bike.merge(e,left_on='startdate',right_on='Date',how='left')\n",
    "joined.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(60148, 60522)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(bike),len(joined)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Unnamed: 0', 'tripduration', 'starttime', 'stoptime',\n",
       "       'start station id', 'start station name', 'start station latitude',\n",
       "       'start station longitude', 'end station id', 'end station name',\n",
       "       'end station latitude', 'end station longitude', 'bikeid', 'usertype',\n",
       "       'birth year', 'gender', 'startdate', 'stopdate', 'Date', 'Event_type',\n",
       "       'End_Time'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tripduration</th>\n",
       "      <th>starttime</th>\n",
       "      <th>stoptime</th>\n",
       "      <th>start station id</th>\n",
       "      <th>start station name</th>\n",
       "      <th>start station latitude</th>\n",
       "      <th>start station longitude</th>\n",
       "      <th>end station id</th>\n",
       "      <th>end station name</th>\n",
       "      <th>end station latitude</th>\n",
       "      <th>end station longitude</th>\n",
       "      <th>bikeid</th>\n",
       "      <th>usertype</th>\n",
       "      <th>birth year</th>\n",
       "      <th>gender</th>\n",
       "      <th>startdate</th>\n",
       "      <th>stopdate</th>\n",
       "      <th>Event_type</th>\n",
       "      <th>End_Time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>173</td>\n",
       "      <td>16:03:10</td>\n",
       "      <td>16:06:03</td>\n",
       "      <td>243</td>\n",
       "      <td>Fulton St &amp; Rockwell Pl</td>\n",
       "      <td>40.688226</td>\n",
       "      <td>-73.979382</td>\n",
       "      <td>241</td>\n",
       "      <td>DeKalb Ave &amp; S Portland Ave</td>\n",
       "      <td>40.68981</td>\n",
       "      <td>-73.974931</td>\n",
       "      <td>23976</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1971.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   tripduration starttime  stoptime  start station id  \\\n",
       "0           173  16:03:10  16:06:03               243   \n",
       "\n",
       "        start station name  start station latitude  start station longitude  \\\n",
       "0  Fulton St & Rockwell Pl               40.688226               -73.979382   \n",
       "\n",
       "   end station id             end station name  end station latitude  \\\n",
       "0             241  DeKalb Ave & S Portland Ave              40.68981   \n",
       "\n",
       "   end station longitude  bikeid    usertype  birth year  gender  startdate  \\\n",
       "0             -73.974931   23976  Subscriber      1971.0       2 2016-01-01   \n",
       "\n",
       "     stopdate Event_type End_Time  \n",
       "0  2016-01-01        NaN      NaN  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined.drop([u'Unnamed: 0', \n",
    "             u'Date'],axis=1,inplace=True)\n",
    "joined.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# assign psuedo-end time for days w/o events\n",
    "for i in joined[joined.End_Time.isnull()].index:\n",
    "    joined.loc[i,'End_Time'] = pd.to_datetime(\"21:00:00\")\n",
    "joined.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "joined['E_Time'] = pd.to_datetime(joined['End_Time']).dt.time\n",
    "joined.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 265,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "joined.to_csv('merged_event_CB2016_800m.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}