Applying functions on groups: sqldf, plyr, doBy, aggregate or data.table ?

March 17, 2011
By

(This article was first published on Recipes, scripts and genomics, and kindly contributed to R-bloggers)

Which one of the sqldf, plyr, doBy and aggregate functions/packages would be faster for applying functions on groups of rows? I was wondering about this earlier in this post.  It seems sqldf would be the fastest according to a post in manipulatr mail list.

Well, here is the ranking from the fastest to the slowest:



(check the link for the post above for details on comparison)

The downside of sqldf is that you will be able to use only sql functions on groups, but in the rest of the other options you can apply R functions on groups.

EDIT: I repeated the experiment on my own computer, this time I included data.table package as well. The new experiment shows that sqldf is still the fastest but data.table ranks second now. But I have been warned that the mean() function has large overhead and in fact if I use .Internal(mean(x)) instead of mean(x) for the group operations, data.table is the fastest!!! Scroll down to see another comparison of the grouping functions, this time using sum() function which doesn't seem to have a large overhead.

The code is below. I mostly copy-pasted from the post at manipulatr, except the data.table part and the plot.





EDIT:  More unbiased way to measure this is to use sum() function on groups. When I do that, data.table comes first!

To leave a comment for the author, please follow the link and comment on his blog: Recipes, scripts and genomics.

R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...



If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Comments are closed.