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

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))
``````

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.

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

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

This topic was automatically closed 42 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.