Rename columns of a dataframe based on values in 3 rows

Question

I am trying to import some data which unfortunately have a very annoying structure.

I can somewhat easily clean up most of the data, but the naming of the columns is currently an issue, as when imported they are labelled as x1, x2, x3 etc.

I want to, based on row position, rename the columns.

I have a small dataframe that I think illustrates the current condition and what I expect the final dataframe to look like. I have tried to come up with a solution using rename_with() but cannot figure out how to create a function that gets the right values and pastes them together.

init_df <- tribble(
  ~variable, ~x1, ~x2, ~x3, ~x4,
  "filler", "A", "B", "C", "D",
  "filler", "1", "2", "3", "4",
  "filler", "01", "02", "03", "04",
  "var1", "10", "12", "14", "16",
  "var2", "0.1", "0.2", "0.3", "0.4"
)

final_df <- tribble(
  ~variable, ~A_1_01, ~B_2_02, ~C_3_03, ~D_4_04,
  "var1", 10, 12, 14, 16,
  "var2", 0.1, 0.2, 0.3, 0.4
)

In this case I want to combine the values found in row 1, 2 and 3 separated by _. Removing the rows afterwards should be trivial.

Answer 1

Here is one option to achieve your desired result:

names(init_df)[-1] <- init_df[1:3, -1] |>
  apply(2, \(x) paste(x, collapse = "_"))

init_df <- init_df[-c(1:3), ]

init_df
#> # A tibble: 2 × 5
#>   variable A_1_01 B_2_02 C_3_03 D_4_04
#>   <chr>    <chr>  <chr>  <chr>  <chr> 
#> 1 var1     10     12     14     16    
#> 2 var2     0.1    0.2    0.3    0.4

Answer 2

Here's an option with rename_with:

library(dplyr)
library(purrr)
init_df %>%
  rename_with(.fn = ~map_chr(.x,~str_c(pull(init_df,.x)[1:3],collapse = "_")),
              .cols = -variable) %>%
 slice(-c(1:3))
## A tibble: 2 × 5
#  variable A_1_01 B_2_02 C_3_03 D_4_04
#  <chr>    <chr>  <chr>  <chr>  <chr> 
#1 var1     10     12     14     16    
#2 var2     0.1    0.2    0.3    0.4