replace all dates that occur after a specified end date with an *NA* in a rowwise manner-CodePudding

I want to replace all dates that occur after a specified end date stored in the column "date_end" with an NA in a rowwise manner.

Original data frame:

date_end <-as.Date(c("2019-07-31", "2019-07-17", "2019-12-18")) 
date1 <-as.Date(c("2019-10-31", "2019-05-01", "2019-07-27")) 
date2 <-as.Date(c("2019-01-30", "2019-07-15", "2019-09-09"))
date3 <-as.Date(c("2019-03-19", "2020-01-15", "2019-12-08"))
date4 <-as.Date(c("2019-07-31", "2020-08-05", "2020-07-01"))

df <- data.frame(date_end, date1, date2, date3, date4)

Want data frame like this:

date_end <-as.Date(c("2019-07-31", "2019-07-17", "2019-12-18")) 
date1 <-as.Date(c("2019-10-31", "2019-05-01", "2019-07-27")) 
date2 <-as.Date(c("2019-01-30", "2019-07-15", "2019-09-09"))
date3 <-as.Date(c("2019-03-19", NA, "2019-12-08"))
date4 <-as.Date(c("2019-07-31", NA, NA))

df_new <- data.frame(date_end, date1, date2, date3, date4)

CodePudding user response：

This should also work - as long as date1 of the first row should be NA because it is after the corresponding end date:

date_end <-as.Date(c("2019-07-31", "2019-07-17", "2019-12-18")) 
date1 <-as.Date(c("2019-10-31", "2019-05-01", "2019-07-27")) 
date2 <-as.Date(c("2019-01-30", "2019-07-15", "2019-09-09"))
date3 <-as.Date(c("2019-03-19", "2020-01-15", "2019-12-08"))
date4 <-as.Date(c("2019-07-31", "2020-08-05", "2020-07-01"))

df <- data.frame(date_end, date1, date2, date3, date4)

# create duplicate dataframe
df_new <- df 

# use a loop to add NA to cells where the date is after the corresponding date_end
for (i in 1:nrow(df_new)) { # fo each row
  for (j in 2:length(df_new)) { # for each column after date_end
    if (df_new[i,1] < df_new[i,j]) { # if date in cell [i,j] is after end date of row i
      df_new[i,j] <- NA # replace with NA 
    }
  }
}

df_new

    date_end      date1      date2      date3      date4
1 2019-07-31       <NA> 2019-01-30 2019-03-19 2019-07-31
2 2019-07-17 2019-05-01 2019-07-15       <NA>       <NA>
3 2019-12-18 2019-07-27 2019-09-09 2019-12-08       <NA>

CodePudding user response：

This should work:

library(dplyr)
library(lubridate)

date_end <-as.Date(c("2019-07-31", "2019-07-17", "2019-12-18")) 
date1 <-as.Date(c("2019-10-31", "2019-05-01", "2019-07-27")) 
date2 <-as.Date(c("2019-01-30", "2019-07-15", "2019-09-09"))
date3 <-as.Date(c("2019-03-19", "2020-01-15", "2019-12-08"))
date4 <-as.Date(c("2019-07-31", "2020-08-05", "2020-07-01"))

df <- data.frame(date_end, date1, date2, date3, date4)


df %>% 
  rowwise() %>% 
  mutate(across(date1:date4, ~case_when(.x <= date_end ~ .x, 
                                       TRUE ~ NA_Date_)))
#> # A tibble: 3 × 5
#> # Rowwise: 
#>   date_end   date1      date2      date3      date4     
#>   <date>     <date>     <date>     <date>     <date>    
#> 1 2019-07-31 NA         2019-01-30 2019-03-19 2019-07-31
#> 2 2019-07-17 2019-05-01 2019-07-15 NA         NA        
#> 3 2019-12-18 2019-07-27 2019-09-09 2019-12-08 NA

^{Created on 2022-05-10 by the reprex package (v2.0.1)}

CodePudding user response：

Here is a base R solution using sapply:

df[,-1][sapply(df[,-1], function(x) as.Date(x) > as.Date(df$date_end))] <- NA

Output

    date_end      date1      date2      date3      date4
1 2019-07-31       <NA> 2019-01-30 2019-03-19 2019-07-31
2 2019-07-17 2019-05-01 2019-07-15       <NA>       <NA>
3 2019-12-18 2019-07-27 2019-09-09 2019-12-08       <NA>