0

I was just wondering how I can go about calculating the cumulative sum with conditions on a matrix. Here's what I mean: Let's say we have a matrix with a column called ID and a column called Value as follows:

   ID       |      VALUE
------------------------------
   2        |       50
   7        |       19
   5        |       32
   2        |       21
   8        |       56
   7        |       5
   7        |       12
   2        |       16
   5        |       42

I wish to compute the cumulative sum on this matrix based on the ID column. This means the cumulative sum column (or vector) would look like:

   ID       |      CUMULATIVE SUM
----------------------------------
   2        |       50
   7        |       19
   5        |       32
   2        |       71
   8        |       56
   7        |       24
   7        |       36
   2        |       87
   5        |       74

Is there a way to do this? A search for this hasn't turned up much at all (I've found stuff relevant for data frames/data tables, but I haven't found anything at all when it comes to 'conditions' with matrices), so any help would be appreciated.

ThePlowKing
  • 341
  • 1
  • 4
  • 15

1 Answers1

1

There are a number of ways to do this, here I use data.table. I edited your data slightly to just use a , as a separator and got rid of the header row:

R> suppressMessages(library(data.table))
R> dat <- fread("   ID       ,      VALUE
   2        ,       50
   7        ,       19
   5        ,       32
   2        ,       21
   8        ,       56
   7        ,       5
   7        ,       12
   2        ,       16
   5        ,       42")
R> dat[, cumsum(VALUE), by=ID]
   ID V1
1:  2 50
2:  2 71
3:  2 87
4:  7 19
5:  7 24
6:  7 36
7:  5 32
8:  5 74
9:  8 56
R>

After that, it is a standard grouping by (which you can do in many different ways) and a cumulative sum in each group.

The reordering here is automatic because of the grouping. If you must keep your order you can.

Dirk Eddelbuettel
  • 360,940
  • 56
  • 644
  • 725
  • Thanks! I've got a data.table method which I've implemented previously but since I'm working with matrices now I wasn't too sure how to implement it – ThePlowKing Jul 09 '17 at 22:41
  • Sure. The `ave()` is your best bet, as in the older question you would have found by searching. But you don't "have a matrix" -- methinks you have data in some other format or backend and choose to store it in a matrix. Anyway... – Dirk Eddelbuettel Jul 09 '17 at 22:43
  • Yeah you are correct in guessing that... it's actually an `xts` object which is where a lot of my confusion is stemming from :/ – ThePlowKing Jul 09 '17 at 23:17