Combine columns by pattern in column name

user_8839068 0
Viewed 328

I have a very wide dataframe with where each column exists three times with varying suffixes: xxx_A1.5, xxx_A2.0, xxx_A2.5. Consider this example:

df1 <- data.frame(col1_A1.5 = c(1,2,3,4), 
                  col1_A2.0 = c(2,3,4,5),
                  col1_A2.5 = c(3,4,5,6),
                  col2_A1.5 = c(10,20,30,40), 
                  col2_A2.0 = c(20,30,40,50),
                  col2_A2.5 = c(30,40,50,60))

I now want to combine all col1_xxx into one column (named col1; same for col2 etc.) according to this pattern: col1_A2.0 [colA1.5, colA2.5]. The desired result in the example is thus:

       col1           col2
1  "2 [1, 3]"    "20 [10, 30]"
2  "3 [2, 4]"    "30 [20, 40]"
3  "4 [3, 5]"    "40 [30, 50]"
4  "5 [4, 6]"    "50 [40, 60]"

Since I have > 100 columns, I need a pattern-based solution; I've tried several combinations of mutate and across but cannot achieve the desired result. Can someone offer a hint or solution? Thanks in advance.

dplyr tidyverse r
2 Answers

You can use split.default to split the dataframe based on pattern in column names and use imap to create one column out of multiple columns in each list.

purrr::imap_dfc(split.default(df1, sub('_.*', '', names(df1))), function(x, y) 
    tibble::tibble(!!y := sprintf('%d [%d, %d]', 
                   x[[paste0(y, '_A2.0')]], x[[paste0(y, '_A1.5')]], 
                   x[[paste0(y, '_A2.5')]])))
          
#   col1     col2       
#  <chr>    <chr>      
#1 2 [1, 3] 20 [10, 30]
#2 3 [2, 4] 30 [20, 40]
#3 4 [3, 5] 40 [30, 50]
#4 5 [4, 6] 50 [40, 60]
user_39629140

I maintain a package on github which allows to work with column patterns. Here we could use dplyover::extract_names to extract to column patterns and use this as input to dplyover::over to loop over the patterns. Inside the lambda function we only need paste0 and here we can access the variables with a special syntax inside .("{.x}_somestring") where .x is the string that we loop over and somestring are the suffixes of your variable names.

df1 <- data.frame(col1_A1.5 = c(1,2,3,4), 
                  col1_A2.0 = c(2,3,4,5),
                  col1_A2.5 = c(3,4,5,6),
                  col2_A1.5 = c(10,20,30,40), 
                  col2_A2.0 = c(20,30,40,50),
                  col2_A2.5 = c(30,40,50,60))

library(dplyr)
library(dplyover) # https://github.com/TimTeaFan/dplyover
library(purrr)

df1 %>% 
  transmute(over(extract_names("col\\d+"),
                 ~ paste0(.("{.x}_A2.0"),
                          " [", .("{.x}_A1.5"), ", ",
                          .("{.x}_A2.5"), "]" )))

#>       col1        col2
#> 1 2 [1, 3] 20 [10, 30]
#> 2 3 [2, 4] 30 [20, 40]
#> 3 4 [3, 5] 40 [30, 50]
#> 4 5 [4, 6] 50 [40, 60]

Created on 2021-09-21 by the reprex package (v2.0.1)

** Update ** We can apply the approach above to the data given in the comments as follows:

df <- structure(
  list(i_cont_AGE__Esti = c(0.0179664619979778),
       i_cont_EDUC__Esti = c(-0.0292110157549253),
       i_cont_AGE__Q025 = c(0.000824695411111768),
       i_cont_EDUC__Q025 = c(-0.0440178234496344),
       i_cont_AGE__Q975 = c(0.0346367937813918),
       i_cont_EDUC__Q975 = c(-0.014399287910954)),
  row.names = c("1"),
  class = c("tbl_df", "tbl", "data.frame" ))

library(dplyr)
library(dplyover) # https://github.com/TimTeaFan/dplyover
library(purrr)

df %>% 
  transmute(over(extract_names("i_cont_[A-Z]+"),
                 ~ paste0(.("{.x}__Esti"),
                          " [", .("{.x}__Q025"), ", ",
                          .("{.x}__Q975"), "]" )))
#> # A tibble: 1 x 2
#>   i_cont_AGE                                                    i_cont_EDUC     
#>   <chr>                                                         <chr>           
#> 1 0.0179664619979778 [0.000824695411111768, 0.0346367937813918] -0.029211015754~

Created on 2021-09-21 by the reprex package (v2.0.1)

user_93493020
Related