use a wildcard in mutate() to modify several variables with corresponding names

Question

I have a database with multiple columns of numerical data which i need to subtract from each other.

they are named t1 and c1 correspondingly, up to t7 and c7. Is there a succinct way using the tidy verse that I can create a new variable (delta) for each of these?

e.g.

df <- df %>% 
   mutate(d* = t* - c*)

rather than having to write

df <- df %>% 
mutate(d1 = t1 - c1) %>%
mutate(d2 = t2 - c2) %>%

etc.

Answer 1

Suppose your data frame looks like this

set.seed(1)

df <- as.data.frame(matrix(rnorm(70), ncol = 14))
df <- setNames(df, c(paste0("c", 1:7), paste0("t", 1:7)))

df
#>           c1         c2         c3          c4          c5          c6
#> 1 -0.6264538 -0.8204684  1.5117812 -0.04493361  0.91897737 -0.05612874
#> 2  0.1836433  0.4874291  0.3898432 -0.01619026  0.78213630 -0.15579551
#> 3 -0.8356286  0.7383247 -0.6212406  0.94383621  0.07456498 -1.47075238
#> 4  1.5952808  0.5757814 -2.2146999  0.82122120 -1.98935170 -0.47815006
#> 5  0.3295078 -0.3053884  1.1249309  0.59390132  0.61982575  0.41794156
#>            c7         t1         t2         t3         t4         t5
#> 1  1.35867955 -0.4149946 -0.1645236 -0.7074952  0.3981059  1.9803999
#> 2 -0.10278773 -0.3942900 -0.2533617  0.3645820 -0.6120264 -0.3672215
#> 3  0.38767161 -0.0593134  0.6969634  0.7685329  0.3411197 -1.0441346
#> 4 -0.05380504  1.1000254  0.5566632 -0.1123462 -1.1293631  0.5697196
#> 5 -1.37705956  0.7631757 -0.6887557  0.8811077  1.4330237 -0.1350546
#>            t6         t7
#> 1  2.40161776  0.1887923
#> 2 -0.03924000 -1.8049586
#> 3  0.68973936  1.4655549
#> 4  0.02800216  0.1532533
#> 5 -0.74327321  2.1726117

Then you can do this in base R with the following one-liner:

setNames(df[paste0("c", 1:7)] - df[paste0("t", 1:7)], paste0("delta", 1:7))
#>       delta1      delta2      delta3     delta4     delta5     delta6
#> 1 -0.2114592 -0.65594479  2.21927633 -0.4430395 -1.0614225 -2.4577465
#> 2  0.5779333  0.74079073  0.02526127  0.5958361  1.1493578 -0.1165555
#> 3 -0.7763152  0.04136133 -1.38977351  0.6027165  1.1186996 -2.1604917
#> 4  0.4952554  0.01911815 -2.10235368  1.9505843 -2.5590713 -0.5061522
#> 5 -0.4336680  0.38336731  0.24382319 -0.8391224  0.7548804  1.1612148
#>       delta7
#> 1  1.1698873
#> 2  1.7021709
#> 3 -1.0778833
#> 4 -0.2070584
#> 5 -3.5496712

Or, if you have to do it in the tidyverse, the equivalent might be something like:

df %>% mutate(delta = across(starts_with("c")) - across(starts_with("t")))
#>           c1         c2         c3          c4          c5          c6
#> 1 -0.6264538 -0.8204684  1.5117812 -0.04493361  0.91897737 -0.05612874
#> 2  0.1836433  0.4874291  0.3898432 -0.01619026  0.78213630 -0.15579551
#> 3 -0.8356286  0.7383247 -0.6212406  0.94383621  0.07456498 -1.47075238
#> 4  1.5952808  0.5757814 -2.2146999  0.82122120 -1.98935170 -0.47815006
#> 5  0.3295078 -0.3053884  1.1249309  0.59390132  0.61982575  0.41794156
#>            c7         t1         t2         t3         t4         t5
#> 1  1.35867955 -0.4149946 -0.1645236 -0.7074952  0.3981059  1.9803999
#> 2 -0.10278773 -0.3942900 -0.2533617  0.3645820 -0.6120264 -0.3672215
#> 3  0.38767161 -0.0593134  0.6969634  0.7685329  0.3411197 -1.0441346
#> 4 -0.05380504  1.1000254  0.5566632 -0.1123462 -1.1293631  0.5697196
#> 5 -1.37705956  0.7631757 -0.6887557  0.8811077  1.4330237 -0.1350546
#>            t6         t7   delta.c1    delta.c2    delta.c3   delta.c4
#> 1  2.40161776  0.1887923 -0.2114592 -0.65594479  2.21927633 -0.4430395
#> 2 -0.03924000 -1.8049586  0.5779333  0.74079073  0.02526127  0.5958361
#> 3  0.68973936  1.4655549 -0.7763152  0.04136133 -1.38977351  0.6027165
#> 4  0.02800216  0.1532533  0.4952554  0.01911815 -2.10235368  1.9505843
#> 5 -0.74327321  2.1726117 -0.4336680  0.38336731  0.24382319 -0.8391224
#>     delta.c5   delta.c6   delta.c7
#> 1 -1.0614225 -2.4577465  1.1698873
#> 2  1.1493578 -0.1165555  1.7021709
#> 3  1.1186996 -2.1604917 -1.0778833
#> 4 -2.5590713 -0.5061522 -0.2070584
#> 5  0.7548804  1.1612148 -3.5496712

^{Created on 2022-01-28 by the reprex package (v2.0.1)}

Answer 2

Here's an alternative way:

library(tidyverse)
set.seed(111)
# some data
df <-
  map_dfc(c("t", "c"), ~ {
    nms <- str_c(.x, 1:3)
    map_dfc(nms, ~ tibble("{.x}" := rnorm(10)))
  })

d_df <- 
as.character(1:3) %>% 
    map_dfc(~ select(df, ends_with(.x)) %>% 
            transmute('d{.x}' := reduce(., `-`)))

bind_cols(df, d_df)
#> # A tibble: 10 × 9
#>        t1      t2     t3     c1      c2     c3     d1      d2      d3
#>     <dbl>   <dbl>  <dbl>  <dbl>   <dbl>  <dbl>  <dbl>   <dbl>   <dbl>
#>  1  0.235 -0.174   0.362 -3.11  -1.18    0.192  3.35   1.00    0.170 
#>  2 -0.331 -0.407   0.347 -0.941 -1.12    1.55   0.611  0.715  -1.21  
#>  3 -0.312  1.85    0.190  1.40  -1.36    0.914 -1.71   3.21   -0.725 
#>  4 -2.30   0.394  -0.160 -1.62   0.481   0.359 -0.682 -0.0871 -0.518 
#>  5 -0.171  0.798   0.327 -2.27   0.742   0.175  2.10   0.0556  0.151 
#>  6  0.140 -1.57    0.598  1.16   0.0278 -0.847 -1.02  -1.59    1.45  
#>  7 -1.50  -0.0859 -1.84  -0.116  0.331   0.978 -1.38  -0.417  -2.82  
#>  8 -1.01  -0.359   2.72   0.334  0.644   1.81  -1.34  -1.00    0.912 
#>  9 -0.948 -1.19    0.191 -0.621  2.49    0.123 -0.328 -3.68    0.0683
#> 10 -0.494  0.364  -1.30  -1.31   1.96   -0.130  0.816 -1.60   -1.17

^{Created on 2022-01-28 by the reprex package (v2.0.1)}