In R is there a way to extract the row with the earliest date per ID if it meets a condition (is equal to 1) and the latest date if it does not?

user_14535220 0
Viewed 439

Sorry if this has been asked before but I couldn’t find a solution on here. In R, I would like to filter my dataset per ID on the earliest datapoint given a condition and the latest datapoint if not.

So given this dataset looking at whether individuals have responded:

    ID   response   follow_up_date
    P1  0   2001-01-01
    P1  0   2002-01-01
    P1  0   2003-01-01
    P2  0   2003-01-01
    P2  1   2004-01-01
    P3  1   2001-01-01
    P3  1   2003-01-01
    P3  1   2004-01-01

I would like to extract the row per ID with the earliest date someone responded (response=1) and, if they haven’t yet responded (response = 0), I would like to extract the row with the last follow-up date. So the output should be:

     ID   response  follow_up_date
     P1 0   2003-01-01
     P2 1   2004-01-01 
     P3 1   2001-01-01

Does anyone know how I would achieve this? I assume there's a way using dplyr or data.table but I haven't quite managed to figure it out.

The code to create the data frame is here:

ID<-c("P1","P1","P1","P2","P2","P3","P3","P3")
response<-c(0,0,0,0,1,1,1,1)
follow_up_date<-as.Date(c("2001-01-01","2002-01-01","2003-01-01","2003-01-01","2004-01-01","2001-01-01","2003-01-01","2004-01-01"))
df<-data.frame(ID,response,follow_up_date)
data.table dplyr subset grouping r
2 Answers
df %>% 
  group_by(ID) %>% # group by ID
  mutate(index = case_when(response == 1 ~ which.min(follow_up_date),  # get earliest date if response == 0
                           response == 0 ~ which.max(follow_up_date))) %>% # get latest date if reponse == 1
  slice(first(index)) %>% # get first occurance of index
  select(-index)

or with data.table

library(data.table)
setDT(df)
df[df[,.I[ifelse(response[1] == 0,which.max(follow_up_date),which.min(follow_up_date))],by = ID]$V1]
user_94240880

You have to use the %>% operator and summarise(). I make my own function to return 0 if sum is 0 or 1 in other cases. If you know better way to convert numbers in a binary you are welcome. Here is an example:

binary_sum <- function (data) {
    s <- sum(data)
    if (s == 0) {
       return(0)
    } else {
       return(1)
    }
}

df %>% group_by(ID) %>% summarise(response = binary_sum(response), follow_up_date = max(follow_up_date))
user_131984820
Related