Calculate sum of a list of variables by group

data.table, r

Solution

Your test data doesn't match the example you gave, but regardless - you can take advantage of the fact that `data.table()` has an operator named `.SD` for "subset of data. So this should work:

x[, lapply(.SD, sum), by = ID]
#----
     ID Count Count2 Count3
1:  210    13      5      5
2: 3917     5      5      5

This is actually covered in the FAQ: type `vignette("datatable-faq", package="data.table")` or find it online.

Problem

I have a data.table with one key and about 100 numeric rows, one of which is set to key. I would like to create a new variable that contains summation of each numeric rows, grouped by key. For example, my data right now is ``` ID Count1 Count2 Count3 1 1 3 0 1 3 3 3 2 1 2 1 3 1 1 2 ``` What I would like to have is: ``` ID Count1 Count2 Count3 1 4 6 3 2 1 2 1 3 1 1 2 ``` I have tried so many ways to get this. I know I can do: ``` Y <- X[, list(Count=sum(Count1), Count2=sum(Count2), Count3=sum(Count3), by = ID] ``` However, I have hundreds of variables, and I only get their names on a list. How should I go about handling this? Thanks a lot for your help. Here is a code to generate test data: ``` ID <-c(rep(210, 9), rep(3917,6)) Count1 <- c(1,1,0,1,3,1,4,1,1,1,1,1,1,0,1) Count2 <- c(1,0,0,1,0,1,0,1,1,1,1,1,1,0,1) Count3 <- c(1,0,0,1,0,1,0,1,1,1,1,1,1,0,1) x <- data.table(ID, Count1, Count2, Count3) setkey(x, ID) ```

Original source