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