0

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)
kmkemp
  • 29
  • 5
  • What's the cutoff date? Do you want everything after 2020-01-01 to be `NA`? When you say row-wise, do you intend it to be a `dplyr` `rowwise()` operation? – DaveArmstrong May 10 '22 at 18:40
  • I realize my original post was unclear. Everything after the value in the date_end column in a rowwise manner. I'll try to edit my original question. – kmkemp May 10 '22 at 19:03
  • If the end date needs to be the date in date_end, shouldn't date1 of the first row be NA as 2019-10-31 is after the date_end of 2019-07-31? – AWestell May 10 '22 at 19:04

3 Answers3

1

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)

DaveArmstrong
  • 18,377
  • 2
  • 13
  • 25
1

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>
AWestell
  • 206
  • 1
  • 4
0

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>
AndrewGB
  • 16,126
  • 5
  • 18
  • 49