Conditional rolling counting function

user_15854334 0
Viewed 27

I would like to implement a rolling count function for the working days in a month. Weekends (Saturday and Sunday) should be assigned a NA.

A replicable example:

#Change language if your are in a non-English location like me
Sys.setlocale("LC_TIME", "C")

workdays <- c("Mon","Tue","Wed","Thu","Fri")

dataset <- data.frame(Date = seq(as.Date("2020-03-01"),as.Date("2020-04-01")-1,"days"))

dataset$Day <- format(dataset$Date,format="%d")
dataset$WeekDay <- format(dataset$Date,format="%a")
dataset$Month <- format(dataset$Date,format="%m")
dataset$Year <- format(dataset$Date,format="%y")
dataset$Workday <- dataset$WeekDay %in% workdays

I wanted to use dplry grouped by the respective month and year to sum conditionally for the working days.

dataset %>%
  group_by(Month,Year) %>%
  mutate(WorkdayNo = ???)

In my example, the first ten rows should then look like this:

[1] NA  1  2  3  4  5 NA NA  6  7 (...)
dplyr r
1 Answers

cumsum with ifelse should help -

library(dplyr)

dataset %>%
  group_by(Month,Year) %>%
  mutate(WorkdayNo = if_else(Workday, cumsum(Workday), NA_integer_)) %>%
  ungroup

#  Date       Day   WeekDay Month Year  Workday WorkdayNo
#   <date>     <chr> <chr>   <chr> <chr> <lgl>       <int>
# 1 2020-03-01 01    Sun     03    20    FALSE          NA
# 2 2020-03-02 02    Mon     03    20    TRUE            1
# 3 2020-03-03 03    Tue     03    20    TRUE            2
# 4 2020-03-04 04    Wed     03    20    TRUE            3
# 5 2020-03-05 05    Thu     03    20    TRUE            4
# 6 2020-03-06 06    Fri     03    20    TRUE            5
# 7 2020-03-07 07    Sat     03    20    FALSE          NA
# 8 2020-03-08 08    Sun     03    20    FALSE          NA
# 9 2020-03-09 09    Mon     03    20    TRUE            6
#10 2020-03-10 10    Tue     03    20    TRUE            7
# … with 21 more rows
user_39629140
Related