Groupwise rollsum: How to add cumsum for partitioned data (window?)

Question

Reviewed:

R: cumulative sum over rolling date range

Consecutive/Rolling sums in a vector in R

https://cran.r-project.org/web/packages/RcppRoll/RcppRoll.pdf

There are clearly several options for using rollapply over a range of columns.

I have a dataframe with customer data grouped by month, sample:

 year_month customer_id             revenue
 2018-01-01    1821148               0.00
 2018-01-01    142163579             0.00
 2018-01-01    16295983              0.00
 2018-02-01    1821148               86.57
 2018-02-01    142163579             191.21
 2018-02-01    16295983              0.00
 2018-03-01    1821148               98.18
 2018-03-01    142163579             47.61
 2018-03-01    16295983              241.88

My question is, how can I use rollapply (e.g. RcppRoll::roll_sum() or any similar function) for each customer only? Even if I order my data by customer_id and year_month, rollapply won't know to go back e.g 3 months rolling just for a specific customer.

E.g. the 3rd last observation is for customer 1821148 in March. In this case I would like the cumsum for this specific customer in Jan:March.

Even If I order by customer id then year_month, the first observation for a customer will sum the previous 3 rows if they are for another customer.

Is there a way to rollsum for a specific grouping, in this case customer_id?

You should check out the **dplyr** or **DT** packages, both of which are specifically designed to accommodate these types of group-aware operations in a succinct way. — jdobres, Sep 28 '18 at 21:50

nghauran · Accepted Answer · 2018-09-28T22:01:03.310

2

Here is another dplyr option taking advantage of .by_group = TRUE

library(dplyr)
df %>% 
  group_by(customer_id) %>%
  arrange(year_month, .by_group = TRUE) %>%
  mutate(rolling_sum = cumsum(revenue))
# output
# A tibble: 9 x 4
# Groups:   customer_id [3]
  year_month customer_id revenue rolling_sum
       <chr>       <int>   <dbl>       <dbl>
1 2018-01-01     1821148    0.00        0.00
2 2018-02-01     1821148   86.57       86.57
3 2018-03-01     1821148   98.18      184.75
4 2018-01-01    16295983    0.00        0.00
5 2018-02-01    16295983    0.00        0.00
6 2018-03-01    16295983  241.88      241.88
7 2018-01-01   142163579    0.00        0.00
8 2018-02-01   142163579  191.21      191.21
9 2018-03-01   142163579   47.61      238.82

edited Sep 28 '18 at 22:01

answered Sep 28 '18 at 21:55

nghauran

6,648
2
20
29

1

Interesting. What is ```.by_group = T``` doing? – Doug Fir Sep 28 '18 at 21:56
1

Adding `.by_group = TRUE` to `arrange()` allows us to sort `year_month` within each group (`customer_id`) – nghauran Sep 28 '18 at 21:58

score 1 · Answer 2 · answered Sep 28 '18 at 21:51

How about either of these?

# data.table
library(data.table)
setDT(dat)
dat[, rolling_sum := cumsum(revenue), by=customer_id]

# dplyr
library(dplyr)
dat %>% group_by(customer_id) %>% mutate(rolling_sum = cumsum(revenue))

If these aren't the results you are looking for, maybe edit the question to specify what's expected.

   year_month customer_id revenue rolling_sum
1: 2018-01-01     1821148    0.00        0.00
2: 2018-01-01   142163579    0.00        0.00
3: 2018-01-01    16295983    0.00        0.00
4: 2018-02-01     1821148   86.57       86.57
5: 2018-02-01   142163579  191.21      191.21
6: 2018-02-01    16295983    0.00        0.00
7: 2018-03-01     1821148   98.18      184.75
8: 2018-03-01   142163579   47.61      238.82
9: 2018-03-01    16295983  241.88      241.88

(Here's how i'm reading in the data)

dat <- 
  read.table(header = T, sep=',', text=
"year_month,customer_id,revenue
2018-01-01,1821148,0.00
2018-01-01,142163579,0.00
2018-01-01,16295983,0.00
2018-02-01,1821148,86.57
2018-02-01,142163579,191.21
2018-02-01,16295983,0.00
2018-03-01,1821148,98.18
2018-03-01,142163579,47.61
2018-03-01,16295983,241.88")

Groupwise rollsum: How to add cumsum for partitioned data (window?)

2 Answers2