Calculate between columns in data.table or dplyr?

data.table, dplyr, r

Solution

If I understand correct, you have twelve fields and wish to keep hardcoding to a minimum. I'm not quite sure what your intended output is but hopefully it's one of the two results below -

colstomean <- setdiff(colnames(DT),c('ID','level'))

Option 1, mean of each variable values within that ID

DT[, lapply(.SD, mean, na.rm=TRUE), 
   by=ID, 
   .SDcols = colstomean
   ]

Output -

    ID val1        val2
1: ID1  1.5  0.37648090
2: ID2  3.5 -0.55484848
3: ID3  5.5 -0.07326365
4: ID4  7.5 -0.37705525
5: ID5  9.5 -0.08075406

Option 2, mean of all variable values within that ID

DT[, mean(unlist(.SD), na.rm = TRUE), 
    by=ID, 
   .SDcols = colstomean
   ]

Output

    ID        V1
1: ID1 0.9382404
2: ID2 1.4725758
3: ID3 2.7133682
4: ID4 3.5614724
5: ID5 4.7096230

Problem

I want to use `data.table` to achieve a very simple task for a large dataset. Calculate mean of val1 and val2 for each ID. For details, please refer to the attached fake data. ``` library(data.table) DT <- data.table(ID = paste0("ID",rep(1:5,each=2)), level= rep(c("CTRL","CTRL","ID1","ID2","ID3"),2), val1 = 1:10, val2 = rnorm(10)) ``` Here I want to calculate for each ID, the mean of val1 and val2. Also notice that in each ID, there're different levels. But for each unique ID, I just want one mean incorporating the different levels, val1, and val2. --- ID | Mean --- -- ID1 | ... -- ID2 | ... -- ID3 | ... I tried the following code, but it doesn't work. ``` topagents <- DT[, mean = mean(list(val1,val2)), by = ID] ``` but it doesn't work. I know how to do it in `reshape2`, first `melt` and then `dcast`. But the original dataset is relatively large with 20M rows and 12 fields, it takes quite a long time to do the calculation. So I prefer to use `data.table` or `dplyr`.

Original source

Related problems