CODEWITHSUNDEEP
rdplyr
Ujjawal Bhandari
Ujjawal Bhandari

Reputation: 1372

Rolling 3 Months Sum

I have 3 columns ID, Date, Flag. I want rolling 3 months sum of Flag of each ID. For example, sum of Flag of ID=1 for reporting month 2/29/2012 is 2 as it is sum of Flag =1 for ID=1 wherein Date <= 2/29/2012. In other words, it is cumulative sum of last 3 values.

dt <- read.table(header = TRUE, 
                 text = '
ID  Date    Flag
1   1/30/2012   1
1   2/29/2012   1
1   3/30/2012   1
1   4/30/2012   1
1   5/30/2012   1
1   6/30/2012   1
1   7/30/2012   0
1   8/30/2012   0
1   9/30/2012   0
1   10/30/2012  0
1   11/30/2012  0
1   12/30/2012  0
2   1/30/2014   1
2   2/28/2014   1
2   3/30/2014   1
2   4/30/2014   1
2   5/30/2014   1
2   6/30/2014   1
2   7/30/2014   0
2   8/30/2014   0
2   9/30/2014   0
2   10/30/2014  0
2   11/30/2014  0
2   12/30/2014  0
')

Upvotes: 0

Views: 251

Answers (1)

G. Grothendieck
G. Grothendieck

Reputation: 270358

Use rollapplyr with partial = TRUE to include partial sums at the beginning of each ID.

library(dplyr)
library(zoo)

dt %>% 
  group_by(ID) %>%
  mutate(roll = rollapplyr(Flag, 3, sum, partial = TRUE)) %>%
  ungroup

Upvotes: 1

Related Questions