Select only the rows where specific column has highest value

user_14959640 0
Viewed 55

I have 4 matrices with random values for example -

      Matrix 1                    Matrix 2             Matrix 3             Matrix 4
    [,1] [,2] [,3]           [,1] [,2] [,3]        [,1] [,2] [,3]        [,1] [,2] [,3]          
[1,] 10   21   36        [1,] 10   13  100     [1,] 12   16   12     [1,] 61   12   20
[2,] 15   21   45        [2,] 12   47  10      [2,] 16   51   10     [2,] 21   15   400 
[3,] 46   23   102       [3,] 54   50  42      [3,] 17   73   14     [3,] 13   31   12

I want to select only the rows which has the highest value in the 3rd column [,3] - Here I want a final matrix as displayed below -

Final Matrix 
    [,1] [,2] [,3] 
[1,] 10   13  100  
[2,] 21   15  400
[3,] 46   23  102

Some suggestions would be helpful using subset() or other functions in R?

r
3 Answers

We can use max.col after placing the matrixes in a list

lst1 <- list(Matrix1, Matrix2, Matrix3, Matrix4)
i1 <-  max.col(sapply(lst1, `[`, ,3))
do.call(rbind, Map(function(m, i) m[i, ], lst1[i1], 1:3))

-output

#     [,1] [,2] [,3]
#[1,]   10   13  100
#[2,]   21   15  400
#[3,]   46   23  102

Or another option is to convert to a 3D array, and use indexing to extract

ar1 <- array(c(Matrix1, Matrix2, Matrix3, Matrix4), dim = c(3, 3, 4))
i1 <- max.col(ar1[, 3, ])
rind <- seq_len(nrow(Matrix1))
array(ar1[as.matrix(cbind(expand.grid(rind, rind), i1))], dim(Matrix1))
#      [,1] [,2] [,3]
#[1,]   10   13  100
#[2,]   21   15  400
#[3,]   46   23  102

data

Matrix1 <- structure(c(10, 15, 46, 21, 21, 23, 36, 45, 102), .Dim = c(3L, 
3L))

Matrix2 <- structure(c(10, 12, 54, 13, 47, 50, 100, 10, 42), .Dim = c(3L, 
3L))

Matrix3 <- structure(c(12, 16, 17, 16, 51, 73, 12, 10, 14), .Dim = c(3L, 
3L))

Matrix4 <- structure(c(61, 21, 13, 12, 15, 31, 20, 400, 12), .Dim = c(3L, 
3L))
user_37322710

A solution with dplyr:

library(dplyr)
# the data
Matrix1 <- structure(c(10, 15, 46, 21, 21, 23, 36, 45, 102), .Dim = c(3L,3L))
Matrix2 <- structure(c(10, 12, 54, 13, 47, 50, 100, 10, 42), .Dim = c(3L,3L))
Matrix3 <- structure(c(12, 16, 17, 16, 51, 73, 12, 10, 14), .Dim = c(3L,3L))
Matrix4 <- structure(c(61, 21, 13, 12, 15, 31, 20, 400, 12), .Dim = c(3L,3L))

# matrices to tibble
x <- as.tibble(rbind(Matrix1, Matrix2, Matrix3, Matrix4))

# dplyr manipulation
x1 <- x %>% 
  dplyr::mutate(group = as.integer(gl(n(), 3, n()))) %>% # create group 
  group_by(group) %>% 
  dplyr::mutate(Max = max(V1, V2, V3, na.rm = TRUE)) %>% # get max
  filter(Max == V3) %>% # max = max of column3
  ungroup() %>% 
  select(-group, -Max)

#tibble to matrix
x1 <- as.matrix(x1)

     V1 V2  V3
[1,] 46 23 102
[2,] 10 13 100
[3,] 21 15 400
user_133216470

You may do this with mapply and which.max.

t(mapply(function(x) x[which.max(x[,3]),,drop=F], list(m1, m2, m3)))
#      [,1] [,2] [,3]
# [1,]   46   23  102
# [2,]   10   13  100
# [3,]   17   73   14

Data:

m1 <- structure(c(10L, 15L, 46L, 21L, 21L, 23L, 36L, 45L, 102L), .Dim = c(3L, 
3L))

m2 <- structure(c(10L, 12L, 54L, 13L, 47L, 50L, 100L, 10L, 42L), .Dim = c(3L, 
3L))

m3 <- structure(c(12L, 16L, 17L, 16L, 51L, 73L, 12L, 10L, 14L), .Dim = c(3L, 
3L))

m4 <- structure(c(61L, 21L, 12L, 15L, 20L, 400L), .Dim = 2:3)
user_65740380
Related