CODEWITHSUNDEEP
rmatrixxtscumulative-sum
ThePlowKing
ThePlowKing

Reputation: 341

Cumulative Sum of Matrix/xts object on Two Columns in R

I posted a question regarding cumulative sums yesterday, but since this one is a fair bit different I thought I should create a new topic (I also made sure to properly search the forum in case something similar could be used).

I would like to compute the cumulative sum of the inventory of particular IDs (ID 100 in the example below) based on information in two columns of a matrix/xts object. For ease of use, I have added an example code below:

a <- c(20,50,30,50,10, 40)
b <- c("100", "200", "300", "100", "100", "100")
c <- c("200", "100", "100", "200", "200", "100")
matrix1 <- cbind(a,b,c)
dim(matrix1) <- c(6,3)
colnames(matrix1) <- c("inventory", "buyer", "seller")

When you put this code into R, a matrix given in the following format should show up:

inventory | buyer   | seller
20        |  100    | 200
50        |  200    | 100
30        |  300    | 100
50        |  100    | 200
10        |  100    | 200
40        |  100    | 100

EDIT: I have changed the letters in the Buyer and Seller columns to numbers so that it works with matrices properly now, sorry! (Note that since this is an xts object there should be a timestamp to the left of the inventory column but I left it out since it isn't relevant).

Now, there are three IDs: 100, 200, and 300, and they each buy and sell from each other, where what happens is that the buyer gains inventory at each row and the seller loses inventory. In addition, if the same ID is in both the buyer and seller column, then the inventory remains unchanged. For my particular problem, I only care about the cumulative inventory of ID 100 and I don't care about the cumulative inventory of 200 and 300.

For the example above, this means that in the first row 100 has a cumulative inventory of 20, and in the second row 100 now has a cumulative inventory of -30. Similarly, for the third row, 100 has a cumulative inventory of -60, in the fourth row it has a cumulative inventory of -10, and in the fifth row 100 has a cumulative inventory of 0. For the sixth row, since 100 is in both the buyer and seller column, the inventory is the same as the fifth column so that it is again 0.

Therefore I would like to obtain a cumulative inventory vector given as c(20, -30, -60, -10, 0, 0)

Also, since this is an xts object, I'm unsure if this changes anything (although I highly doubt it does change anything, the only thing I have omitted is the fact that there should actually be a timestamp column to the left of the matrix, but I excluded it since it isn't relevant to the question).

Is there a good way of computing the cumulative sum based on two columns and based on individual IDs?

Upvotes: 0

Views: 653

Answers (1)

Evan Friedland
Evan Friedland

Reputation: 3194

I have edited it to work on either a data.frame or a matrix. Let me know if this works for you. 

cum.inv <- function(ID, data){
  stopifnot(colnames(data) %in% c("inventory", "buyer", "seller"))
  cumsum(ifelse(data[,"buyer"] == data[,"seller"], 0, 
                ifelse(data[,"buyer"] == ID, as.numeric(matrix1[,"inventory"]), 
                       ifelse(data[,"seller"] == ID, -as.numeric(matrix1[,"inventory"]), 0))))
}
cum.inv(100, matrix1)
#[1]  20 -30 -60 -10   0   0

cum.inv(200, matrix1)
#[1] -20  30  30 -20 -30 -30

cum.inv(300, matrix1)
#[1]  0  0 30 30 30 30

Upvotes: 1

Related Questions