R - fastest way to select the rows of a matrix that satisfy multiple conditions

Question

This is an extension to the question on returning the rows of a matrix that meet a condition in R. Say I have the matrix:

       one two three four
 [1,]   1   6    11   16
 [2,]   2   7    12   17
 [3,]   3   8    11   18
 [4,]   4   9    11   19
 [5,]   5  10    15   20
 [6,]   1   6    15   20
 [7,]   5   7    12   20

I want to return all rows, where matrix$two == 7 AND matrix$three == 12 as fast as possible. This is the way I know to do it:

 out <- mat[mat$two == 7,]
 final_out <- out[out$three == 12, ]

There should obviously be a method to get the contents of final_out in a one-liner, something like: final_out <- which(mat$two == 7 && mat$three == 12) that is faster and more succinct than the two line of codes above.

What is the fastest R code to return this multiple condition matrix query?

Simon O'Hanlon · Accepted Answer · 2013-08-08T14:36:26.387

12

Just use [ subsetting with logical comparison...

#  Reproducible data
set.seed(1)
m <- matrix( sample(12,28,repl=T) , 7 , 4 )
     [,1] [,2] [,3] [,4]
[1,]    4    8   10    3
[2,]    5    8    6    8
[3,]    7    1    9    2
[4,]   11    3   12    4
[5,]    3    3    5    5
[6,]   11    9   10    1
[7,]   12    5   12    5


#  Subset according to condition
m[ m[,2] == 3 & m[,3] == 12 , ]
[1] 11  3 12  4

edited Aug 08 '13 at 14:36

answered Aug 08 '13 at 14:27

Simon O'Hanlon

58,647
14
142
184

dayne · Answer 2 · 2013-08-08T15:49:08.857

UPDATE USING MICROBENCHMARK:

Using benchmark gives the opposite answer. It seems the answer given by @SimonO101 provides a slightly faster implementation.

require(microbenchmark)
set.seed(1)
m <- matrix( sample(12,100,repl=T) , 25 , 4 )
colnames(m) <- c("one","two","three","four")

bench1 <- microbenchmark(m[which(m[,'two']==7 & m[,'three'] == 12, arr.ind = TRUE),])
summary(bench1$time)
   Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
   7700    8750    9449    9688    9800   22400

bench2 <- microbenchmark(m[ m[,2] == 3 & m[,3] == 12 , ])
summary(bench2$time)
   Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
   6300    7350    7351    7599    8050   15400

OLD ANSWER:

Combining the answers given by @Jiber and @SimonO101 gives a slightly faster answer, at least on my computer.

I made the matrix much larger to separate the computation times.

set.seed(1)
m <- matrix( sample(12,1000000000,repl=T) , 1e8 , 10 )
colnames(m) <- c("one","two","three","four","five","six","seven","eight","nine","ten")

system.time(m[which(m[,'two']==7 & m[,'three'] == 12, arr.ind = TRUE),])
   user  system elapsed 
   6.49    1.58    8.06 
system.time(m[ m[,2] == 3 & m[,3] == 12 , ])
   user  system elapsed 
   8.23    1.29    9.52

This obviously assumes the matrix columns are named.

You should use `library(microbenchmark)` for calculating times...it's much more reliable. — Thomas, Aug 08 '13 at 15:04
@Thomas thanks for the tip. This was my first attempt at benchmarking. I will update the answer after I install the package. — dayne, Aug 08 '13 at 15:08
It can take a little while...look around SO, you'll see other examples of how people use it. — Thomas, Aug 08 '13 at 15:32

Jilber Urbina · Answer 3 · 2013-08-08T14:44:57.870

1

Use which with arr.ind=TRUE as in:

> mat[which(mat[,"two"]==7 & mat[,"three"] == 12, arr.ind = TRUE),]
  one two three four
2   2   7    12   17
7   5   7    12   20

edited Aug 08 '13 at 14:44

answered Aug 08 '13 at 14:26

Jilber Urbina

58,147
10
114
138

Or `mat[mat[,"two"] == 7 & mat[,"three"] == 12,]` – Roland Aug 08 '13 at 14:27
I had not read fully the question and I hadn't realized that the OP has already used `which` – Jilber Urbina Aug 08 '13 at 14:30
Is the $ sub-setting appropriate for matrices? I get an error. – dayne Aug 08 '13 at 14:40

score 1 · Answer 4 · answered Apr 25 '18 at 13:29

If you have a lot of rows, still it would be better to subset first, as you can see in the following code

set.seed(1)
m <- matrix( sample(12,28,repl=T) , 12e6 , 4 )

#  Subset according to condition
microbenchmark(sample0=m[ m[,2] == 3 & m[,3] == 12 , ],times = 10L)

microbenchmark(sample1=m[ m[,2] == 3, ],
           sample2= sample1[sample1[,3] == 12, ],times = 10L)

The results below:

microbenchmark(sample0=m[ m[,2] == 3 & m[,3] == 12 , ],times = 10L)
Unit: milliseconds
expr        min         lq        mean     median         uq        max neval
sample0 342.085212 347.333083 381.6039635 349.920741 375.383425 584.068743    10
microbenchmark(sample1=m[ m[,2] == 3, ],
              sample2= sample1[sample1[,3] == 12, ],times = 10L)
Unit: milliseconds
expr        min         lq        mean      median         uq        max neval cld
 sample1 188.647995 189.832552 215.9355769 194.2375715 199.118962 404.631420    10   b
 sample2   5.097811   5.262028   5.3260160   5.2868025   5.401471   5.571351    10  a

score -2 · Answer 5 · answered Aug 08 '13 at 14:28

-2

the absolute fastest way in R will be ifelse which unlike if allows for vectorized conditionals. You can also cache vectors of conditionals (e.g. isSeven <- mat[, 'two'] == 7) and use/reuse those later.

I don't have a reproducible example here but I would do something like

ifelse(mat[, 'two'] == 7 & mat[, 'three'] == 12, "both", "not both")

You can plop other conditionals in there or have it return anything that will result in a conformable vector.

answered Aug 08 '13 at 14:28

Adam Hyland

878
1
9
21

I don't think `ifelse` will be faster than logical subsetting. Afterall, that's exactly what `ifelse` does internally. – Roland Aug 08 '13 at 14:33

R - fastest way to select the rows of a matrix that satisfy multiple conditions

5 Answers5

Linked