0

Looking to calculate a rolling sum of counts in R. I reviewed this SO thread :

R dplyr rolling sum

and others.

library(tidyverse)
library(RcppRoll)

client <- c('a','a','b','b','c','c')
count <- c(1,2,3,5,6,4)
date <- c('2018-01-31','2018-02-28','2018-01-31','2018-02-28','2018-01- 
31','2018-02-28')

df <- data.frame(client, count, date)

rolling<- df %>%
arrange(client, date) %>%
group_by(client, date ) %>%
mutate(roll_sum = rollapplyr(count, 12, sum, partial=T))

Can someone point out what I am doing wrong so I can correct this? The roll_sum in this example is only equal to the original count. I would like to create a rolling sum of the groups.

Updating to show sample with more than 12 months of data:

library(tidyverse)
library(RcppRoll)

client <- c('a')
count <- c(1,2,3,5,6,4,4,8,6,9,10,12,13)
date <- c('2018-01-31','2018-02-28','2018-03-31','2018-04-30','2018-05- 
31','2018-06-30', '2018-07-31','2018-08-31','2018-09-30','2018-10-31','2018- 
11-30','2018-12-31', '2019-01-31')

df <- data.frame(client, count, date)

rolling<- df %>%
arrange(client, date) %>%
group_by(client) %>%
mutate(roll_sum = rollapplyr(count, 12, sum, partial=T))

Updated to show desired output:

Client     Period         Count    12 Month Rolling Sum
a          2018-01-31        1     1
a          2018-02-28        2     3
a          2018-03-31        3     6
a          2018-04-30        4     10
a          2018-05-31        5     15
a          2018-06-30        6     21 
a          2018-07-31        7     28
a          2018-08-31        8     36
a          2018-09-30        9     45
a          2018-10-31        10    55
a          2018-11-30        11    66
a          2018-12-31        12    78
a          2019-01-31         5    82

Note the row for 2019-01-31 starts a new 12 month period. Each month after should also Thanks in advance

cowboy
  • 613
  • 5
  • 20
  • If you're trying to get rolling sums for clients across dates, then I think you want to drop `date` from your call to `group_by()`, so just `group_by(client)`. – ulfelder Nov 05 '19 at 17:28
  • That does fix the problem. In my larger data set though, I have more than 12 months. I didn't think about that when creating my sample. I want to see the rolling 12 month summary at each month. My current solution continually sums. See updated question – cowboy Nov 05 '19 at 18:21
  • *I cant get it to work* ... this is not helpful for us. Please describe errors and/or undesired results. Even better show us desired result with data. – Parfait Nov 05 '19 at 18:58
  • Thanks, I have updated the question with a desired output – cowboy Nov 05 '19 at 20:12

1 Answers1

1

For the rollapplyr function in the last line, can you try zoo::rollapplyr:

df %>%
  arrange(client, date) %>%
  group_by(client) %>%
  mutate(roll_sum = zoo::rollapplyr(count, 12, sum, partial=TRUE))
Tianyu Z
  • 121
  • 1
  • 4