在R中将宽格式转换为长格式的复杂重塑

Question

我有一个数据框，长这样：

stocks <- tibble(
  ID = 1:10,
  time1 = as.Date('2009-01-01'),
  time2 = as.Date('2009-01-02'),
  time3 = as.Date('2009-01-03'),
  X1 = rnorm(10, 0, 1),
  X2 = rnorm(10, 0, 1),
  X3 = rnorm(10, 0, 1),
  Y1 = rnorm(10, 0, 1),
  Y2 = rnorm(10, 0, 1),
  Y3 = rnorm(10, 0, 1),
  Z1 = rnorm(10, 0, 1),
  Z2 = rnorm(10, 0, 1),
  Z3 = rnorm(10, 0, 4)
)

我的目标是创建一个类似于这样的数据框：

goal <- tibble(
  ID =  rep(1, 12),
  Time = c(rep(as.Date('2009-01-01'), 9), rep(as.Date('2009-01-02'), 3)),
  Group = c("X1", "X2", "X3", "Y1", "Y2", "Y3", "Z1", "Z2", "Z3", "X1", "X2", "X3"),
  Numbers = c(-0.46805576,  0.8490237,  -1.1647371, -0.7519070, -1.0930102, -1.19820707,    0.302461964,
              -1.48164386, -10.0257282, -1.03852259,    -0.4597602, -0.8727067)  
)

我一直在 struggle with gather 和 melt，但问题似乎是我想要在不同的列中聚合时间，而不是其他数据。有人知道怎么做吗？

提前感谢。

- Rosanne

Answer 1

一个选择是使用 pivot_longer 将数据重塑为“长”格式，其中有一个列用于“时间”，“X”，“Y”，“Z”，然后在“X”，“Y”，“Z”列上执行第二个 pivot_longer。

library(dplyr)
library(tidyr)
stocks %>%
    pivot_longer(cols = -ID, names_to = c(".value", "Group"),
           names_sep= "(?<=[^0-9])(?=[0-9])") %>% 
    pivot_longer(cols = X:Z, values_to = "Numbers") %>%
    unite(Group, name, Group, sep="")
# A tibble: 90 x 4
#      ID Group time       Numbers
#   <int> <chr> <date>       <dbl>
# 1     1 X1    2009-01-01   1.05 
# 2     1 Y1    2009-01-01   0.525
# 3     1 Z1    2009-01-01  -0.590
# 4     1 X2    2009-01-02   0.195
# 5     1 Y2    2009-01-02  -0.858
# 6     1 Z2    2009-01-02  -1.03 
# 7     1 X3    2009-01-03  -0.417
# 8     1 Y3    2009-01-03  -0.746
# 9     1 Z3    2009-01-03   1.01 
#10     2 X1    2009-01-01  -0.971

- akrun