How would I to sum multiple lagging rows into a new row?

[rvaducks]

I have a column which has values in it. I want to create a second and third column that sums the previous 3 and 6 observations prior to that time period. Here's an example df:

x <- data.frame(Vessel = c(1, 0, 1, 0, 0, 1, 0, 0, 1, 0, 1, 0, 0, 1)

Ideal outcome would look like this:

 x <- data.frame(Vessel = c(1, 0, 1, 0, 0, 1, 0, 0, 1, 0, 1, 0, 0, 0), minus3 = c(NA, NA, NA, 2, 1, 1, 1, 1, 1, 2, 1, 1, 1, 0))

[FactOREO]

Hello,

I think what you are looking for is something like this:

x <- data.frame(Vessel = c(1, 0, 1, 0, 0, 1, 0, 0, 1, 0, 1, 0, 0, 1))

# without offset
x |>
  collapse::fmutate(
    minus3 = RcppRoll::roll_sum(Vessel, n = 3L, align = 'right', fill = NA)
  )
#>    Vessel minus3
#> 1       1     NA
#> 2       0     NA
#> 3       1      2
#> 4       0      1
#> 5       0      1
#> 6       1      1
#> 7       0      1
#> 8       0      1
#> 9       1      1
#> 10      0      1
#> 11      1      2
#> 12      0      1
#> 13      0      1
#> 14      1      1

# with offset
x |>
  collapse::fmutate(
    minus3 = RcppRoll::roll_sum(data.table::shift(Vessel,type = 'lag'), n = 3L, align = 'right', fill = NA)
    )
#>    Vessel minus3
#> 1       1     NA
#> 2       0     NA
#> 3       1     NA
#> 4       0      2
#> 5       0      1
#> 6       1      1
#> 7       0      1
#> 8       0      1
#> 9       1      1
#> 10      0      1
#> 11      1      1
#> 12      0      2
#> 13      0      1
#> 14      1      1

^{Created on 2022-10-11 with reprex v2.0.2}

RcppRoll::roll_sum() creates a rolling sum window. In this case the current row value is equal to the sum of the 2 previous and the current value. Your expected output is some what odd, since the last value in your minus3 column is 0, but in your original data.frame there were a 1 inside (also it is not present in your expected outcome).

Kind regards

Edit: Added an option with offset.

[rvaducks]

@FactOREO

RcppRoll

This is great, thanks. Is there a simple change to make this look forward (plus3 instead of minus3)?

[FactOREO]

Please see the documentation for this. The relevant argument is align to shift the window, but I think it is profitable if you understand what the function does and how you can adjust ist.

If the code above solves your issue, feel free to mark it as a solution (little checkbox).

Kind regards