MTA Turnstile Data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from datetime import date, timedelta\n",
    "import urllib.request\n",
    "import shutil\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Batch download MTA turnstile files\n",
    "start_date = date(2019, 1, 5)\n",
    "end_date = date(2020, 1, 1)\n",
    "while start_date <= end_date:\n",
    "    filename = 'turnstile_'+start_date.strftime('%y%m%d')\n",
    "    url = 'http://web.mta.info/developers/data/nyct/turnstile/'+filename+'.txt'\n",
    "    print('Downloading '+filename+'.csv')\n",
    "    with urllib.request.urlopen(url) as response, open('./data/'+start_date.strftime('%Y')+'/'+filename+'.csv', 'wb') as out_file:\n",
    "        shutil.copyfileobj(response, out_file)\n",
    "    start_date += timedelta(weeks=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import station data with latitude and longitude\n",
    "pd.options.display.float_format = '{:.6f}'.format\n",
    "url = 'https://raw.githubusercontent.com/chriswhong/nycturnstiles/master/geocoded.csv'\n",
    "with urllib.request.urlopen(url) as response, open('./data/geocoded.csv', 'wb') as out_file:\n",
    "        shutil.copyfileobj(response, out_file)\n",
    "geocoded = pd.read_csv('./data/geocoded.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def filter_mta(data):\n",
    "    # Filter by regular records, remove PATH and Staten Island\n",
    "    data_filtered = data[(data['DESC']=='REGULAR') | (data['DESC']=='RECOVR AUD')]\n",
    "    data_filtered = data_filtered[(data_filtered['DIVISION']!='PTH') & (data_filtered['DIVISION']!='SRT')]\n",
    "\n",
    "    # Get diff for each row\n",
    "    data_filtered['ENTRIES_DIFF'] = data_filtered.groupby(['UNIT', 'SCP'])['ENTRIES'].diff()\n",
    "    data_filtered['EXITS_DIFF'] = data_filtered.groupby(['UNIT', 'SCP'])['EXITS'].diff()\n",
    "    \n",
    "    # Some turnstiles count backward??\n",
    "    data_filtered = data_filtered[(data_filtered['ENTRIES_DIFF']>=-5000) & (data_filtered['EXITS_DIFF']>=-5000)]\n",
    "    data_filtered['ENTRIES_DIFF'] = data_filtered['ENTRIES_DIFF'].abs()\n",
    "    data_filtered['EXITS_DIFF'] = data_filtered['EXITS_DIFF'].abs()\n",
    "\n",
    "    # Counts occasionally make huge jumps \n",
    "    data_filtered = data_filtered[(data_filtered['ENTRIES_DIFF']<10000) & (data_filtered['EXITS_DIFF']<10000)]\n",
    "\n",
    "    # Remove some columns\n",
    "    data_filtered.drop(columns=['STATION', 'LINENAME', 'DIVISION', 'DESC'], inplace=True)\n",
    "    \n",
    "    return data_filtered"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Batch process\n",
    "directory = './data/2019/'\n",
    "dir = os.fsencode(directory)\n",
    "dfs = []\n",
    "for file in os.listdir(dir):\n",
    "    filename = os.fsdecode(file)\n",
    "    print('Cleaning '+filename)\n",
    "\n",
    "    # Import MTA weekly turnstile data\n",
    "    data = pd.read_csv(directory+filename, parse_dates=['DATE'])\n",
    "    data.columns = data.columns.str.strip()\n",
    "    \n",
    "    data_filtered = filter_mta(data)\n",
    "    data_geocoded = pd.merge(data_filtered, geocoded, how='left', left_on=['UNIT'], right_on=['UNIT'])\n",
    "    \n",
    "    # Group and sum \n",
    "    data_cleaned = data_geocoded.groupby(['DATE', 'STATION', 'LATITUDE', 'LONGITUDE']).agg(\n",
    "        {'ENTRIES_DIFF': 'sum', 'EXITS_DIFF': 'sum', 'LINENAME': lambda x: x.iloc[0], 'UNIT': lambda x: x.iloc[0]})\n",
    "    data_cleaned = data_cleaned.astype({'ENTRIES_DIFF': 'int', 'EXITS_DIFF': 'int'})\n",
    "    \n",
    "    dfs.append(data_cleaned)\n",
    "    \n",
    "# Export\n",
    "pd.concat(dfs).to_csv('./cleaned/cleaned_2019.csv', float_format='%.6f')"
   ]
  }
 ],
 "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}