R SQL-ish aggregation

September 10, 2010

(This article was first published on Struggling Through Problems » R, and kindly contributed to R-bloggers)

I came to R from SQL. I’ve heard that packages such as reshape and plyr are quite beautiful, but to me they are somewhat non-intuitive. I’m always looking for

SELECT mean(sodium) as mean, variance(sodium) as var
    FROM texas GROUP BY weight;

I struggled with this a bit. First, it’s tempting to use tapply(), except that’s more of a one-column-in, one-column-out sort of thing. There’s by(), which is nice, and with a short wrapper can be OK. Well here’s what I did.

First, the pretty stuff. Because R, while it is generally considered a practical language, is, to me, more of a pretty language. A great way to make things pretty in R is to abuse dynamic binding.

Say I have a table like this:

> Table
  Alice Bob       Cathy Doug
1     1   1  0.08309254    a
2     2   2 -0.76015321    a
3     3   3  0.30887905    a
4     4   4  0.74900962    a
5     1   5  0.69447607    a
6     2   6  0.32485197    a

I’d like to call select() like this:

select(Table, Alice,
        a = mean(Cathy * Doug),
        b = sum(Cathy - Doug) / sum(Doug)

Where ‘Alice’ gives the column to group by, and a=, b= give the columns to create.

So this could look like

select = function(Table, Key, Add.Key=T, ...) {
        Columns = as.list(substitute(list(...)))[-1L]

        S.Key    = substitute(Key)
        Key.Str  = deparse(S.Key)
        Group.By = eval(S.Key, envir=Table)



First, the as.list(substitute(list(…)))[-1L] trick to get the unevaluated expressions for the columns. Then substitute(Key) gets the unevaluated key. deparse(S.Key) gives it a name (better would let the caller specify the name, but I haven’t wanted to do that yet so I didn’t implement it).

Group.By = eval(S.Key, envir=Table)

evaluates the grouping column, so now we have it as a vector, with (hopefully) the same number of rows as the table.

Now the next step turned out to suffer some unusual efficiency problems. The obvious approach is to use by() to split Table into many subtables, and evaluate the output columns on all of those. This works, but when the number of output rows is very large, this is extraordinarily slow.

An implementation using by() would look like this

select1  = function(Table, Key, Add.Key=T, ...) {
        Columns = as.list(substitute(list(...)))[-1L]

        S.Key = substitute(Key)
        Key.Str = deparse(S.Key)
        Group.By = eval(S.Key, envir=Table)
        Table[[Key.Str]] = Group.By

        Group.Gen = function(Group) {
                List.Out = sapply(Columns, function(Col) {
                        eval(Col, envir=Group)

                if (Add.Key) {
                        List.Key = list()
                        List.Key[[Key.Str]] = unique(Group[[Key.Str]])

                        List.Out = c(List.Key, List.Out)


        Group.List = by(Table, Group.By, Group.Gen, simplify=F)
        names(Group.List) = c()

        Result  = do.call(rbind, Group.List)

  1. Split it into tables with by().
  2. Evaluate the columns in each subtable.
  3. rbind() the outputs together.

The following implementation, which, instead of using by(), deals with vectors directly (not making a data.frame until the end), is about 10 times faster.

select2  = function(Table, Key, Add.Key=T, ...) {
        Columns = as.list(substitute(list(...)))[-1L]

        S.Key    = substitute(Key)
        Key.Str  = deparse(S.Key)
        Group.By = eval(S.Key, envir=Table)

        Indices  = tapply(1:nrow(Table), Group.By, c)

        Col.List = as.list(Table)

        Col.Out.List = lapply(Columns, function(Column) {
                sapply(Indices, function(Group) {
                        Subset = lapply(Col.List, function(Col) Col[Group])
                        eval(Column, envir=Subset)
        if (Add.Key) {
                Key.List = list();
                Key.List[[Key.Str]] = sapply(Indices, function(Group) {

                Col.Out.List = c(Key.List, Col.Out.List)

        Table.Out = do.call(data.frame, Col.Out.List)


With a large table

Table = data.frame(
        Alice = rep(c(1, 2, 3, 4), 1000),
        Bob   = rep(1:2000, 2),
        Cathy = rnorm(4000),
        Doug  = rep('a', 4000)

The difference becomes apparent

> system.time(select1(Table, Bob, m=mean(Cathy)))
   user  system elapsed
  3.000   0.010   3.026
> system.time(select2(Table, Bob, m=mean(Cathy)))
   user  system elapsed
  0.290   0.000   0.286 

Unfortunately, when the number of output rows is very, very large, even select2() becomes too slow (and much slower than tapply()) (plyr is even slower). The best solutions I have found are

  • If there’s only one input column and one output column, use tapply().
  • Use sqldf to do the query it SQLite.

To leave a comment for the author, please follow the link and comment on their blog: Struggling Through Problems » R.

R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: Data science, Big Data, R jobs, 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.


Mango solutions

plotly webpage

dominolab webpage

Zero Inflated Models and Generalized Linear Mixed Models with R

Quantide: statistical consulting and training





CRC R books series

Six Sigma Online Training

Contact us if you wish to help support R-bloggers, and place your banner here.

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)