How can i iterate a multiplication between two columns to create a new column?

Question

I have 6 ratings that i have to multiply by their respective weights (it's a nasa tlx) to obtain a weighted rating for each domain, like this:

6 ratings r_mental, r_physical, r_temporal, r_effort, r_performance, r_frustration

6 weights: w_mental, w_physical, w_temporal, w_effort, w_performance, w_frustration

and i have to multiply each r_xxx with each w_xxx to obtain a "weighted_xxx" column.

now, to make the new column i just do: mutate(nasa, weighted_mental = r_mental*w_mental)

and i would probably be already done manually writing this line six times to get weighted_physical, weighted_temporal, etc. , but i'm sure there's a smarter way to do it.

how do i iterate the operation 6 times for consecutive columns? how do i make r get the name of the weighted_xxx column correctly?

Answer 1

The problem with what you're trying to do is that your data isn't actually tidy. You should read that, but the tl;dr version is that you shouldn't encode important information in column names. Break that data out into it's own column. Once that is done, the operation becomes incredibly easy:

# load libraries and setting the random seed
library(tidyverse)
set.seed(0)

# creating the sample dataset
nms <- c("r_mental", "r_physical", "r_temporal", "r_effort", "r_performance", "r_frustration",
         "w_mental", "w_physical", "w_temporal", "w_effort", "w_performance", "w_frustration")

df <- data.frame(matrix(sample(1:100, 12*10, replace=T), ncol=12, dimnames=list(NULL, nms)))


df |>
  # tidy the data
  pivot_longer(cols = everything(), names_to = c(".value", "type"), names_pattern = "(\\w)_(\\w+)") |>
  # add the weighted column
  mutate(weighted = r * w)

Output:

# A tibble: 60 × 4
   type            r     w weighted
   <chr>       <int> <int>    <int>
 1 mental         14    75     1050
 2 physical       51    29     1479
 3 temporal       37    17      629
 4 effort         70    51     3570
 5 performance    40    29     1160
 6 frustration    14    40      560
 7 mental         68    81     5508
 8 physical       97    13     1261
 9 temporal       89    73     6497
10 effort         74    93     6882
# ℹ 50 more rows