R: Melt dataframe by 1 column and stack the other columns that have the same name patterns-CodePudding

I have this simplified data situation:

df <- data.frame(Year = 1:3,
                 A.x = 4:6,
                 A.y = 7:9,
                 A.z = 10:12,
                 B.x = 4:6,
                 B.y = 7:9,
                 B.z = 10:12,
                 C.x = 4:6,
                 C.y = 7:9,
                 C.z = 10:12)

My goal is this:

df_goal <- data.frame(Year = rep(1:3, each = 3),
                 x = rep(4:6, each = 3),
                 y = rep(7:9, each = 3),
                 z = rep(10:12, each = 3))

Please note that these values are arbitrary, only the column names, pattern, and the order of the target Year matter.

I tried to create the target dataframe "manually", e.g.: column i contains values from positions x to z of the original data set. But it looks really clumsy since the data I'm dealing with is huge. Are there better ways to reshape the data more systematically?

Thank you so much!

CodePudding user response：

We may use pivot_longer

library(dplyr)
library(tidyr)
 df %>% 
   pivot_longer(cols = -Year, names_to = ".value", 
      names_pattern = "^[^.] \\.(.*)")

# A tibble: 9 × 4
   Year     x     y     z
  <int> <int> <int> <int>
1     1     4     7    10
2     1     4     7    10
3     1     4     7    10
4     2     5     8    11
5     2     5     8    11
6     2     5     8    11
7     3     6     9    12
8     3     6     9    12
9     3     6     9    12