Elena Elena - 2 months ago 22
R Question

Cumulative sums of matrix columns with calculation

I have a matrix that looks like this:

date 1 2 3 4
201601 2 4 6 1
201602 3 7 7 4
201603 4 8 9 6
201604 6 4 5 7


Rows indicates prod_Date and column header (1 to 4) the age. The numbers indicates the sales of the product.

I need to generate a dataframe that has PER YEAR, the comulative sum of sales, and also, I want to multiply this by a "maturity" factor which is given by Age/max(Age) - in this case for example 1/4 or 2/4. please note age could vary.
The final output should look like this:

age cum.sales sales*maturity
1 15 3.75
2 38 19.00
3 65 48.75
4 83 83.00


Any reccomandation on how to do it fast?
Thanks in advance

Answer

Assuming that the initial dataset is a data.frame (as matrix cannot hold mixed class or else the 'date' would be 'numeric' class. If it is a numeric class, the below solution still works). Create a data.frame with 'age' as column from the column names of the matrix/data.frame, mutate to create the 'cum.sales' (from getting the cumulative sum of column sums of the dataset without the 'date' column) and 'salesmaturity' by multiplying the 'cum.sales' with the fraction of 'age' with max(age).

library(dplyr)
d1 <- data.frame(age = as.numeric(colnames(df1)[-1])) 
d1 %>% 
    mutate(cum.sales = cumsum(colSums(df1[-1])), 
            salesmaturity = cum.sales*age/max(age))
#   age cum.sales salesmaturity
#1   1        15          3.75
#2   2        38         19.00
#3   3        65         48.75
#4   4        83         83.00

data

df1 <- structure(list(date = 201601:201604, `1` = c(2L, 3L, 4L, 6L), 
`2` = c(4L, 7L, 8L, 4L), `3` = c(6L, 7L, 9L, 5L), `4` = c(1L, 
4L, 6L, 7L)), .Names = c("date", "1", "2", "3", "4"),
class = "data.frame", row.names = c(NA, -4L))