pivot_longer multiple group of columns

tidyverse
1

I have a dataframe with variables that I need to transpose along the table, using pivot_longer. First I need to transpose the columns Sample 1 Sample 2 Sample 3 Sample 4 with the column name "Sample". Then, for each of those names (Sample 1, Sample 2, Sample 3 and Sample 4), I need to associate the values of columns VE, VE_2, VE_3 and VE_4 with the name of column "VE" and the values of columns u_VE, u_VE_2, u_VE_3, u_VE_4, with the name of column "u_VE" .

This is my original dataframe:

tibble::tribble(
  ~Cod_participante,            ~Parametro, ~Muestra.1, ~Muestra.2, ~Muestra.3, ~Muestra.4,   ~VE, ~VE_2, ~VE_3, ~VE_4,  ~u_VE, ~u_VE_2, ~u_VE_3, ~u_VE_4,
               2517, "Aluminio total (Al)",         NA,         NA,         NA,         NA,  1.51,    NA,    NA,    NA,   0.05,      NA,      NA,      NA,
               3796, "Aluminio total (Al)",         NA,         NA,         NA,         NA,  1.51,    NA,    NA,    NA,   0.05,      NA,      NA,      NA,
               4012, "Aluminio total (Al)",         NA,         NA,         NA,         NA,  1.51,    NA,    NA,    NA,   0.05,      NA,      NA,      NA,
               4266, "Aluminio total (Al)",         NA,         NA,         NA,         NA,  1.51,    NA,    NA,    NA,   0.05,      NA,      NA,      NA,
               4667, "Aluminio total (Al)",         NA,         NA,         NA,         NA,  1.51,    NA,    NA,    NA,   0.05,      NA,      NA,      NA,
               5284, "Aluminio total (Al)",         NA,         NA,         NA,         NA,  1.51,    NA,    NA,    NA,   0.05,      NA,      NA,      NA,
               6506, "Aluminio total (Al)",         NA,         NA,         NA,         NA,  1.51,    NA,    NA,    NA,   0.05,      NA,      NA,      NA,
               6628, "Aluminio total (Al)",         NA,         NA,         NA,         NA,  1.51,    NA,    NA,    NA,   0.05,      NA,      NA,      NA,
               9234, "Aluminio total (Al)",         NA,         NA,         NA,         NA,  1.51,    NA,    NA,    NA,   0.05,      NA,      NA,      NA,
               9609, "Aluminio total (Al)",         NA,         NA,         NA,         NA,  1.51,    NA,    NA,    NA,   0.05,      NA,      NA,      NA,
               3796, "Arsenico total (As)",         NA,         NA,         NA,         NA, 0.701,    NA,    NA,    NA, 0.0125,      NA,      NA,      NA,
               4012, "Arsenico total (As)",         NA,         NA,         NA,         NA, 0.701,    NA,    NA,    NA, 0.0125,      NA,      NA,      NA,
               4266, "Arsenico total (As)",         NA,         NA,         NA,         NA, 0.701,    NA,    NA,    NA, 0.0125,      NA,      NA,      NA,
               6506, "Arsenico total (As)",         NA,         NA,         NA,         NA, 0.701,    NA,    NA,    NA, 0.0125,      NA,      NA,      NA,
               6628, "Arsenico total (As)",         NA,         NA,         NA,         NA, 0.701,    NA,    NA,    NA, 0.0125,      NA,      NA,      NA
  )
