Filter on groups where where at the max value of one variable, another variable equals a particular value

user_10006534 0
Viewed 81

I want to filter on groups where at the max value of one variable, another variable equals a particular value.

I have data like so:

library(tidyverse)
df1 <- data.frame(grp = rep(letters[1:2],each=5),
                      day = 1:5,
                     value = c(0,5,7,1,1,5,8,5,3,0)) %>% 
group_by(grp)

   grp day value
1    a   1     0
2    a   2     5
3    a   3     7
4    a   4     1
5    a   5     1
6    b   1     5
7    b   2     8
8    b   3     5
9    b   4     3
10   b   5     0

And I want to filter on groups where at the max(day), value equals 1.

So the output would look like this:

  grp     day value
1 a         1     0
2 a         2     5
3 a         3     7
4 a         4     1
5 a         5     1

Data.table or dplyr solutions are welcome. Thanks!

data.table dplyr grouping filter r
1 Answers

As it is already grouped, simply apply filter by checking whether 'value' that corresponds to max value of day (which.max(day)) is 1

library(dplyr)
df1 %>%
    filter(value[which.max(day)] ==1)
# A tibble: 5 x 3
# Groups:   grp [1]
#  grp     day value
#  <fct> <int> <dbl>
#1 a         1     0
#2 a         2     5
#3 a         3     7
#4 a         4     1
#5 a         5     1

Or have two conditions and wrap with any

df1 %>% 
      filter(any(value ==1 & day == max(day)))
user_37322710
Related