How to transform wide format data with multiple variables into long format data in one step in R-CodePudding

I have a dataframe with wide format named df.

And I want to tranform it into long format named df2.

I do not know how to change df to df2 with pivot_longer() function in one step.

Any help will be highly appreciated!

df = data.frame(
  x.a = 1:3,
  x.b = 4:6,
  y.a = 1:3,
  y.b = 4:6
)

df
#>   x.a x.b y.a y.b
#> 1   1   4   1   4
#> 2   2   5   2   5
#> 3   3   6   3   6

df2 = data.frame(
  x = 1:6,
  y = 1:6,
  type = c('a', 'a', 'a', 'b', 'b', 'b')
)

df2
#>   x y type
#> 1 1 1    a
#> 2 2 2    a
#> 3 3 3    a
#> 4 4 4    b
#> 5 5 5    b
#> 6 6 6    b

^{Created on 2022-10-31 by the reprex package (v2.0.1)}

CodePudding user response：

When you want to simultaneously put multiple variables in long format, like here, you should use a combination of names_to and names_sep (or names_pattern); with .value as one of the two elements of names_to. Here, you have a clear separator between the two elements of your column names that will be in long format (a dot).

df %>% 
  pivot_longer(everything(),
               names_to = c(".value", "type"),
               names_sep = "\\.")

# A tibble: 6 × 3
  type      x     y
  <chr> <int> <int>
1 a         1     1
2 b         4     4
3 a         2     2
4 b         5     5
5 a         3     3
6 b         6     6

But sometimes, it is not possible to do so, and you need to encapsulate groups in regex format with names_pattern, and with each group between parenthesis:

df %>% 
  pivot_longer(everything(),
               names_to = c(".value", "type"),
               names_pattern = "(x|y).(a|b)")

CodePudding user response：

Using melt

library(data.table)
 melt(setDT(df), measure.vars = measure(value.name, type, sep = "."))

-output

    type     x     y
   <char> <int> <int>
1:      a     1     1
2:      a     2     2
3:      a     3     3
4:      b     4     4
5:      b     5     5
6:      b     6     6