<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="http://www.r-bloggers.com/using-microsoft-r-server-on-a-single-machine-for-experiments-with-600-million-taxi-rides/"></div></div><div><a data-url="http://www.r-bloggers.com/using-microsoft-r-server-on-a-single-machine-for-experiments-with-600-million-taxi-rides/" data-counturl="http://www.r-bloggers.com/using-microsoft-r-server-on-a-single-machine-for-experiments-with-600-million-taxi-rides/" data-count="horizontal" data-text="Using Microsoft R Server on a single machine for experiments with 600 million taxi rides." class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://blog.revolutionanalytics.com/2016/06/taxi2.html"> Revolutions</a></strong>, and kindly contributed to <a rel="nofollow" href="http://www.r-bloggers.com/">R-bloggers)</a> </div>
<div>
<p>by Dmitry Pechyoni, Microsoft Data Scientist</p>
<p>The <a target="_blank" href="http://www.nyc.gov/html/tlc/html/about/trip_record_data.shtml">New York City taxi dataset</a> is one of the largest publicly available datasets. It has about 1.1 billion taxi rides in New York City. Previously this dataset was explored and visualized in a number of blog posts, where the authors used various technologies (e.g., <a target="_blank" href="http://toddwschneider.com/posts/analyzing-1-1-billion-nyc-taxi-and-uber-trips-with-a-vengeance/">PostgreSQL</a> and <a target="_blank" href="http://tech.marksblogg.com/billion-nyc-taxi-rides-elasticsearch.html">Apache Elastic Search</a>). Moreoever, in a recent <a target="_blank" href="http://blog.revolutionanalytics.com/2016/04/mrs-nyc-taxi.html">blog post</a> our colleagues showed how to build machine learning models over one year of this dataset using Microsoft R Server (MRS) running in a 4-node Hadoop cluster. In this blog post we will use a single commodity machine to show an end-to-end process of downloading and cleaning 4 years of data, as well building and evaluating a machine learning model. This entire process takes around 12 hours.</p>
<p>We used the <a target="_blank" href="https://azure.microsoft.com/en-us/marketplace/partners/microsoft-ads/standard-data-science-vm/">Data Science Virtual Machine</a> in Azure cloud. Our virtual machine runs Windows and has Standard A4 configuration (8 cores and 14Gb of memory). This machine comes with 126Gb of hard disk, which is not enough to store NYC taxi dataset. To store the dataset locally, we followed <a target="_blank" href="https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-attach-disk-portal/">these</a> instructions and attached a 1Tb hard disk to our virtual machine.</p>
<p>New York City taxi dataset has data from 2009-2015 for yellow taxis and from 2013-2015 for green taxis. Yellow and green taxis have different data schema. Also, we have found that the yellow taxi rides from 2010-2013 have exactly the same schema, whereas the schema of the yellow taxi rides from 2009, 2014, 2015 is different. To simplify our code, we have focused on yellow rides from 2010-2013 and discarded other ones. These are more than 600 million yellow taxi rides from 2010-2013, which take 115Gb. Our goal is to build a binary classification model that will predict if a passenger will pay a tip.</p>
<h2>Obtaining the data, data cleaning and feature generation</h2>
<p>We start with downloading the data. We used <a target="_blank" href="https://cran.r-project.org/web/packages/httr/">httr</a> package. <a target="_blank" href="https://github.com/Azure/Azure-MachineLearning-DataScience/blob/master/Misc/MicrosoftR/Samples/NYCTaxiSingleMachine/download.r">This code</a> downloads 115Gb of data to our virtual machine. It takes 1 hour and 10 minutes.</p>
<p>To preprocess a big volume of data in a short time, we used parallel programming capabilities of <a target="_blank" href="https://www.microsoft.com/en-us/server-cloud/products/r-server/">Microsoft R Server</a> (MRS), in particular the <a target="_blank" href="https://msdn.microsoft.com/en-us/microsoft-r/scaler-distributed-computing#parallel-computing-with-rxexec">rxExec function</a>. This function allows us to distribute a data processing over multiple cores. Since our machine has 8 cores, by using rxExec we were able to speed up the data processing significantly.</p>
<p>The original dataset has one csv file per month. Initially we convert each csv file to the xdf file format. Xdf files store data in compressed and optimized format and are input to all subsequent Microsoft R Server functions. <a target="_blank" href="https://github.com/Azure/Azure-MachineLearning-DataScience/blob/master/Misc/MicrosoftR/Samples/NYCTaxiSingleMachine/data_cleaning.r">This code</a> does the above conversion, removes unnecessary columns, removes rows with noisy data, computes several new columns (duration of the trip, weekday, hour and label column) and finally converts several columns to factors.</p>
<p>By leveraging rxExec function and 8 cores, we are able to process 8 csv files in parallel. The data cleaning step takes 3 hours 50 minutes. The resulting xdf files occupy 26 Gb of disk space, a significant reduction from the original 115 Gb.</p>
<h2>Generating training and test sets</h2>
<p>In the next step we combine multiple xdf files into a single training and a single test file. We used 2010-2012 data for training and 2013 data for testing. When merging the files, we leverage again multiple cores and rxExec function. The merge is done using binary tree scheme, the merges at the same level are executed in parallel. The following picture shows an example of merging 6 files:</p>
<p><a target="_blank" href="http://revolution-computing.typepad.com/.a/6a010534b1db25970b01b8d1f72d16970c-pi"><img title="6_files" alt="6_files" src="http://revolution-computing.typepad.com/.a/6a010534b1db25970b01b8d1f72d16970c-800wi" border="0"></a></p>
<p>In this example initially 3 pairs of files (1+2, 3+4 and 5+6) are merged in parallel. Then 1+2 is merged with 3+4, and finally 1+2+3+4 is merged with 5+6. <a target="_blank" href="https://github.com/Azure/Azure-MachineLearning-DataScience/blob/master/Misc/MicrosoftR/Samples/NYCTaxiSingleMachine/merge.r">This code</a> implements binary tree merge.</p>
<p>Merging of small files and creation of train.xdf and test.xdf files takes 5.5 hours. The resulting train.xdf file has 506M row and takes 18 Gb. The test file test.xdf has 148M row and takes 6 Gb.</p>
<p><em>An alternative way of preparing the data for modeling would be to convert csv files to a single composite xdf file and then to use it as an input to machine learning algorithm. In this case we can skip the merging step. However, the conversion from csv to composite xdf is done by a single call to rxImport function. This function processes csv files sequentially and is much slower than parallel creation of xdf files and their subsequent merge.</em></p>
<h2>Training and Scoring</h2>
<p>At this point we are ready to do training and scoring. Notice that the training file has 18 Gb and does not fit into memory (which is 14 Gb). Fortunately, implementations of machine learning algorithms in MRS can handle such big datasets. We ran a single iteration of logistic regression over the training set, which took 1 hour and 10 minutes.  Then we ran scoring over the test set, which took 10 minutes.</p>
<p>We performed experiments with 3 feature sets. In the first feature set we used passenger type, trip distance, pickup latitude, pickup longitude, drop-off latitude, drop-off longitude, fare amount, MTA tax, tolls amount, surcharge, duration, hour as numeric features. We also used payment type, rate code, weekday as categorical features. Surprisingly, our model has AUC of 0.984 over the test set. Such a high AUC looks too good to be true and after additional data exploration we have found that the payment type is an excellent predictor is a passenger will leave a tip:</p>
<div>
<div>
<pre>> rxSummary<span>(</span><a target="_blank" href="http://inside-r.org/r-doc/stats/formula"><span>formula</span></a> = label ~ payment_type<span>,</span> <a target="_blank" href="http://inside-r.org/r-doc/utils/data"><span>data</span></a> = <span>&quot;.<span>\\</span>xdf/train.xdf&quot;</span><span>,</span>  
            summaryStats = <a target="_blank" href="http://inside-r.org/r-doc/base/c"><span>c</span></a><span>(</span><span>&quot;Mean&quot;</span><span>,</span><span>&quot;ValidObs&quot;</span><span>)</span><span>,</span> reportProgress = <span>0</span><span>)</span>
 
 
