How to use rowwise-wise arithmetic operations with names of columns stored in variables in R?

Question

I have a data frame like this. The name of the second column is stored in a variable (supposed to be dynamic, fed into a function); the names of the first and the last ones are constant.

sel_col<-"w" #fed into a function previously

df <- tibble(id = 1:6, w = 10:15, x = 20:25)

df
#> # A tibble: 6 × 5
#>      id     w     x   
#>   <int> <int> <int> 
#> 1     1    10    20    
#> 2     2    11    21    
#> 3     3    12    22    
#> 4     4    13    23    
#> # ℹ 2 more rows

I also have variables with the sum of the values in columns.

sel_col_tot = sum(df[[sel_col]])
x_tot = sum(df$x)

Now, I need to add columns with the difference between the total value and the column value to yield this:

#>      id     w     x  sel_out   x_out 
#>   <int> <int> <int>  <int>   <int>
#> 1     1    10    20    65     115
#> 2     2    11    21    64     114
#> 3     3    12    22    63     115
#> 4     4    13    23    62     116

And I need to do this using the column name coded in the variable. I tried to use mutate and {{}} or .[[]] syntaxis for it to use the var-coded column name. Like this:

... %>%
mutate(sel_out = sel_col_tot-.[[sel_tot]],
           x_out = x_tot-x)

However, every time, it calculates the list of differences in all the rows and returns this error:

Error in `mutate()`:
ℹ In argument: `sel_out = sel_col_tot-.[[sel_tot]]`.
ℹ In row 1.
Caused by error:
! `sel_out` must be size 1, not 6.

How could I fix this and get the difference by row?

Thank you in advance for your help!

score 1 · Accepted Answer · answered Aug 23 '23 at 00:02

One option to access columns provided as a character string would be to use the .data pronoun:

library(dplyr, warn = FALSE)

sel_col <- "w"

df <- tibble(id = 1:6, w = 10:15, x = 20:25)

df |>
  mutate(
    sel_out = sum(.data[[sel_col]]) - .data[[sel_col]],
    x_out = sum(x) - x
  )
#> # A tibble: 6 × 5
#>      id     w     x sel_out x_out
#>   <int> <int> <int>   <int> <int>
#> 1     1    10    20      65   115
#> 2     2    11    21      64   114
#> 3     3    12    22      63   113
#> 4     4    13    23      62   112
#> 5     5    14    24      61   111
#> 6     6    15    25      60   110

andrew_reece · Answer 2 · 2023-08-23T00:13:53.323

You can use {{}} notation if you wrap the pipe in a function:

library(dplyr)

f <- function(col) df |> 
  mutate(sel_out = sum({{col}}) - {{col}},
         x_out = sum(x) - x)

f(w)
# A tibble: 6 × 5
     id     w     x sel_out x_out
  <int> <int> <int>   <int> <int>
1     1    10    20      65   115
2     2    11    21      64   114
3     3    12    22      63   113
4     4    13    23      62   112
5     5    14    24      61   111
6     6    15    25      60   110

Alternately, you can just use across and leave out id:

df |> 
  mutate(across(-id, \(col) sum(col) - col, .names = "{.col}_out"))

How to use rowwise-wise arithmetic operations with names of columns stored in variables in R?

2 Answers2