Filtering by conditional values in R

user_6312511 0
Viewed 131

So, my data take the general shape of:

library(tidyverse)

id <- c(1, 1, 2, 2, 3, 3)
group <- c("A", "B", "A", "A", "B", "B")
value <- c(34, 12, 56, 78, 90, 91)

df <- tibble(id, group, value)
df

     id group value
  <dbl> <chr> <dbl>
1     1 A        34
2     1 B        12
3     2 A        56
4     2 A        78
5     3 B        90
6     3 B        91

What I want to do can be described as "for each id, take the maximum value of group A. But, if A is not there, take the maximum value of group B." So my desired output would look something like:

     id group value
  <dbl> <chr> <dbl>
1     1 A        34
4     2 A        78
6     3 B        91

I tried to do this using the code...

desired <- df %>%
  group_by(id) %>%
  filter(if (exists(group == "A")) max(value) else if (exists(group == "B")) (max(value)))

...but I received an error. Help?

dplyr r
3 Answers

One option could be:

df %>%
 group_by(id) %>%
 arrange(group, desc(value), .by_group = TRUE) %>%
 slice(which.max(group == "A"))

     id group value
  <dbl> <chr> <dbl>
1     1 A        34
2     2 A        78
3     3 B        91
user_59645570

Here is a base R option

subset(
  df[order(id, group, -value), ],
  ave(rep(TRUE, nrow(df)), id, FUN = function(x) seq_along(x) == 1)
)

which gives

     id group value
  <dbl> <chr> <dbl>
1     1 A        34
2     2 A        78
3     3 B        91

The basic idea is:

  • We reorder the rows of df via df[order(id, group, -value), ]
  • Then we take the first value in the reordered df by id
user_121587570

Using data.table

library(data.table)
setDT(df)[order(id, group, -value), .SD[1], id]
#    id group value
#1:  1     A    34
#2:  2     A    78
#3:  3     B    91
user_37322710
Related