select rows where value in specific column is max for the row across all columns

user_12857915 0
Viewed 138

I'm trying to select rows in a dataframe, where value in X1 is the maximum value across all rows.

X1 <- c(0.7,0.6,0.2,0.5,0.2,0.1,0.1,0.1,0.3,0.2)
X2 <- c(0.1,0.2,0.6,0.3,0.7,0.8,0.1,0.1,0.2,0.7)
X3 <- c(0.1,0.2,0.8,0.2,0.2,0.1,0.4,0.2,0.1,0.2)
df <- data.frame(X1,X2,X3)

I can see from visualizing the data that X1 is the maximum for rows 1,2,4,9. I tried writing a something like this with a loop but no success.

df[ df$X1[i] == max(df[ [i] ,] ) , ]

Does it need to be done by loop, or can it be done using a function? Any help is greatly appreciated. Thanks

r
6 Answers

do.call(pmax, df) will give you maximum value in each row, you can compare that with X1 to select rows where maximum value in a row is X1.

df[df$X1 == do.call(pmax, df), ]

#   X1  X2  X3
#1 0.7 0.1 0.1
#2 0.6 0.2 0.2
#4 0.5 0.3 0.2
#9 0.3 0.2 0.1

The same can also be implemented with apply :

df[df$X1 == apply(df, 1, max), ]
user_39629140

Another way of dealing with it, in case you are interested:

library(dplyr)

df %>%
  rowwise() %>%
  filter(max(c_across(X1:X3)) == X1)

# A tibble: 4 x 3
# Rowwise: 
     X1    X2    X3
  <dbl> <dbl> <dbl>
1   0.7   0.1   0.1
2   0.6   0.2   0.2
3   0.5   0.3   0.2
4   0.3   0.2   0.1
user_143145200

We can use max.col from base R

df[max.col(df, "first") == 1,]
#  X1  X2  X3
#1 0.7 0.1 0.1
#2 0.6 0.2 0.2
#4 0.5 0.3 0.2
#9 0.3 0.2 0.1
user_37322710

Another base R option using rowSums

> subset(df, rowSums(df > X1) == 0)
   X1  X2  X3
1 0.7 0.1 0.1
2 0.6 0.2 0.2
4 0.5 0.3 0.2
9 0.3 0.2 0.1
user_121587570

Using dplyr and max.col is a clean solution to this problem:

library(dplyr)
df%>%filter(max.col(.)==1)

# A tibble: 4 x 3
# Rowwise: 
     X1    X2    X3
  <dbl> <dbl> <dbl>
1   0.7   0.1   0.1
2   0.6   0.2   0.2
3   0.5   0.3   0.2
4   0.3   0.2   0.1
user_139723330

One more approach using pmap inside filter

library(tidyverse)
df %>% filter(X1 == pmap_dbl(., pmax))

#>    X1  X2  X3
#> 1 0.7 0.1 0.1
#> 2 0.6 0.2 0.2
#> 3 0.5 0.3 0.2
#> 4 0.3 0.2 0.1

Created on 2021-06-09 by the reprex package (v2.0.0)

user_28848590
Related