某些条件下的累积和

Question

为了得到名为cumulative_sum的变量，我想对变量"nbre_lignes“进行累加求和，我设法编辑了它，但它不是自动的。有人能帮我把它自动化吗？

library(FSA)
library(dplyr)

months.numeric <- lubridate:::months.numeric

strwr <- function(str) gsub(" ", "\n", str)

waterfall <- data.frame(table= strwr(c("Concaténation", "Doublons & NPI","DGC & CAR-REU", "BDD", paste("Répondants",format(as.yearqtr(Sys.Date()-base::months(12)),"T%q"), "&",  format(as.yearqtr(Sys.Date()-months(9)),"T%q-%y")), paste("Sollicités au ", format(as.yearqtr(Sys.Date()-months(3)),"T%q-%y")), "Exclusions ", "QD", "Cible Finale")),
                        nbre_lignes=c(638334, -362769, -17674,41927,-1540, -20149, -300, -10,  19928))
#                         

waterfall$time <- 1:nrow(waterfall)
waterfall$flow <- factor(sign(waterfall$nbre_lignes))
waterfall$table <- factor(waterfall$table, levels = waterfall[["table"]])

 b <- pcumsum(waterfall$nbre_lignes[1:3])

 l <- pcumsum(waterfall$nbre_lignes[4:8])

 cumulative_sum <- c(b,l, 0)

waterfall <-  waterfall %>% cbind(cumulative_sum)

                   table nbre_lignes time flow cumulative_sum
1            Concaténation      638334    1    1              0
2         Doublons\n&\nNPI     -362769    2   -1         638334
3          DGC\n&\nCAR-REU      -17674    3   -1         275565
4                      BDD       41927    4    1              0
5 Répondants\nT3\n&\nT4-18       -1540    5   -1          41927
6  Sollicités\nau\n\nT2-19      -20149    6   -1          40387
7             Exclusions\n        -300    7   -1          20238
8                       QD         -10    8   -1          19938
9            Cible\nFinale       19928    9    1              0

Answer 1

我们可以使用cumsum(flow == 1)形成一个分组变量，如下所示：

waterfall %>%
  group_by(grp = cumsum(flow == 1)) %>%
  mutate(cumsum = lag(cumsum(nbre_lignes), default = 0 )) %>%
  ungroup %>%
  select(- grp)

给予：

# A tibble: 9 x 5
  table                      nbre_lignes  time flow  cumsum
  <fct>                            <dbl> <int> <fct>  <dbl>
1 Concaténation                   638334     1 1          0
2 "Doublons\n&\nNPI"             -362769     2 -1    638334
3 "DGC\n&\nCAR-REU"               -17674     3 -1    275565
4 BDD                              41927     4 1          0
5 "Répondants\nT3\n&\nT4-18"       -1540     5 -1     41927
6 "Sollicités\nau\n\nT2-19"       -20149     6 -1     40387
7 "Exclusions\n"                    -300     7 -1     20238
8 QD                                 -10     8 -1     19938
9 "Cible\nFinale"                  19928     9 1          0