r - 通过运行信号值计算 `cumsum`

Question

我想计算cumsum从每次运行信号开始的某个值，其中 signal == 1 .

示例数据:

set.seed(123)
df <- data.frame(Date = seq.Date(as.Date('2016-09-01'),as.Date('2016-09-30'),by = 'days'),
                 value = sample(1:10,size=30,replace = TRUE),
                 signal = c(rep(0,3),rep(1,2),rep(0,1),rep(1,5),rep(0,6),rep(1,3),rep(0,5),rep(1,5)))

> head(df,12)
         Date value signal
1  2016-09-01    10      0
2  2016-09-02    10      0
3  2016-09-03     7      0
4  2016-09-04     8      1
5  2016-09-05     1      1
6  2016-09-06     5      0
7  2016-09-07     8      1
8  2016-09-08     3      1
9  2016-09-09     4      1
10 2016-09-10     3      1
11 2016-09-11     2      1
12 2016-09-12     5      0

到目前为止我做了什么:

我的解决方案是有效的，但我认为使用 dplyr 有一种更有效、更优雅的方法来做到这一点。或 data.table .

df$pl <- rep(0,length(df))
# calculating the indices of start/end of runs where signal == 1
runs <- rle(df$signal)
start <- cumsum(runs$lengths) +1
start <- start[seq(1, length(start), 2)]
end <- cumsum(runs$lengths)[-1]
end <- end[seq(1, length(end), 2)]
for(i in 1:length(start))
{
  df$pl[start[i]:end[i]] <- cumsum(df$value[start[i]:end[i]])
}

> head(df,12)
         Date value signal pl
1  2016-09-01    10      0  0
2  2016-09-02    10      0  0
3  2016-09-03     7      0  0
4  2016-09-04     8      1  8
5  2016-09-05     1      1  9
6  2016-09-06     5      0  0
7  2016-09-07     8      1  8
8  2016-09-08     3      1 11
9  2016-09-09     4      1 15
10 2016-09-10     3      1 18
11 2016-09-11     2      1 20
12 2016-09-12     5      0  0

Answer 1

使用 data.table ，你可以这样做

library(data.table)
set.seed(123)
seq.Date(as.Date('2016-09-01'),as.Date('2016-09-30'),by = 'days')
sample(1:10,size=30,replace = TRUE)
c(rep(0,3),rep(1,2),rep(0,1),rep(1,5),rep(0,6),rep(1,3),rep(0,5),rep(1,5))
df <- data.table(Date = seq.Date(as.Date('2016-09-01'),as.Date('2016-09-30'),by = 'days'),
                 value = sample(1:10,size=30,replace = TRUE),
                 signal = c(rep(0,3),rep(1,2),rep(0,1),rep(1,5),rep(0,6),rep(1,3),rep(0,5),rep(1,5)))

df[, pl := cumsum(value)*signal, by = .(signal, rleid(signal))]
#>           Date value signal pl
#>  1: 2016-09-01    10      0  0
#>  2: 2016-09-02    10      0  0
#>  3: 2016-09-03     7      0  0
#>  4: 2016-09-04     8      1  8
#>  5: 2016-09-05     1      1  9
#>  6: 2016-09-06     5      0  0
#>  7: 2016-09-07     8      1  8
#>  8: 2016-09-08     3      1 11
#>  9: 2016-09-09     4      1 15
#> 10: 2016-09-10     3      1 18
#> 11: 2016-09-11     2      1 20
#> 12: 2016-09-12     5      0  0
#> 13: 2016-09-13     5      0  0
#> 14: 2016-09-14     4      0  0
#> 15: 2016-09-15     2      0  0
#> 16: 2016-09-16     2      0  0
#> 17: 2016-09-17     3      0  0
#> 18: 2016-09-18     5      1  5
#> 19: 2016-09-19     3      1  8
#> 20: 2016-09-20     9      1 17
#> 21: 2016-09-21     1      0  0
#> 22: 2016-09-22     5      0  0
#> 23: 2016-09-23     8      0  0
#> 24: 2016-09-24     2      0  0
#> 25: 2016-09-25     6      0  0
#> 26: 2016-09-26     3      1  3
#> 27: 2016-09-27     2      1  5
#> 28: 2016-09-28     8      1 13
#> 29: 2016-09-29     9      1 22
#> 30: 2016-09-30     4      1 26
#>           Date value signal pl

与 dplyr ，我不知道任何等效的 data.table::rleid ，所以它使用它:

library(dplyr)

df %>% 
  group_by(id = data.table::rleidv(signal)) %>%
  mutate(pl = cumsum(value) * signal) %>%
  select(-id) %>%
  head(12)
#> Adding missing grouping variables: `id`
#> Source: local data frame [12 x 5]
#> Groups: id [5]
#> 
#>       id       Date value signal    pl
#>    <int>     <date> <int>  <dbl> <dbl>
#> 1      1 2016-09-01    10      0     0
#> 2      1 2016-09-02    10      0     0
#> 3      1 2016-09-03     7      0     0
#> 4      2 2016-09-04     8      1     8
#> 5      2 2016-09-05     1      1     9
#> 6      3 2016-09-06     5      0     0
#> 7      4 2016-09-07     8      1     8
#> 8      4 2016-09-08     3      1    11
#> 9      4 2016-09-09     4      1    15
#> 10     4 2016-09-10     3      1    18
#> 11     4 2016-09-11     2      1    20
#> 12     5 2016-09-12     5      0     0