Search code examples
rmergereshapemeltdata-transform

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

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:

image.png

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!

Solution

  • 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