R - exclude weekends from time interval calculations with lubridate-CodePudding

I wish to calculate the intervals between dates. The differences in days should take weekends in account. I have over 200 dates stamps.

For example, the currently displayed time difference between 5th (Tuesday) and 11th (Monday) January are 5 days. I would like to obtain 3 days.

I could manage to get to a solution without excluding Saturday and Sunday with the following code and the packages lubridate and dplyr.

Could you please guide me how to exclude the weekends for calculation? Thank you.

library(lubridate)
library(dplyr)


dates <- c("2021-01-01", "2021-01-04", "2021-01-05", "2021-01-06", "2021-01-11", "2021-01-13", "2021-01-14", "2021-01-18", "2021-01-25", "2021-01-29")

d <- do.call(rbind, lapply(dates, as.data.frame))

dateoverview <- rename(d, Dates = 1)

dateoverview$Dates <- lubridate::ymd(dateoverview$Dates)

datecalculation <- dateoverview %>%
                   mutate(Days = Dates - lag(Dates)) %>% 
                   mutate(Weekday = wday(Dates, label = FALSE))

datecalculation


##         Dates    Days Weekday
## 1  2021-01-01 NA days       6
## 2  2021-01-04  3 days       2
## 3  2021-01-05  1 days       3
## 4  2021-01-06  1 days       4
## 5  2021-01-11  5 days       2
## 6  2021-01-13  2 days       4
## 7  2021-01-14  1 days       5
## 8  2021-01-18  4 days       2
## 9  2021-01-25  7 days       2
## 10 2021-01-29  4 days       6

CodePudding user response：

Probably, there is a function somewhere already doing this but here is a custom one which can help you calculate date difference excluding weekends.

library(dplyr)
library(purrr)

date_diff_excluding_wekeends <- function(x, y) {
  if(is.na(x) || is.na(y)) return(NA)
  sum(!format(seq(x, y - 1, by = '1 day'), '%u') %in% 6:7)
}

datecalculation %>%
  mutate(Days = map2_dbl(lag(Dates), Dates, date_diff_excluding_wekeends))

#        Dates Days Weekday
#1  2021-01-01   NA       6
#2  2021-01-04    1       2
#3  2021-01-05    1       3
#4  2021-01-06    1       4
#5  2021-01-11    3       2
#6  2021-01-13    2       4
#7  2021-01-14    1       5
#8  2021-01-18    2       2
#9  2021-01-25    5       2
#10 2021-01-29    4       6

seq(x, y - 1, by = '1 day') creates a sequence of dates between previous date and current date - 1.
format(..., "%u") returns day of the week. 1 is for Monday, 7 for Sunday.
Using sum(!format(...) %in% 6:7) we count number of days that are present on weekdays.

CodePudding user response：

Another possible solution:

library(lubridate)

# sample data
df = data.frame(Dates = seq(ymd('2021-01-01'),ymd('2021-12-31'),by='days'))
df_weekdays = df %>% filter(!(weekdays(as.Date(df$Dates)) %in% c('Saturday','Sunday')))

#Application to your data 
datecalculation =   datecalculation %>% 
    filter(!(weekdays(as.Date(datecalculation$Dates)) %in% c('Saturday','Sunday')))