Check rows from all columns that matches specific value

Question

If I have a data.table:

d <- data.table("ID" = c(1, 2, 2, 4, 6, 6), 
                "TYPE" = c(1, 1, 2, 2, 3, 3), 
                "CLASS" = c(1, 2, 3, 4, 5, 6))

I know I can remove values greater than a specific value like this:

r <- d[!(d$TYPE > 2), ]

However, if I want to apply this to all of the columns in the entire table instead of just TYPE (basically drop any rows that have a value > 2 in the entire table), how would I generalize the above statement (avoiding using a for loop if possible).

I know I can do d > 2 resulting in a boolean index table, but if I put that into the above line of code it give me an error:

d[!d>2, ]

Results in a invalid matrix type

Note

It was brought up that this question is similar to Return an entire row if the value in any specific set of columns meets a certain criteria. However, they are working with a data.frame and I am working with a data.table the notation is different. Not a duplicate question due to that.

Possible duplicate of [Return an entire row if the value in any specific set of columns meets a certain criteria](https://stackoverflow.com/questions/51540364/return-an-entire-row-if-the-value-in-any-specific-set-of-columns-meets-a-certain) — IceCreamToucan, Jul 27 '18 at 19:57
@Ryan They are working with a `data.frame` and I am working with a `data.table` the notation is different. Not a duplicate question due to that. — Bear, Jul 27 '18 at 20:20
Do you want to include the `ID` column in the check or should this be exempt? — Uwe, Jul 28 '18 at 06:08

score 2 · Accepted Answer · answered Jul 27 '18 at 19:04

2

Using apply with any

d[!apply(d>2,1,any)]
   ID TYPE CLASS
1:  1    1     1
2:  2    1     2

Or rowSums

d[rowSums(d>2)==0,]
   ID TYPE CLASS
1:  1    1     1
2:  2    1     2

answered Jul 27 '18 at 19:04

BENY

317,841
20
164
234

What if we want to ignore the `ID` column? @Wen – Bear Jul 27 '18 at 19:06
1

@KAS `d[rowSums((d>2)[,!(colnames(d) == "ID")])==0,]` or `d[!apply((d>2)[,!(colnames(d) == "ID")],1,any)]` – BENY Jul 27 '18 at 19:08
2

or `d[rowSums(d[, -'ID'] > 2) == 0]` – IceCreamToucan Jul 27 '18 at 20:28

score 2 · Answer 2 · edited Jul 28 '18 at 12:23

I was wondering what the fastest approach would be for a varying number of rows and columns.

So, here is a benchmark.

It excludes the ID column from being checked for which is not exactly in line with OP's question but is a sensible decision, IMHO.

library(data.table)
library(bench)
bm <- press(
  n_row = c(1E1, 1E3, 1E5),
  n_col = c(2, 10, 50),
  {  
    set.seed(1L)
    d <- data.table(
      ID = seq_len(n_row),
      matrix(sample(10, n_row*n_col, TRUE), ncol = n_col)
    )
    mark(
      m1 = d[d[, !apply(.SD > 2, 1, any), .SDcols = -"ID"]],
      m2 = d[!d[, apply(.SD > 2, 1, any), .SDcols = -"ID"]],
      m3 = d[!d[, which(apply(.SD > 2, 1, any)), .SDcols = -"ID"]],
      m4 = d[d[, rowSums(.SD > 2) == 0, .SDcols = -"ID"]],
      m5 = d[!d[, Reduce(any, lapply(.SD, `>`, y = 2)), by = 1:nrow(d), .SDcols = -"ID"]$V1]
    )
  })

ggplot2::autoplot(bm)

Apparently, the rowSums() approach is almost always the fastest method.

Check rows from all columns that matches specific value

2 Answers2