identifying columns with similar endings to perform mathematical functions-CodePudding

I have a data.frame with multiple columns with similar names for different types of exercise, recording the weight used, number of repititions and number of sets. In my MWE it is bicep curls and lateral raises. All the different exercises have similar ending column names: Wt Sets Reps

Is there a way to calculated Wt * Reps * Sets for each exercise, save the result as a new column ending Load, and then add all columns ending Load for Total_load?

MWE:

exercise =  data.frame(BicWt= c( 3,3,4),
                       BicSets = c(2,2,2),
                       BicReps = c(10,12,12),
                       LatWt = c(4,4,4),
                       LatSets = c( 2,2,2),
                       LatReps = c(6,8,10))

 BicWt BicSets BicReps LatWt LatSets LatReps
1     3       2      10     4       2       6
2     3       2      12     4       2       8
3     4       2      12     4       2      10

Desired output is:

BicWt BicSets BicReps BicLoad LatWt LatSets LatReps LatLoad Total_Load
1     3       2      10      60     4       2       6      48        108
2     3       2      12      72     4       2       8      64        136
3     4       2      12      96     4       2      10      80        176

where BicLoad = BicWt * BicSets * BicReps etc.

EDIT

Some of the exercise columns have CAPITAL letters before Wt,Reps and Sets.


exercise =  data.frame(BicWt= c( 3,3,4),
                       BicSets = c(2,2,2),
                       BicReps = c(10,12,12),
                       LatWt = c(4,4,4),
                       LatSets = c( 2,2,2),
                       LatReps = c(6,8,10),
                       PullUpWt = c(5,5,5),
                       PullUpSets = c(2,2,2),
                       PullUpReps = c(6,8,10))

BicWt BicSets BicReps BicLoad LatWt LatSets LatReps LatLoad PullUpWt PullUpSets PullUpReps PullUpLoad Total_Load
1     3       2      10      60     4       2       6      48        5          2          6         60        168
2     3       2      12      72     4       2       8      64        5          2          8         80        216
3     4       2      12      96     4       2      10      80        5          2         10        100        276

CodePudding user response：

We could remove the suffix part of the column names and split into a list of data.frames, then do elementwise multiply with Reduce on those datasets. Create the total by doing elementwise sum across the list

lst1 <- lapply(split.default(exercise, 
     sub("[A-Z][a-z] $", "", names(exercise))), \(x) Reduce(`*`, x))
exercise[paste0(names(lst1), "Load")] <- lst1
exercise$Total_Load <- Reduce(` `, lst1)

-output

> exercise
  BicWt BicSets BicReps LatWt LatSets LatReps BicLoad LatLoad Total_Load
1     3       2      10     4       2       6      60      48        108
2     3       2      12     4       2       8      72      64        136
3     4       2      12     4       2      10      96      80        176

Or in tidyverse, create the 'BicLoad', 'LatLoad' columns individually by looping across the 'Bic', 'Lat' columns, do the elementwise multiplication with reduce and create the 'Total_load' by adding the BicLoad and LatLoad

library(dplyr)
library(purrr)
exercise %>%
   mutate(BicLoad = across(starts_with('Bic')) %>%
      reduce(`*`), .after = BicReps) %>% 
   mutate(LatLoad = across(starts_with('Lat')) %>% 
    reduce(`*`), .after = LatReps) %>%
   mutate(Total_Load = BicLoad   LatLoad)

-output

BicWt BicSets BicReps BicLoad LatWt LatSets LatReps LatLoad Total_Load
1     3       2      10      60     4       2       6      48        108
2     3       2      12      72     4       2       8      64        136
3     4       2      12      96     4       2      10      80        176