计算的节假日到节假日前的最后一天的轮班金额

Question

假设我有一个这样的data.frame：

df <- data.frame(Day = c("2020-21-12", "2020-22-12", "2020-23-12", "2020-24-12","2020-25-12"), Amount = c(1000, 1000, 1000, 1000, 1000), Holiday=(0,0,0,1,1))

现在，我希望将每个假日的列金额(Holiday=1)中的值移动到非假日的最后一天，并将假日的金额设置为零。

输出应如下所示：

df_output <- data.frame(Day = c("2020-21-12", "2020-22-12", "2020-23-12", "2020-24-12","2020-25-12"), Amount = c(1000, 1000, 3000, 0, 0), Holiday=(0,0,0,1,1))

我尝试使用循环，但这既不高效也不优雅，因为可以有连续3天的假期。任何帮助都是非常感谢的。

Answer 1

工作的代码，我会试着改进它一点。我给出一个稍微长一点的例子来正确地验证它。

df <- data.frame( Day = c("2020-21-12", "2020-22-12", "2020-23-12", "2020-24-12","2020-25-12","2020-26-12","2020-27-12","2020-28-12","2020-29-12"), 
                 Amount = c(1000, 1000, 1000, 1000, 1000, 1000, 1000,1000,1000),
                 Holiday=c(0,0,0,1,1,0,1,0,1))

df$nH <- !as.logical(df$Holiday)

df$nH_c <- cumsum(df$nH)

df$Amount_c <- df$Amount

which_h <- which(!as.logical(df$Holiday))

for (i in seq_len(nrow(df))) {
  df$Amount_c[which_h[df$nH_c[i]]] <-  df$Amount_c[which_h[df$nH_c[i]]] + df$Amount[i]
  df$Amount_c[i] <-  df$Amount_c[i] -  df$Amount[i]
}

> df
         Day Amount Holiday    nH nH_c Amount_c
1 2020-21-12   1000       0  TRUE    1     1000
2 2020-22-12   1000       0  TRUE    2     1000
3 2020-23-12   1000       0  TRUE    3     3000
4 2020-24-12   1000       1 FALSE    3        0
5 2020-25-12   1000       1 FALSE    3        0
6 2020-26-12   1000       0  TRUE    4     2000
7 2020-27-12   1000       1 FALSE    4        0
8 2020-28-12   1000       0  TRUE    5     2000
9 2020-29-12   1000       1 FALSE    5        0

Answer 2

以下是一种dplyr方式：

library(tidyverse)

df <- data.frame( Day = c("2020-21-12", "2020-22-12", "2020-23-12", "2020-24-12","2020-25-12","2020-26-12","2020-27-12","2020-28-12","2020-29-12"), 
                  Amount = c(1000, 1000, 1000, 1000, 1000, 1000, 1000,1000,1000),
                  Holiday=c(0,0,0,1,1,0,1,0,1))


 df %>% 
   arrange(desc(Day)) %>%
   mutate(lagsum = ifelse(lag(Holiday, default = 0) == 1, 
                   Amount + lag(Amount, default = 0), 
                   Amount)) %>%
  mutate(Amount2 = ifelse(Holiday == 0 & lag(Holiday) == 1, 
                   Amount + dplyr::lag(lagsum, default = 0),
                   Amount),
        Amount2 = ifelse(Holiday == 1, 0, Amount2)) %>%
   arrange(Day) %>%
   select(Day, Amount = Amount2, Holiday)
  

        Day Amount Holiday
1 2020-21-12   1000       0
2 2020-22-12   1000       0
3 2020-23-12   3000       0
4 2020-24-12      0       1
5 2020-25-12      0       1
6 2020-26-12   2000       0
7 2020-27-12      0       1
8 2020-28-12   2000       0
9 2020-29-12      0       1