#> # A tibble: 15 x 14
#>    Cod_participante Parametro Muestra.1 Muestra.2 Muestra.3 Muestra.4    VE
#>               <dbl> <chr>     <lgl>     <lgl>     <lgl>     <lgl>     <dbl>
#>  1             2517 Aluminio~ NA        NA        NA        NA        1.51 
#>  2             3796 Aluminio~ NA        NA        NA        NA        1.51 
#>  3             4012 Aluminio~ NA        NA        NA        NA        1.51 
#>  4             4266 Aluminio~ NA        NA        NA        NA        1.51 
#>  5             4667 Aluminio~ NA        NA        NA        NA        1.51 
#>  6             5284 Aluminio~ NA        NA        NA        NA        1.51 
#>  7             6506 Aluminio~ NA        NA        NA        NA        1.51 
#>  8             6628 Aluminio~ NA        NA        NA        NA        1.51 
#>  9             9234 Aluminio~ NA        NA        NA        NA        1.51 
#> 10             9609 Aluminio~ NA        NA        NA        NA        1.51 
#> 11             3796 Arsenico~ NA        NA        NA        NA        0.701
#> 12             4012 Arsenico~ NA        NA        NA        NA        0.701
#> 13             4266 Arsenico~ NA        NA        NA        NA        0.701
#> 14             6506 Arsenico~ NA        NA        NA        NA        0.701
#> 15             6628 Arsenico~ NA        NA        NA        NA        0.701
#> # ... with 7 more variables: VE_2 <lgl>, VE_3 <lgl>, VE_4 <lgl>, u_VE <dbl>,
#> #   u_VE_2 <lgl>, u_VE_3 <lgl>, u_VE_4 <lgl>

Created on 2021-04-20 by the reprex package (v1.0.0)

Then I usie the command:

