BixiLR/BixiLinearPandasOpt-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.\n",
    "\n",
    "We are using the Pandas package with an explicitly optimized setting for database connection to transfer data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Import required pacakges."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas.io.sql as psql;\n",
    "import pandas as pd;\n",
    "import pymonetdb.sql;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Database connection information. We will try to explicitly optimize the database connection by providing a very high value for the data buffer."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "host='cerberus'; dbname='bixi'; user='bixi'; passwd='bixi'; databuffersize=1000000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Connect to the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "con = pymonetdb.Connection(dbname,hostname=host,username=user,password=passwd,autocommit=True);\n",
    "con.replysize = databuffersize;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use a SQL to read the minimal data required for training from the database.\n",
    "\n",
    "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."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "gtripData = pd.DataFrame(psql.read_sql_query(' \\\n",
    "select t.duration, g.gdistm, g.gduration \\\n",
    "from \\\n",
    "( \\\n",
    "  select stscode, endscode \\\n",
    "  from bixi.tripdata2017 \\\n",
    "  where stscode<>endscode \\\n",
    "  group by stscode, endscode \\\n",
    "  having count(*) >= 50 \\\n",
    ")s, tripdata2017 t, gmdata2017 g \\\n",
    "where t.stscode = s.stscode \\\n",
    "  and t.endscode = s.endscode \\\n",
    "  and t.stscode = g.stscode \\\n",
    "  and t.endscode = g.endscode \\\n",
    ";', con));"
   ]
  },
  {
   "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": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "guniqueTripDist = gtripData.loc[:,['gdistm']].drop_duplicates().sort_values(by=['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": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "gtestTripDist = guniqueTripDist[::3];\n",
    "gtrainTripDist = guniqueTripDist[~guniqueTripDist['gdistm'].isin(gtestTripDist['gdistm'])];"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will next extract the training data set and normalize its features."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "gtrainData = gtripData[gtripData['gdistm'].isin(gtrainTripDist['gdistm'])];\n",
    "gtrainData = gtrainData.loc[:, ['gdistm', 'duration']];\n",
    "\n",
    "gmaxdist = guniqueTripDist['gdistm'].max();\n",
    "gmaxduration = gtripData['duration'].max();\n",
    "gtrainData['gdistm'] = gtrainData['gdistm']/gmaxdist;\n",
    "gtrainData['duration'] = gtrainData['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": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "gtrainDataSet = gtrainData.loc[:, ['gdistm']];\n",
    "gtrainDataSet.insert(0, 'x0', 1);\n",
    "gtrainDataSetDuration = gtrainData.loc[:, ['duration']];\n",
    "gparams = pd.DataFrame({'x0':[1], 'gdistm':[1]}, index=['duration']);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us try to run a prediction using these parameters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "gpred = gtrainDataSet.dot(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": 10,
   "metadata": {
    "collapsed": false
   },
   "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": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "duration    0.541968\n",
      "dtype: float64\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": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def gradDesc(actual, predicted, indata):\n",
    "    return (predicted-actual).T.dot(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": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "            gdistm        x0\n",
      "duration  0.986518  0.896168\n"
     ]
    }
   ],
   "source": [
    "alpha = 0.1;\n",
    "\n",
    "gparams = gparams - alpha * gradDesc(gtrainDataSetDuration, gpred, gtrainDataSet);\n",
    "print(gparams);"
   ]
  },
  {
   "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": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "duration    0.437908\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "gpred = gtrainDataSet.dot(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": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Error rate after 100 iterations is duration    0.002415\n",
      "dtype: float64\n",
      "Error rate after 200 iterations is duration    0.002352\n",
      "dtype: float64\n",
      "Error rate after 300 iterations is duration    0.002297\n",
      "dtype: float64\n",
      "Error rate after 400 iterations is duration    0.00225\n",
      "dtype: float64\n",
      "Error rate after 500 iterations is duration    0.002209\n",
      "dtype: float64\n",
      "Error rate after 600 iterations is duration    0.002173\n",
      "dtype: float64\n",
      "Error rate after 700 iterations is duration    0.002142\n",
      "dtype: float64\n",
      "Error rate after 800 iterations is duration    0.002115\n",
      "dtype: float64\n",
      "Error rate after 900 iterations is duration    0.002092\n",
      "dtype: float64\n",
      "Error rate after 1000 iterations is duration    0.002072\n",
      "dtype: float64\n",
      "            gdistm        x0\n",
      "duration  0.671629  0.002959\n",
      "duration    0.002072\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "for i in range(0, 1000):\n",
    "    gpred = gtrainDataSet.dot(gparams.T);\n",
    "    gparams = gparams - alpha*gradDesc(gtrainDataSetDuration, gpred, gtrainDataSet);\n",
    "    if((i+1)%100 == 0):\n",
    "        print(\"Error rate after {} iterations is {}\".format(i+1, squaredErr(gtrainDataSetDuration, gpred)))\n",
    "    \n",
    "print(gparams);\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": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "duration    99215.984246\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "gtestData = gtripData[gtripData['gdistm'].isin(gtestTripDist['gdistm'])];\n",
    "gtestData = gtestData.loc[:, ['gdistm', 'duration', 'gduration']];\n",
    "gtestData['gdistm'] = gtestData['gdistm']/gmaxdist;\n",
    "gtestData['duration'] = gtestData['duration']/gmaxduration;\n",
    "gtestDataSet = gtestData.loc[:, ['gdistm']];\n",
    "gtestDataSet.insert(0, 'x0', 1);\n",
    "gtestDataSetDuration = gtestData.loc[:, ['duration']];\n",
    "\n",
    "gtestpred = gtestDataSet.dot(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": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "duration    111763.379836\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "gtestsqerr2 = squaredErr(gtestDataSetDuration*gmaxduration, gtestData.loc[:,['gduration']] \\\n",
    "                         .rename(columns={'gduration':'duration'}));\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
}