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.
>Solution :
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