CODEWITHSUNDEEP
rfunctionloopsapply
Tracy
Tracy

Reputation: 709

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

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!!

Upvotes: 0

Views: 820

Answers (3)

A. Webb
A. Webb

Reputation: 26446

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

Upvotes: 1

Chris Glosser
Chris Glosser

Reputation: 21

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)

Upvotes: 1

Simon Jackson
Simon Jackson

Reputation: 3174

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

Upvotes: 1

Related Questions