pivot_longer but for multiple sets of columns

Question

I have a data frame as follows:

Z1      Z2      Y1      Y2      smpl
0.451   0.333   0.034   0.173   A        
0.491   0.270   0.033   0.207   B

Columns Z1 and Z2 are multiple observations from the same sample. Same goes for Y1 and Y2.

I need to merge columns Z1 and Z2, and columns Y1 and Y2. yielding the following:

Z       Y       smpl
0.451   0.034   A
0.333   0.173   A
0.491   0.033   B   
0.270   0.207   B

I am sure the solution is trivial, but... Well, I am a bit of a n00b, and hence I am also stuck. How can I do this?

score 4 · Accepted Answer · answered Jul 24 '20 at 13:07

You can use :

tidyr::pivot_longer(df, cols = -smpl, 
                        names_to = '.value', 
                        names_pattern = '([A-Z])')
# A tibble: 4 x 3
#  smpl    Z     Y
#  <chr> <dbl> <dbl>
#1 A     0.451 0.034
#2 A     0.333 0.173
#3 B     0.491 0.033
#4 B     0.27  0.207

Roman · Answer 2 · 2020-07-24T13:10:36.767

you can try

library(tidyverse)
df %>% 
  pivot_longer(matches("Z|Y")) %>% 
  separate(name, letters[1:2], sep = 1) %>% 
  pivot_wider(names_from = a,  values_from = value)
# A tibble: 4 x 4
  smpl  b         Z     Y
  <fct> <chr> <dbl> <dbl>
1 A     1     0.451 0.034
2 A     2     0.333 0.173
3 B     1     0.491 0.033
4 B     2     0.27  0.207

Or easier

df %>% 
pivot_longer(matches("Z|Y"),
             names_to = c(".value", "b"),
             names_sep = 1)

if you don't need the information about subgroups of Y and Z you can finish the lines of code using select(-b)

pivot_longer but for multiple sets of columns

2 Answers2