Data frame with multiple colums, from a group of same values in one column select the maximum in the other column

user_11593995 0
Viewed 84

I have the following data frame:

DF <- data.frame(A=c(0.1,0.1,0.1,0.1,0.2,0.2,0.2,0.3,0.4,0.4 ), B=c(1,2,1,5,10,2,3,1,6,2), B=c(1000,50,400,6,300,2000,20,30,40,50))

and I want to filter DF for each group of equal values in A select the Maximum in B.

For example for 0.1 in A the maximum in B is 5.

Ending with the new data frame:

A    B    C  
0.1  5    6  
0.2  10   300  
0.3  1    30  
0.4  6    40

I am not sure if this a problem to solve with base R or with a library. Because I am thinking to use dplyr and group A. I am correct?

r
3 Answers

There are a couple of base R options:

  • Using subset + ave
> subset(DF,as.logical(ave(B,A,FUN = function(x) x == max(x))))
    A  B B.1
4 0.1  5   6
5 0.2 10 300
8 0.3  1  30
9 0.4  6  40
  • Using merge + aggregate
> merge(aggregate(B~A,DF,max),DF)
    A  B B.1
1 0.1  5   6
2 0.2 10 300
3 0.3  1  30
4 0.4  6  40
user_121587570

An option with data.table where group by 'A', get the index where 'B' is max with which.max, wrap with .I to return the row index. If we don't specify or rename, by default, it returns as 'V1' column, which we extract as vector to subset the rows of dataset

library(data.table)
setDT(DF)[DF[, .I[which.max(B)], A]$V1]

-output

#     A  B B.1
#1: 0.1  5   6
#2: 0.2 10 300
#3: 0.3  1  30
#4: 0.4  6  40
user_37322710

You're right, using dplyr and grouping by A, you can use slice_max() (also from dplyr) to select the max value in B for each group

library(dplyr)

DF %>% 
  group_by(A) %>% 
  slice_max(B)

Output:

# A tibble: 4 x 3
# Groups:   A [4]
      A     B     C
  <dbl> <dbl> <dbl>
1   0.1     5     6
2   0.2    10   300
3   0.3     1    30
4   0.4     6    40
user_119638300
Related