Statistics <a target="_blank" href="http://inside-r.org/r-doc/base/by"><span>by</span></a> category <span>(</span><span>2</span> categories<span>)</span>:
 
 Category                           payment_type Means        ValidObs 
 label <span>for</span> payment_type=Cash        Cash         <span>0.0005490915</span> <span>289816908</span>
 label <span>for</span> payment_type=Credit Card Credit Card  <span>0.9720573755</span> <span>216974429</span></pre>
</div>
</div>
<p>As we see in the training set, 97% of passengers who pay by credit card, leave a tip. But less than 0.1% of passengers who paid by cash left a tip. The test set has similar statistics. Hence predicting the tip at the time of payment is a very easy task. A more interesting task would be to predict if a passenger will leave a tip before the actual payment. We excluded the payment type feature, reran the experiment and got AUC of only 0.553 . This is better than the baseline AUC of 0.5, but not that impressive. At the next step we defined the hour feature as categorical variable and improved slightly the test set AUC to 0.564 . This relatively low AUC indicates that there is lots of room for improving our model.</p>
<p><a target="_blank" href="https://github.com/Azure/Azure-MachineLearning-DataScience/blob/master/Misc/MicrosoftR/Samples/NYCTaxiSingleMachine/train_score.r">This code</a> runs training and scoring with our latest feature set.</p>
<h2>Summary</h2>
<p>The following table summarizes the running times of all steps of our experiment:</p>
<p><a target="_blank" href="http://revolution-computing.typepad.com/.a/6a010534b1db25970b01b7c86d5768970b-pi"><img title="0_table" alt="0_table" src="http://revolution-computing.typepad.com/.a/6a010534b1db25970b01b7c86d5768970b-800wi" border="0"></a></p>
<p>By leveraging Microsoft R Server, we were able to perform the entire process of building and evaluating machine learning model over hundreds of millions of examples using a single commodity machine within a number of hours. Our model is a simple logistic regression with a small set of features. In the future, it will be interesting to develop a model with larger feature set that will have more accurate prediction of tips before the actual payment.</p>
<p>The code used to create this experiment is in <a target="_blank" href="https://github.com/Azure/Azure-MachineLearning-DataScience/tree/master/Misc/MicrosoftR/Samples/NYCTaxiSingleMachine">Github repository</a>.</p>
</div>
<p><div><div><div><div data-width="55" data-show-faces="false" data-send="true" data-layout="box_count" data-height="62" data-href="http://www.r-bloggers.com/using-microsoft-r-server-on-a-single-machine-for-experiments-with-600-million-taxi-rides/"></div></div><div><a data-url="http://www.r-bloggers.com/using-microsoft-r-server-on-a-single-machine-for-experiments-with-600-million-taxi-rides/" data-counturl="http://www.r-bloggers.com/using-microsoft-r-server-on-a-single-machine-for-experiments-with-600-million-taxi-rides/" data-count="vertical" data-text="Using Microsoft R Server on a single machine for experiments with 600 million taxi rides." class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="http://blog.revolutionanalytics.com/2016/06/taxi2.html"> Revolutions</a></strong>.</div>
<hr>
<a rel="nofollow" href="http://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="http://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a title="The R Project for Statistical Computing" rel="nofollow" href="http://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="http://www.r-bloggers.com/?s=data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="http://www.r-bloggers.com/?s=Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="http://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="http://www.r-bloggers.com/?s=map">maps</a>, <a title="Animation in R" rel="nofollow" href="http://www.r-bloggers.com/?s=animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="http://www.r-bloggers.com/?s=RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="http://www.r-bloggers.com/?s=sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="http://www.r-bloggers.com/?s=LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="http://www.r-bloggers.com/?s=SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="http://www.r-bloggers.com/?s=eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="http://www.r-bloggers.com/?s=git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="http://www.r-bloggers.com/?s=hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="http://www.r-bloggers.com/?s=Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="http://www.r-bloggers.com/?s=PCA">PCA</a>, <a title="Time series" rel="nofollow" href="http://www.r-bloggers.com/?s=time+series">time series</a>, <a title="finance trading" rel="nofollow" href="http://www.r-bloggers.com/?s=trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/i2swOROj7qY"></a></div>