Select group with highest mean in R

user_12689934 0
Viewed 157

I've been dealing for a while with this problem but still haven't found the optimal solution. Hope you can help me.

I have a dataset with several columns, that I need to filter. As an example let's say that I have the following:

DB <- data.frame(A = c(rep("GeneA",6), rep("GeneB",6)), B = c("one", "one", "two", "two", "three", "three", "one", "one", "two", "two", "three", "three"), C = c(1,2,5,4,8,5,8,7,4,5,1,8))

What I would like to have is a filtered dataset for each value in column A with the groups in column B with highest mean.

In that case the desired output would be:

DB <- data.frame(A = c("GeneA","GeneA","GeneB","GeneB"), B = c("three", "three", "one", "one"), C = c(8,5,8,7))

Searching on the web only have found the case where you can filter the highest value of each group and I need each row of the group.

With:

result <- DB %>%
  group_by(A,B) %>%
  summarize(c = mean(C))

I just obtain the means. I have also tried with aggegate, etc. but no way. And i'm sure that there is a easy way, probably with data.table.

r group-by
3 Answers

Not sure if this is what you are looking for. Using dplyr:

DB %>%
  group_by(A,B) %>%
  mutate(D = mean(C)) %>%
  group_by(A) %>%
  filter(D==max(D)) %>%
  select(-D)

returns

# A tibble: 4 x 3
# Groups:   A [2]
  A     B         C
  <chr> <chr> <dbl>
1 GeneA three     8
2 GeneA three     5
3 GeneB one       8
4 GeneB one       7
user_125052510

A base R option using subset + ave

DBout <- subset(DB,!ave(ave(C,A,B),A,FUN = function(x) x != max(x)))

such that

> DBout
      A     B C
5 GeneA three 8
6 GeneA three 5
7 GeneB   one 8
8 GeneB   one 7
user_121587570

Using combination of base R and dplyr :

library(dplyr)

DB %>% group_by(A) %>% filter(B == names(which.max(tapply(C, B, mean))))

#    A     B       C
#  <chr> <chr> <dbl>
#1 GeneA three     8
#2 GeneA three     5
#3 GeneB one       8
#4 GeneB one       7

For every group in A, we filter the rows to select max value of mean for B.

user_39629140
Related