Transpose lines to column by id

[laplanca]

I have a file with various rows for each id and I want to transpose some informations in column, in that way I will have 1 rows for each id.

ex :
I want to pass from this :
id / name / surname/ date_test/ result_test
1 / franck / wilson / 01-2002 / P
1 / franck / wilson / 07-2004 / N
1 / franck / wilson / 04-2008 / N
2 / annie/ mark/ 08-2001 / N
2 / annie/ mark/ 03-2005 / P

To this :
id / name / surname/ date_test1/ result_test1/ date_test2/ result_test2/ date_test3/ result_test3
1 / franck / wilson / 01-2002 / P / 07-2004 / N / 04-2008 / N
2 / annie/ mark/ 08-2001 / N / 03-2005 / P

I have first a problematic to transpose my row and then i don't know how to do that my column names about the test are named with the number at the end.

Thank you in advance for answering me!

[Matthias]

The trick is to convert it first to a long table, so we can store the type of variable in there and then convert it to a wide table. Thanks to the pivot_wider function we can add the replicate ID we can calculate before:

library(tidyverse)

input_data = tribble(
  ~"id", ~"name", ~"surname", ~"date_test", ~"result_test",
  1 , "franck" , "wilson" , "01-2002" , "P",
  1 , "franck" , "wilson" , "07-2004" , "N",
  1 , "franck" , "wilson" , "04-2008" , "N",
  2 , "annie", "mark", "08-2001" , "N",
  2 , "annie", "mark", "03-2005" , "P"
)

output_data = input_data %>% 
  # add a counter for the tests performed
  group_by(id, name, surname) %>% 
  mutate(test_rep = 1:n()) %>%
  pivot_longer(cols = c(date_test, result_test),
               names_to = "variable",
               values_to = "readout") %>% 
  pivot_wider(id_cols = c(id, name, surname),
              names_from = c(variable, test_rep),
              names_sep = "_",
              values_from = readout)

output_data
# A tibble: 2 x 9
# Groups:   id, name, surname [2]
     id name   surname date_test_1 result_test_1 date_test_2 result_test_2 date_test_3 result_test_3
  <dbl> <chr>  <chr>   <chr>       <chr>         <chr>       <chr>         <chr>       <chr>        
1     1 franck wilson  01-2002     P             07-2004     N             04-2008     N            
2     2 annie  mark    08-2001     N             03-2005     P             NA          NA