pivoting for 2 columns of values

July 7, 2023

I have the following data:

structure(list(criteria = c("D_max", "ENT_max", "Q_max", "MDCT_min", 
"BOD_min", "GF_max", "VOL_min"), Mean_ME_opaco = c(2.31951219512195, 
3.59547738693467, 3.88265306122449, 5.68948655256724, 6.3643216080402, 
6.55635491606715, 6.7), Mean_ME_Transp = c(0.869565217391304, 
10.8641975308642, 2.1264367816092, 1, 3.67901234567901, 3.70967741935484, 
2.18840579710145), sd_ME_opaco = c(4.13735558695493, 4.39425771945945, 
4.71884778320564, 6.86415183201686, 6.50095388599195, 6.43475495235123, 
6.56397890542807), sd_ME_Transp = c(1.78154793465634, 6.37525114489569, 
3.09088907846522, 2.09208307535884, 4.75086087065762, 4.41824494780745, 
3.46102399977589)), row.names = c(NA, -7L), class = c("tbl_df", 
"tbl", "data.frame"))

I Trying to create to pivot columns that start with Mean_ME_ for one column and the columns that starts with sd_ME_ for another column, I trying the follow code, but wont works:

Tabela %>%
    pivot_longer(cols = starts_with("Mean_"), 
                 names_to = c("referente", ".value"),
                 names_pattern = "Mean_(.*)") %>%
    pivot_longer(cols = starts_with("sd_"),
                 names_to = c("referente", "desvio"),
                 names_pattern = "sd_(.*)_(.*)",
                 values_to = "sd")

The expected output:

A tibble: 14 x 4
   criteria referente    Mean    sd
   <chr>    <chr>       <dbl> <dbl>
 1 D_max    opaco        2.32  4.14
 2 D_max    Transp       0.870 1.78
 3 ENT_max  opaco        3.60  4.39
 4 ENT_max  Transp      10.9   6.38
 5 Q_max    opaco        3.88  4.72
 6 Q_max    Transp       2.13  3.09
 7 MDCT_min opaco        5.69  6.86
 8 MDCT_min Transp       1.00  2.09
 9 BOD_min  opaco        6.36  6.50
10 BOD_min  Transp       3.68  4.75
11 GF_max   opaco        6.56  6.43
12 GF_max   Transp       3.71  4.42
13 VOL_min  opaco        6.70  6.56
14 VOL_min  Transp       2.19  3.46

Thanks any help

>Solution :

You need the names_to argument to have three names if you are using names_sep = '_'. Check the solution below. On the other hand, if using names_pattern = "([^_]+)_.*_(.*)" the two groups ie the value and the referent should be captured

df %>%
   pivot_longer(-criteria, names_to = c(".value",NA, "referente"), names_sep = '_')
# A tibble: 14 × 4
   criteria referente   Mean    sd
   <chr>    <chr>      <dbl> <dbl>
 1 D_max    opaco      2.32   4.14
 2 D_max    Transp     0.870  1.78
 3 ENT_max  opaco      3.60   4.39
 4 ENT_max  Transp    10.9    6.38
 5 Q_max    opaco      3.88   4.72
 6 Q_max    Transp     2.13   3.09
 7 MDCT_min opaco      5.69   6.86
 8 MDCT_min Transp     1      2.09
 9 BOD_min  opaco      6.36   6.50
10 BOD_min  Transp     3.68   4.75
11 GF_max   opaco      6.56   6.43
12 GF_max   Transp     3.71   4.42
13 VOL_min  opaco      6.7    6.56
14 VOL_min  Transp     2.19   3.46