fairly novice in data.table so I must be missing something obvious.
DT <- data.table(a=1:4,b=c(0,1,2,3),c=c(0,2,0,3),d=c(0,3,2,1),e=c(0,8,5,3))
a b c d e
1: 1 0 0 0 0
2: 2 1 2 3 8
3: 3 2 0 2 5
4: 4 3 3 1 3
I want to select the rows where columns b,c,d and e are ==0 without assuming I know the column names before hand. So using a colNames <- c('b','c','d','e') vector for it.
I tried
DT[all(DT[,colNames]==0)]
to no avail obviously.
Any pointers welcome !
DT[,colNames] tries to extract a column named colNames from DT. To take colNames as a vector of column names, you can use ..colNames, and then use rowSums to check the condition row wise:
DT[rowSums(DT[, ..colNames] != 0) > 0]
# a b c d e
#1: 2 1 2 3 8
#2: 3 2 0 2 5
#3: 4 3 3 1 3
If selecting rows where b,c,d,e columns are all zero:
DT[rowSums(DT[, ..colNames] != 0) == 0]
# a b c d e
#1: 1 0 0 0 0
Another two options:
use with=FALSE:
DT[rowSums(DT[, colNames, with=FALSE] != 0) > 0]
or use mget:
DT[rowSums(DT[, mget(colNames)] != 0) > 0]
We can use Reduce with +
DT[DT[, !Reduce(`+`, lapply(.SD, `!=`, 0)), .SDcols = colNames]]
# a b c d e
#1: 1 0 0 0 0
For the sake of completeness, there is also a data.table approach using join to subset by variable columns programmatically :
library(magrittr) # just for convenience
# create helper table with selection criteria to join with
mDT <- colNames %>% length() %>% rep(0, .) %>% as.list() %>% as.data.table() %>% setnames(colNames)
# join to subset
DT[mDT, on = names(mDT), nomatch = 0L]
a b c d e 1: 1 0 0 0 0
The helper table mDT looks like:
b c d e 1: 0 0 0 0
This approach will also work with other mDT, e.g.,
mDT <- CJ(a = 3:4, b = 2:3) # cross join
a b 1: 3 2 2: 3 3 3: 4 2 4: 4 3
DT[mDT, on = names(mDT), nomatch = 0L]
a b c d e 1: 3 2 0 2 5 2: 4 3 3 1 3