MTA_data_processing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### We are processing MTA turnstil data from 2010 to 2019 in this notebook. however, the turnstil data was not in same format before 20141011, so we need to reformat the data:\n",
    "\n",
    "#### Before 20141011, each row of turnstil data has three records. we only need to keep one record for each row.\n",
    "#### Before  20141011, turnstil data does not have station information, which we need to merge with MTA station dataset. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Table of Contents:\n",
    "* [Processing data from 2010 to 2011](#1)\n",
    "* [Processing data from 2013 to 2014](#2)\n",
    "* [Merge with MTA data to create station name in turnstile dataset](#3)\n",
    "* [Processing data from 2015 to 2019](#4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='1'></a>\n",
    "## 1. Processing data from 2010 to 2011"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### From 2010 to 2012, MTA turnstil did not keep data record for every week, so I will concatenate files in few steps, and skips those dates that do not have turnstil records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import *\n",
    "from datetime import time\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "date_from = date(2010,6,5)\n",
    "date_to = date(2011,11,17)\n",
    "filenames_10_11 = []\n",
    "\n",
    "\n",
    "pointer = date_from\n",
    "while pointer <= date_to:\n",
    "    filenames_10_11.append('turnstile_' + pointer.strftime(\"%y%m%d\") + '.txt')\n",
    "    pointer += timedelta(days = 7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataframe_10_11 = []\n",
    "for link in filenames_10_11:\n",
    "    dataframe_10_11.append(pd.read_csv(link, header = None, usecols=range(0, 43)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "date_from = date(2011,11,19)\n",
    "date_to = date(2011,12,10)\n",
    "filenames_11 = []\n",
    "\n",
    "\n",
    "pointer = date_from\n",
    "while pointer <= date_to:\n",
    "    filenames_11.append('turnstile_' + pointer.strftime(\"%y%m%d\") + '.txt')\n",
    "    pointer += timedelta(days = 7)\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "filenames_11.append('turnstile_111219.txt')\n",
    "filenames_11.append('turnstile_111224.txt')\n",
    "filenames_11.append('turnstile_111231.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataframe_11 = []\n",
    "for link in filenames_11:\n",
    "    dataframe_11.append(pd.read_csv(link, header = None, usecols=range(0, 43)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Concatenate pandas series together (data from 2010 to 2011)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_10_11 = dataframe_11 + dataframe_10_11"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean(df):\n",
    "    info =  df.iloc[:,0:3]\n",
    "    df1 = pd.concat([info,df.iloc[:,3:8]],axis=1)\n",
    "    df2 = pd.concat([info,df.iloc[:,8:13]],axis=1)\n",
    "    df3 = pd.concat([info,df.iloc[:,13:18]],axis=1)\n",
    "    df4 = pd.concat([info,df.iloc[:,18:23]],axis=1)\n",
    "    df5 = pd.concat([info,df.iloc[:,23:28]],axis=1)\n",
    "    df6 = pd.concat([info,df.iloc[:,28:33]],axis=1)\n",
    "    df7 = pd.concat([info,df.iloc[:,33:38]],axis=1)\n",
    "    df8 = pd.concat([info,df.iloc[:,38:43]],axis=1)\n",
    "    listofdf = [df1,df2,df3,df4,df5,df6,df7,df8]\n",
    "    for df in listofdf:\n",
    "        df.columns = ['C/A','UNIT','SCP','DATE','TIME','DESC','ENTRIES','EXITS']\n",
    "    wholedf = pd.concat([df1,df2,df3,df4,df5,df6,df7,df8],axis=0)\n",
    "    return(wholedf.sort_values(by = ['C/A','UNIT','SCP','DATE','TIME']))  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Now reformatting the data from 2010 to 2011, and put it into dataframe "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# combine all list into one dataframe\n",
    "data_2010_11 = pd.concat([clean(i) for i in df_10_11], axis = 0)\n",
    "\n",
    "# set column index\n",
    "data_2010_11 = data_2010_11.reset_index()\n",
    "\n",
    "# drop the first index column\n",
    "data_2010_11.drop(['index'], axis = 1, inplace = True)\n",
    "\n",
    "# remove error data with 'DATE' == NaN\n",
    "data_2010_11_removed = data_2010_11[data_2010_11['DATE'].notnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>C/A</th>\n",
       "      <th>UNIT</th>\n",
       "      <th>SCP</th>\n",
       "      <th>DATE</th>\n",
       "      <th>TIME</th>\n",
       "      <th>DESC</th>\n",
       "      <th>ENTRIES</th>\n",
       "      <th>EXITS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>11-12-11</td>\n",
       "      <td>03:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>3391232.0</td>\n",
       "      <td>1171297.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>11-12-11</td>\n",
       "      <td>07:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>3391249.0</td>\n",
       "      <td>1171304.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>11-12-11</td>\n",
       "      <td>11:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>3391339.0</td>\n",
       "      <td>1171414.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>11-12-11</td>\n",
       "      <td>15:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>3391584.0</td>\n",
       "      <td>1171474.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>11-12-11</td>\n",
       "      <td>19:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>3391978.0</td>\n",
       "      <td>1171530.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    C/A  UNIT       SCP      DATE      TIME     DESC    ENTRIES      EXITS\n",
       "0  A002  R051  02-00-00  11-12-11  03:00:00  REGULAR  3391232.0  1171297.0\n",
       "1  A002  R051  02-00-00  11-12-11  07:00:00  REGULAR  3391249.0  1171304.0\n",
       "2  A002  R051  02-00-00  11-12-11  11:00:00  REGULAR  3391339.0  1171414.0\n",
       "3  A002  R051  02-00-00  11-12-11  15:00:00  REGULAR  3391584.0  1171474.0\n",
       "4  A002  R051  02-00-00  11-12-11  19:00:00  REGULAR  3391978.0  1171530.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_2010_11_removed .head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(17668545, 8)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_2010_11_removed.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='2'></a>\n",
    "## 2. Processing data from 2010 to 2012"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "date_from = date(2013,1,5)\n",
    "date_to = date(2014,10,11)\n",
    "filenames_13_14 = []\n",
    "\n",
    "\n",
    "pointer = date_from\n",
    "while pointer <= date_to:\n",
    "    filenames_13_14.append('turnstile_' + pointer.strftime(\"%y%m%d\") + '.txt')\n",
    "    pointer += timedelta(days = 7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# read date\n",
    "dataframe_list = []\n",
    "for link in filenames_13_14:\n",
    "    dataframe_list.append(pd.read_csv(link, header = None, usecols=range(0, 43)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# define a function that rearrange the data in every line\n",
    "def clean(df):\n",
    "    info =  df.iloc[:,0:3]\n",
    "    df1 = pd.concat([info,df.iloc[:,3:8]],axis=1)\n",
    "    df2 = pd.concat([info,df.iloc[:,8:13]],axis=1)\n",
    "    df3 = pd.concat([info,df.iloc[:,13:18]],axis=1)\n",
    "    df4 = pd.concat([info,df.iloc[:,18:23]],axis=1)\n",
    "    df5 = pd.concat([info,df.iloc[:,23:28]],axis=1)\n",
    "    df6 = pd.concat([info,df.iloc[:,28:33]],axis=1)\n",
    "    df7 = pd.concat([info,df.iloc[:,33:38]],axis=1)\n",
    "    df8 = pd.concat([info,df.iloc[:,38:43]],axis=1)\n",
    "    listofdf = [df1,df2,df3,df4,df5,df6,df7,df8]\n",
    "    for df in listofdf:\n",
    "        df.columns = ['C/A','UNIT','SCP','DATE','TIME','DESC','ENTRIES','EXITS']\n",
    "    wholedf = pd.concat([df1,df2,df3,df4,df5,df6,df7,df8],axis=0)\n",
    "    return(wholedf.sort_values(by = ['C/A','UNIT','SCP','DATE','TIME']))  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# combine all list into one dataframe\n",
    "data_2013 = pd.concat([clean(i) for i in dataframe_list], axis = 0)\n",
    "\n",
    "# set column index\n",
    "data_2013 = data_2013.reset_index()\n",
    "\n",
    "# drop the first index column\n",
    "data_2013.drop(['index'], axis = 1, inplace = True)\n",
    "\n",
    "# remove error data with 'DATE' == NaN\n",
    "data_2013_removed = data_2013[data_2013['DATE'].notnull()]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>C/A</th>\n",
       "      <th>UNIT</th>\n",
       "      <th>SCP</th>\n",
       "      <th>DATE</th>\n",
       "      <th>TIME</th>\n",
       "      <th>DESC</th>\n",
       "      <th>ENTRIES</th>\n",
       "      <th>EXITS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>01-01-13</td>\n",
       "      <td>03:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>3932284.0</td>\n",
       "      <td>1355714.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>01-01-13</td>\n",
       "      <td>07:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>3932299.0</td>\n",
       "      <td>1355721.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>01-01-13</td>\n",
       "      <td>11:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>3932327.0</td>\n",
       "      <td>1355774.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>01-01-13</td>\n",
       "      <td>15:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>3932427.0</td>\n",
       "      <td>1355811.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>01-01-13</td>\n",
       "      <td>19:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>3932662.0</td>\n",
       "      <td>1355849.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    C/A  UNIT       SCP      DATE      TIME     DESC    ENTRIES      EXITS\n",
       "0  A002  R051  02-00-00  01-01-13  03:00:00  REGULAR  3932284.0  1355714.0\n",
       "1  A002  R051  02-00-00  01-01-13  07:00:00  REGULAR  3932299.0  1355721.0\n",
       "2  A002  R051  02-00-00  01-01-13  11:00:00  REGULAR  3932327.0  1355774.0\n",
       "3  A002  R051  02-00-00  01-01-13  15:00:00  REGULAR  3932427.0  1355811.0\n",
       "4  A002  R051  02-00-00  01-01-13  19:00:00  REGULAR  3932662.0  1355849.0"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_2013_removed.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "<a id='3'></a>\n",
    "## 3. Merge with MTA data to create station name in turnstile datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "stationname = pd.read_excel('Remote-Booth-Station.xls')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "395"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check how many stations are there\n",
    "stationname['Station'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Remote</th>\n",
       "      <th>Booth</th>\n",
       "      <th>Station</th>\n",
       "      <th>Line Name</th>\n",
       "      <th>Division</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>R001</td>\n",
       "      <td>A060</td>\n",
       "      <td>WHITEHALL ST</td>\n",
       "      <td>R1</td>\n",
       "      <td>BMT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>R001</td>\n",
       "      <td>A058</td>\n",
       "      <td>WHITEHALL ST</td>\n",
       "      <td>R1</td>\n",
       "      <td>BMT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>R001</td>\n",
       "      <td>R101S</td>\n",
       "      <td>SOUTH FERRY</td>\n",
       "      <td>R1</td>\n",
       "      <td>IRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>R002</td>\n",
       "      <td>A077</td>\n",
       "      <td>FULTON ST</td>\n",
       "      <td>ACJZ2345</td>\n",
       "      <td>BMT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>R002</td>\n",
       "      <td>A081</td>\n",
       "      <td>FULTON ST</td>\n",
       "      <td>ACJZ2345</td>\n",
       "      <td>BMT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Remote  Booth       Station Line Name Division\n",
       "0   R001   A060  WHITEHALL ST        R1      BMT\n",
       "1   R001   A058  WHITEHALL ST        R1      BMT\n",
       "2   R001  R101S   SOUTH FERRY        R1      IRT\n",
       "3   R002   A077     FULTON ST  ACJZ2345      BMT\n",
       "4   R002   A081     FULTON ST  ACJZ2345      BMT"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check the dataframe\n",
    "stationname.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Remote', 'Booth', 'Station', 'Line Name', 'Division'], dtype='object')"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stationname.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check which index is unique for every turnstile, Remote or Booth\n",
    "#(stationname['Remote'].value_counts()>1).any()\n",
    "(stationname['Booth'].value_counts()>1).any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# keep only 'Booth' and 'Station' columns\n",
    "stationname = stationname[['Booth', 'Station','Line Name','Division']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Booth', 'Station', 'Line Name', 'Division'], dtype='object')"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stationname.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# change column index name in order to bring into correspondence with turnstiles dataframe\n",
    "stationname.rename(columns = {'Booth': 'C/A', 'Station': 'STATION','Line Name':'LINENAME','Division':'DIVISION'}, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check is there any missing values\n",
    "stationname['C/A'].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "768"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check duplicates\n",
    "len(stationname)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Combine into one single dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge turnstiles dataframe and stationname on 'C/A', delete duplicate rows\n",
    "final_df_2010_11 = pd.merge(data_2010_11_removed, stationname, on = ['C/A'])\n",
    "final_df_2010_11 = final_df_2010_11.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df_2010_11.to_csv('turnstile_2010_11.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#from 2013 to 20141011\n",
    "final_df_2013 = pd.merge(data_2013_removed, stationname, on = ['C/A'])\n",
    "final_df_2013=final_df_2013.drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Since the data only include until 20141011, now we are including other weeks in 2014, and save it in csv file for MTA turnstile data of 2013-2014"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "date_from = date(2014,10,11)\n",
    "date_to = date(2014,12,27)\n",
    "links_2014 = []\n",
    "\n",
    "\n",
    "pointer = date_from\n",
    "while pointer <= date_to:\n",
    "    links_2014.append('turnstile_' + pointer.strftime(\"%y%m%d\") + '.txt')\n",
    "    pointer += timedelta(days = 7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataframe_list2 = []\n",
    "for link in links_2014:\n",
    "    dataframe_list2.append(pd.read_csv(link, header = None, usecols=range(0, 11),low_memory=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "for df in dataframe_list2:\n",
    "        df.columns = ['C/A', 'UNIT', 'SCP', 'STATION', 'LINENAME', 'DIVISION', 'DATE', 'TIME',\n",
    "       'DESC', 'ENTRIES',\n",
    "       'EXIT']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# combine all list into one dataframe\n",
    "data_2014 = pd.concat([i for i in dataframe_list2], axis = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1898328, 11)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_2014.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### After we formated the data from two different year, now we are combining the data and save it in a csv file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_13_14 = pd.concat([final_df_2013, data_2014], join='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_13_14 .to_csv('turnstile_2013_14.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='4'></a>\n",
    "## 4. Processing data from 2015 to 2019"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "mydate = date(2015, 1, 3)\n",
    "date_to = date(2019,9,14)\n",
    "\n",
    "filenames_15_19 = []\n",
    "\n",
    "\n",
    "pointer = date_from\n",
    "while pointer <= date_to:\n",
    "    filenames_15_19.append('turnstile_' + pointer.strftime(\"%y%m%d\") + '.txt')\n",
    "    pointer += timedelta(days = 7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_15_19 = []\n",
    "for link in filenames_15_19:\n",
    "    df_15_19.append(pd.read_csv(link, header = None, usecols=range(0, 11),low_memory=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "for df in df_15_19:\n",
    "        df.columns = ['C/A', 'UNIT', 'SCP', 'STATION', 'LINENAME', 'DIVISION', 'DATE', 'TIME',\n",
    "       'DESC', 'ENTRIES',\n",
    "       'EXIT']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_2015_19 = pd.concat([i for i in df_15_19], axis = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(50340281, 11)"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_2015_19 .shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_2015_19.to_csv('turnstile_2015_19.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge all together "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_all = pd.concat([data_13_14,data_2015_19,final_df_2010_11], join='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_all.to_csv('turnstile_all.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "-a"
   ]
  }
 ],
 "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.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}