Dividing a set of columns by another set of columns based on their column names in R-CodePudding

I'd like to divide a set of columns by another set of columns based on their common suffices in column names. To be more specific, in the following dataframe I would like to divide each column with prefix1 with the corresponding columns with prefix2 (i.e. "prefix1 column1" with "prefix2 column1", "prefix1 column2" with "prefix2 column2" etc.).

dt <- data.frame(replicate(6,sample(1:15,10,rep=TRUE)))
colnames(dt) <- c("prefix1 column1","prefix1 column2","prefix1 column3","prefix2 column1","prefix2 column2","prefix2 column3")
View(dt)

The desirable output would be a dataframe with additional 3 columns having the results of the divisions. My head has badly stuck with this task - I would appreciate any suggestions.

CodePudding user response：

We can loop across the 'prefix1' columns, replace the substring 'prefix1' in column name (cur_column()) with 'prefix2', get the value and divide, create new columns by updating the .names

library(dplyr)
library(stringr)
dt <- dt %>% 
   mutate(across(starts_with('prefix1'), ~ ./get(str_replace(cur_column(), 
      'prefix1', 'prefix2')), .names = '{.col}_new'))

Or use base R

dt[paste0(names(dt)[1:3], "_new")] <- dt[1:3]/dt[4:6]

CodePudding user response：

Another solution, based on purrr::reduce:

library(tidyverse)

dt <- data.frame(replicate(6,sample(1:15,10,rep=TRUE)))
colnames(dt) <- c("prefix1 column1","prefix1 column2","prefix1 column3","prefix2 column1","prefix2 column2","prefix2 column3")

reduce(paste0("column",1:3), function(x,y)
  { z <- x[,paste("prefix1",y)] / x[,paste("prefix2",y)];
  bind_cols(x, z %>% data.frame %>% setNames(. ,paste0("d",y))) }, .init=dt)

#>    prefix1 column1 prefix1 column2 prefix1 column3 prefix2 column1
#> 1                2              12               4              13
#> 2               13              14              12              11
#> 3                6               3               4               6
#> 4                1               9               5               6
#> 5               15               1               2               8
#> 6                4               7              15               1
#> 7                5               9               1              10
#> 8                4              10               1               5
#> 9                6               8               3              12
#> 10              13              13               9               2
#>    prefix2 column2 prefix2 column3  dcolumn1    dcolumn2  dcolumn3
#> 1               15              15 0.1538462  0.80000000 0.2666667
#> 2                1              11 1.1818182 14.00000000 1.0909091
#> 3                5               1 1.0000000  0.60000000 4.0000000
#> 4               13              13 0.1666667  0.69230769 0.3846154
#> 5               15               4 1.8750000  0.06666667 0.5000000
#> 6                9               4 4.0000000  0.77777778 3.7500000
#> 7                7               8 0.5000000  1.28571429 0.1250000
#> 8               14               5 0.8000000  0.71428571 0.2000000
#> 9               13               1 0.5000000  0.61538462 3.0000000
#> 10              14              14 6.5000000  0.92857143 0.6428571