Filtering a table to find a single row in R with an if_else or case_when statement

I need to filter my data to select a single row that is the latest Date.
When there are two rows with the latest dates, I then need to filter based on the test result:

If the max(test) >= 60 I want to select the row with the min(test), else I want to select the row with max(test)

library(dplyr)
Date= c("2020/04/16", "2020-07-01", "2020-07-01")
ID= c(12,12,12)
test= rnorm(3, mean=150, sd=60)
data= data.frame(ID, Date, test)
data$Date = lubridate::ymd(data$Date)

I have attempted the following

data = data%>%
  filter(Date == max(Date))%>%
  filter(if_else(max(test) >= 60, test == min(test), test == max(test)))

and

data = data%>%
  filter(Date == max(Date))%>%
  filter(test == if_else(max(test) >= 60, test == min(test), test == max(test)))

and

data1 = data%>%
  filter(Date == max(Date))%>%
  if_else(max(test) >= 60, filter(test == min(test)), filter(test == max(test)))

None of these work, though logically I think they should. I get errors for all of them.

Created on 2020-11-18 by the reprex package (v0.3.0)

I would use row_number:

data1 <- data %>%
  arrange(desc(Date, test)) %>% 
  mutate(rn = row_number(ID)) %>% 
  filter(rn == 1)
1 Like

d1 <- data %>%
  filter(Date == max(Date))

d2 <- filter(d1,test == if (max(test >= 60)) min(test) else max(test))
1 Like

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.