I have a data frame like this:
library(tibble)
Building on assumption that we can also split the dataset by Test (SCREEN, INDEX, others) and final description, Code, Date & group values are defined by SCREEN subset.
Once dataset is split, we can join SCREEN & INDEX subsets by ID, ID.2, unite columns and bind back together with the rest:
library(dplyr)
library(forcats)
test_split <- split(data, fct_other(data$Test, keep = c("SCREEN", "INDEX")))
left_join(test_split$SCREEN,
select(test_split$INDEX, ID, ID.2, Test, value),
by = join_by(ID, ID.2)) %>%
mutate(Test = paste(Test.x, Test.y, sep = ", "),
value = paste(value.x, value.y, sep = ", "),
.keep = "unused", .after = "Code") %>%
bind_rows(test_split$Other)
#> # A tibble: 10 × 9
#> SheetID ID ID.2 description Code Test value Date group
#> <chr> <dbl> <dbl> <chr> <dbl> <chr> <chr> <chr> <chr>
#> 1 Sheet1 170 36 "pain" 395 SCREEN, INDEX ND, 0.4 15/11/2… A
#> 2 Sheet1 199 86 "skin" 395 SCREEN, INDEX ND, 0.2 17/11/2… A
#> 3 Sheet1 239 66 "Not provided" 395 SCREEN, INDEX ND, 0 19/11/2… A
#> 4 Sheet1 268 72 "done" 381 DDA <7 21/11/2… A
#> 5 Sheet1 485 34 "" 321 Sy N 22/11/2… M
#> 6 Sheet1 485 34 "" 322 RaP N 23/11/2… M
#> 7 Sheet1 485 34 "" 323 SS-DJA N 24/11/2… M
#> 8 Sheet1 485 34 "" 324 RAT N 25/11/2… M
#> 9 Sheet1 485 34 "" 325 SSMU N 26/11/2… M
#> 10 Sheet1 485 34 "" 326 SLI20 N 27/11/2… M
test_split :
#> $INDEX
#> # A tibble: 3 × 9
#> SheetID ID ID.2 description Code Test value Date group
#> <chr> <dbl> <dbl> <chr> <dbl> <chr> <chr> <chr> <chr>
#> 1 Sheet1 170 36 "" 398 INDEX 0.4 16/11/2020 A
#> 2 Sheet1 199 86 "" 398 INDEX 0.2 18/11/2020 A
#> 3 Sheet1 239 66 "" 398 INDEX 0 20/11/2020 A
#>
#> $SCREEN
#> # A tibble: 3 × 9
#> SheetID ID ID.2 description Code Test value Date group
#> <chr> <dbl> <dbl> <chr> <dbl> <chr> <chr> <chr> <chr>
#> 1 Sheet1 170 36 pain 395 SCREEN ND 15/11/2020 A
#> 2 Sheet1 199 86 skin 395 SCREEN ND 17/11/2020 A
#> 3 Sheet1 239 66 Not provided 395 SCREEN ND 19/11/2020 A
#>
#> $Other
#> # A tibble: 7 × 9
#> SheetID ID ID.2 description Code Test value Date group
#> <chr> <dbl> <dbl> <chr> <dbl> <chr> <chr> <chr> <chr>
#> 1 Sheet1 268 72 "done" 381 DDA <7 21/11/2020 A
#> 2 Sheet1 485 34 "" 321 Sy N 22/11/2020 M
#> 3 Sheet1 485 34 "" 322 RaP N 23/11/2020 M
#> 4 Sheet1 485 34 "" 323 SS-DJA N 24/11/2020 M
#> 5 Sheet1 485 34 "" 324 RAT N 25/11/2020 M
#> 6 Sheet1 485 34 "" 325 SSMU N 26/11/2020 M
#> 7 Sheet1 485 34 "" 326 SLI20 N 27/11/2020 M
Created on 2023-11-20 with reprex v2.0.2