Converting data from wide to long format when id variables are encoded in column header

We can use pivot_longer from tidyr specifying the names_to and names_pattern argument.

tidyr::pivot_longer(df, 
                    cols = starts_with("treat"), 
                    names_to = c("treatmeant", "round", ".value"), 
                    names_pattern =  "(\\w+)\\.(\\d+)\\.(\\w+)")

#  subject_id   age sex   treatmeant round param1 param2
#       <int> <int> <fct> <chr>      <chr>  <int>  <int>
#1          1    23 M     treat1     1          1      2
#2          1    23 M     treat1     2          3      4
#3          2    25 W     treat1     1          5      6
#4          2    25 W     treat1     2          7      8

data

df <- structure(list(subject_id = 1:2, age = c(23L, 25L), sex = structure(1:2, 
.Label = c("M", "W"), class = "factor"), 
treat1.1.param1 = c(1L, 5L), treat1.1.param2 = c(2L, 6L), 
treat1.2.param1 = c(3L, 7L), treat1.2.param2 = c(4L, 8L)), 
class = "data.frame", row.names = c(NA, -2L))

You could use tidyr gather, separate and spread:

tibble::tibble(subject_id = 1:2,
               age = c(23,25),
               sex = c("M", "W"),
               round_1_param_1 = c(1,5),
               round_1_param_2 = c(2,6),
               round_2_param_1 = c(3,7),
               round_2_param_2 = c(4,8)) %>% 
  tidyr::gather("key", "value", -subject_id, -age, -sex) %>% 
  tidyr::separate(key, c("round", "param"), sep = "param") %>%
  dplyr::mutate_at(vars("round", "param"), ~ tidyr::extract_numeric(.)) %>% 
  tidyr::spread(param, value)

# A tibble: 4 x 6
  subject_id   age sex   round   `1`   `2`
       <int> <dbl> <chr> <dbl> <dbl> <dbl>
1          1    23 M         1     1     2
2          1    23 M         2     3     4
3          2    25 W         1     5     6
4          2    25 W         2     7     8

Tags:

R

Reshape2

Tidyr