How to use cumsum-Lapply when i+1 column is needed?

Question

I am currently working on a pretty big file containing stops/go of several machinas (about 60) + their production over a long period (more than 60 000 rows). Stops are indexed by "-1" and go by "1" :

**Date                     n1_prod   n1_stops   n2_prod   n2_stops  n3_prod     

1  2011-12-13 00:00:00          2        1         0        -1        14                    
2  2011-12-13 01:00:00         10        1       -10        -1        24                   
3  2011-12-13 02:00:00         24        1        -5        -1        23                       
4  2011-12-13 03:00:00         25        1         0        -1        22                      
5  2011-12-13 04:00:00         23        1        12         1        13                      
6  2011-12-13 05:00:00          0       -1        11         1        17                      
7  2011-12-13 06:00:00         -2       -1        21         1        18  

My purpose is to get for each device a new column cumulative production per stop/go (possibly on a new df). For device n°1 for instance it would be:

**Date                     n1_prod   n1_stops   n1_agprod   
1  2011-12-13 00:00:00          2        1         2                          
2  2011-12-13 01:00:00         10        1        12                          
3  2011-12-13 02:00:00         24        1        36                             
4  2011-12-13 03:00:00         25        1        61                             
5  2011-12-13 04:00:00         23        1        84                            
6  2011-12-13 05:00:00          0       -1         0                               
7  2011-12-13 06:00:00         -2       -1        -2         

For one column, I can get the desire result using :

df<-as_tibble(df)%>%
 group_by(n1_stops) %>% 
 dplyr::mutate(n1_agprod= cumsum(n1_prod))

But I don't know how to generalize it, since I need a different column each time for groups and I am currently not able to replace the name of the column by the column index...

Do you know how I can manage that ?

Answer 1

You can split based on the prefix of every column name and apply the cumsum there, i.e.

sapply(split.default(df[-1], sub('_.*','',names(df[-1]))), 
                                          function(i) ave(i[[1]], i[[2]], FUN = cumsum))
#     n1  n2
#[1,]  2   0
#[2,] 12 -10
#[3,] 36 -15
#[4,] 61 -15
#[5,] 84  12
#[6,]  0  23
#[7,] -2  44

Answer 2

We can first separate columns which end with "prod" and "stop", then use mapply and ave to cumsum for each group and create new columns.

prod_cols <- grep("prod$", names(df))
stop_cols <- grep("stops$", names(df))

df[paste0("agprod", 1:length(prod_cols))] <- 
    mapply(ave, df[prod_cols], df[stop_cols], MoreArgs = list(FUN = cumsum))


df
#                Date n1_prod n1_stops n2_prod n2_stops agprod1 agprod2
#1 2011-12-1300:00:00       2        1       0       -1       2       0
#2 2011-12-1301:00:00      10        1     -10       -1      12     -10
#3 2011-12-1302:00:00      24        1      -5       -1      36     -15
#4 2011-12-1303:00:00      25        1       0       -1      61     -15
#5 2011-12-1304:00:00      23        1      12        1      84      12
#6 2011-12-1305:00:00       0       -1      11        1       0      23
#7 2011-12-1306:00:00      -2       -1      21        1      -2      44