Column names in a list/multi column grouped criteria in data.table

user_2085438 0
Viewed 66
DT <- data.table(criteria=c("a","b","c","d","d","c","b","a"),
val1=1:8,
val2=c(-1,1,2,3,3,2,1,1),
val3=c(-3,2,0,3,3,0,2,3),
val4=c(5,3,2,1,1,2,3,-5),
val5=c(1,8,5,3,3,5,8,-1))   

    criteria val1 val2 val3 val4 val5
1:        a    1   -1   -3    5    1
2:        b    2    1    2    3    8
3:        c    3    2    0    2    5
4:        d    4    3    3    1    3
5:        d    5    3    3    1    3
6:        c    6    2    0    2    5
7:        b    7    1    2    3    8
8:        a    8    1    3   -5   -1

I want to select the rows where, grouped by the column criteria (doing a sum of the values), columns val2 to val5 are equal to zero (bonus points for a solution that takes a function as criteria, so not limited to the case where the criteria is ==0) without assuming I know the column names before hand. So using a colNames <- c('val2','val3','val4','val5') vector for it.

In the example above the result should be rows 1 and 8.

In the case where I would not use an additional criteria I could do :

DT[DT[, Reduce(`|`, lapply(.SD, function(x){return(x==0)})), .SDcols = colNames]]

But I can't figure out how to add the additional grouping criteria.

Any pointers welcome !

data.table grouping r
2 Answers

Is there a specific reason you might be opposed to chaining? You could do:

DT[, sum := sum(.SD), by= criteria, .SDcols = colNames][sum==0]

#   criteria val1 val2 val3 val4 val5 sum
#1:        a    1   -1   -3    5    1   0
#2:        a    8    1    3   -5   -1   0

Or without chaining, you do the group by to get the rows and then select those from DT:

DT[DT[, .I[sum(.SD)==0], by= criteria, .SDcols = colNames]$V1]

As requested, if you want a generic function in the subsetting you could do:

DT[DT[, .I[vapply(list(.SD), function(x) sum(x) == 0, logical(1))], by= criteria, .SDcols = colNames]$V1]

It's important to note that I use vapply so that I'm ensuring I return a logical vector. You could use sapply or lapply but you'd need to make sure you're returning the correct type of vector otherwise the subsetting won't work. I also use list(.SD) so that it will perform the sum on all of .SD at once rather than each individual column.

user_61057200

using a bit of base R and library(magrittr)

 s=function(m){
 DT[aggregate(.~criteria,DT[-2],sum)%>%
      {.[apply(.,1,function(x)any(x==m)),1]}%>%{DT[,1]%in%.},]
 }

 s(0)#sum==0
   criteria val1 val2 val3 val4 val5
 1        a    1   -1   -3    5    1
 3        c    3    2    0    2    5
 6        c    6    2    0    2    5
 8        a    8    1    3   -5   -1


 s(4)#Sum==4
 criteria val1 val2 val3 val4 val5
 2        b    2    1    2    3    8
 3        c    3    2    0    2    5
 6        c    6    2    0    2    5
 7        b    7    1    2    3    8
user_83802720
Related