# Finding both count and average of a column in R data.table, after group by

I have a data table of 5 columns. I want to do a group by on one column and want to get the count of another column, id.

I do this in this way: dt[,length(id),by=dt$cid]. If I also want to get sum of id, how do I proceed? I tried this: dt[,(sum(id) length(id)),by=dt$cid]


Thanks

• You could use the sqldf package. Aug 4 '15 at 1:08

If you want to get but the sum and length of id with data.table, you can do it like this:

dt[,.(total=sum(id), n=length(id)), by=cid]


You could even write this in shorter format with the .N operator of data.table:

dt[,.(total=sum(id), .N), by=cid]

• +1 for using .N. This is probably the most efficient.
– Sam
May 19 '16 at 15:32

Use tapply,

with(dt,tapply(id, cid, FUN=sum))

• This will only find the sum. And it is better to say "You could use" Sep 4 '15 at 7:10

You can use tapply or aggregate along with a custom function.

aggregate(Id~cid, data=dt, FUN=function(x) c(mn=mean(x),ln=length(x)))

You may like to see my blog where I discuss few other scenario.

http://r4sqlminded.blogspot.com/2015/06/selecting-data-from-data-frame.html