Get_CitiBike.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Management: Citi Bike 2016 Dataset\n",
    "\n",
    "## This notebook:\n",
    "1. Downloads Citi Bike Data in 2016\n",
    "2. Subsets stations within a range of Barclays Center using Carto DB SQL query\n",
    "3. Subsets trips made within a time range\n",
    "4. Returns a list of stations in 2016 (stations_2016.csv)\n",
    "5. Returns a csv with subset Citi Bike trips ('CB2016\\_subset\\_%sm_%h.csv'%(dist, time))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Set parameters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# set Euclidean distance as radius from Barclays Center\n",
    "dist = 800\n",
    "\n",
    "# set the hour of day before which the trips will be dropped\n",
    "# i.e. subset trips within x to 24 hour of day only\n",
    "time = 16"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Download Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# import packages\n",
    "import os\n",
    "import sys\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# import the getCitiBikeCSV function from Prof. Bianco (fedhere)\n",
    "def getCitiBikeCSV(datestring):\n",
    "    '''Downloads citibike data and unzips it.\n",
    "       If the data is downloaded by not unzippeds it zips it.\n",
    "       Moves the data to $PUIDATA\n",
    "    Arguments:\n",
    "        date string as yyyymm\n",
    "    '''\n",
    "    print (\"Downloading\", datestring)\n",
    "    ### First I will heck that it is not already there\n",
    "    if not os.path.isfile(os.getenv(\"PUIDATA\") + \"/\" + datestring + \"-citibike-tripdata.csv\"):\n",
    "        if os.path.isfile(datestring + \"-citibike-tripdata.csv\"):\n",
    "            # if in the current dir just move it\n",
    "            if os.system(\"mv \" + datestring + \"-citibike-tripdata.csv \" + os.getenv(\"PUIDATA\")):\n",
    "                print (\"Error moving file!, Please check!\")\n",
    "        #otherwise start looking for the zip file\n",
    "        else:\n",
    "            if not os.path.isfile(os.getenv(\"PUIDATA\") + \"/\" + datestring + \"-citibike-tripdata.zip\"):\n",
    "                if not os.path.isfile(datestring + \"-citibike-tripdata.zip\"):\n",
    "                    os.system(\"curl -O https://s3.amazonaws.com/tripdata/\" + datestring + \"-citibike-tripdata.zip\")\n",
    "                ###  To move it I use the os.system() functions to run bash commands with arguments\n",
    "                os.system(\"mv \" + datestring + \"-citibike-tripdata.zip \" + os.getenv(\"PUIDATA\"))\n",
    "            ### unzip the csv \n",
    "            os.system(\"unzip \" + os.getenv(\"PUIDATA\") + \"/\" + datestring + \"-citibike-tripdata.zip\")\n",
    "            ## NOTE: old csv citibike data had a different name structure. \n",
    "            if '2014' in datestring:\n",
    "                os.system(\"mv \" + datestring[:4] + '-' +  datestring[4:] + \n",
    "                          \"\\ -\\ Citi\\ Bike\\ trip\\ data.csv \" + datestring + \"-citibike-tripdata.csv\")\n",
    "            os.system(\"mv \" + datestring + \"-citibike-tripdata.csv \" + os.getenv(\"PUIDATA\"))\n",
    "    ### One final check:\n",
    "    if not os.path.isfile(os.getenv(\"PUIDATA\") + \"/\" + datestring + \"-citibike-tripdata.csv\"):\n",
    "        print (\"WARNING!!! something is wrong: the file is not there!\")\n",
    "\n",
    "    else:\n",
    "        print (\"file in place, you can continue\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Downloading 201601\n",
      "file in place, you can continue\n",
      "Downloading 201602\n",
      "file in place, you can continue\n",
      "Downloading 201603\n",
      "file in place, you can continue\n",
      "Downloading 201604\n",
      "file in place, you can continue\n",
      "Downloading 201605\n",
      "file in place, you can continue\n",
      "Downloading 201606\n",
      "file in place, you can continue\n",
      "Downloading 201607\n",
      "file in place, you can continue\n",
      "Downloading 201608\n",
      "file in place, you can continue\n",
      "Downloading 201609\n",
      "file in place, you can continue\n",
      "Downloading 201610\n",
      "file in place, you can continue\n",
      "Downloading 201611\n",
      "file in place, you can continue\n",
      "Downloading 201612\n",
      "file in place, you can continue\n"
     ]
    }
   ],
   "source": [
    "# download Citi Bike data in 2016\n",
    "for i in range(12):\n",
    "    datestring = str(201601 + i)\n",
    "    getCitiBikeCSV(datestring)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Generate station data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['tripduration', 'starttime', 'stoptime', 'start station id',\n",
       "       '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'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# use latest data (2016/12) to obtain unique stations\n",
    "CB = pd.read_csv(os.getenv(\"PUIDATA\") + \"/\" + '201609' + \"-citibike-tripdata.csv\")\n",
    "CB.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/Yuwen/anaconda/lib/python3.6/site-packages/ipykernel_launcher.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n"
     ]
    },
    {
     "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>start station id</th>\n",
       "      <th>start station name</th>\n",
       "      <th>start station latitude</th>\n",
       "      <th>start station longitude</th>\n",
       "      <th>Coordinates</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>255</th>\n",
       "      <td>72</td>\n",
       "      <td>W 52 St &amp; 11 Ave</td>\n",
       "      <td>40.767272</td>\n",
       "      <td>-73.993929</td>\n",
       "      <td>(40.76727216, -73.99392888)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2152</th>\n",
       "      <td>79</td>\n",
       "      <td>Franklin St &amp; W Broadway</td>\n",
       "      <td>40.719116</td>\n",
       "      <td>-74.006667</td>\n",
       "      <td>(40.71911552, -74.00666661)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>383</th>\n",
       "      <td>82</td>\n",
       "      <td>St James Pl &amp; Pearl St</td>\n",
       "      <td>40.711174</td>\n",
       "      <td>-74.000165</td>\n",
       "      <td>(40.71117416, -74.00016545)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>356</th>\n",
       "      <td>83</td>\n",
       "      <td>Atlantic Ave &amp; Fort Greene Pl</td>\n",
       "      <td>40.683826</td>\n",
       "      <td>-73.976323</td>\n",
       "      <td>(40.68382604, -73.97632328)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>116</td>\n",
       "      <td>W 17 St &amp; 8 Ave</td>\n",
       "      <td>40.741776</td>\n",
       "      <td>-74.001497</td>\n",
       "      <td>(40.74177603, -74.00149746)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      start station id             start station name  start station latitude  \\\n",
       "255                 72               W 52 St & 11 Ave               40.767272   \n",
       "2152                79       Franklin St & W Broadway               40.719116   \n",
       "383                 82         St James Pl & Pearl St               40.711174   \n",
       "356                 83  Atlantic Ave & Fort Greene Pl               40.683826   \n",
       "18                 116                W 17 St & 8 Ave               40.741776   \n",
       "\n",
       "      start station longitude                  Coordinates  \n",
       "255                -73.993929  (40.76727216, -73.99392888)  \n",
       "2152               -74.006667  (40.71911552, -74.00666661)  \n",
       "383                -74.000165  (40.71117416, -74.00016545)  \n",
       "356                -73.976323  (40.68382604, -73.97632328)  \n",
       "18                 -74.001497  (40.74177603, -74.00149746)  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# extract data from non-duplicated start stations\n",
    "stations = CB.drop_duplicates('start station id')\n",
    "stations.sort_values('start station id', inplace=True)\n",
    "stations = stations.iloc[:,range(3,7)]\n",
    "stations['Coordinates'] = list(zip(stations['start station latitude'], stations['start station longitude']))\n",
    "stations.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# export csv to Carto for query analysis\n",
    "stations.to_csv('stations_2016.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Identify stations within 800 meter of the Barclays Center"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# import packages (compatible between py2 and py3)\n",
    "try:\n",
    "    import urllib2\n",
    "    from urllib2 import HTTPError\n",
    "    from urllib2 import urlopen\n",
    "    from urllib import urlencode\n",
    "    from StringIO import StringIO as io\n",
    "\n",
    "except ImportError:\n",
    "    import urllib\n",
    "    from urllib.error import HTTPError\n",
    "    from urllib.request import urlopen, Request\n",
    "    from urllib.parse import urlencode\n",
    "    from io import BytesIO as io\n",
    "\n",
    "import ast"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# set SQL source\n",
    "SQL_SOURCE = 'https://ywc249.carto.com/api/v2/sql?q='\n",
    "\n",
    "# define query function\n",
    "def queryCartoDB(query, format='CSV', source=SQL_SOURCE):\n",
    "    '''queries carto datasets from a given carto account\n",
    "    Arguments: \n",
    "    query - string: a valid sql query string\n",
    "    format - outlut format  OPTIONAL (default CSV)\n",
    "    source - a valid sql api endpoint OPTIONAL (default carto fb55 account)\n",
    "    Returns:\n",
    "    the return of the sql query AS A STRING\n",
    "    NOTES:\n",
    "    designed for the carto API, tested only with CSV return format'''\n",
    "    \n",
    "    data = urlencode({'format':format, 'q':query}).encode('utf-8')\n",
    "    try:\n",
    "        response = urlopen(source, data)\n",
    "    except HTTPError as e:\n",
    "        raise ValueError('\\n'.join(ast.literal_eval(e.readline())['error']))\n",
    "    except Exception:\n",
    "        raise\n",
    "    return response.read()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# of stations within 800-meter range: 20\n"
     ]
    }
   ],
   "source": [
    "# select stations within 800m of the Barclays Center\n",
    "q1 = '''\n",
    "SELECT\n",
    "    start_station_id,\n",
    "    CDB_TransformToWebmercator(CDB_LatLng(\n",
    "        start_station_latitude,\n",
    "        start_station_longitude\n",
    "        )\n",
    ") as the_geom_webmercator, MIN(cartodb_id) as cartodb_id\n",
    "FROM stations\n",
    "WHERE\n",
    "     ST_DWithin(CDB_LatLng(\n",
    "         start_station_latitude,\n",
    "         start_station_longitude\n",
    ")::geography, CDB_LatLng(40.683048,-73.975961)::geography, %s)\n",
    "GROUP BY\n",
    "      start_station_id,\n",
    "      start_station_latitude,\n",
    "      start_station_longitude\n",
    "'''%dist\n",
    "\n",
    "nearby = pd.read_csv(io(queryCartoDB(q1)), sep=',')\n",
    "nearby = list(nearby['start_station_id'])\n",
    "nearby.sort()\n",
    "print('# of stations within %s-meter range: %s'%(dist, len(nearby)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Subset and concatenate the major dataframe for analysis\n",
    "- Subset trips beginning from the nearby stations near the Barclays Center\n",
    "- Subset trips beginning between 16:00 - 24:00"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['tripduration', 'starttime', 'stoptime', 'start station id',\n",
      "       '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'],\n",
      "      dtype='object')\n",
      "Index(['tripduration', 'starttime', 'stoptime', 'start station id',\n",
      "       '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'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "# examine variables\n",
    "print(CB.columns)\n",
    "CB2 = pd.read_csv(os.getenv(\"PUIDATA\") + \"/\" + '201601' + \"-citibike-tripdata.csv\")\n",
    "print(CB2.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# subset data by month and append as a list of 12 dfs\n",
    "CB2016 = []\n",
    "CBshape = []\n",
    "\n",
    "for i in range(12):\n",
    "    datestring = str(201601 + i)\n",
    "    df = pd.read_csv(os.getenv(\"PUIDATA\") + \"/\" + datestring + \"-citibike-tripdata.csv\")\n",
    "    #df.columns = [c.lower() for c in df.columns]\n",
    "    df.columns = ['tripduration', 'starttime', 'stoptime',\n",
    "                  'start station id', 'start station name', 'start station latitude', 'start station longitude',\n",
    "                  'end station id', 'end station name', 'end station latitude', 'end station longitude',\n",
    "                  'bikeid', 'usertype', 'birth year', 'gender']\n",
    "    df = df[df['start station id'].apply(lambda x: x in nearby)]\n",
    "    df['starttime'] = pd.to_datetime(df['starttime'])\n",
    "    \n",
    "    # subset trips after given hour in day to 24\n",
    "    df = df[df['starttime'].dt.hour >= time]\n",
    "    CB2016.append(df)\n",
    "    CBshape.append(df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total trips for 12 months: 60148\n",
      "Merged shape: (60148, 15)\n"
     ]
    },
    {
     "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>173</td>\n",
       "      <td>2016-01-01 16:03:10</td>\n",
       "      <td>1/1/2016 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.689810</td>\n",
       "      <td>-73.974931</td>\n",
       "      <td>23976</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1971.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>136</td>\n",
       "      <td>2016-01-01 16:05:54</td>\n",
       "      <td>1/1/2016 16:08:11</td>\n",
       "      <td>420</td>\n",
       "      <td>Clermont Ave &amp; Lafayette Ave</td>\n",
       "      <td>40.687645</td>\n",
       "      <td>-73.969689</td>\n",
       "      <td>270</td>\n",
       "      <td>Adelphi St &amp; Myrtle Ave</td>\n",
       "      <td>40.693083</td>\n",
       "      <td>-73.971789</td>\n",
       "      <td>17362</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>653</td>\n",
       "      <td>2016-01-01 16:13:47</td>\n",
       "      <td>1/1/2016 16:24:40</td>\n",
       "      <td>83</td>\n",
       "      <td>Atlantic Ave &amp; Fort Greene Pl</td>\n",
       "      <td>40.683826</td>\n",
       "      <td>-73.976323</td>\n",
       "      <td>278</td>\n",
       "      <td>Concord St &amp; Bridge St</td>\n",
       "      <td>40.697666</td>\n",
       "      <td>-73.984764</td>\n",
       "      <td>16270</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1976.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>659</td>\n",
       "      <td>2016-01-01 16:13:47</td>\n",
       "      <td>1/1/2016 16:24:46</td>\n",
       "      <td>83</td>\n",
       "      <td>Atlantic Ave &amp; Fort Greene Pl</td>\n",
       "      <td>40.683826</td>\n",
       "      <td>-73.976323</td>\n",
       "      <td>278</td>\n",
       "      <td>Concord St &amp; Bridge St</td>\n",
       "      <td>40.697666</td>\n",
       "      <td>-73.984764</td>\n",
       "      <td>22909</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1985.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1419</td>\n",
       "      <td>2016-01-01 16:20:39</td>\n",
       "      <td>1/1/2016 16:44:19</td>\n",
       "      <td>83</td>\n",
       "      <td>Atlantic Ave &amp; Fort Greene Pl</td>\n",
       "      <td>40.683826</td>\n",
       "      <td>-73.976323</td>\n",
       "      <td>532</td>\n",
       "      <td>S 5 Pl &amp; S 4 St</td>\n",
       "      <td>40.710451</td>\n",
       "      <td>-73.960876</td>\n",
       "      <td>20506</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   tripduration           starttime           stoptime  start station id  \\\n",
       "0           173 2016-01-01 16:03:10  1/1/2016 16:06:03               243   \n",
       "1           136 2016-01-01 16:05:54  1/1/2016 16:08:11               420   \n",
       "2           653 2016-01-01 16:13:47  1/1/2016 16:24:40                83   \n",
       "3           659 2016-01-01 16:13:47  1/1/2016 16:24:46                83   \n",
       "4          1419 2016-01-01 16:20:39  1/1/2016 16:44:19                83   \n",
       "\n",
       "              start station name  start station latitude  \\\n",
       "0        Fulton St & Rockwell Pl               40.688226   \n",
       "1   Clermont Ave & Lafayette Ave               40.687645   \n",
       "2  Atlantic Ave & Fort Greene Pl               40.683826   \n",
       "3  Atlantic Ave & Fort Greene Pl               40.683826   \n",
       "4  Atlantic Ave & Fort Greene Pl               40.683826   \n",
       "\n",
       "   start station longitude  end station id             end station name  \\\n",
       "0               -73.979382             241  DeKalb Ave & S Portland Ave   \n",
       "1               -73.969689             270      Adelphi St & Myrtle Ave   \n",
       "2               -73.976323             278       Concord St & Bridge St   \n",
       "3               -73.976323             278       Concord St & Bridge St   \n",
       "4               -73.976323             532              S 5 Pl & S 4 St   \n",
       "\n",
       "   end station latitude  end station longitude  bikeid    usertype  \\\n",
       "0             40.689810             -73.974931   23976  Subscriber   \n",
       "1             40.693083             -73.971789   17362  Subscriber   \n",
       "2             40.697666             -73.984764   16270  Subscriber   \n",
       "3             40.697666             -73.984764   22909  Subscriber   \n",
       "4             40.710451             -73.960876   20506  Subscriber   \n",
       "\n",
       "   birth year  gender  \n",
       "0      1971.0       2  \n",
       "1      1980.0       1  \n",
       "2      1976.0       1  \n",
       "3      1985.0       2  \n",
       "4      1993.0       1  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# concatenate data from all 12 months and validate merge\n",
    "CB_concat = pd.concat(CB2016, axis=0)\n",
    "CB_concat.reset_index(inplace=True)\n",
    "CB_concat = CB_concat.drop('index', axis=1)\n",
    "\n",
    "print('Total trips for 12 months: %s\\nMerged shape: %s'%(sum([s[0] for s in CBshape]), CB_concat.shape))\n",
    "CB_concat.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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",
       "    </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.689810</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",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>136</td>\n",
       "      <td>16:05:54</td>\n",
       "      <td>16:08:11</td>\n",
       "      <td>420</td>\n",
       "      <td>Clermont Ave &amp; Lafayette Ave</td>\n",
       "      <td>40.687645</td>\n",
       "      <td>-73.969689</td>\n",
       "      <td>270</td>\n",
       "      <td>Adelphi St &amp; Myrtle Ave</td>\n",
       "      <td>40.693083</td>\n",
       "      <td>-73.971789</td>\n",
       "      <td>17362</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1980.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>2016-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>653</td>\n",
       "      <td>16:13:47</td>\n",
       "      <td>16:24:40</td>\n",
       "      <td>83</td>\n",
       "      <td>Atlantic Ave &amp; Fort Greene Pl</td>\n",
       "      <td>40.683826</td>\n",
       "      <td>-73.976323</td>\n",
       "      <td>278</td>\n",
       "      <td>Concord St &amp; Bridge St</td>\n",
       "      <td>40.697666</td>\n",
       "      <td>-73.984764</td>\n",
       "      <td>16270</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1976.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>2016-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>659</td>\n",
       "      <td>16:13:47</td>\n",
       "      <td>16:24:46</td>\n",
       "      <td>83</td>\n",
       "      <td>Atlantic Ave &amp; Fort Greene Pl</td>\n",
       "      <td>40.683826</td>\n",
       "      <td>-73.976323</td>\n",
       "      <td>278</td>\n",
       "      <td>Concord St &amp; Bridge St</td>\n",
       "      <td>40.697666</td>\n",
       "      <td>-73.984764</td>\n",
       "      <td>22909</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1985.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>2016-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1419</td>\n",
       "      <td>16:20:39</td>\n",
       "      <td>16:44:19</td>\n",
       "      <td>83</td>\n",
       "      <td>Atlantic Ave &amp; Fort Greene Pl</td>\n",
       "      <td>40.683826</td>\n",
       "      <td>-73.976323</td>\n",
       "      <td>532</td>\n",
       "      <td>S 5 Pl &amp; S 4 St</td>\n",
       "      <td>40.710451</td>\n",
       "      <td>-73.960876</td>\n",
       "      <td>20506</td>\n",
       "      <td>Subscriber</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>2016-01-01</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",
       "1           136  16:05:54  16:08:11               420   \n",
       "2           653  16:13:47  16:24:40                83   \n",
       "3           659  16:13:47  16:24:46                83   \n",
       "4          1419  16:20:39  16:44:19                83   \n",
       "\n",
       "              start station name  start station latitude  \\\n",
       "0        Fulton St & Rockwell Pl               40.688226   \n",
       "1   Clermont Ave & Lafayette Ave               40.687645   \n",
       "2  Atlantic Ave & Fort Greene Pl               40.683826   \n",
       "3  Atlantic Ave & Fort Greene Pl               40.683826   \n",
       "4  Atlantic Ave & Fort Greene Pl               40.683826   \n",
       "\n",
       "   start station longitude  end station id             end station name  \\\n",
       "0               -73.979382             241  DeKalb Ave & S Portland Ave   \n",
       "1               -73.969689             270      Adelphi St & Myrtle Ave   \n",
       "2               -73.976323             278       Concord St & Bridge St   \n",
       "3               -73.976323             278       Concord St & Bridge St   \n",
       "4               -73.976323             532              S 5 Pl & S 4 St   \n",
       "\n",
       "   end station latitude  end station longitude  bikeid    usertype  \\\n",
       "0             40.689810             -73.974931   23976  Subscriber   \n",
       "1             40.693083             -73.971789   17362  Subscriber   \n",
       "2             40.697666             -73.984764   16270  Subscriber   \n",
       "3             40.697666             -73.984764   22909  Subscriber   \n",
       "4             40.710451             -73.960876   20506  Subscriber   \n",
       "\n",
       "   birth year  gender   startdate    stopdate  \n",
       "0      1971.0       2  2016-01-01  2016-01-01  \n",
       "1      1980.0       1  2016-01-01  2016-01-01  \n",
       "2      1976.0       1  2016-01-01  2016-01-01  \n",
       "3      1985.0       2  2016-01-01  2016-01-01  \n",
       "4      1993.0       1  2016-01-01  2016-01-01  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# convert datetime format\n",
    "CB_concat['stoptime'] = pd.to_datetime(CB_concat['stoptime'])\n",
    "\n",
    "# split date and time to different columns\n",
    "CB_concat['startdate'] = CB_concat['starttime'].dt.date\n",
    "CB_concat['starttime'] = CB_concat['starttime'].dt.time\n",
    "CB_concat['stopdate'] = CB_concat['stoptime'].dt.date\n",
    "CB_concat['stoptime'] = CB_concat['stoptime'].dt.time\n",
    "CB_concat.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# export csv file\n",
    "CB_concat.to_csv('CB2016_subset_%sm_%sh.csv'%(dist, time))"
   ]
  },
  {
   "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
}