Does this help you?
mydata <-
data.frame(
id = c(1, 2, 3),
`1wk_day` = c(1, 4, 2), # days
`1wk_lab1` = c(120, 140, 257),
`1wk_lab2` = c(10, 24, 14),
`1wk_lab11` = c(50, 34, 64),
`2wk_day` = c(8, 9, 13), # days
`2wk_lab1` = c(122, 124, 143),
`2wk_lab2` = c(18, 13, 14),
`2wk_lab11` = c(74, 75, 84),
`10wk_day` = c(16, 19, 20), # days
`10wk_lab1` = c(198, 125, 231),
`10wk_lab2` = c(19, 13, 15),
`10wk_lab11` = c(47, 84, 37),check.names = FALSE
)
mydata
#> id 1wk_day 1wk_lab1 1wk_lab2 1wk_lab11 2wk_day 2wk_lab1 2wk_lab2 2wk_lab11
#> 1 1 1 120 10 50 8 122 18 74
#> 2 2 4 140 24 34 9 124 13 75
#> 3 3 2 257 14 64 13 143 14 84
#> 10wk_day 10wk_lab1 10wk_lab2 10wk_lab11
#> 1 16 198 19 47
#> 2 19 125 13 84
#> 3 20 231 15 37
library(tidyr)
library(dplyr)
Long1 <- mydata |> pivot_longer(cols = -id, names_pattern = "(.+)_(.+)",
names_to = c("Week", "Second"))
Days <- Long1 |> filter(Second == "day") |>
rename(Day = value) |> select(-Second)
Labs <- Long1 |> filter(Second != "day") |>
pivot_wider(names_from = "Second", values_from = "value")
Final <- inner_join(Days, Labs, by = c("id","Week"))
Final
#> # A tibble: 9 × 6
#> id Week Day lab1 lab2 lab11
#> <dbl> <chr> <dbl> <dbl> <dbl> <dbl>
#> 1 1 1wk 1 120 10 50
#> 2 1 2wk 8 122 18 74
#> 3 1 10wk 16 198 19 47
#> 4 2 1wk 4 140 24 34
#> 5 2 2wk 9 124 13 75
#> 6 2 10wk 19 125 13 84
#> 7 3 1wk 2 257 14 64
#> 8 3 2wk 13 143 14 84
#> 9 3 10wk 20 231 15 37
Created on 2023-06-13 with reprex v2.0.2