Preprocess - MTA Data Extract.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Clean data and prepare data for project #\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import datetime\n",
    "import urllib2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['http://web.mta.info/developers/data/nyct/turnstile/turnstile_141018.txt', 'http://web.mta.info/developers/data/nyct/turnstile/turnstile_141025.txt', 'http://web.mta.info/developers/data/nyct/turnstile/turnstile_141101.txt', 'http://web.mta.info/developers/data/nyct/turnstile/turnstile_141108.txt', 'http://web.mta.info/developers/data/nyct/turnstile/turnstile_141115.txt']\n",
      "[]\n",
      "[]\n",
      "['http://web.mta.info/developers/data/nyct/turnstile/turnstile_141018.txt', 'http://web.mta.info/developers/data/nyct/turnstile/turnstile_141025.txt', 'http://web.mta.info/developers/data/nyct/turnstile/turnstile_141101.txt', 'http://web.mta.info/developers/data/nyct/turnstile/turnstile_141108.txt', 'http://web.mta.info/developers/data/nyct/turnstile/turnstile_141115.txt']\n",
      "['http://web.mta.info/developers/data/nyct/turnstile/turnstile_141206.txt', 'http://web.mta.info/developers/data/nyct/turnstile/turnstile_141213.txt', 'http://web.mta.info/developers/data/nyct/turnstile/turnstile_141220.txt', 'http://web.mta.info/developers/data/nyct/turnstile/turnstile_141227.txt']\n"
     ]
    }
   ],
   "source": [
    "#get files address for 2016 from MTA\n",
    "\n",
    "files_array = [] # total files\n",
    "spring_files = [] \n",
    "summer_files = []\n",
    "fall_files = []\n",
    "winter_files = []\n",
    "\n",
    "# get files address and sort by season\n",
    "def sort_by_season(t,addr):\n",
    "    month = str(t.strftime(\"%m\"))\n",
    "    if month[0] == '0':\n",
    "        month = month[1]\n",
    "        \n",
    "    if month == '3' or month == '4' or month == '5':\n",
    "        spring_files.append(addr)\n",
    "    elif month == '6' or month == '7' or month == '8':\n",
    "        summer_files.append(addr)\n",
    "    elif month == '9' or month == '10' or month == '11':\n",
    "        fall_files.append(addr)\n",
    "    else:\n",
    "        winter_files.append(addr)\n",
    "\n",
    "# edit start_date and end date to get range of file address\n",
    "start_date = datetime.date(2014,10,18)\n",
    "end_date = datetime.date(2014,12,27)\n",
    "url = \"http://web.mta.info/developers/data/nyct/turnstile/turnstile_\"\n",
    "while (start_date <= end_date):\n",
    "    address = url + str(start_date.strftime(\"%y%m%d\"))+\".txt\"\n",
    "    files_array.append(address)\n",
    "    sort_by_season(start_date,address) \n",
    "    start_date += datetime.timedelta(days=7)\n",
    "    \n",
    "print files_array[:5]\n",
    "print spring_files[:5]\n",
    "print summer_files[:5]\n",
    "print fall_files[:5]\n",
    "print winter_files[:5]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('A060', 'R001', '00-00-00', 'WHITEHALL ST', 'R1', 'BMT', '10/11/2014', '01:00:00', 'REGULAR', '0000805439', '000114108'), ('A060', 'R001', '00-00-00', 'WHITEHALL ST', 'R1', 'BMT', '10/11/2014', '05:00:00', 'REGULAR', '0000805459', '000114114'), ('A060', 'R001', '00-00-00', 'WHITEHALL ST', 'R1', 'BMT', '10/11/2014', '09:00:00', 'REGULAR', '0000805589', '000114125'), ('A060', 'R001', '00-00-00', 'WHITEHALL ST', 'R1', 'BMT', '10/11/2014', '13:00:00', 'REGULAR', '0000805834', '000114151'), ('A060', 'R001', '00-00-00', 'WHITEHALL ST', 'R1', 'BMT', '10/11/2014', '17:00:00', 'REGULAR', '0000806150', '000114190'), ('A060', 'R001', '00-00-00', 'WHITEHALL ST', 'R1', 'BMT', '10/11/2014', '21:00:00', 'REGULAR', '0000806431', '000114230'), ('A060', 'R001', '00-00-00', 'WHITEHALL ST', 'R1', 'BMT', '10/12/2014', '01:00:00', 'REGULAR', '0000806591', '000114253'), ('A060', 'R001', '00-00-00', 'WHITEHALL ST', 'R1', 'BMT', '10/12/2014', '05:00:00', 'REGULAR', '0000806609', '000114261'), ('A060', 'R001', '00-00-00', 'WHITEHALL ST', 'R1', 'BMT', '10/12/2014', '09:00:00', 'REGULAR', '0000806670', '000114299'), ('A060', 'R001', '00-00-00', 'WHITEHALL ST', 'R1', 'BMT', '10/12/2014', '13:00:00', 'REGULAR', '0000807189', '000114352')]\n"
     ]
    }
   ],
   "source": [
    "#use these functions to get data that after 10/18/2014 - new data\n",
    "\n",
    "my_array = []\n",
    "\n",
    "# convert json to array\n",
    "def convert_to_array(data,arr):\n",
    "    my_line = data\n",
    "    my_line = my_line.split(',')\n",
    "    if len(my_line) == 11:\n",
    "        my_line_10 = my_line[10][:9]\n",
    "        my_line.remove(my_line[10])\n",
    "        my_line.append(my_line_10)\n",
    "        my_line = tuple(my_line)\n",
    "        arr.append(my_line)\n",
    "    \n",
    "# get data from url\n",
    "def get_data_from_url(input_file,arr):\n",
    "    data = urllib2.urlopen(input_file)\n",
    "    iter_data = iter(data)\n",
    "    next(iter_data)\n",
    "    for i in data:\n",
    "        convert_to_array(i,arr)\n",
    "        \n",
    "        \n",
    "#use for loop to get all the data\n",
    "get_data_from_url(files_array[0],my_array)\n",
    "\n",
    "print(my_array[:10])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[['A002', 'R051', '02-00-00', '12-25-10', '03:00:00', 'REGULAR', '002987895', '001031727'], ['A002', 'R051', '02-00-00', '12-25-10', '07:00:00', 'REGULAR', '002987896', '001031732'], ['A002', 'R051', '02-00-00', '12-25-10', '09:01:47', 'DOOR', '002987900', '001031744'], ['A002', 'R051', '02-00-00', '12-25-10', '09:03:29', 'OPEN', '002987900', '001031744'], ['A002', 'R051', '02-00-00', '12-25-10', '09:05:29', 'DOOR', '002987900', '001031744'], ['A002', 'R051', '02-00-00', '12-25-10', '09:17:09', 'OPEN', '002987905', '001031750'], ['A002', 'R051', '02-00-00', '12-25-10', '11:00:00', 'DOOR', '002987913', '001031766'], ['A002', 'R051', '02-00-00', '12-25-10', '15:00:00', 'OPEN', '002987975', '001031788'], ['A002', 'R051', '02-00-00', '12-25-10', '19:00:00', 'REGULAR', '002988054', '001031812'], ['A002', 'R051', '02-00-00', '12-25-10', '23:00:00', 'REGULAR', '002988082', '001031818']]\n"
     ]
    }
   ],
   "source": [
    "#use these functions to get data that before 10/18/2014 - old data\n",
    "\n",
    "my_array_old = []\n",
    "\n",
    "def convert_to_array_old(data,arr):\n",
    "    ca = data.split(',')[0]\n",
    "    booth = data.split(',')[1]\n",
    "    ignore = data.split(',')[2]\n",
    "    i=iter(data.split(',')[3:])\n",
    "    remaining = map(\",\".join,zip(*[i]*5))\n",
    "    for parts in remaining:\n",
    "        buildString = []\n",
    "        parts = parts.split(',')\n",
    "        buildString.append(ca)\n",
    "        buildString.append(booth)\n",
    "        buildString.append(ignore)\n",
    "        for i in parts:\n",
    "            if '\\n' in i:\n",
    "                buildString.append(i[:9])\n",
    "            else:\n",
    "                buildString.append(i)\n",
    "                \n",
    "        arr.append(buildString)\n",
    "    \n",
    "def get_data_from_url_old(input_file,arr):\n",
    "    data = urllib2.urlopen(input_file)\n",
    "    iter_data = iter(data)\n",
    "    for i in data:\n",
    "        convert_to_array_old(i,arr)\n",
    "\n",
    "get_data_from_url_old(files_array[0],my_array_old)\n",
    "\n",
    "print(my_array_old[:10])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1868519"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# store a year data in an array, this work for data after 10/18/2014 - new data\n",
    "array_2016 = []\n",
    "def get_a_year_data(m_file,m_arr):\n",
    "    for i in xrange(0,len(m_file)):\n",
    "        get_data_from_url(m_file[i],m_arr)\n",
    "\n",
    "get_a_year_data(files_array,array_2016)\n",
    "\n",
    "len(array_2016)\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# store a year data in an array, this work for data before 10/18/2014 - old data\n",
    "array_2011 = []\n",
    "def get_a_year_data_old(m_file,m_arr):\n",
    "    for i in xrange(0,len(m_file)):\n",
    "        get_data_from_url_old(m_file[i],m_arr)\n",
    "\n",
    "get_a_year_data_old(files_array,array_2011)\n",
    "\n",
    "len(array_2011)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# hard code for weird address in 2011\n",
    "f1='http://web.mta.info/developers/data/nyct/turnstile/turnstile_111219.txt'\n",
    "f2='http://web.mta.info/developers/data/nyct/turnstile/turnstile_111224.txt'\n",
    "f3='http://web.mta.info/developers/data/nyct/turnstile/turnstile_111231.txt'\n",
    "get_data_from_url_old(f1,array_2011)\n",
    "get_data_from_url_old(f2,array_2011)\n",
    "get_data_from_url_old(f3,array_2011)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# export array to csv\n",
    "import csv\n",
    "with open(\"output.csv\", \"wb\") as f:\n",
    "    writer = csv.writer(f)\n",
    "    writer.writerows(array_2016)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def mapper1(row):\n",
    "    return (row[3])\n",
    "\n",
    "def reducer1(counts, pair):\n",
    "    counts[pair] = counts.get(pair, 0)+1\n",
    "    return counts\n",
    "\n",
    "#get popular station, this function will take time to run.\n",
    "def get_popular_station(m_file,m_arr):\n",
    "    for i in xrange(0,len(m_file)):\n",
    "        get_data_input_url(m_file[i],m_arr)\n",
    "    \n",
    "    output1 = reduce(reducer1, map(mapper1, m_arr), {}).items()\n",
    "    output1.sort(key=lambda x: x[1], reverse=True)\n",
    "\n",
    "    print output1[:10]\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [python2]",
   "language": "python",
   "name": "Python [python2]"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}