How to create a variable with the last date available-CodePudding

Here is a representation of my dataset

ID<-1:5
Date1<-c(NA,NA,"2022-06-10",NA,NA)
Date2<-c(NA,NA,NA,NA,NA)
Date3<-c("2022-02-08",NA,NA,NA,NA)
Date4<-c(NA,NA,"2022-06-24",NA,"2022-05-13")

mydata<-data.frame(ID,Date1,Date2,Date3,Date4)

I want to create an additionnal date column in which I will have the last date available for each individual.

Like below

ID      Date1 Date2      Date3      Date4  last_date
1  1       <NA>    NA 2022-02-08       <NA> 2022-02-08
2  2       <NA>    NA       <NA>       <NA>       <NA>
3  3 2022-06-10    NA       <NA> 2022-06-24 2022-06-24
4  4       <NA>    NA       <NA>       <NA>       <NA>
5  5       <NA>    NA       <NA> 2022-05-13 2022-05-13

CodePudding user response：

We may use coalesce

library(dplyr)
 mydata %>%
    mutate(last_date = coalesce(Date4, Date3, Date2, Date1))

-output

ID      Date1 Date2      Date3      Date4  last_date
1  1       <NA>    NA 2022-02-08       <NA> 2022-02-08
2  2       <NA>    NA       <NA>       <NA>       <NA>
3  3 2022-06-10    NA       <NA> 2022-06-24 2022-06-24
4  4       <NA>    NA       <NA>       <NA>       <NA>
5  5       <NA>    NA       <NA> 2022-05-13 2022-05-13

If the values are not ordered in the 'Date1', 'Date2',..., use pmax

library(purrr)
mydata %>% 
  mutate(last_date = exec(pmax, !!! .[-1],  na.rm = TRUE))
ID      Date1 Date2      Date3      Date4  last_date
1  1       <NA>    NA 2022-02-08       <NA> 2022-02-08
2  2       <NA>    NA       <NA>       <NA>       <NA>
3  3 2022-06-10    NA       <NA> 2022-06-24 2022-06-24
4  4       <NA>    NA       <NA>       <NA>       <NA>
5  5       <NA>    NA       <NA> 2022-05-13 2022-05-13

CodePudding user response：

Using base R

mydata["last_date"] <- apply(mydata[,-1],1,max, na.rm=T)

  ID      Date1 Date2      Date3      Date4  last_date
1  1       <NA>    NA 2022-02-08       <NA> 2022-02-08
2  2       <NA>    NA       <NA>       <NA>       <NA>
3  3 2022-06-10    NA       <NA> 2022-06-24 2022-06-24
4  4       <NA>    NA       <NA>       <NA>       <NA>
5  5       <NA>    NA       <NA> 2022-05-13 2022-05-13