Using dplyr to compute calculated fields depending on multiple columns without explicitly writing column names

user_3625022 0
Viewed 33

Consider the following code.

set.seed(56)
library(dplyr)

df <- data.frame(
  NUM_1 = sample.int(500, replace = TRUE),
  DENOM_1 = sample.int(500, replace = TRUE),
  NUM_2 = sample.int(500, replace = TRUE),
  DENOM_2 = sample.int(500, replace = TRUE)
)

head(df)

  NUM_1 DENOM_1 NUM_2 DENOM_2
1   417     379   154     173
2   160     437   239     154
3   243     315   106     361
4   291     169   393     340
5   170     450   429     421
6   422     131    75      64

Without having to manually specify each of the column names (the actual problem has about 40 of these I need to create), I would like to create columns FRAC_1 and FRAC_2 for which FRAC_X = NUM_X/DENOM_X.

So, this would be what I'm looking for with regard to output, but since I'm dealing with about 40 of these, I don't want to have to manually type out each column:

df_frac <- df %>%
  mutate(FRAC_1 = NUM_1 / DENOM_1,
         FRAC_2 = NUM_2 / DENOM_2)

head(df_frac)

  NUM_1 DENOM_1 NUM_2 DENOM_2    FRAC_1    FRAC_2
1   417     379   154     173 1.1002639 0.8901734
2   160     437   239     154 0.3661327 1.5519481
3   243     315   106     361 0.7714286 0.2936288
4   291     169   393     340 1.7218935 1.1558824
5   170     450   429     421 0.3777778 1.0190024
6   422     131    75      64 3.2213740 1.1718750

I would strongly prefer a dplyr solution to this. I thought maybe I could use mutate() with across(), but it isn't clear to me how to tell across() to pair the NUM_x with the corresponding DENOM_x columns.

dplyr r
1 Answers

Here is one in tidyverse

  1. Loop across the columns with names starts_with 'NUM'
  2. Extract the column name cur_column(), replace the substring from 'NUM' to 'DENOM' in str_replace
  3. get the column value, divide by the NUM column, and change the column name in .names to create the 'FRAC' columns
library(dplyr)
library(stringr)
df <- df %>% 
     mutate(across(starts_with("NUM"), ~ 
        ./get(str_replace(cur_column(), 'NUM', 'DENOM')), 
       .names = "{str_replace(.col, 'NUM', 'FRAC')}"))

-output

head(df)
NUM_1 DENOM_1 NUM_2 DENOM_2    FRAC_1    FRAC_2
1   417     379   154     173 1.1002639 0.8901734
2   160     437   239     154 0.3661327 1.5519481
3   243     315   106     361 0.7714286 0.2936288
4   291     169   393     340 1.7218935 1.1558824
5   170     450   429     421 0.3777778 1.0190024
6   422     131    75      64 3.2213740 1.1718750
user_37322710
Related