2

I am trying to figure out a way to calculate rolling sum values based on a vector of data.Below is a sample dataframe and the answer I am trying to compute, but can't figure out the proper solution. Essentially, i'm trying to multiply each x column value by the y vector and sum based on the period.

period= c(1,2,3)
x=c(1,1,1)
y= c(2,3,4)

df=data.frame(period,x,y)

This is how I solved the answer.

2+0+0
3+2+0
4+3+2
0+4+3
0+0+4

answer= c(2,5,9,7,4)

I've looked at the slam package as well as the crossprod function to no avail.

Thanks in advance!

jsimpsno
  • 448
  • 4
  • 19

3 Answers3

2

We may use

c(cumsum(df$y), rev(cumsum(rev(df$y)))[-1])
# [1] 2 5 9 7 4

cumsum(df$y) gives a cumulative sum starting from one end, then rev(cumsum(rev(df$y)) gives starting from the other one, and we have [-1] as full sums coincide:

cumsum(df$y)
# [1] 2 5 9
rev(cumsum(rev(df$y)))
# [1] 9 7 4
Julius Vainora
  • 47,421
  • 9
  • 90
  • 102
2

If the aim is to calculate a rolling sum of 3 values such that there are implicitly 0s added to ensure that the output has 5 elements even though the input has 3 then try these:

1) rollapply Multiply x and y and insert 0's depending on whether right, center or left alignment is used and depending on whether partial= is used. align="center" is the default of rollapply and align = "right" is the default of rollapplyr.

library(zoo)

rollapply(c(0, x*y, 0), 3, sum, partial = TRUE)
## [1] 2 5 9 7 4

rollapplyr(c(x*y, 0, 0), 3, sum, partial = TRUE)
## [1] 2 5 9 7 4

rollapplyr(c(0, 0, x*y), 3, sum, align = "left", partial = TRUE)
## [1] 2 5 9 7 4

rollapply(c(0, 0, x*y, 0, 0), 3, sum)
## [1] 2 5 9 7 4

rollsum(c(0, 0, x*y, 0, 0), 3) #  this solution has the lowest character count
## [1] 2 5 9 7 4

2) Base R A base solution can be written using embed:

rowSums(embed(c(0, 0, x*y, 0, 0), 3))
## [1] 2 5 9 7 4

2a) or take the cumulative sum and subtract the cumulative sum 3 back:

cumsum(c(x*y,0,0)) - cumsum(c(0, 0, 0, (x*y)[-3]))
## [1] 2 5 9 7 4

2b) If the idea is that a circular calculation is to be done then:

c(filter(c(0, x*y, 0), c(1,1,1), circular = TRUE))
## [1] 2 5 9 7 4
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
0

In case anyone wants to do a similar calculation by group, the code below should work.

df_nest<-df%>% group_by(variable)%>%nest()

df_nest%>%
  mutate(NewColumn = map(data, ~rollapplyr(c(.$x*.$y, 0, 0), 3, sum, partial = TRUE)))%>%
  unnest()
jsimpsno
  • 448
  • 4
  • 19