Wide to Long preserving a group

Question

How would I transform the following data.frame from wide to long while also generating a group column?

Example data:

dat <- data.frame(ID=paste0("id",1:10),readout1_g1=rnorm(10),readout1_g2=rnorm(10),readout1_g3=rnorm(10),readout2_g1=rnorm(10),readout2_g2=rnorm(10),readout2_g3=rnorm(10))

I have the following groups I am interested in that are part of the column names

groups <- c("g1","g2","g3","g1","g2","g3")

dat
     ID readout1_g1 readout1_g2 readout1_g3 readout2_g1 readout2_g2 readout2_g3
1   id1   0.2732715  -0.6328317  -1.3024276   1.7248944  -0.3343275  1.36908631
2   id2   2.0209029  -0.8575063   0.6022883  -0.9226497   1.4893213 -0.98697968
3   id3   1.6034079   0.8023266   1.0845380   1.2936800  -0.7395568 -0.29720316
4   id4  -1.5122641  -2.5982733   0.4245139  -1.2592326   1.5311672  0.38242248
5   id5   0.8096931  -0.6260401   0.7163551   0.2675392   0.8919616  0.01563483
6   id6  -0.8557278   0.1875746   1.3020202   0.4973898   2.7477934  0.38654868
7   id7   0.1556841  -1.1668916   0.6434463  -0.2245761  -0.6832238 -2.50482389
8   id8   0.7511197   0.4819956   0.2385116  -1.0582192   0.1067799  2.15636372
9   id9  -0.4613545  -1.2080645   1.3165922  -2.1785463  -1.2692302 -0.44555107
10 id10   0.3601222  -0.2414607  -1.5775119   2.0834873  -0.4656254  1.58457613

I would like to obtain the following long format:

ID readout1 readout2 group
id1 value value g1
id2 value value g1
...
id10 value value g3

I tried using pivot_longer in its most simple form but the rows were not ordered by groups properly in that rows having g1 for readout1 and g2 for readout2 for example.

You're correct, google did not lead me to this unfortunately. — gdeniz, May 17 '22 at 22:37

score 2 · Accepted Answer · answered May 17 '22 at 22:33

You could use pivot_longer to extract the group followed by pivot_wider to restore the readout columns:

library(dplyr)
library(tidyr)

dat %>% 
  pivot_longer(-ID, names_pattern = "(.*)_(.*)", names_to = c("name", "grp")) %>% 
  pivot_wider()

This returns

# A tibble: 30 x 4
   ID    grp   readout1 readout2
   <chr> <chr>    <dbl>    <dbl>
 1 id1   g1      0.732    0.499 
 2 id1   g2     -0.301   -0.0941
 3 id1   g3      0.198    1.62  
 4 id2   g1      0.653    0.732 
 5 id2   g2     -0.325    1.49  
 6 id2   g3     -0.0475  -1.73  
 7 id3   g1     -1.59     1.25  
 8 id3   g2     -1.59     0.688 
 9 id3   g3     -0.738    0.341 
10 id4   g1      1.12     1.11  
# ... with 20 more rows

Wide to Long preserving a group

1 Answers1