Replace future dates with NA using dplyr

Question

I have a tibble with several columns containing dates, some of which are in the future (e.g., 2019-08-26). I need to replace the dates that are in the future with NA, and want to do so using dplyr.

So far I have the following, which does not work:

library(lubridate)
library(dplyr)
library(tibble)
d <- tibble("ID" = 1:9,
            "Date1" = as_date(c("2019-08-05", "2019-08-26", "2019-08-26",
                                "2019-08-26", "2019-07-29", "2019-08-12", 
                                "2019-08-19", "2019-08-26", "2019-08-26")),
            "Date2" = as_date(c("2019-08-12", "2019-09-02", "2019-09-02",
                                 "2019-09-02", "2019-08-05", "2019-08-19",
                                 "2019-08-26", "2019-09-02", "2019-09-02")))

d %>%
  select("Date1") %>%
  replace(. > today(), NA) #This line doesn't work

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

From help(replace)

Arguments

x: vector

So use pull instead

library(dplyr)
d %>%
  pull("Date1") %>%
  replace(. > today(), NA)
#[1] "2019-08-05" NA           NA           NA           "2019-07-29"
#[6] "2019-08-12" "2019-08-19" NA           NA

If you need a tibble and try to replace those values, use mutate

d %>%
 mutate(Date2 = replace(Date1, Date1 > Sys.Date(), NA))
# A tibble: 9 x 3
#     ID Date1      Date2     
#  <int> <date>     <date>    
#1     1 2019-08-05 2019-08-05
#2     2 2019-08-26 NA        
#3     3 2019-08-26 NA        
#4     4 2019-08-26 NA        
#5     5 2019-07-29 2019-07-29
#6     6 2019-08-12 2019-08-12
#7     7 2019-08-19 2019-08-19
#8     8 2019-08-26 NA        
#9     9 2019-08-26 NA

As pointed out by @d.b. in the comments and also in How to prevent ifelse() from turning Date objects into numeric objects if_else and ifelse, and dates don't go along together very well.

# don't run
# d %>% mutate(Date2 = ifelse(Date1 < today(), Date1, NA))

update

If you have multiple columns an option is mutate_if

d %>%
  mutate_if(., is.Date, .funs = ~replace(., . > today(), NA))

Making `d %>% mutate(Date2 = ifelse(Date1 < today(), Date1, NA))` work is quite a hassle — d.b, Aug 19 '19 at 19:42
This works great! It is possible to do this to multiple columns at once using something like `mutate_at`? — Brigadeiro, Aug 19 '19 at 19:51
markus and @d.b. thank you both. Very new to dplyr so still learning how it works. Appreciate it! — Brigadeiro, Aug 19 '19 at 19:59

Replace future dates with NA using dplyr

1 Answers1