Better way to pivot longer?

[Wobniar]

Hi everybody, I was able to pivot longer an object by using pivot_longer and then pivot_wider. I want to know if it is possible to do with only pivot_longer command? Thanks.

# Data set
df = tibble(key=1:2, x_1=paste('x', 1:2, sep=''), x_2=paste('x', 3:4, sep=''),
              y_1=paste('y', 1:2, sep=''), y_2=paste('y',3:4, sep=''),
              z_1=paste('z', 1:2, sep=''), z_2=paste('z',3:4, sep=''))
df

# Desired output
df_long=tibble(key=rep(1:2,2), x=paste('x',1:4, sep=''), y=paste('y',1:4, sep=''), z=paste('z',1:4, sep=''))
df_long

# Long solution
pivot_longer(df,cols=2:7, names_to=c('var','rep'), names_pattern=("(.)_(.)"), values_to = 'val') %>%
        pivot_wider(names_from='var', values_from='val')

[mara]

Ooff! This one's tricky because you've basically got a variable value encoded in the column names, and I haven't come up with a better solution… Thought having a reprex showing the output might help, though

library(tidyverse)
df <- tibble(
  key = 1:2, x_1 = paste("x", 1:2, sep = ""), x_2 = paste("x", 3:4, sep = ""),
  y_1 = paste("y", 1:2, sep = ""), y_2 = paste("y", 3:4, sep = ""),
  z_1 = paste("z", 1:2, sep = ""), z_2 = paste("z", 3:4, sep = "")
)
df
#> # A tibble: 2 x 7
#>     key x_1   x_2   y_1   y_2   z_1   z_2  
#>   <int> <chr> <chr> <chr> <chr> <chr> <chr>
#> 1     1 x1    x3    y1    y3    z1    z3   
#> 2     2 x2    x4    y2    y4    z2    z4

# Desired output
df_long <- tibble(key = rep(1:2, 2), x = paste("x", 1:4, sep = ""), y = paste("y", 1:4, sep = ""), z = paste("z", 1:4, sep = ""))
df_long
#> # A tibble: 4 x 4
#>     key x     y     z    
#>   <int> <chr> <chr> <chr>
#> 1     1 x1    y1    z1   
#> 2     2 x2    y2    z2   
#> 3     1 x3    y3    z3   
#> 4     2 x4    y4    z4

# Long solution
pivot_longer(df, cols = -key, names_to = c("var", "rep"), names_pattern = ("(.)_(.)"), values_to = "val") %>%
  pivot_wider(names_from = "var", values_from = "val")
#> # A tibble: 4 x 5
#>     key rep   x     y     z    
#>   <int> <chr> <chr> <chr> <chr>
#> 1     1 1     x1    y1    z1   
#> 2     1 2     x3    y3    z3   
#> 3     2 1     x2    y2    z2   
#> 4     2 2     x4    y4    z4

^{Created on 2020-06-03 by the reprex package (v0.3.0.9001)}

[Wobniar]

@mara, thanks for your reply. This is my first post and I will remember using reprex the next time. Best.

[siddharthprabhu]

It can be done by using the .value special name as the first argument in names_to.

library(tidyverse)

df <- tibble(key = 1:2, 
             x_1 = paste("x", 1:2, sep = ""), 
             x_2 = paste("x", 3:4, sep = ""), 
             y_1 = paste("y", 1:2, sep = ""), 
             y_2 = paste("y", 3:4, sep = ""), 
             z_1 = paste("z", 1:2, sep = ""), 
             z_2 = paste("z", 3:4, sep = ""))

pivot_longer(df, cols = -key, names_to = c(".value", "rep"), names_pattern = ("(.)_(.)"))
#> # A tibble: 4 x 5
#>     key rep   x     y     z    
#>   <int> <chr> <chr> <chr> <chr>
#> 1     1 1     x1    y1    z1   
#> 2     1 2     x3    y3    z3   
#> 3     2 1     x2    y2    z2   
#> 4     2 2     x4    y4    z4

^{Created on 2020-06-04 by the reprex package (v0.3.0)}

[Wobniar]

@siddharthprabhu Wow, this is a great solution. Thanks, I learn something new today.