Melt using tidyverse (dplyr) functions, when needing `measure = patterns("x", "y")` from data.table

Question

I have a nice and quite long pipe of different tidyverse commands, but halfway through I need to use a SetDT() %>% melt(...) %>% as_tibble(). My code works fine, but I was wondering if a pure tidyverse solution using either gather() or pivot_longer() could do the same for me. I have tried a lot, but cannot figure it out.

This is sample dataframe

library(tidyverse)
library(data.table)

df <- tibble(ID1 = c("A", "B", "C", "E", "F"),
             ID2 = 1:5,
             PR_1  = c(100, 150, 200, 250, 300),
             QL_1  = c(2.5, 4, 5, 2, 1),
             PR_2  = c(105, 150, 210, 275, 301),
             QL_2  = c(3, 4.5, 5.1, 2.5, 3))
df

# A tibble: 5 x 6
  ID1     ID2  PR_1  QL_1  PR_2  QL_2
  <chr> <int> <dbl> <dbl> <dbl> <dbl>
1 A         1   100   2.5   105   3  
2 B         2   150   4     150   4.5
3 C         3   200   5     210   5.1
4 E         4   250   2     275   2.5
5 F         5   300   1     301   3

And this is the expected outcome

   ID1     ID2 variable    PR    QL
   <chr> <int> <chr>    <dbl> <dbl>
 1 A         1 1          100   2.5
 2 B         2 1          150   4  
 3 C         3 1          200   5  
 4 E         4 1          250   2  
 5 F         5 1          300   1  
 6 A         1 2          105   3  
 7 B         2 2          150   4.5
 8 C         3 2          210   5.1
 9 E         4 2          275   2.5
10 F         5 2          301   3

I now use this code (which works but requires the data.table package)

df %>%
  setDT() %>% 
  melt(id = c("ID1",  
              "ID2"),
       measure = patterns("^PR", "^QL"), 
       variable.factor = FALSE,
       value.name = c("PR", "QL")) %>%
  as_tibble()

My question is, what would be the tidyverse equivalent? I really like the data.table package, but for this project I would like to stick to just tidyverse code

score 1 · Accepted Answer · answered Jul 18 '20 at 13:23

You can use pivot_longer like :

tidyr::pivot_longer(df, 
                    cols = -starts_with('ID'), 
                    names_to = c('.value', 'variable'), 
                    names_sep = "_")

# A tibble: 10 x 5
#   ID1     ID2 variable    PR    QL
#   <chr> <int> <chr>    <dbl> <dbl>
# 1 A         1 1          100   2.5
# 2 A         1 2          105   3  
# 3 B         2 1          150   4  
# 4 B         2 2          150   4.5
# 5 C         3 1          200   5  
# 6 C         3 2          210   5.1
# 7 E         4 1          250   2  
# 8 E         4 2          275   2.5
# 9 F         5 1          300   1  
#10 F         5 2          301   3

exactly what I was looking for! – L Smeets Jul 18 '20 at 13:26 — L Smeets, Jul 18 '20 at 13:26

score 0 · Answer 2 · answered Jul 18 '20 at 13:27

You can try this:

library(tidyverse)

df %>% pivot_longer(cols = names(df)[-c(1,2)]) %>%
  separate(name,into = c('char','var'),sep = '_') %>%
  pivot_wider(names_from = char,values_from = value)

# A tibble: 10 x 5
   ID1     ID2 var      PR    QL
   <chr> <int> <chr> <dbl> <dbl>
 1 A         1 1       100   2.5
 2 A         1 2       105   3  
 3 B         2 1       150   4  
 4 B         2 2       150   4.5
 5 C         3 1       200   5  
 6 C         3 2       210   5.1
 7 E         4 1       250   2  
 8 E         4 2       275   2.5
 9 F         5 1       300   1  
10 F         5 2       301   3

This also works fine, but is a little less elegant than the solution by Ronak Shah. — L Smeets, Jul 18 '20 at 13:32

Melt using tidyverse (dplyr) functions, when needing `measure = patterns("x", "y")` from data.table

2 Answers2