How can I conditionally add a value to rows that match a certain condition in R?-CodePudding

I run some code for the next four weeks to determine capacity hours for our professional services team. I need a way to account for the reduction in available hours due to company holidays. I have a table below with some hypothetical holidays. The table contains the actual date of the holiday and the Monday of the week it begins on.

Each week, I run a model to project PTO and capacity for the next four weeks. I also want to check my list of holidays to see if one occurs. If it does, I want a new column Holiday Hours to reflect the amount of holiday hours for that week, which is Billable Capacity Hours / 5 for a single holiday in a 5-day work week.

library(tidyverse)
library(lubridate)

Four_week_capacity_PTO <- read_csv("Week,Billable PTO Hours,Billable Capacity Hours\n
         2022-07-11,10.00000,691\n
         2022-07-18,80.03333,691\n
         2022-07-25,19.00000,691\n 
         2022-08-01,33.0000,691\n
         2022-08-08,30.6000,691\n")

holidays <- ymd(c("2022-08-02","2022-08-04","2022-11-24","2022-12-26"))

holiday_df <- tibble(
  holidays,
  Week = holidays - wday(holidays, week_start = 2), #This finds the Monday of the week in which the holiday occurs
  
)

For some reason, when I try to create the Holiday Hours variable using the below code, the code is executing with no errors or warnings but no variable is being created.

Four_week_capacity_PTO_holiday <- Four_week_capacity_PTO %>%
      rowwise() %>% 
      mutate(
        for (i in length(Week)){
          `Holiday Hours` = if_else(
            holiday_df$Week[i] %in% Week, `Billable PTO Hours`/5, 0)
        }
      )

The other issue I'm having is that if a week has two holidays, such as Christmas Eve and Christmas, I need to reduce the weekly Billable Capacity Hours by two days of work -- i.e., (Billable Capacity Hours / 5) * 2...

I have tested the individual conditionals in my for loop and they are correctly evaluating to TRUE and FALSE as it iterates, so I don't understand why no variable is being created.

CodePudding user response：

How about making your holiday_df include a count of holidays for each week

  holidays,
  Week = holidays - wday(holidays, week_start = 2), #This finds the Monday of the week in which the holiday occurs
  
) %>% group_by(Week) %>% summarise(H_Count=n())

Then join that to your original data set and use the H_Count for your logic and calculations

Four_week_capacity_PTO_holiday  <- Four_week_capacity_PTO %>% 
  left_join(holiday_df) %>% 
  mutate(`Holiday Hours` =if_else(H_Count>0,`Billable PTO Hours`/5, 0 ), 
         NEW_BCH=if_else(is.na(H_Count), `Billable Capacity Hours`, `Billable Capacity Hours`/5*H_Count))

Or if you didn't want to joinn and keep the backbone of your previous work flow you don't really need the for loop but need to swith the order of the "in" argument. It is not useful for the Billable Capactiy hours though without the count.

Four_week_capacity_PTO_holiday <- Four_week_capacity_PTO %>%
  mutate(
      `Holiday Hours` = if_else(
       Week %in% holiday_df$Week, `Billable PTO Hours`/5, 0)
    
  )