faster way to create variable that aggregates a column by id

Question

Is there a faster way to do this? I guess this is unnecessary slow and that a task like this can be accomplished with base functions.

df <- ddply(df, "id", function(x) cbind(x, perc.total = sum(x$cand.perc)))

I'm quite new to R. I have looked at by(), aggregate() and tapply(), but didn't get them to work at all or in the way I wanted. Rather than returning a shorter vector, I want to attach the sum to the original dataframe. What is the best way to do this?

Edit: Here is a speed comparison of the answers applied to my data.

> # My original solution
> system.time( ddply(df, "id", function(x) cbind(x, perc.total = sum(x$cand.perc))) )
   user  system elapsed 
 14.405   0.000  14.479 

> # Paul Hiemstra
> system.time( ddply(df, "id", transform, perc.total = sum(cand.perc)) )
   user  system elapsed 
 15.973   0.000  15.992 

> # Richie Cotton
> system.time( with(df, tapply(df$cand.perc, df$id, sum))[df$id] )
   user  system elapsed 
  0.048   0.000   0.048 

> # John
> system.time( with(df, ave(cand.perc, id, FUN = sum)) )
       user  system elapsed 
      0.032   0.000   0.030 

> # Christoph_J
> system.time( df[ , list(perc.total = sum(cand.perc)), by="id"][df])
   user  system elapsed 
  0.028   0.000   0.028 

Answer 1

Since you are quite new to R and speed is apparently an issue for you, I recommend the data.table package, which is really fast. One way to solve your problem in one line is as follows:

library(data.table)
DT <- data.table(ID = rep(c(1:3), each=3),
                 cand.perc = 1:9,
                 key="ID")
DT <- DT[ , perc.total := sum(cand.perc), by = ID]
DT
      ID Perc.total cand.perc
 [1,]  1          6         1
 [2,]  1          6         2
 [3,]  1          6         3
 [4,]  2         15         4
 [5,]  2         15         5
 [6,]  2         15         6
 [7,]  3         24         7
 [8,]  3         24         8
 [9,]  3         24         9

Disclaimer: I'm not a data.table expert (yet ;-), so there might faster ways to do that. Check out the package site to get you started if you are interested in using the package: http://datatable.r-forge.r-project.org/

Answer 2

For any kind of aggregation where you want a resulting vector the same length as the input vector with replicates grouped across the grouping vector ave is what you want.

df$perc.total <- ave(df$cand.perc, df$id, FUN = sum)

Answer 3

Use tapply to get the group stats, then add them back into your dataset afterwards.

Reproducible example:

means_by_wool <- with(warpbreaks, tapply(breaks, wool, mean))
warpbreaks$means.by.wool <- means_by_wool[warpbreaks$wool]

Untested solution for your scenario:

sum_by_id <- with(df, tapply(cand.perc, id, sum))
df$perc.total <- sum_by_id[df$id]

Answer 4

Why are you using cbind(x, ...) the output of ddply will be append automatically. This should work:

ddply(df, "id", transform, perc.total = sum(cand.perc))

getting rid of the superfluous cbind should speed things up.

Answer 5

ilprincipe if none of the above fits your needs you could try transposing your data

dft=t(df)

then use aggregate

dfta=aggregate(dft,by=list(rownames(dft)),FUN=sum)

next have back your rownames

rownames(dfta)=dfta[,1]
dfta=dfta[,2:ncol(dfta)]

Transpose back to original orientation

df2=t(dfta)

and bind to original data

newdf=cbind(df,df2)

Answer 6

You can also load up your favorite foreach backend and try the .parallel=TRUE argument for ddply.