operation over data frame by column name

[john22]

Hi,

I have a numeric data frame (df) with this columns names:
1Y_a, 2Y_a,3Y_a,
1Y_b, 2Y_b,3Y_b,
1Y_c, 2Y_c,3Y_c

Is there a smart way to perform these operations based on the pattern in column names:
(df$1Y_a-df$1Y_b)/df$1Y_c
(df$2Y_a-df$2Y_b)/df$2Y_c
(df$3Y_a-df$3Y_b)/df$3Y_c

Thank you!

[FJCC]

I don't think this is a better solution than just writing out the formulas but it does use the pattern of the names.

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
library(tidyr)
DF <- data.frame(`1Y_a`=1:4,`2Y_a`=2:5,`3Y_a`=3:6,
                 `1Y_b`=11:14,`2Y_b`=12:15,`3Y_b`=13:16,
                 `1Y_c`=21:24,`2Y_c`=22:25,`3Y_c`=23:26,check.names = FALSE)
DF
#>   1Y_a 2Y_a 3Y_a 1Y_b 2Y_b 3Y_b 1Y_c 2Y_c 3Y_c
#> 1    1    2    3   11   12   13   21   22   23
#> 2    2    3    4   12   13   14   22   23   24
#> 3    3    4    5   13   14   15   23   24   25
#> 4    4    5    6   14   15   16   24   25   26
DF <- mutate(DF,Row=row_number())
DF2 <- pivot_longer(DF,cols =-Row,c("Group","Dummy","Group2"),
             names_pattern = "(.)(Y_)(.)",values_to = "Value")
DF3 <- pivot_wider(DF2,names_from = "Group2",
            values_from = "Value")
DF3 <- DF3 %>% rowwise() %>% mutate(Value=(a-b)/c)
DF3
#> # A tibble: 12 x 7
#> # Rowwise: 
#>      Row Group Dummy     a     b     c  Value
#>    <int> <chr> <chr> <int> <int> <int>  <dbl>
#>  1     1 1     Y_        1    11    21 -0.476
#>  2     1 2     Y_        2    12    22 -0.455
#>  3     1 3     Y_        3    13    23 -0.435
#>  4     2 1     Y_        2    12    22 -0.455
#>  5     2 2     Y_        3    13    23 -0.435
#>  6     2 3     Y_        4    14    24 -0.417
#>  7     3 1     Y_        3    13    23 -0.435
#>  8     3 2     Y_        4    14    24 -0.417
#>  9     3 3     Y_        5    15    25 -0.4  
#> 10     4 1     Y_        4    14    24 -0.417
#> 11     4 2     Y_        5    15    25 -0.4  
#> 12     4 3     Y_        6    16    26 -0.385

^{Created on 2020-11-28 by the reprex package (v0.3.0)}