1. Setup Project.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NYC Taxi Dataset Project - Setup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Overall Steps\n",
    "\n",
    "**Step 0:** Prerequisites\n",
    "\n",
    "**Step 1:** Setup Spark cluster in AWS (do this from the shell/git bash)\n",
    "\n",
    "**Step 2:** Sanity check to ensure Spark & S3 are setup properly\n",
    "\n",
    "**Step 3:** Upload data files into S3 (you can skip this and use my S3 location)\n",
    "\n",
    "**Step 4:** Check cluster performance on dataset\n",
    "\n",
    "In the end I have also added other **uselful notes**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note: Step 1 is based on the CS109 [instructions](https://piazza.com/class/icf0cypdc3243c?cid=1369). However there are modifications for optimizing performance for this project\n",
    "\n",
    "### Step 0: Prerequisites\n",
    "\n",
    "1. You need the files CS109.pem and credentials.csv.If you had followed the cs109 instructions (for lab8 or HW5) you will already have these files.\n",
    "\n",
    "2. Create a new directory in your machine for the project and copy the following files into it:\n",
    "    \n",
    "    a) CS109.pem\n",
    "    \n",
    "    b) credentials.csv\n",
    "    \n",
    "    c) Setup Project.ipynb (this notebook)\n",
    "    \n",
    "    d) myConfig.json"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 1: Setup Spark cluster in AWS & perform sanity check"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "**Note: Run all the items in Step 1 from unix shell/Git bash (not from the Jupyter notebook)**\n",
    "\n",
    "#### Step 1a) Create the cluster in AWS: run the following command in unix shell/Git bash (you may change the instance type/count)\n",
    "\n",
    "**Enhancements incorporated to the script**: \n",
    "1. Setup Spark to use the maximum available resources (the myConfig.json file has the instructions)\n",
    "2. Download the admin application Ganglia"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "export CLUSTER_ID=`aws emr create-cluster --name \"CS109 Proj Spark cluster\" \\\n",
    "--release-label emr-4.2.0 --applications Name=Spark Name=Ganglia --ec2-attributes KeyName=CS109 \\\n",
    "--instance-type c3.2xlarge --instance-count 5 --configurations file://myConfig.json --use-default-roles \\\n",
    "--bootstrap-actions Path=s3://cs109-2015/install-anaconda-emr,Name=Install_Anaconda \\\n",
    "--query 'ClusterId' --output text` && echo $CLUSTER_ID"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 1c) Wait for the cluster to be ready: AWS web console has to show \"WAITING\""
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "aws emr describe-cluster --cluster-id $CLUSTER_ID --query 'Cluster.Status.State' --output text"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 1d)  Get the cluster master's IP:"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "export DNS_NAME=`aws emr describe-cluster --cluster-id $CLUSTER_ID \\\n",
    "--query 'Cluster.MasterPublicDnsName' --output text` && echo $DNS_NAME"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 1e) Run the script to configure Spark "
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "ssh -o ServerAliveInterval=10 -i CS109.pem hadoop@$DNS_NAME 'sh configure-spark.sh'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 1f) Create an SSH tunel to the AWS box and connect to the cluster. This command assumes your SSH key is on the same directory you are invoking the SSH command from. At the end of this you will be in a terminal session on the cluster's master node."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "ssh -o ServerAliveInterval=10 -i CS109.pem hadoop@$DNS_NAME -L 8989:localhost:8888"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "pyspark"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 1f) Open your browser and got to http://localhost:8989\n",
    "\n",
    "Note: The notebook you open will **already** have the spark context set up for you."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 2: Sanity check to ensure Spark & S3 are setup properly"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 2a) Upload this Jupyter Notebook using the console from http://localhost:8989\n",
    "\n",
    "Note: all the steps in Step 2 are to be executed from the Jupyter Notebook iteself"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 2b) Setup the SparkContext (automatically setup by YARN)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pyspark.context.SparkContext at 0x7f6b79cfce50>"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "u'yarn-client'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Just an informational message\n",
    "sc.master"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 2c) Spark sanity check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sys\n",
    "rdd = sc.parallelize(xrange(10),10)\n",
    "aa = rdd.map(lambda x: sys.version)\n",
    "aa.cache()\n",
    "aa.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 2d) S3 sanity check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/home/hadoop\n",
      "mkdir: cannot create directory ‘test_s3’: File exists\n",
      "--2015-12-02 04:43:25--  http://en.wiktionary.org/wiki/awesome\n",
      "Resolving en.wiktionary.org (en.wiktionary.org)... 208.80.154.224, 2620:0:861:ed1a::1\n",
      "Connecting to en.wiktionary.org (en.wiktionary.org)|208.80.154.224|:80... connected.\n",
      "HTTP request sent, awaiting response... 301 TLS Redirect\n",
      "Location: https://en.wiktionary.org/wiki/awesome [following]\n",
      "--2015-12-02 04:43:26--  https://en.wiktionary.org/wiki/awesome\n",
      "Connecting to en.wiktionary.org (en.wiktionary.org)|208.80.154.224|:443... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: unspecified [text/html]\n",
      "Saving to: ‘test_s3/awesome.1’\n",
      "\n",
      "awesome.1               [ <=>                  ]  65.39K  --.-KB/s   in 0.003s \n",
      "\n",
      "2015-12-02 04:43:26 (22.3 MB/s) - ‘test_s3/awesome.1’ saved [66964]\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#Get a wikipedia page and store it in a local folder\n",
    "!pwd\n",
    "!mkdir test_s3\n",
    "!wget http://en.wiktionary.org/wiki/awesome -P test_s3/ --trust-server-names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Create a S3 bucket : provide a unique name below: replace the \"testaaset1\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "make_bucket: s3://sdaultonbucket1/\n",
      "upload: test_s3/awesome to s3://sdaultonbucket1/awesome\n"
     ]
    }
   ],
   "source": [
    "# Add the downloaded file to S3: remeber to replace \"testaaset1\" with a unique bucket name\n",
    "!aws s3 mb s3://sdaultonbucket1\n",
    "    \n",
    "# Add the downloaded file to the test bucket in S3: remeber to replace \"testaaset1\" with a unique bucket name\n",
    "!aws s3 cp test_s3/awesome s3://sdaultonbucket1/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Test if you are able to lookup the S3 file from Spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "443"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "testS3RDD = sc.textFile(\"s3://sdaultonbucket1/awesome\")\n",
    "testS3RDD.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Congrats! Now you have a working spark cluster with ability to connect with S3!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 3: Upload data files into S3 \n",
    "\n",
    "Note: You **can skip** as i have uploaded the files to my S3 location ** s3://testsetu/nyc/  **\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/home/hadoop\r\n"
     ]
    }
   ],
   "source": [
    "!pwd\n",
    "!mkdir datafiles"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Download the data files into local folder\n",
    "\n",
    "**Note**: We are downloading the data from **2013 onwards only** - though data is available from 2009\n",
    "\n",
    "**Data source**: http://www.nyc.gov/html/tlc/html/about/trip_record_data.shtml"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "make_bucket: s3://sdaultonbucket1/nyc/\n",
      "mount: /dev/xvdb is already mounted or /home/hadoop/datafiles busy\n",
      "       /dev/xvdb is already mounted on /mnt\n",
      "       /dev/xvdb is already mounted on /media/ephemeral0\n",
      "       /dev/xvdb is already mounted on /home/hadoop/datafiles\n",
      "mkdir: cannot create directory ‘datafiles/nyc’: File exists\n",
      "mkdir: cannot create directory ‘datafiles/nycg’: File exists\n"
     ]
    }
   ],
   "source": [
    "#Setup the S3 bucket for storing the nyc data\n",
    "!aws s3 mb s3://sdaultonbucket1/nyc\n",
    "    \n",
    "#Use the ephemerial/tmp space in EC2 node    \n",
    "!mkdir datafiles\n",
    "!sudo mount /dev/xvdb datafiles\n",
    "!mkdir datafiles/nyc\n",
    "\n",
    "#for green cabs\n",
    "!mkdir datafiles/nycg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Setup the variables\n",
    "\n",
    "baseUrl = \"http://storage.googleapis.com/tlc-trip-data/\"\n",
    "#Yellow/green cab filename prefix\n",
    "yCabFNPrefix = \"/yellow_tripdata_\"\n",
    "gCabFNPrefix = \"/green_tripdata_\"\n",
    "\n",
    "#Availaiblity of data set by month & year\n",
    "yDict = {}\n",
    "gDict = {}\n",
    "\n",
    "#availablity for Yellow cab\n",
    "yDict[2015] = range(1,7) #available till jun 2015\n",
    "yDict[2014] = range(1,13)\n",
    "yDict[2013] = range(1,13)\n",
    "\n",
    "#availablity for Green cab\n",
    "gDict[2015] = range(1,7) #available till jun 2015\n",
    "gDict[2014] = range(1,13)\n",
    "gDict[2013] = range(8,13) #avialable only from august 2013"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#  Yellow cab data file name list\n",
    "# file name is of format:  yellow_tripdata_2015-01.csv\n",
    "yCabUrls = []\n",
    "yCabFilenames = []\n",
    "for year, monthList in yDict.iteritems():\n",
    "    yearStr = str(year)\n",
    "    for month in monthList:\n",
    "        monthStr = str(month)\n",
    "        if len(monthStr) == 1:\n",
    "            monthStr = \"0\"+monthStr    \n",
    "        url = baseUrl+yearStr+yCabFNPrefix+yearStr+'-'+monthStr+\".csv\"\n",
    "        yCabUrls.append(url)\n",
    "        yCabFilenames.append(yCabFNPrefix+yearStr+'-'+monthStr+\".csv\")\n",
    "\n",
    "#  green cab data file name list\n",
    "gCabUrls = []\n",
    "gCabFilenames = []\n",
    "for year, monthList in gDict.iteritems():\n",
    "    yearStr = str(year)\n",
    "    for month in monthList:\n",
    "        monthStr = str(month)\n",
    "        if len(monthStr) == 1:\n",
    "            monthStr = \"0\"+monthStr    \n",
    "        url = baseUrl+yearStr+gCabFNPrefix+yearStr+'-'+monthStr+\".csv\"\n",
    "        gCabFilenames.append(gCabFNPrefix+yearStr+'-'+monthStr+\".csv\")\n",
    "        gCabUrls.append(url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--2015-12-02 04:44:24--  http://storage.googleapis.com/tlc-trip-data/2013/yellow_tripdata_2013-01.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 74.125.22.128, 2607:f8b0:400d:c09::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|74.125.22.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2472351469 (2.3G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2013-01.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   2.30G   112MB/s   in 20s    \n",
      "\n",
      "2015-12-02 04:44:45 (118 MB/s) - ‘datafiles/nyc/yellow_tripdata_2013-01.csv’ saved [2472351469/2472351469]\n",
      "\n",
      "--2015-12-02 04:44:45--  http://storage.googleapis.com/tlc-trip-data/2013/yellow_tripdata_2013-02.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 74.125.22.128, 2607:f8b0:400d:c09::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|74.125.22.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2344381323 (2.2G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2013-02.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   2.18G   113MB/s   in 19s    \n",
      "\n",
      "2015-12-02 04:45:04 (116 MB/s) - ‘datafiles/nyc/yellow_tripdata_2013-02.csv’ saved [2344381323/2344381323]\n",
      "\n",
      "--2015-12-02 04:45:05--  http://storage.googleapis.com/tlc-trip-data/2013/yellow_tripdata_2013-03.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 74.125.22.128, 2607:f8b0:400d:c09::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|74.125.22.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2639810975 (2.5G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2013-03.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   2.46G   113MB/s   in 22s    \n",
      "\n",
      "2015-12-02 04:45:27 (116 MB/s) - ‘datafiles/nyc/yellow_tripdata_2013-03.csv’ saved [2639810975/2639810975]\n",
      "\n",
      "--2015-12-02 04:45:27--  http://storage.googleapis.com/tlc-trip-data/2013/yellow_tripdata_2013-04.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 74.125.22.128, 2607:f8b0:400d:c09::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|74.125.22.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2533430839 (2.4G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2013-04.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   2.36G  93.3MB/s   in 21s    \n",
      "\n",
      "2015-12-02 04:45:49 (114 MB/s) - ‘datafiles/nyc/yellow_tripdata_2013-04.csv’ saved [2533430839/2533430839]\n",
      "\n",
      "--2015-12-02 04:45:49--  http://storage.googleapis.com/tlc-trip-data/2013/yellow_tripdata_2013-05.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 74.125.22.128, 2607:f8b0:400d:c09::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|74.125.22.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2565221965 (2.4G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2013-05.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   2.39G   113MB/s   in 21s    \n",
      "\n",
      "2015-12-02 04:46:10 (118 MB/s) - ‘datafiles/nyc/yellow_tripdata_2013-05.csv’ saved [2565221965/2565221965]\n",
      "\n",
      "--2015-12-02 04:46:10--  http://storage.googleapis.com/tlc-trip-data/2013/yellow_tripdata_2013-06.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 74.125.22.128, 2607:f8b0:400d:c09::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|74.125.22.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2417268120 (2.3G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2013-06.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   2.25G   119MB/s   in 20s    \n",
      "\n",
      "2015-12-02 04:46:30 (116 MB/s) - ‘datafiles/nyc/yellow_tripdata_2013-06.csv’ saved [2417268120/2417268120]\n",
      "\n",
      "--2015-12-02 04:46:30--  http://storage.googleapis.com/tlc-trip-data/2013/yellow_tripdata_2013-07.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 74.125.22.128, 2607:f8b0:400d:c09::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|74.125.22.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2322372042 (2.2G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2013-07.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   2.16G   117MB/s   in 19s    \n",
      "\n",
      "2015-12-02 04:46:50 (117 MB/s) - ‘datafiles/nyc/yellow_tripdata_2013-07.csv’ saved [2322372042/2322372042]\n",
      "\n",
      "--2015-12-02 04:46:50--  http://storage.googleapis.com/tlc-trip-data/2013/yellow_tripdata_2013-08.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c09::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2103004295 (2.0G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2013-08.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   1.96G   107MB/s   in 17s    \n",
      "\n",
      "2015-12-02 04:47:08 (117 MB/s) - ‘datafiles/nyc/yellow_tripdata_2013-08.csv’ saved [2103004295/2103004295]\n",
      "\n",
      "--2015-12-02 04:47:08--  http://storage.googleapis.com/tlc-trip-data/2013/yellow_tripdata_2013-09.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c09::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2380395466 (2.2G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2013-09.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   2.22G   125MB/s   in 20s    \n",
      "\n",
      "2015-12-02 04:47:28 (115 MB/s) - ‘datafiles/nyc/yellow_tripdata_2013-09.csv’ saved [2380395466/2380395466]\n",
      "\n",
      "--2015-12-02 04:47:28--  http://storage.googleapis.com/tlc-trip-data/2013/yellow_tripdata_2013-10.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c09::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2532937359 (2.4G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2013-10.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   2.36G   111MB/s   in 21s    \n",
      "\n",
      "2015-12-02 04:47:49 (117 MB/s) - ‘datafiles/nyc/yellow_tripdata_2013-10.csv’ saved [2532937359/2532937359]\n",
      "\n",
      "--2015-12-02 04:47:49--  http://storage.googleapis.com/tlc-trip-data/2013/yellow_tripdata_2013-11.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c09::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2420444667 (2.3G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2013-11.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   2.25G   122MB/s   in 20s    \n",
      "\n",
      "2015-12-02 04:48:09 (118 MB/s) - ‘datafiles/nyc/yellow_tripdata_2013-11.csv’ saved [2420444667/2420444667]\n",
      "\n",
      "--2015-12-02 04:48:09--  http://storage.googleapis.com/tlc-trip-data/2013/yellow_tripdata_2013-12.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c09::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2350229417 (2.2G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2013-12.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   2.19G   111MB/s   in 19s    \n",
      "\n",
      "2015-12-02 04:48:29 (116 MB/s) - ‘datafiles/nyc/yellow_tripdata_2013-12.csv’ saved [2350229417/2350229417]\n",
      "\n",
      "--2015-12-02 04:48:29--  http://storage.googleapis.com/tlc-trip-data/2014/yellow_tripdata_2014-01.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c09::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2324817062 (2.2G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2014-01.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   2.17G   115MB/s   in 19s    \n",
      "\n",
      "2015-12-02 04:48:48 (117 MB/s) - ‘datafiles/nyc/yellow_tripdata_2014-01.csv’ saved [2324817062/2324817062]\n",
      "\n",
      "--2015-12-02 04:48:49--  http://storage.googleapis.com/tlc-trip-data/2014/yellow_tripdata_2014-02.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c09::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2205460986 (2.1G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2014-02.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   2.05G   110MB/s   in 18s    \n",
      "\n",
      "2015-12-02 04:49:07 (115 MB/s) - ‘datafiles/nyc/yellow_tripdata_2014-02.csv’ saved [2205460986/2205460986]\n",
      "\n",
      "--2015-12-02 04:49:07--  http://storage.googleapis.com/tlc-trip-data/2014/yellow_tripdata_2014-03.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 74.125.22.128, 2607:f8b0:400d:c09::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|74.125.22.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2604044841 (2.4G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2014-03.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   2.42G   118MB/s   in 28s    \n",
      "\n",
      "2015-12-02 04:49:35 (90.2 MB/s) - ‘datafiles/nyc/yellow_tripdata_2014-03.csv’ saved [2604044841/2604044841]\n",
      "\n",
      "--2015-12-02 04:49:35--  http://storage.googleapis.com/tlc-trip-data/2014/yellow_tripdata_2014-04.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 74.125.22.128, 2607:f8b0:400d:c04::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|74.125.22.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2463299452 (2.3G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2014-04.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   2.29G   115MB/s   in 20s    \n",
      "\n",
      "2015-12-02 04:49:55 (119 MB/s) - ‘datafiles/nyc/yellow_tripdata_2014-04.csv’ saved [2463299452/2463299452]\n",
      "\n",
      "--2015-12-02 04:49:55--  http://storage.googleapis.com/tlc-trip-data/2014/yellow_tripdata_2014-05.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 74.125.22.128, 2607:f8b0:400d:c04::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|74.125.22.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2488952100 (2.3G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2014-05.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   2.32G   109MB/s   in 20s    \n",
      "\n",
      "2015-12-02 04:50:16 (116 MB/s) - ‘datafiles/nyc/yellow_tripdata_2014-05.csv’ saved [2488952100/2488952100]\n",
      "\n",
      "--2015-12-02 04:50:17--  http://storage.googleapis.com/tlc-trip-data/2014/yellow_tripdata_2014-06.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 74.125.22.128, 2607:f8b0:400d:c04::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|74.125.22.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2326220589 (2.2G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2014-06.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   2.17G   112MB/s   in 19s    \n",
      "\n",
      "2015-12-02 04:50:36 (117 MB/s) - ‘datafiles/nyc/yellow_tripdata_2014-06.csv’ saved [2326220589/2326220589]\n",
      "\n",
      "--2015-12-02 04:50:36--  http://storage.googleapis.com/tlc-trip-data/2014/yellow_tripdata_2014-07.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 74.125.22.128, 2607:f8b0:400d:c04::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|74.125.22.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2204602640 (2.1G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2014-07.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   2.05G   118MB/s   in 18s    \n",
      "\n",
      "2015-12-02 04:50:54 (118 MB/s) - ‘datafiles/nyc/yellow_tripdata_2014-07.csv’ saved [2204602640/2204602640]\n",
      "\n",
      "--2015-12-02 04:50:54--  http://storage.googleapis.com/tlc-trip-data/2014/yellow_tripdata_2014-08.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 74.125.22.128, 2607:f8b0:400d:c04::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|74.125.22.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2133936569 (2.0G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2014-08.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   1.99G   115MB/s   in 17s    \n",
      "\n",
      "2015-12-02 04:51:12 (118 MB/s) - ‘datafiles/nyc/yellow_tripdata_2014-08.csv’ saved [2133936569/2133936569]\n",
      "\n",
      "--2015-12-02 04:51:12--  http://storage.googleapis.com/tlc-trip-data/2014/yellow_tripdata_2014-09.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 74.125.22.128, 2607:f8b0:400d:c04::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|74.125.22.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2260069774 (2.1G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2014-09.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   2.10G   118MB/s   in 19s    \n",
      "\n",
      "2015-12-02 04:51:32 (114 MB/s) - ‘datafiles/nyc/yellow_tripdata_2014-09.csv’ saved [2260069774/2260069774]\n",
      "\n",
      "--2015-12-02 04:51:32--  http://storage.googleapis.com/tlc-trip-data/2014/yellow_tripdata_2014-10.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 74.125.22.128, 2607:f8b0:400d:c04::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|74.125.22.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2409216587 (2.2G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2014-10.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   2.24G  75.3MB/s   in 25s    \n",
      "\n",
      "2015-12-02 04:51:57 (92.4 MB/s) - ‘datafiles/nyc/yellow_tripdata_2014-10.csv’ saved [2409216587/2409216587]\n",
      "\n",
      "--2015-12-02 04:51:57--  http://storage.googleapis.com/tlc-trip-data/2014/yellow_tripdata_2014-11.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 74.125.22.128, 2607:f8b0:400d:c04::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|74.125.22.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2235168890 (2.1G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2014-11.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   2.08G  56.7MB/s   in 37s    \n",
      "\n",
      "2015-12-02 04:52:35 (57.0 MB/s) - ‘datafiles/nyc/yellow_tripdata_2014-11.csv’ saved [2235168890/2235168890]\n",
      "\n",
      "--2015-12-02 04:52:35--  http://storage.googleapis.com/tlc-trip-data/2014/yellow_tripdata_2014-12.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 74.125.22.128, 2607:f8b0:400d:c04::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|74.125.22.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2198671160 (2.0G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2014-12.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   2.05G   121MB/s   in 36s    \n",
      "\n",
      "2015-12-02 04:53:11 (58.7 MB/s) - ‘datafiles/nyc/yellow_tripdata_2014-12.csv’ saved [2198671160/2198671160]\n",
      "\n",
      "--2015-12-02 04:53:12--  http://storage.googleapis.com/tlc-trip-data/2015/yellow_tripdata_2015-01.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 74.125.22.128, 2607:f8b0:400d:c04::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|74.125.22.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 1985964692 (1.8G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2015-01.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   1.85G   121MB/s   in 20s    \n",
      "\n",
      "2015-12-02 04:53:32 (94.1 MB/s) - ‘datafiles/nyc/yellow_tripdata_2015-01.csv’ saved [1985964692/1985964692]\n",
      "\n",
      "--2015-12-02 04:53:32--  http://storage.googleapis.com/tlc-trip-data/2015/yellow_tripdata_2015-02.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 74.125.29.128, 2607:f8b0:400d:c04::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|74.125.29.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 1945357622 (1.8G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2015-02.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   1.81G  35.0MB/s   in 27s    \n",
      "\n",
      "2015-12-02 04:54:00 (67.9 MB/s) - ‘datafiles/nyc/yellow_tripdata_2015-02.csv’ saved [1945357622/1945357622]\n",
      "\n",
      "--2015-12-02 04:54:00--  http://storage.googleapis.com/tlc-trip-data/2015/yellow_tripdata_2015-03.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 74.125.29.128, 2607:f8b0:400d:c04::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|74.125.29.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2087971794 (1.9G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2015-03.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   1.94G  33.3MB/s   in 28s    \n",
      "\n",
      "2015-12-02 04:54:29 (70.8 MB/s) - ‘datafiles/nyc/yellow_tripdata_2015-03.csv’ saved [2087971794/2087971794]\n",
      "\n",
      "--2015-12-02 04:54:29--  http://storage.googleapis.com/tlc-trip-data/2015/yellow_tripdata_2015-04.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 74.125.29.128, 2607:f8b0:400d:c08::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|74.125.29.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2046225765 (1.9G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2015-04.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   1.91G   123MB/s   in 17s    \n",
      "\n",
      "2015-12-02 04:54:46 (117 MB/s) - ‘datafiles/nyc/yellow_tripdata_2015-04.csv’ saved [2046225765/2046225765]\n",
      "\n",
      "--2015-12-02 04:54:46--  http://storage.googleapis.com/tlc-trip-data/2015/yellow_tripdata_2015-05.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 74.125.29.128, 2607:f8b0:400d:c08::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|74.125.29.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 2061869121 (1.9G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2015-05.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   1.92G   120MB/s   in 17s    \n",
      "\n",
      "2015-12-02 04:55:03 (118 MB/s) - ‘datafiles/nyc/yellow_tripdata_2015-05.csv’ saved [2061869121/2061869121]\n",
      "\n",
      "--2015-12-02 04:55:03--  http://storage.googleapis.com/tlc-trip-data/2015/yellow_tripdata_2015-06.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 74.125.29.128, 2607:f8b0:400d:c08::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|74.125.29.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 1932049357 (1.8G) [text/csv]\n",
      "Saving to: ‘datafiles/nyc/yellow_tripdata_2015-06.csv’\n",
      "\n",
      "yellow_tripdata_201 100%[=====================>]   1.80G  80.7MB/s   in 21s    \n",
      "\n",
      "2015-12-02 04:55:25 (86.0 MB/s) - ‘datafiles/nyc/yellow_tripdata_2015-06.csv’ saved [1932049357/1932049357]\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#Download the yellow cab files\n",
    "for url in yCabUrls:\n",
    "    !wget $url -P datafiles/nyc --trust-server-names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "65G\tdatafiles/nyc\r\n"
     ]
    }
   ],
   "source": [
    "#Disk space of the Yellow Cab files\n",
    "!du -mh datafiles/nyc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def preprocess_data(cabFilenames, isYellow):\n",
    "    \"\"\"\n",
    "    Function that takes a list of filenames (strings) and a boolean as parameters.\n",
    "    Removes the header from the each file and verifies the schema of the data.\n",
    "    \"\"\"\n",
    "    # Dictionary where key = filename, value = (schema, bool==True if there is a blank line after header)\n",
    "    file_schemas = {}\n",
    "    prefix = 'datafiles/nycg/'\n",
    "    if isYellow:\n",
    "        prefix = 'datafiles/nyc/'\n",
    "        \n",
    "    for filename in cabFilenames:\n",
    "        # Fetch schema\n",
    "        with open(prefix+filename,'r') as in_fp:\n",
    "            #read first two lines\n",
    "            lines = [in_fp.readline() for i in xrange(2)]\n",
    "\n",
    "        # now open again to write out\n",
    "        file_schemas[filename] = (tuple(lines[0].split(',')), lines[1]=='\\r\\n')\n",
    "    \n",
    "    # verify all files have the necessary columns in the same position\n",
    "    for (schema,blank) in file_schemas.values():\n",
    "        assert 'ickup' in schema[1]\n",
    "        assert 'atetime' in schema[1]\n",
    "        assert 'ickup' in schema[5]\n",
    "        assert 'ongitude' in schema[5]\n",
    "        assert 'ickup' in schema[6]\n",
    "        assert 'atitude' in schema[6]\n",
    "    print \"Schema:\", file_schemas[filename][0]\n",
    "    \n",
    "    # Remove header and blank line from file\n",
    "    for filename in cabFilenames:\n",
    "        print \"Writing to %r\" % filename \n",
    "        with open(prefix+filename,'r') as in_fp:\n",
    "            #read whole file\n",
    "            lines = in_fp.readlines()\n",
    "\n",
    "        with open(prefix+filename,'w') as out_fp:\n",
    "\n",
    "            # check if there is a blank line after the header\n",
    "            if file_schemas[filename][1]:\n",
    "                out_fp.writelines(lines[2:])\n",
    "            else:\n",
    "                out_fp.writelines(lines[1:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Schema: ('VendorID', 'tpep_pickup_datetime', 'tpep_dropoff_datetime', 'passenger_count', 'trip_distance', 'pickup_longitude', 'pickup_latitude', 'RateCodeID', 'store_and_fwd_flag', 'dropoff_longitude', 'dropoff_latitude', 'payment_type', 'fare_amount', 'extra', 'mta_tax', 'tip_amount', 'tolls_amount', 'improvement_surcharge', 'total_amount\\r\\n')\n",
      "Writing to '/yellow_tripdata_2013-01.csv'\n",
      "Writing to '/yellow_tripdata_2013-02.csv'\n",
      "Writing to '/yellow_tripdata_2013-03.csv'\n",
      "Writing to '/yellow_tripdata_2013-04.csv'\n",
      "Writing to '/yellow_tripdata_2013-05.csv'\n",
      "Writing to '/yellow_tripdata_2013-06.csv'\n",
      "Writing to '/yellow_tripdata_2013-07.csv'\n",
      "Writing to '/yellow_tripdata_2013-08.csv'\n",
      "Writing to '/yellow_tripdata_2013-09.csv'\n",
      "Writing to '/yellow_tripdata_2013-10.csv'\n",
      "Writing to '/yellow_tripdata_2013-11.csv'\n",
      "Writing to '/yellow_tripdata_2013-12.csv'\n",
      "Writing to '/yellow_tripdata_2014-01.csv'\n",
      "Writing to '/yellow_tripdata_2014-02.csv'\n",
      "Writing to '/yellow_tripdata_2014-03.csv'\n",
      "Writing to '/yellow_tripdata_2014-04.csv'\n",
      "Writing to '/yellow_tripdata_2014-05.csv'\n",
      "Writing to '/yellow_tripdata_2014-06.csv'\n",
      "Writing to '/yellow_tripdata_2014-07.csv'\n",
      "Writing to '/yellow_tripdata_2014-08.csv'\n",
      "Writing to '/yellow_tripdata_2014-09.csv'\n",
      "Writing to '/yellow_tripdata_2014-10.csv'\n",
      "Writing to '/yellow_tripdata_2014-11.csv'\n",
      "Writing to '/yellow_tripdata_2014-12.csv'\n",
      "Writing to '/yellow_tripdata_2015-01.csv'\n",
      "Writing to '/yellow_tripdata_2015-02.csv'\n",
      "Writing to '/yellow_tripdata_2015-03.csv'\n",
      "Writing to '/yellow_tripdata_2015-04.csv'\n",
      "Writing to '/yellow_tripdata_2015-05.csv'\n",
      "Writing to '/yellow_tripdata_2015-06.csv'\n"
     ]
    }
   ],
   "source": [
    "#Preprocess Yellow Cab files -- check schema\n",
    "preprocess_data(yCabFilenames, True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "upload: datafiles/nyc/yellow_tripdata_2013-01.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2013-01.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2013-02.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2013-02.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2013-03.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2013-03.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2013-04.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2013-04.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2013-05.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2013-05.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2013-06.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2013-06.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2013-07.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2013-07.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2013-08.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2013-08.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2013-09.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2013-09.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2013-10.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2013-10.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2013-11.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2013-11.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2013-12.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2013-12.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2014-01.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2014-01.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2014-02.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2014-02.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2014-03.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2014-03.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2014-04.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2014-04.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2014-05.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2014-05.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2014-06.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2014-06.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2014-07.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2014-07.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2014-08.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2014-08.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2014-09.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2014-09.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2014-10.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2014-10.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2014-11.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2014-11.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2014-12.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2014-12.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2015-01.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2015-01.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2015-02.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2015-02.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2015-03.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2015-03.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2015-04.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2015-04.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2015-06.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2015-06.csv\n",
      "upload: datafiles/nyc/yellow_tripdata_2015-05.csv to s3://sdaultonbucket1/nyc/yellow_tripdata_2015-05.csv\n"
     ]
    }
   ],
   "source": [
    "#add to s3\n",
    "!aws s3 sync datafiles/nyc/ s3://sdaultonbucket1/nyc/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#free up space\n",
    "!rm datafiles/nyc/*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--2015-12-02 05:33:12--  http://storage.googleapis.com/tlc-trip-data/2013/green_tripdata_2013-08.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c07::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 1147696 (1.1M) [text/csv]\n",
      "Saving to: ‘datafiles/nycg/green_tripdata_2013-08.csv’\n",
      "\n",
      "green_tripdata_2013 100%[=====================>]   1.09M  --.-KB/s   in 0.06s  \n",
      "\n",
      "2015-12-02 05:33:13 (17.2 MB/s) - ‘datafiles/nycg/green_tripdata_2013-08.csv’ saved [1147696/1147696]\n",
      "\n",
      "--2015-12-02 05:33:13--  http://storage.googleapis.com/tlc-trip-data/2013/green_tripdata_2013-09.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c07::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 7685009 (7.3M) [text/csv]\n",
      "Saving to: ‘datafiles/nycg/green_tripdata_2013-09.csv’\n",
      "\n",
      "green_tripdata_2013 100%[=====================>]   7.33M  --.-KB/s   in 0.1s   \n",
      "\n",
      "2015-12-02 05:33:13 (49.6 MB/s) - ‘datafiles/nycg/green_tripdata_2013-09.csv’ saved [7685009/7685009]\n",
      "\n",
      "--2015-12-02 05:33:13--  http://storage.googleapis.com/tlc-trip-data/2013/green_tripdata_2013-10.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c07::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 26375774 (25M) [text/csv]\n",
      "Saving to: ‘datafiles/nycg/green_tripdata_2013-10.csv’\n",
      "\n",
      "green_tripdata_2013 100%[=====================>]  25.15M  78.2MB/s   in 0.3s   \n",
      "\n",
      "2015-12-02 05:33:14 (78.2 MB/s) - ‘datafiles/nycg/green_tripdata_2013-10.csv’ saved [26375774/26375774]\n",
      "\n",
      "--2015-12-02 05:33:14--  http://storage.googleapis.com/tlc-trip-data/2013/green_tripdata_2013-11.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c07::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 59160743 (56M) [text/csv]\n",
      "Saving to: ‘datafiles/nycg/green_tripdata_2013-11.csv’\n",
      "\n",
      "green_tripdata_2013 100%[=====================>]  56.42M  90.1MB/s   in 0.6s   \n",
      "\n",
      "2015-12-02 05:33:15 (90.1 MB/s) - ‘datafiles/nycg/green_tripdata_2013-11.csv’ saved [59160743/59160743]\n",
      "\n",
      "--2015-12-02 05:33:15--  http://storage.googleapis.com/tlc-trip-data/2013/green_tripdata_2013-12.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c07::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 93586486 (89M) [text/csv]\n",
      "Saving to: ‘datafiles/nycg/green_tripdata_2013-12.csv’\n",
      "\n",
      "green_tripdata_2013 100%[=====================>]  89.25M  97.6MB/s   in 0.9s   \n",
      "\n",
      "2015-12-02 05:33:16 (97.6 MB/s) - ‘datafiles/nycg/green_tripdata_2013-12.csv’ saved [93586486/93586486]\n",
      "\n",
      "--2015-12-02 05:33:16--  http://storage.googleapis.com/tlc-trip-data/2014/green_tripdata_2014-01.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c07::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 124843531 (119M) [text/csv]\n",
      "Saving to: ‘datafiles/nycg/green_tripdata_2014-01.csv’\n",
      "\n",
      "green_tripdata_2014 100%[=====================>] 119.06M  93.9MB/s   in 1.3s   \n",
      "\n",
      "2015-12-02 05:33:18 (93.9 MB/s) - ‘datafiles/nycg/green_tripdata_2014-01.csv’ saved [124843531/124843531]\n",
      "\n",
      "--2015-12-02 05:33:18--  http://storage.googleapis.com/tlc-trip-data/2014/green_tripdata_2014-02.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c07::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 156606941 (149M) [text/csv]\n",
      "Saving to: ‘datafiles/nycg/green_tripdata_2014-02.csv’\n",
      "\n",
      "green_tripdata_2014 100%[=====================>] 149.35M   108MB/s   in 1.4s   \n",
      "\n",
      "2015-12-02 05:33:20 (108 MB/s) - ‘datafiles/nycg/green_tripdata_2014-02.csv’ saved [156606941/156606941]\n",
      "\n",
      "--2015-12-02 05:33:20--  http://storage.googleapis.com/tlc-trip-data/2014/green_tripdata_2014-03.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c07::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 202092093 (193M) [text/csv]\n",
      "Saving to: ‘datafiles/nycg/green_tripdata_2014-03.csv’\n",
      "\n",
      "green_tripdata_2014 100%[=====================>] 192.73M  78.0MB/s   in 2.5s   \n",
      "\n",
      "2015-12-02 05:33:23 (78.0 MB/s) - ‘datafiles/nycg/green_tripdata_2014-03.csv’ saved [202092093/202092093]\n",
      "\n",
      "--2015-12-02 05:33:23--  http://storage.googleapis.com/tlc-trip-data/2014/green_tripdata_2014-04.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c07::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 204863497 (195M) [text/csv]\n",
      "Saving to: ‘datafiles/nycg/green_tripdata_2014-04.csv’\n",
      "\n",
      "green_tripdata_2014 100%[=====================>] 195.37M   106MB/s   in 1.8s   \n",
      "\n",
      "2015-12-02 05:33:25 (106 MB/s) - ‘datafiles/nycg/green_tripdata_2014-04.csv’ saved [204863497/204863497]\n",
      "\n",
      "--2015-12-02 05:33:25--  http://storage.googleapis.com/tlc-trip-data/2014/green_tripdata_2014-05.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c07::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 222722613 (212M) [text/csv]\n",
      "Saving to: ‘datafiles/nycg/green_tripdata_2014-05.csv’\n",
      "\n",
      "green_tripdata_2014 100%[=====================>] 212.40M   104MB/s   in 2.0s   \n",
      "\n",
      "2015-12-02 05:33:27 (104 MB/s) - ‘datafiles/nycg/green_tripdata_2014-05.csv’ saved [222722613/222722613]\n",
      "\n",
      "--2015-12-02 05:33:28--  http://storage.googleapis.com/tlc-trip-data/2014/green_tripdata_2014-06.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c07::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 209637247 (200M) [text/csv]\n",
      "Saving to: ‘datafiles/nycg/green_tripdata_2014-06.csv’\n",
      "\n",
      "green_tripdata_2014 100%[=====================>] 199.92M   123MB/s   in 1.6s   \n",
      "\n",
      "2015-12-02 05:33:30 (123 MB/s) - ‘datafiles/nycg/green_tripdata_2014-06.csv’ saved [209637247/209637247]\n",
      "\n",
      "--2015-12-02 05:33:30--  http://storage.googleapis.com/tlc-trip-data/2014/green_tripdata_2014-07.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c07::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 199578900 (190M) [text/csv]\n",
      "Saving to: ‘datafiles/nycg/green_tripdata_2014-07.csv’\n",
      "\n",
      "green_tripdata_2014 100%[=====================>] 190.33M   113MB/s   in 1.7s   \n",
      "\n",
      "2015-12-02 05:33:32 (113 MB/s) - ‘datafiles/nycg/green_tripdata_2014-07.csv’ saved [199578900/199578900]\n",
      "\n",
      "--2015-12-02 05:33:32--  http://storage.googleapis.com/tlc-trip-data/2014/green_tripdata_2014-08.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c07::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 210824549 (201M) [text/csv]\n",
      "Saving to: ‘datafiles/nycg/green_tripdata_2014-08.csv’\n",
      "\n",
      "green_tripdata_2014 100%[=====================>] 201.06M   116MB/s   in 1.7s   \n",
      "\n",
      "2015-12-02 05:33:34 (116 MB/s) - ‘datafiles/nycg/green_tripdata_2014-08.csv’ saved [210824549/210824549]\n",
      "\n",
      "--2015-12-02 05:33:34--  http://storage.googleapis.com/tlc-trip-data/2014/green_tripdata_2014-09.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c07::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 213461849 (204M) [text/csv]\n",
      "Saving to: ‘datafiles/nycg/green_tripdata_2014-09.csv’\n",
      "\n",
      "green_tripdata_2014 100%[=====================>] 203.57M   119MB/s   in 1.7s   \n",
      "\n",
      "2015-12-02 05:33:36 (119 MB/s) - ‘datafiles/nycg/green_tripdata_2014-09.csv’ saved [213461849/213461849]\n",
      "\n",
      "--2015-12-02 05:33:36--  http://storage.googleapis.com/tlc-trip-data/2014/green_tripdata_2014-10.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c07::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 233727643 (223M) [text/csv]\n",
      "Saving to: ‘datafiles/nycg/green_tripdata_2014-10.csv’\n",
      "\n",
      "green_tripdata_2014 100%[=====================>] 222.90M   114MB/s   in 2.0s   \n",
      "\n",
      "2015-12-02 05:33:38 (114 MB/s) - ‘datafiles/nycg/green_tripdata_2014-10.csv’ saved [233727643/233727643]\n",
      "\n",
      "--2015-12-02 05:33:38--  http://storage.googleapis.com/tlc-trip-data/2014/green_tripdata_2014-11.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c07::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 242624000 (231M) [text/csv]\n",
      "Saving to: ‘datafiles/nycg/green_tripdata_2014-11.csv’\n",
      "\n",
      "green_tripdata_2014 100%[=====================>] 231.38M  78.3MB/s   in 3.0s   \n",
      "\n",
      "2015-12-02 05:33:42 (78.3 MB/s) - ‘datafiles/nycg/green_tripdata_2014-11.csv’ saved [242624000/242624000]\n",
      "\n",
      "--2015-12-02 05:33:42--  http://storage.googleapis.com/tlc-trip-data/2014/green_tripdata_2014-12.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c07::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 257796237 (246M) [text/csv]\n",
      "Saving to: ‘datafiles/nycg/green_tripdata_2014-12.csv’\n",
      "\n",
      "green_tripdata_2014 100%[=====================>] 245.85M   118MB/s   in 2.1s   \n",
      "\n",
      "2015-12-02 05:33:44 (118 MB/s) - ‘datafiles/nycg/green_tripdata_2014-12.csv’ saved [257796237/257796237]\n",
      "\n",
      "--2015-12-02 05:33:44--  http://storage.googleapis.com/tlc-trip-data/2015/green_tripdata_2015-01.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c07::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 243307680 (232M) [text/csv]\n",
      "Saving to: ‘datafiles/nycg/green_tripdata_2015-01.csv’\n",
      "\n",
      "green_tripdata_2015 100%[=====================>] 232.04M   123MB/s   in 1.9s   \n",
      "\n",
      "2015-12-02 05:33:47 (123 MB/s) - ‘datafiles/nycg/green_tripdata_2015-01.csv’ saved [243307680/243307680]\n",
      "\n",
      "--2015-12-02 05:33:47--  http://storage.googleapis.com/tlc-trip-data/2015/green_tripdata_2015-02.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c07::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 254498963 (243M) [text/csv]\n",
      "Saving to: ‘datafiles/nycg/green_tripdata_2015-02.csv’\n",
      "\n",
      "green_tripdata_2015 100%[=====================>] 242.71M   123MB/s   in 2.0s   \n",
      "\n",
      "2015-12-02 05:33:49 (123 MB/s) - ‘datafiles/nycg/green_tripdata_2015-02.csv’ saved [254498963/254498963]\n",
      "\n",
      "--2015-12-02 05:33:49--  http://storage.googleapis.com/tlc-trip-data/2015/green_tripdata_2015-03.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c07::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 278503144 (266M) [text/csv]\n",
      "Saving to: ‘datafiles/nycg/green_tripdata_2015-03.csv’\n",
      "\n",
      "green_tripdata_2015 100%[=====================>] 265.60M   120MB/s   in 2.2s   \n",
      "\n",
      "2015-12-02 05:33:52 (120 MB/s) - ‘datafiles/nycg/green_tripdata_2015-03.csv’ saved [278503144/278503144]\n",
      "\n",
      "--2015-12-02 05:33:52--  http://storage.googleapis.com/tlc-trip-data/2015/green_tripdata_2015-04.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c07::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 269283302 (257M) [text/csv]\n",
      "Saving to: ‘datafiles/nycg/green_tripdata_2015-04.csv’\n",
      "\n",
      "green_tripdata_2015 100%[=====================>] 256.81M   121MB/s   in 2.1s   \n",
      "\n",
      "2015-12-02 05:33:54 (121 MB/s) - ‘datafiles/nycg/green_tripdata_2015-04.csv’ saved [269283302/269283302]\n",
      "\n",
      "--2015-12-02 05:33:54--  http://storage.googleapis.com/tlc-trip-data/2015/green_tripdata_2015-05.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c07::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 289318204 (276M) [text/csv]\n",
      "Saving to: ‘datafiles/nycg/green_tripdata_2015-05.csv’\n",
      "\n",
      "green_tripdata_2015 100%[=====================>] 275.92M   126MB/s   in 2.2s   \n",
      "\n",
      "2015-12-02 05:33:57 (126 MB/s) - ‘datafiles/nycg/green_tripdata_2015-05.csv’ saved [289318204/289318204]\n",
      "\n",
      "--2015-12-02 05:33:57--  http://storage.googleapis.com/tlc-trip-data/2015/green_tripdata_2015-06.csv\n",
      "Resolving storage.googleapis.com (storage.googleapis.com)... 173.194.207.128, 2607:f8b0:400d:c07::80\n",
      "Connecting to storage.googleapis.com (storage.googleapis.com)|173.194.207.128|:80... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 265179055 (253M) [text/csv]\n",
      "Saving to: ‘datafiles/nycg/green_tripdata_2015-06.csv’\n",
      "\n",
      "green_tripdata_2015 100%[=====================>] 252.89M   126MB/s   in 2.0s   \n",
      "\n",
      "2015-12-02 05:33:59 (126 MB/s) - ‘datafiles/nycg/green_tripdata_2015-06.csv’ saved [265179055/265179055]\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#Download the green cab files\n",
    "for url in gCabUrls:\n",
    "    !wget $url -P datafiles/nycg --trust-server-names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4.0G\tdatafiles/nycg\r\n"
     ]
    }
   ],
   "source": [
    "#Disk space of the Yellow Cab files\n",
    "!du -mh datafiles/nycg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Schema: ('VendorID', 'lpep_pickup_datetime', 'Lpep_dropoff_datetime', 'Store_and_fwd_flag', 'RateCodeID', 'Pickup_longitude', 'Pickup_latitude', 'Dropoff_longitude', 'Dropoff_latitude', 'Passenger_count', 'Trip_distance', 'Fare_amount', 'Extra', 'MTA_tax', 'Tip_amount', 'Tolls_amount', 'Ehail_fee', 'improvement_surcharge', 'Total_amount', 'Payment_type', 'Trip_type \\r\\n')\n",
      "Writing to '/green_tripdata_2013-08.csv'\n",
      "Writing to '/green_tripdata_2013-09.csv'\n",
      "Writing to '/green_tripdata_2013-10.csv'\n",
      "Writing to '/green_tripdata_2013-11.csv'\n",
      "Writing to '/green_tripdata_2013-12.csv'\n",
      "Writing to '/green_tripdata_2014-01.csv'\n",
      "Writing to '/green_tripdata_2014-02.csv'\n",
      "Writing to '/green_tripdata_2014-03.csv'\n",
      "Writing to '/green_tripdata_2014-04.csv'\n",
      "Writing to '/green_tripdata_2014-05.csv'\n",
      "Writing to '/green_tripdata_2014-06.csv'\n",
      "Writing to '/green_tripdata_2014-07.csv'\n",
      "Writing to '/green_tripdata_2014-08.csv'\n",
      "Writing to '/green_tripdata_2014-09.csv'\n",
      "Writing to '/green_tripdata_2014-10.csv'\n",
      "Writing to '/green_tripdata_2014-11.csv'\n",
      "Writing to '/green_tripdata_2014-12.csv'\n",
      "Writing to '/green_tripdata_2015-01.csv'\n",
      "Writing to '/green_tripdata_2015-02.csv'\n",
      "Writing to '/green_tripdata_2015-03.csv'\n",
      "Writing to '/green_tripdata_2015-04.csv'\n",
      "Writing to '/green_tripdata_2015-05.csv'\n",
      "Writing to '/green_tripdata_2015-06.csv'\n"
     ]
    }
   ],
   "source": [
    "# Preprocess Green Cab files\n",
    "preprocess_data(gCabFilenames, False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "upload: datafiles/nycg/green_tripdata_2013-08.csv to s3://sdaultonbucket1/nycg/green_tripdata_2013-08.csv\n",
      "upload: datafiles/nycg/green_tripdata_2013-09.csv to s3://sdaultonbucket1/nycg/green_tripdata_2013-09.csv\n",
      "upload: datafiles/nycg/green_tripdata_2013-10.csv to s3://sdaultonbucket1/nycg/green_tripdata_2013-10.csv\n",
      "upload: datafiles/nycg/green_tripdata_2013-11.csv to s3://sdaultonbucket1/nycg/green_tripdata_2013-11.csv\n",
      "upload: datafiles/nycg/green_tripdata_2013-12.csv to s3://sdaultonbucket1/nycg/green_tripdata_2013-12.csv\n",
      "upload: datafiles/nycg/green_tripdata_2014-01.csv to s3://sdaultonbucket1/nycg/green_tripdata_2014-01.csv\n",
      "upload: datafiles/nycg/green_tripdata_2014-02.csv to s3://sdaultonbucket1/nycg/green_tripdata_2014-02.csv\n",
      "upload: datafiles/nycg/green_tripdata_2014-03.csv to s3://sdaultonbucket1/nycg/green_tripdata_2014-03.csv\n",
      "upload: datafiles/nycg/green_tripdata_2014-04.csv to s3://sdaultonbucket1/nycg/green_tripdata_2014-04.csv\n",
      "upload: datafiles/nycg/green_tripdata_2014-05.csv to s3://sdaultonbucket1/nycg/green_tripdata_2014-05.csv\n",
      "upload: datafiles/nycg/green_tripdata_2014-06.csv to s3://sdaultonbucket1/nycg/green_tripdata_2014-06.csv\n",
      "upload: datafiles/nycg/green_tripdata_2014-07.csv to s3://sdaultonbucket1/nycg/green_tripdata_2014-07.csv\n",
      "upload: datafiles/nycg/green_tripdata_2014-08.csv to s3://sdaultonbucket1/nycg/green_tripdata_2014-08.csv\n",
      "upload: datafiles/nycg/green_tripdata_2014-09.csv to s3://sdaultonbucket1/nycg/green_tripdata_2014-09.csv\n",
      "upload: datafiles/nycg/green_tripdata_2014-10.csv to s3://sdaultonbucket1/nycg/green_tripdata_2014-10.csv\n",
      "upload: datafiles/nycg/green_tripdata_2014-11.csv to s3://sdaultonbucket1/nycg/green_tripdata_2014-11.csv\n",
      "upload: datafiles/nycg/green_tripdata_2014-12.csv to s3://sdaultonbucket1/nycg/green_tripdata_2014-12.csv\n",
      "upload: datafiles/nycg/green_tripdata_2015-01.csv to s3://sdaultonbucket1/nycg/green_tripdata_2015-01.csv\n",
      "upload: datafiles/nycg/green_tripdata_2015-03.csv to s3://sdaultonbucket1/nycg/green_tripdata_2015-03.csv\n",
      "upload: datafiles/nycg/green_tripdata_2015-02.csv to s3://sdaultonbucket1/nycg/green_tripdata_2015-02.csv\n",
      "upload: datafiles/nycg/green_tripdata_2015-05.csv to s3://sdaultonbucket1/nycg/green_tripdata_2015-05.csv\n",
      "upload: datafiles/nycg/green_tripdata_2015-06.csv to s3://sdaultonbucket1/nycg/green_tripdata_2015-06.csv\n",
      "upload: datafiles/nycg/green_tripdata_2015-04.csv to s3://sdaultonbucket1/nycg/green_tripdata_2015-04.csv\n"
     ]
    }
   ],
   "source": [
    "!aws s3 sync datafiles/nycg/ s3://sdaultonbucket1/nycg/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#free up space\n",
    "!rm datafiles/nycg/*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 4: Check cluster performance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 0 ns, sys: 0 ns, total: 0 ns\n",
      "Wall time: 2.87 ms\n",
      "CPU times: user 0 ns, sys: 12 ms, total: 12 ms\n",
      "Wall time: 14.6 s\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "12450521"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "myRDD = sc.textFile(\"s3://sdaultonbucket1/nyc/yellow_tripdata_2015-02.csv\")\n",
    "%time myRDD.cache()\n",
    "%time myRDD.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "myRDD.is_cached"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Other useful notes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**1. Enable the web admin interface** from the AWS console (follow the steps it says). Note: in this step when you open the SSH conection (as per instructions), the connection might not show ANY thing status etc) - this is fine. The SSH command (As per instruction) is:  ssh -i CS109.pem $DNS_NAME -ND 8157\n",
    "\n",
    "**2. Admin UI's**\n",
    "    \n",
    "    a) To get to the Spark Jobs Admin Console: Go to the Hadoop Resource Manager UI (from AWS console) and click on \"Application master\" link (it will be one of the items in the listed running applications).\n",
    "    \n",
    "    b) Spark history server: http://<domain>:18080/\n",
    "    \n",
    "    c) For CPU/Memory performance on each node use the Ganglia UI (link from the AWS console)\n",
    "    "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}