CODEWITHSUNDEEP
r
Saleem Khan
Saleem Khan

Reputation: 749

count weekdays between sys.date and each date in column in R

I have a dataframe with a list of dates in the column "thedate".

for each value in this column, i need the difference in weekdays between that and Sys.Date(), then multiplied by 195.

I want something like:

Sys.Date() "2017-11-21"

thedate    | weekdays 
-----------+--------------------------
2017-11-21 | 0 
2017-11-22 | 195 
2017-11-23 | 390 
2017-11-24 | 585
2017-11-25 | 585 (because it's a saturday)

My code is as follows:

dates <- as.Date("2017-11-21"):as.Date("2020-11-20")
forecasting <- data.frame(thedate = as.Date(dates, origin = "1970-01-01"))
forecasting$weekdays <- 195 * sum(!weekdays(seq(Sys.Date(), forecasting$thedate, "days")) %in% c("Saturday", "Sunday"))

seq(Sys.Date(), forecasting$thedate, "days") throws an error back: "'to' must be of length 1" presumably because i'm referring to the entire column, but I don't know how else to count the weekdays for each column.

is there a way to adjust this so that for each date in the "thedate" column I get the difference in weekdays between that and the sys.date?

Upvotes: 2

Views: 118

Answers (1)

Patrik_P
Patrik_P

Reputation: 3200

You could do a function that calculates the number of business days between a certain date and Sys.Date():

nr_bdays <- function(day){
library(lubridate)
  if(day<=Sys.Date()){
    x <- seq.Date(day, Sys.Date(), by="day")
    } else {x <- seq.Date(Sys.Date(), day, by="day")}
return(length(x[grep("1|2|3|4|5", wday(x, week_start=1))])) #counts number of days Monday to Friday excluding weekends 
}

For instance

day <- ymd("2017-11-10")
nr_bdays(day)
#[1] 8

In vectorised form you can do:

dates <- seq.Date(as.Date("2017-11-21"), as.Date("2017-12-20"), by="day")
sapply(dates, nr_bdays)
#[1]  1  2  3  4  4  4  5  6  7  8  9  9  9 10 11 12 13 14 14 14 15 16 17 18 19 19 19 20 21 22

Upvotes: 1

Related Questions