Pivot<-df2%>%pivot_longer(cols="Sample 1": "Sample 4",names_to = Sample")%>%pivot_longer(cols="VE": "VE_4",values_to = "VE").

My new dataframe multiply 4 times Sample 1 and so on for each sample, when there should be 1 VE value for Sample 1, instead of 4:

tibble::tribble(
  ~Cod_participante,            ~Parametro,  ~u_VE, ~u_VE_2, ~u_VE_3, ~u_VE_4,    ~Muestra, ~value,  ~name,   ~VE,
               2517, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA,   "VE",  1.51,
               2517, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_2",    NA,
               2517, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_3",    NA,
               2517, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_4",    NA,
               2517, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA,   "VE",  1.51,
               2517, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_2",    NA,
               2517, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_3",    NA,
               2517, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_4",    NA,
               2517, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA,   "VE",  1.51,
               2517, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_2",    NA,
               2517, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_3",    NA,
               2517, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_4",    NA,
               2517, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA,   "VE",  1.51,
               2517, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_2",    NA,
               2517, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_3",    NA,
               2517, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_4",    NA,
               3796, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA,   "VE",  1.51,
               3796, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_2",    NA,
               3796, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_3",    NA,
               3796, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_4",    NA,
               3796, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA,   "VE",  1.51,
               3796, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_2",    NA,
               3796, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_3",    NA,
               3796, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_4",    NA,
               3796, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA,   "VE",  1.51,
               3796, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_2",    NA,
               3796, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_3",    NA,
               3796, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_4",    NA,
               3796, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA,   "VE",  1.51,
               3796, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_2",    NA,
               3796, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_3",    NA,
               3796, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_4",    NA,
               4012, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA,   "VE",  1.51,
               4012, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_2",    NA,
               4012, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_3",    NA,
               4012, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_4",    NA,
               4012, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA,   "VE",  1.51,
               4012, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_2",    NA,
               4012, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_3",    NA,
               4012, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_4",    NA,
               4012, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA,   "VE",  1.51,
               4012, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_2",    NA,
               4012, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_3",    NA,
               4012, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_4",    NA,
               4012, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA,   "VE",  1.51,
               4012, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_2",    NA,
               4012, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_3",    NA,
               4012, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_4",    NA,
               4266, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA,   "VE",  1.51,
               4266, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_2",    NA,
               4266, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_3",    NA,
               4266, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_4",    NA,
               4266, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA,   "VE",  1.51,
               4266, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_2",    NA,
               4266, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_3",    NA,
               4266, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_4",    NA,
               4266, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA,   "VE",  1.51,
               4266, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_2",    NA,
               4266, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_3",    NA,
               4266, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_4",    NA,
               4266, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA,   "VE",  1.51,
               4266, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_2",    NA,
               4266, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_3",    NA,
               4266, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_4",    NA,
               4667, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA,   "VE",  1.51,
               4667, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_2",    NA,
               4667, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_3",    NA,
               4667, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_4",    NA,
               4667, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA,   "VE",  1.51,
               4667, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_2",    NA,
               4667, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_3",    NA,
               4667, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_4",    NA,
               4667, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA,   "VE",  1.51,
               4667, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_2",    NA,
               4667, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_3",    NA,
               4667, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_4",    NA,
               4667, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA,   "VE",  1.51,
               4667, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_2",    NA,
               4667, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_3",    NA,
               4667, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_4",    NA,
               5284, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA,   "VE",  1.51,
               5284, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_2",    NA,
               5284, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_3",    NA,
               5284, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_4",    NA,
               5284, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA,   "VE",  1.51,
               5284, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_2",    NA,
               5284, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_3",    NA,
               5284, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_4",    NA,
               5284, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA,   "VE",  1.51,
               5284, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_2",    NA,
               5284, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_3",    NA,
               5284, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_4",    NA,
               5284, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA,   "VE",  1.51,
               5284, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_2",    NA,
               5284, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_3",    NA,
               5284, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_4",    NA,
               6506, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA,   "VE",  1.51,
               6506, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_2",    NA,
               6506, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_3",    NA,
               6506, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_4",    NA,
               6506, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA,   "VE",  1.51,
               6506, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_2",    NA,
               6506, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_3",    NA,
               6506, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_4",    NA,
               6506, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA,   "VE",  1.51,
               6506, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_2",    NA,
               6506, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_3",    NA,
               6506, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_4",    NA,
               6506, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA,   "VE",  1.51,
               6506, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_2",    NA,
               6506, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_3",    NA,
               6506, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_4",    NA,
               6628, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA,   "VE",  1.51,
               6628, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_2",    NA,
               6628, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_3",    NA,
               6628, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_4",    NA,
               6628, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA,   "VE",  1.51,
               6628, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_2",    NA,
               6628, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_3",    NA,
               6628, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_4",    NA,
               6628, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA,   "VE",  1.51,
               6628, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_2",    NA,
               6628, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_3",    NA,
               6628, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_4",    NA,
               6628, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA,   "VE",  1.51,
               6628, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_2",    NA,
               6628, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_3",    NA,
               6628, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_4",    NA,
               9234, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA,   "VE",  1.51,
               9234, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_2",    NA,
               9234, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_3",    NA,
               9234, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_4",    NA,
               9234, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA,   "VE",  1.51,
               9234, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_2",    NA,
               9234, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_3",    NA,
               9234, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_4",    NA,
               9234, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA,   "VE",  1.51,
               9234, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_2",    NA,
               9234, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_3",    NA,
               9234, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_4",    NA,
               9234, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA,   "VE",  1.51,
               9234, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_2",    NA,
               9234, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_3",    NA,
               9234, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_4",    NA,
               9609, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA,   "VE",  1.51,
               9609, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_2",    NA,
               9609, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_3",    NA,
               9609, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 1",     NA, "VE_4",    NA,
               9609, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA,   "VE",  1.51,
               9609, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_2",    NA,
               9609, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_3",    NA,
               9609, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 2",     NA, "VE_4",    NA,
               9609, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA,   "VE",  1.51,
               9609, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_2",    NA,
               9609, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_3",    NA,
               9609, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 3",     NA, "VE_4",    NA,
               9609, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA,   "VE",  1.51,
               9609, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_2",    NA,
               9609, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_3",    NA,
               9609, "Aluminio total (Al)",   0.05,      NA,      NA,      NA, "Muestra 4",     NA, "VE_4",    NA,
               3796, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 1",     NA,   "VE", 0.701,
               3796, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 1",     NA, "VE_2",    NA,
               3796, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 1",     NA, "VE_3",    NA,
               3796, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 1",     NA, "VE_4",    NA,
               3796, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 2",     NA,   "VE", 0.701,
               3796, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 2",     NA, "VE_2",    NA,
               3796, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 2",     NA, "VE_3",    NA,
               3796, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 2",     NA, "VE_4",    NA,
               3796, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 3",     NA,   "VE", 0.701,
               3796, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 3",     NA, "VE_2",    NA,
               3796, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 3",     NA, "VE_3",    NA,
               3796, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 3",     NA, "VE_4",    NA,
               3796, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 4",     NA,   "VE", 0.701,
               3796, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 4",     NA, "VE_2",    NA,
               3796, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 4",     NA, "VE_3",    NA,
               3796, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 4",     NA, "VE_4",    NA,
               4012, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 1",     NA,   "VE", 0.701,
               4012, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 1",     NA, "VE_2",    NA,
               4012, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 1",     NA, "VE_3",    NA,
               4012, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 1",     NA, "VE_4",    NA,
               4012, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 2",     NA,   "VE", 0.701,
               4012, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 2",     NA, "VE_2",    NA,
               4012, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 2",     NA, "VE_3",    NA,
               4012, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 2",     NA, "VE_4",    NA,
               4012, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 3",     NA,   "VE", 0.701,
               4012, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 3",     NA, "VE_2",    NA,
               4012, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 3",     NA, "VE_3",    NA,
               4012, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 3",     NA, "VE_4",    NA,
               4012, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 4",     NA,   "VE", 0.701,
               4012, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 4",     NA, "VE_2",    NA,
               4012, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 4",     NA, "VE_3",    NA,
               4012, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 4",     NA, "VE_4",    NA,
               4266, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 1",     NA,   "VE", 0.701,
               4266, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 1",     NA, "VE_2",    NA,
               4266, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 1",     NA, "VE_3",    NA,
               4266, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 1",     NA, "VE_4",    NA,
               4266, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 2",     NA,   "VE", 0.701,
               4266, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 2",     NA, "VE_2",    NA,
               4266, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 2",     NA, "VE_3",    NA,
               4266, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 2",     NA, "VE_4",    NA,
               4266, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 3",     NA,   "VE", 0.701,
               4266, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 3",     NA, "VE_2",    NA,
               4266, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 3",     NA, "VE_3",    NA,
               4266, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 3",     NA, "VE_4",    NA,
               4266, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 4",     NA,   "VE", 0.701,
               4266, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 4",     NA, "VE_2",    NA,
               4266, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 4",     NA, "VE_3",    NA,
               4266, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 4",     NA, "VE_4",    NA,
               6506, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 1",     NA,   "VE", 0.701,
               6506, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 1",     NA, "VE_2",    NA,
               6506, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 1",     NA, "VE_3",    NA,
               6506, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 1",     NA, "VE_4",    NA,
               6506, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 2",     NA,   "VE", 0.701,
               6506, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 2",     NA, "VE_2",    NA,
               6506, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 2",     NA, "VE_3",    NA,
               6506, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 2",     NA, "VE_4",    NA,
               6506, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 3",     NA,   "VE", 0.701,
               6506, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 3",     NA, "VE_2",    NA,
               6506, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 3",     NA, "VE_3",    NA,
               6506, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 3",     NA, "VE_4",    NA,
               6506, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 4",     NA,   "VE", 0.701,
               6506, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 4",     NA, "VE_2",    NA,
               6506, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 4",     NA, "VE_3",    NA,
               6506, "Arsenico total (As)", 0.0125,      NA,      NA,      NA, "Muestra 4",     NA, "VE_4",    NA,
  )
#> # A tibble: 240 x 10
#>    Cod_participante Parametro  u_VE u_VE_2 u_VE_3 u_VE_4 Muestra value name 
#>               <dbl> <chr>     <dbl> <lgl>  <lgl>  <lgl>  <chr>   <lgl> <chr>
#>  1             2517 Aluminio~  0.05 NA     NA     NA     Muestr~ NA    VE   
#>  2             2517 Aluminio~  0.05 NA     NA     NA     Muestr~ NA    VE_2 
#>  3             2517 Aluminio~  0.05 NA     NA     NA     Muestr~ NA    VE_3 
#>  4             2517 Aluminio~  0.05 NA     NA     NA     Muestr~ NA    VE_4 
#>  5             2517 Aluminio~  0.05 NA     NA     NA     Muestr~ NA    VE   
#>  6             2517 Aluminio~  0.05 NA     NA     NA     Muestr~ NA    VE_2 
#>  7             2517 Aluminio~  0.05 NA     NA     NA     Muestr~ NA    VE_3 
#>  8             2517 Aluminio~  0.05 NA     NA     NA     Muestr~ NA    VE_4 
#>  9             2517 Aluminio~  0.05 NA     NA     NA     Muestr~ NA    VE   
#> 10             2517 Aluminio~  0.05 NA     NA     NA     Muestr~ NA    VE_2 
#> # ... with 230 more rows, and 1 more variable: VE <dbl>

Created on 2021-04-20 by the reprex package (v1.0.0)

Thank you.

2

From your explanation, it is not clear what is your desired result. The function pivot_longer() works exactly as it designed, so on this single row example could you explain how letters and numbers should be matched in the output tibble?

suppressMessages(library(tidyverse))

df <- tribble(
  ~letter1, ~letter2, ~number1, ~number2,
  "A", "B", 1, 2
)
df
#> # A tibble: 1 x 4
#>   letter1 letter2 number1 number2
#>   <chr>   <chr>     <dbl>   <dbl>
#> 1 A       B             1       2

df %>%
  pivot_longer(cols = c("letter1","letter2"),
               names_to = "letter_num",
               values_to = "letter") %>%
  pivot_longer(cols = c("number1", "number2"),
               names_to = "number_num",
               values_to = "number")
#> # A tibble: 4 x 4
#>   letter_num letter number_num number
#>   <chr>      <chr>  <chr>       <dbl>
#> 1 letter1    A      number1         1
#> 2 letter1    A      number2         2
#> 3 letter2    B      number1         1
#> 4 letter2    B      number2         2
3

Indeed, it was not very clear, I will explain it with the following images, based on your example:

This is my original table:

image

This is what I need to get to.

image

So, regarding my example, after creating my "Sample" column with the names "Sample 1" to "Sample 4", I need to assign the value of VE to Sample 1; the value of VE_2 to Sample 2; the value of VE_3 to Sample 3 and the value of VE_4 to Sample 4. But what it turns out is that it replicates 4 times Sample 1 for each value of VE, VE_2, VE_3 and VE_4; and so on for the other columns. This for each Cod_ETFA and Parameter.

I hope it can be understood.

Thank you very much.

4

Now I understand what's your aim.

Depending on the structure of your data set you can use either option 1 or 2. I hope you can easily understand the essential difference between df1 and df2.

suppressMessages(library(tidyverse))

# Option 1
df1 <- tribble(
  ~letter1, ~letter2, ~number1, ~number2,
  "A", "B", 1, 2,
)

df1
#> # A tibble: 1 x 4
#>   letter1 letter2 number1 number2
#>   <chr>   <chr>     <dbl>   <dbl>
#> 1 A       B             1       2


df1 %>%
  pivot_longer(cols = c("letter1","letter2"),
               names_to = "letter_num",
               values_to = "letter") %>%
  mutate(number = case_when(letter == "A" ~ number1,
                            letter == "B" ~ number2))
#> # A tibble: 2 x 5
#>   number1 number2 letter_num letter number
#>     <dbl>   <dbl> <chr>      <chr>   <dbl>
#> 1       1       2 letter1    A           1
#> 2       1       2 letter2    B           2

# Option 2
df2 <- tribble(
  ~letter1, ~letter2, ~number1, ~number2,
  "A", NA, 1, NA,
  NA, "B", NA, 2
)

df2
#> # A tibble: 2 x 4
#>   letter1 letter2 number1 number2
#>   <chr>   <chr>     <dbl>   <dbl>
#> 1 A       <NA>          1      NA
#> 2 <NA>    B            NA       2


df2 %>%
  unite(col = "letter",
        starts_with("letter"),
        remove = TRUE,
        na.rm = TRUE) %>%
  unite(col = "number",
        starts_with("number"),
        remove = TRUE,
        na.rm = TRUE)
#> # A tibble: 2 x 2
#>   letter number
#>   <chr>  <chr> 
#> 1 A      1     
#> 2 B      2
1 Like
5

Thank you very much, I will check the code.

Best regards!

6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.