Filter rows by conditions including equal and get maximum values using R

December 14, 2021

Say I have a dataframe df as follows:

df <- structure(list(date = c("2021-10-1", "2021-10-2", "2021-10-3", 
"2021-10-4", "2021-10-5", "2021-10-6", "2021-10-7", "2021-10-8", 
"2021-10-9"), value = c(190.3, 174.9, 163.2, 168.4, 168.6, 168.2, 
163.5, 161.6, 172.9), type = c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 
2L)), class = "data.frame", row.names = c(NA, -9L))

I try to filter rows where two conditions were met (or condtions, not and):

type==2
type==1 and max(date).

My trial code:

df$date <- as.Date(df$date)

Method 1:

df[type==2 | date==max(df[type==1]$date)]

Out:

Error in `[.data.frame`(df, type == 2 | date == max(df[type == 1]$date)) : 
object 'type' not found

Method 2:

df %>%
  filter(type==2|date==max(df[type==1]$date))

Out:

Error: Problem with `filter()` input `..1`.
i Input `..1` is `type == 3 | date == max(df[type == 2]$date)`.
x undefined columns selected

But it works out when I use in code geom_point(data=df[type==3 | date==max(df[type==2]$date)],size=2, aes(shape=type)) from this link.

The expected result:

I am wondering how could I filter correctly using two methods above? Thanks.

>Solution :

Please see if this generates the expected output.

library(dplyr)

df2 <- df %>%
  mutate(date = as.Date(date)) %>%
  filter(type == 2 | (type == 1 & date == max(date[type == 1])))
df2
#         date value type
# 1 2021-10-05 168.6    1
# 2 2021-10-06 168.2    2
# 3 2021-10-07 163.5    2
# 4 2021-10-08 161.6    2
# 5 2021-10-09 172.9    2