Row-wise cumulative sum over some columns, followed by row-wise division

Question

I have the following data.frame:

New_Sign_ups   Spend   2021-05  2021-06      2021-07  MRR_sum  time
100             100       0       10         100       110     1.5
50              200       10      10          40        60     1.8

First, I need to do a row-wise cumulative sum for columns 3 ~ 5:

New_Sign_ups   Spend   2021-05  2021-06      2021-07  MRR_sum  time
100             100       0       10         110       110     1.5
50              200       10      20          60        60     1.8

Then I need to divide the cumulative sum by the Spend value of that row:

New_Sign_ups   Spend   2021-05  2021-06      2021-07  MRR_sum  time
100             100       0       0.1         1.1       110     1.5
50              200       0.05    0.1         0.3        60     1.8

How can I do this?

Answer 1

dat <- structure(list(New_Sign_ups = c(100L, 50L), Spend = c(100L, 200L
), `2021-05` = c(0L, 10L), `2021-06` = c(10L, 10L), `2021-07` = c(100L, 
40L), MRR_sum = c(110L, 60L), time = c(1.5, 1.8)), class = "data.frame", 
row.names = c(NA, -2L))

Without using any packages

dat[3:5] <- do.call("cbind", Reduce(`+`, dat[3:5], accumulate = TRUE)) / dat$Spend
#  New_Sign_ups Spend 2021-05 2021-06 2021-07 MRR_sum time
#1          100   100    0.00     0.1     1.1     110  1.5
#2           50   200    0.05     0.1     0.3      60  1.8

Using package matrixStats

library(matrixStats)
dat[3:5] <- rowCumsums(as.matrix(dat[3:5])) / dat$Spend
#  New_Sign_ups Spend 2021-05 2021-06 2021-07 MRR_sum time
#1          100   100    0.00     0.1     1.1     110  1.5
#2           50   200    0.05     0.1     0.3      60  1.8