<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/timing-column-indexing-in-r/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://www.win-vector.com/blog/2018/09/timing-column-indexing-in-r/"> R – Win-Vector Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>I’ve ended up (almost accidentally) collecting a number of different solutions to <a rel="nofollow" href="http://www.win-vector.com/blog/2018/09/using-a-column-as-a-column-index/" target="_blank">the “use a column to choose values from other columns in R” problem</a>.</p>
<p>Please read on for a brief benchmark comparing these methods/solutions.</p>
<p><span></span></p>
<p>
<p>What we did is: build a 1,000,000 row variation of the original example. In this variation we ensured that the selections are all valid column names, and removed any extra code that the methods had included to defend against mis-matches. We then <a rel="nofollow" href="https://github.com/WinVector/Examples/blob/master/TimeColumnLookups/timings.md" target="_blank">timed 13 solution variations</a>. We present the results in a graph here.</p>
<p><img border="0" src="https://i0.wp.com/www.win-vector.com/blog/wp-content/uploads/2018/09/present-1.png?resize=660%2C471" alt="Present 1" width="450" title="present-1.png" data-recalc-dims="1"></p>
<p>For this plot: being more to the left (having smaller run times) is better. We have marked 1 second as an important perceptual boundary (things longer than one second tend to be perceived as slow).</p>
<p>We have grouped the solution methods by the implementing family (base R, data.table, tidyverse). Each run is repeated 10 times in random order to try an get a good measurement. There are also several variations of solution strategy (matrix index, row-wise operation, and grouping by column choice to make the choice piecewise constant).</p>
<p>In this first graph we see that most methods run-times are under 1 second of run-time, “base_R_split_apply” is just about 1.5 seconds, and there is a remaining set of slow methods (typically taking between 9 to 72 seconds). All of the slow methods (base_R_sapply, base_R_get0, dplyr_rowwise_parse, dplyr_rowwise_index, purr_get0) are variations on user code running over rows. This confirms that running user code per-row is a usually an inefficient way to do things: even if you dress it up with an apply, map, rowwise, or so-on. One really wants to write vectorized code where the user code is written in terms of columns, and R itself (or a C/C++ package) is dealing with the rows.</p>
<p>The observed mean runtimes are given here.</p>
<pre>
               method  mean_seconds
   rqdatatable_direct   0.054
   dplyr_group_assign   0.063
 data.table_SD_method   0.106
  data.table_I_method   0.110
  base_R_matrix_index   0.136
     rqdatatable_full   0.172
  dplyr_choice_gather   0.676
   base_R_split_apply   1.547
          base_R_get0   9.038
           purrr_get0   9.483
        base_R_sapply  24.006
  dplyr_rowwise_index  71.826
  dplyr_rowwise_parse  72.227
</pre>
<p>Note: the fast timings have noise which makes ordering the fast methods by mean difficult. For instance rqdatatable_direct must be slower than data.table_I_method, as it essentially calls it. Also note: the data.table methods are paying the cost of copying/converting in their timings (which may not be entirely fair to data.table). Notice the means confuse method order, and the medians (portrayed on the graph) seem more reliable. Some of the variation in run times (and hence means) may be garbage collects (independent of method), which would require a large number of runs to average out of the fast measurements.</p>
<p>We can increase the legibility of the fast methods by switching to a log-scale plot.</p>
<p><img border="0" src="https://i0.wp.com/www.win-vector.com/blog/wp-content/uploads/2018/09/present-2.png?resize=660%2C471" alt="Present 2" width="450" title="present-2.png" data-recalc-dims="1"></p>
<p>In this plot (which expands detail of the fast method timings at the expense of compressing detail of the slow method timings) we can see the data.table_I_method is routinely the fastest. This makes sense as this is an in-place indexing method designed to minimize data-motion. The rqdatatable timings are timings of rqdatatable using data.table to solve the problem in direct mode and base-R to solve the problem in full mode (it can also use data.table in full mode but we have not performed this timing which would fall between the two rqdatatable timings observed). Please keep in mind rqdatatable is not part of data.table, but a user of data.table.</p>
<p>Some notes on solution strategies:</p>
<ul>
<li>The dplyr_group_assign solution is a port of the data.table_SD_method to dplyr notation.</li>
<li>The base_R_split_apply solution is also is a port of the data.table_SD_method to base-R methods.</li>
<li>The base_R_matrix_index method is technique apparently unique to base-R (likely spending most of its time in a possibly avoidable match() sub-step). </li>
<li>The dplyr_choice_gather method is interesting as it uses a data-reshaping (a tidyr::gather()). This may, at first, sound heavy-handed but it is in fact a very good idea. In fact in a database the data would have been stored in a thin/tall configuration (each fact in its own row, instead of multiple facts per row) and a join/filter based solution would be the natural solution.</li>
</ul>
<p>Notice how solution strategy has a large influence on method speed. For example the splitting strategy is in general good, independent of package. Likewise row-wise strategies are bad, independent of package. Rowwise performance varies from pacakge to package, but avoiding user-rowwise code is a more important point.</p>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="http://www.win-vector.com/blog/2018/09/timing-column-indexing-in-r/"> R – Win-Vector Blog</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/j7BUxG-J0gk" height="1"></a></div>