Cumulative total by group

Question

For the following dataset:

d = data.frame(date = as.Date(as.Date('2015-01-01'):as.Date('2015-04-10'), origin = "1970-01-01"),
               group = rep(c('A','B','C','D'), 25), value = sample(1:100))
head(d)
         date group value
1: 2015-01-01     A     4
2: 2015-01-02     B    32
3: 2015-01-03     C    46
4: 2015-01-04     D    40
5: 2015-01-05     A    93
6: 2015-01-06     B    10

.. can anyone advise a more elegant way to calculate a cumulative total of values by group than this data.table) method?

library(data.table)
setDT(d)
d.cast = dcast.data.table(d, group ~ date, value.var = 'value', fun.aggregate = sum)
c.sum = d.cast[, as.list(cumsum(unlist(.SD))), by = group]

.. which is pretty clunky and yields a flat matrix that needs dplyr::gather or reshape2::melt to reformat.

Surely R can do better than this??

I'm confused. What you describe in words is `setDT(d)[,cumsum(value),by=group]` — Frank, May 22 '15 at 15:04
You should really use `set.seed()` to make the example [reproducible](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) and you should also include the desired output for your sample input. — MrFlick, May 22 '15 at 15:04

score 10 · Accepted Answer · answered May 22 '15 at 15:04

10

If you just want cumulative sums per group, then you can do

transform(d, new=ave(value,group,FUN=cumsum))

with base R.

answered May 22 '15 at 15:04

MrFlick

195,160
17
277
295

Tick for a hole in one, and preserving original data order :) – geotheory May 22 '15 at 15:19

score 8 · Answer 2 · answered May 22 '15 at 15:07

8

This should work

library(dplyr)
d %>% 
  group_by(group) %>% 
  arrange(date) %>% 
  mutate(Total = cumsum(value))

answered May 22 '15 at 15:07

Akhil Nair

3,144
1
17
32

This is probably the direction of best practice though – geotheory May 22 '15 at 15:19
Fortran is better. Binary is best. – geotheory Jul 05 '17 at 22:38

David Arenburg · Answer 3 · 2015-05-22T16:03:22.003

4

As this question was tagged with data.table, you are probably looking for (a modification of @Franks comment).

setDT(d)[order(date), new := cumsum(value), by = group]

This will simultaneously rearrange the data by date (not sure if needed, if not, you can get rid of order(date)) and update your data set in place utilizing the := operator

edited May 22 '15 at 16:03

answered May 22 '15 at 15:23

David Arenburg

91,361
17
137
196

@akrun I'm not as popular as Flick probably ;) – David Arenburg May 22 '15 at 16:02
Actually I didn't add that tag I think @Frank did. Spologies I should've rolled back sooner – geotheory May 22 '15 at 16:10
I don't think you need `order(date)` (as you mention). – Arun May 22 '15 at 16:14
didn't know you can use `order` as an `i` argument. +1! – MichaelChirico May 22 '15 at 18:52

score 0 · Answer 4 · answered Jul 05 '17 at 19:47

0

Is this it?

sp <- split(d, d$group)
res <- lapply(seq_along(sp), function(i) cumsum(sp[[i]]$value))
res <- lapply(seq_along(res), function(i){
        sp[[i]]$c.sum <- res[[i]]
        sp[[i]]
    }) 
res <- do.call(rbind, res)
res

answered Jul 05 '17 at 19:47

Rui Barradas

70,273
8
34
66

Cumulative total by group

4 Answers4

Linked

Related