[FIXED] How to pivot_longer two groups of columns using regular expression

Issue

I have this data

myvar = tibble(var_1 = c("a", "b", "c"),
       var_2 = c("d", "e", "f"),
       var_3 = c("g", "h", "i"),
       something_var_1 = 1:3,
       something_var_2 = 4:6,
       something_var_3 = 7:9)

And I want this output

 n    var  something_var
 1    a    1
 2    d    4
 3    g    7
 4    b    2
 etc

I looked at this example and tried to modify it: https://tidyr.tidyverse.org/reference/pivot_longer.html (under heading # Multiple observations per row)

myvar %>% 
  pivot_longer(everything(),
               names_to = c(".value", "set"),
               names_pattern = "(.var_)(.)"
  )

This doesn't work though. I don't understand regular expressions much, but this is how I thought it works. .var_ would match anything then var_, which means that it would match with both var_ and something_var_ (like x and y in the example in the link). And then it would split and match against anything (which would be the numbers 1-5. So the var_ and something_var values would get the values corresponding to the 1 in row 1 etc.

I would be supergrateful if you could explain how you are thinking when using the regular expressions in this case. I have gone through a small regex course I found online but I haven't been able to solve it.

Solution

Put the underscore(_) between two groups:

library(tidyr)

myvar %>% 
  pivot_longer(everything(),
               names_to = c(".value", "set"),
               names_pattern = "(.+)_(.)")

# # A tibble: 9 x 3
#   set   var   something_var
#   <chr> <chr>         <int>
# 1 1     a                 1
# 2 2     d                 4
# 3 3     g                 7
# 4 1     b                 2
# 5 2     e                 5
# 6 3     h                 8
# 7 1     c                 3
# 8 2     f                 6
# 9 3     i                 9

or set the argument names_sep.

myvar %>% 
  pivot_longer(everything(),
               names_to = c(".value", "set"),
               names_sep = "_(?=\\d)")

Answered By - Darren Tsai

Answer Checked By - Timothy Miller (PHPFixing Admin)