 Elena - 2 years ago 92
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? akrun

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))
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download