R how to avoid a loop. Counting weekends between two dates in a row for each row in a dataframe

Question

I have two columns of dates. Two example dates are:

Date1= "2015-07-17"
Date2="2015-07-25"

I am trying to count the number of Saturdays and Sundays between the two dates each of which are in their own column (5 & 7 in this example code). I need to repeat this process for each row of my dataframe. The end results will be one column that represents the number of Saturdays and Sundays within the date range defined by two date columns.

I can get the code to work for one row:

sum(weekdays(seq(Date1[1,5],Date2[1,7],"days")) %in% c("Saturday",'Sunday')*1))

The answer to this will be 3. But, if I take out the "1" in the row position of date1 and date2 I get this error:

Error in seq.Date(Date1[, 5], Date2[, 7], "days") : 
  'from' must be of length 1

How do I go line by line and have one vector that lists the number of Saturdays and Sundays between the two dates in column 5 and 7 without using a loop? Another issue is that I have 2 million rows and am looking for something with a little more speed than a loop.

Thank you!!

Still a loop, but `sapply(seq_len(nrow(Date1)), function(i) sum(weekdays(seq(Date1[i,5],Date2[i,7],"days")) %in% c("Saturday",'Sunday')*1)))` should work if Date1 and Date2 are data.frames. — lmo, Jan 30 '17 at 20:30
This is helpful. I should have added that I have 2 million rows and was wondering if there is any way to speed it up! But, your code worked! — Tracy, Jan 30 '17 at 20:38
Please provide a [reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) — Heroka, Jan 30 '17 at 20:48

score 1 · Answer 1 · edited May 23 '17 at 11:45

map2* functions from the purrr package will be a good way to go. They take two vector inputs (eg two date columns) and apply a function in parallel. They're pretty fast too (eg previous post)!

Here's an example. Note that the _int requests an integer vector back.

library(purrr)

# Example data
d <- data.frame(
  Date1 = as.Date(c("2015-07-17", "2015-07-28", "2015-08-15")),
  Date2 = as.Date(c("2015-07-25", "2015-08-14", "2015-08-20"))
)

# Wrapper function to compute number of weekend days between dates
n_weekend_days <- function(date_1, date_2) {
  sum(weekdays(seq(date_1, date_2, "days")) %in% c("Saturday",'Sunday'))
}

# Iterate row wise
map2_int(d$Date1, d$Date2, n_weekend_days)
#> [1] 3 4 2

If you want to add the results back to your original data frame, mutate() from the dplyr package can help:

library(dplyr)
d <- mutate(d, end_days = map2_int(Date1, Date2, n_weekend_days))
d
#>        Date1      Date2 end_days
#> 1 2015-07-17 2015-07-25        3
#> 2 2015-07-28 2015-08-14        4
#> 3 2015-08-15 2015-08-20        2

Great! Happy to help :) – Simon Jackson Jan 30 '17 at 21:30 — Simon Jackson, Jan 30 '17 at 21:30

score 1 · Answer 2 · answered Jan 30 '17 at 21:15

Here is a solution that uses dplyr to clean things up. It's not too difficult to use with to assign the columns in the dataframe directly.

Essentially, use a reference date, calculate the number of full weeks (by floor or ceiling). Then take the difference between the two. The code does not include cases in which the start date or end data fall on Saturday or Sunday.

# weekdays(as.Date(0,"1970-01-01"))  -> "Friday"
require(dplyr)

startDate = as.Date(0,"1970-01-01")  # this is a friday
df <- data.frame(start = "2015-07-17", end = "2015-07-25")
df$start <- as.Date(df$start,"", format = "%Y-%m-%d", origin="1970-01-01")
df$end <- as.Date(df$end, format = "%Y-%m-%d","1970-01-01")

# you can use with to define the columns directly instead of %>% 

df <- df %>% 
  mutate(originDate = startDate) %>%
  mutate(startDayDiff = as.numeric(start-originDate), endDayDiff = as.numeric(end-originDate)) %>%
  mutate(startWeekDiff = floor(startDayDiff/7),endWeekDiff = floor(endDayDiff/7)) %>%
  mutate(NumSatsStart =  startWeekDiff + ifelse(startDayDiff %% 7>=1,1,0),   
         NumSunsStart =  startWeekDiff + ifelse(startDayDiff %% 7>=2,1,0),
         NumSatsEnd =  endWeekDiff + ifelse(endDayDiff %% 7 >= 1,1,0),
         NumSunsEnd =  endWeekDiff + ifelse(endDayDiff %% 7 >= 2,1,0)
         ) %>%
  mutate(NumSats = NumSatsEnd - NumSatsStart, NumSuns = NumSunsEnd - NumSunsStart)

Wow @Chris! This took no time at all. Literally! Thank you so much. — Tracy, Jan 31 '17 at 13:54

score 1 · Answer 3 · answered Jan 30 '17 at 21:35

Dates are number of days since 1970-01-01, a Thursday.

So the following is the number of Saturdays or Sundays since that date

f <- function(d) {d <- as.numeric(d); r <- d %% 7; 2*(d %/% 7) + (r>=2) + (r>=3)}

For the number of Saturdays or Sundays between two dates, just subtract, after decrementing the start date to have an inclusive count.

g <- function(d1, d2) f(d2) - f(d1-1)

These are all vectorized functions so you can just call directly on the columns.

# Example data, as in Simon Jackson's answer
d <- data.frame(
  Date1 = as.Date(c("2015-07-17", "2015-07-28", "2015-08-15")),
  Date2 = as.Date(c("2015-07-25", "2015-08-14", "2015-08-20"))
)

As follows

within(d, end_days<-g(Date1,Date2))
#        Date1      Date2 end_days
# 1 2015-07-17 2015-07-25        3
# 2 2015-07-28 2015-08-14        4
# 3 2015-08-15 2015-08-20        2

R how to avoid a loop. Counting weekends between two dates in a row for each row in a dataframe

3 Answers3