BixiLR/BixiLinearAIDA-RWS-Basic.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this Python worflow we explore the Montreal Bixi biking data set for the year 2017 https://www.kaggle.com/aubertsigouin/biximtl/data\n",
    "\n",
    "We have additionally enriched this data set with the biking distance/duration available via Google map API as gmdata2017\n",
    "\n",
    "Our objective is to predict the \"trip duration\", given the distance between two stations.\n",
    "\n",
    "This is a \"basic\" workflow where the user directly builds the training dataset with minimal to no exploration, an unrealistic, but best case scenario."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Import AIDA components"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from aida.aida import *;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Connection information to AIDA's server"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "host='cerberus'; dbname='bixi'; user='bixi'; passwd='bixi'; jobName='bixiLinear'; port=55660;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Establish a connection and get a handle to the database workspace."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dw = AIDA.connect(host, dbname, user, passwd, jobName, port);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will not be concerned with trips that started and ended at the same station as those are noise. Also, to weed out any further fluctuations in the input data set, we will limit ourselves to only those station combinations which has at the least 50 trips.\n",
    "\n",
    "We can use AIDA's powerful relational API to accomplish this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "freqStations = dw.tripdata2017.filter(Q('stscode', 'endscode', CMP.NE)) \\\n",
    "    .aggregate(('stscode','endscode',{COUNT('*'):'numtrips'}), ('stscode','endscode')) \\\n",
    "    .filter(Q('numtrips',C(50), CMP.GTE));"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next we will enrich the trip data set by using the distance information provided by the Google maps' API.\n",
    "This can be accomplished by the relational join operators provided by AIDA."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Google also provides its estimated duration for the trip. We will have to see in the end if our trained model is able to predict the trip duration better than google's estimate. So we will also save Google's estimate for the trip duration for that comparison."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "gtripData = dw.gmdata2017 \\\n",
    "    .join(dw.tripdata2017, ('stscode','endscode'), ('stscode', 'endscode'), COL.ALL, COL.ALL) \\\n",
    "    .join(freqStations, ('stscode','endscode'), ('stscode', 'endscode') \\\n",
    "                          , ('id', 'duration', 'gdistm', 'gduration') );"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As there are multiple trips between the same stations, many trips will have the same distance. So we want to keep some values of distance apart for testing. For this purpose, we will first get distinct values for distance and then sort it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "guniqueTripDist = gtripData[:,['gdistm']].distinct().order('gdistm');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will keep roughly 30% of these distances apart for testing and the rest, we will use for training."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "gtestTripDist = guniqueTripDist[::3];\n",
    "gtrainTripDist = guniqueTripDist.filter(Q('gdistm', gtestTripDist, CMP.NOTIN));"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will next extract the training data set and normalize its features."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "gtrainData = gtripData.project(('gdistm', 'duration')).filter(Q('gdistm', gtrainTripDist, CMP.IN));\n",
    "\n",
    "gmaxdist = guniqueTripDist.max('gdistm');\n",
    "gmaxduration = gtripData.max('duration');\n",
    "gtrainData = gtrainData.project((1.0*F('gdistm')/gmaxdist, 1.0*F('duration')/gmaxduration));"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our linear regression equation is of the form.\n",
    "\n",
    "dur = a + b*dist\n",
    "\n",
    "we will re-organize the training data set to fit this format and also setup our initial parameters for a and b."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "gtrainDataSet = dw._ones((gtrainData.numRows, 1), (\"x0\",)).hstack(gtrainData[:,['gdistm']]);\n",
    "gtrainDataSetDuration = gtrainData[:,['duration']];\n",
    "gparams = dw._ones((1,2), (\"a\",\"b\"));"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us try to run a prediction using these parameters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "gpred = gtrainDataSet @ gparams.T;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to compute the squared error for the predictions. Since we will be reusing them, we might as well store it as a function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def squaredErr(actual, predicted):\n",
    "    return ((predicted-actual)**2).sum()/(2*(actual.shape[0]));"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us see what is the error for the first iteration."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.5419675497480458\n"
     ]
    }
   ],
   "source": [
    "gsqerr = squaredErr(gtrainDataSetDuration, gpred);\n",
    "print(gsqerr);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to perform a gradient descent based on the squared errors. We will write another function to perform this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def gradDesc(actual, predicted, indata):\n",
    "    return (predicted-actual).T @ indata / actual.shape[0];"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us update our params using gradient descent using the error we got. We also need to use a learning rate, alpha (arbitrarily chosen)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "OrderedDict([('a', array([0.89616804])), ('b', array([0.98651787]))])\n"
     ]
    }
   ],
   "source": [
    "alpha = 0.1;\n",
    "\n",
    "gparams = gparams - alpha * gradDesc(gtrainDataSetDuration, gpred, gtrainDataSet);\n",
    "print(gparams.rows);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let us try to use the updated params to train the model again and see if the error is decreasing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.4379084758530774\n"
     ]
    }
   ],
   "source": [
    "gpred = gtrainDataSet @ gparams.T;\n",
    "gsqerr = squaredErr(gtrainDataSetDuration, gpred);\n",
    "print(gsqerr);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is good our error rate is decreasing with iteration. Hopefully this will help us construct the right parameters.\n",
    "\n",
    "We are done with the feature selection and feature engineering phase for now.\n",
    "\n",
    "Next we will proceed to train our linear regression model using the training data set.\n",
    "\n",
    "Meanwhile, we will also let it printout the error rate at frequent intervals so that we know it is decreasing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Error rate after 100 iterations is 0.002414885788154281\n",
      "Error rate after 200 iterations is 0.0023518940367528206\n",
      "Error rate after 300 iterations is 0.0022972381907587756\n",
      "Error rate after 400 iterations is 0.0022498149319043433\n",
      "Error rate after 500 iterations is 0.0022086671747070705\n",
      "Error rate after 600 iterations is 0.0021729644844393505\n",
      "Error rate after 700 iterations is 0.002141986317482605\n",
      "Error rate after 800 iterations is 0.0021151074794789697\n",
      "Error rate after 900 iterations is 0.002091785507800193\n",
      "Error rate after 1000 iterations is 0.00207154972368958\n",
      "OrderedDict([('a', array([0.00295944])), ('b', array([0.67162878]))])\n",
      "0.00207154972368958\n"
     ]
    }
   ],
   "source": [
    "def trainModel(w, numiters, alpha):\n",
    "    gtrainDataSet = w.gtrainDataSet; gtrainDataSetDuration=w.gtrainDataSetDuration;\n",
    "    gparams = w.gparams; gradDesc = w.gradDesc;\n",
    "    for i in range(0, numiters):\n",
    "        gpred = gtrainDataSet @ gparams.T;\n",
    "        gparams = gparams - alpha*gradDesc(gtrainDataSetDuration, gpred, gtrainDataSet);\n",
    "    w.gpred = gpred; w.gparams = gparams;\n",
    "\n",
    "#Export any objects and functions required for execution in the remote workspace.\n",
    "dw.gtrainDataSet = gtrainDataSet; dw.gtrainDataSetDuration = gtrainDataSetDuration; \n",
    "dw.gparams = gparams; dw.gradDesc = gradDesc; \n",
    "\n",
    "for i in range(0, 10):\n",
    "    dw._X(trainModel, 100, alpha);\n",
    "    print(\"Error rate after {} iterations is {}\".format((i+1)*100, squaredErr(gtrainDataSetDuration, dw.gpred)))\n",
    "\n",
    "gparams = dw.gparams; gpred = dw.gpred;\n",
    "print(gparams.rows);\n",
    "gsqerr = squaredErr(gtrainDataSetDuration, gpred);\n",
    "print(gsqerr);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us see how our model performs in predictions against the test data set we had kept apart."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "99215.98424571125\n"
     ]
    }
   ],
   "source": [
    "gtestData = gtripData.project(('gdistm', 'duration', 'gduration')).filter(Q('gdistm', gtestTripDist, CMP.IN));\n",
    "gtestData = gtestData.project((1.0*F('gdistm')/gmaxdist, 1.0*F('duration')/gmaxduration, 'gduration'));\n",
    "gtestDataSet = dw._ones((gtestData.numRows, 1), (\"x0\",)).hstack(gtestData[:,['gdistm']]);\n",
    "gtestDataSetDuration = gtestData[:,['duration']];\n",
    "\n",
    "gtestpred = gtestDataSet @ gparams.T;\n",
    "\n",
    "gtestsqerr1 = squaredErr(gtestDataSetDuration*gmaxduration, gtestpred*gmaxduration);\n",
    "print(gtestsqerr1);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We would also like to check how the duration provided by Google maps' API hold up to the test data set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "111763.37983591038\n"
     ]
    }
   ],
   "source": [
    "gtestsqerr2 = squaredErr(gtestDataSetDuration*gmaxduration, gtestData[:,['gduration']]);\n",
    "print(gtestsqerr2);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So yes, our model is able to do a better job."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}