Adding date column to dataframe in reference to another date column

user_10952140 0
Viewed 37

I have a machine, which is serviced sometimes

service_dates <- c(as.Date("2019/09/04"), as.Date("2019/10/06"), as.Date("2019/11/15"))

The machine is also run sometimes (not necessarily weekly as below, but I have a list of dates)

run_dates <- seq(as.Date("2019/09/03"), by = "week", length.out = 15)

I'd like to determine the last time the machine was serviced for each element of run_dates. I can do it manually like so

df <- data.frame(run_dates)

df_target <- df %>% 
  mutate(last_service = case_when(run_dates >= service_dates[3] ~ service_dates[3],
                                  between(run_dates, service_dates[2], service_dates[3]) ~ service_dates[2],
                                  between(run_dates, service_dates[1], service_dates[2]) ~ service_dates[1],
                                  TRUE ~ as.Date("2018/12/31"))) # arbitrary "pre-service" date
    run_dates last_service
1  2019-09-03   2018-12-31
2  2019-09-10   2019-09-04
3  2019-09-17   2019-09-04
4  2019-09-24   2019-09-04
5  2019-10-01   2019-09-04
6  2019-10-08   2019-10-06
7  2019-10-15   2019-10-06
8  2019-10-22   2019-10-06
9  2019-10-29   2019-10-06
10 2019-11-05   2019-10-06
11 2019-11-12   2019-10-06
12 2019-11-19   2019-11-15
13 2019-11-26   2019-11-15
14 2019-12-03   2019-11-15
15 2019-12-10   2019-11-15

How can I get last_service without manually calling each date range (between(Date, service_date[1], service_date[2]) etc?

dplyr r
1 Answers

1. findInterval

Here are two solutions with findInterval to do all the work.

The first one is a base R solution.

i <- findInterval(run_dates, service_dates)  
df$last_service <- service_dates[i]

The other one is a dplyr pipe.

library(dplyr)

df_target <- df %>% 
  mutate(i = findInterval(run_dates, service_dates),
         last_service = service_dates[i]) %>%
  select(-i)

df_target
#    run_dates last_service
#1  2019-09-04   2019-09-04
#2  2019-09-11   2019-09-04
#3  2019-09-18   2019-09-04
#4  2019-09-25   2019-09-04
#5  2019-10-02   2019-09-04
#6  2019-10-09   2019-10-06
#7  2019-10-16   2019-10-06
#8  2019-10-23   2019-10-06
#9  2019-10-30   2019-10-06
#10 2019-11-06   2019-10-06
#11 2019-11-13   2019-10-06
#12 2019-11-20   2019-11-15
#13 2019-11-27   2019-11-15
#14 2019-12-04   2019-11-15
#15 2019-12-11   2019-11-15

2. cut.Date

Two other solutions, this time with the cut method for objects of class "Date". 

df$last_service <- as.Date(cut(run_dates, c(service_dates, Inf)))

And the dplyr equivalent.

df_target <- df %>% 
  mutate(last_service = as.Date(cut(run_dates, c(service_dates, Inf))))
user_82454060
Related