r-收集多个键和值列

Question

我有一个数据帧，每行都表示一个唯一的id。

ID <- 1:12
Date1 <- seq(as.Date("2000/1/1"), length.out = 12, by = "months")
Date2 <- seq(as.Date("2001/1/1"), length.out = 12, by = "months")
Date3 <- seq(as.Date("2002/1/1"), length.out = 12, by = "months")
Fcast1 <- rnorm(12)
Fcast2 <- rnorm(12)
Fcast3 <- rnorm(12)
df <- data.frame(ID, Date1, Fcast1, Date2, Fcast2, Date3, Fcast3)

我想在Date和Fcast2列中收集Date1到Date3和Fcast1到Fcast3列，并重复ID 3次。基本上创建数据的长视图或绑定每对Date和Fcast。所需输出形状：

ID <- rep(ID, 3) 
Date = c(Date1, Date2, Date3)
Fcast = c(Fcast1, Fcast2, Fcast3)
df <- data.frame(ID, Date, Fcast)

Answer 1

使用tidyr::pivot_longer-

tidyr::pivot_longer(df, 
cols = -ID, 
names_to = '.value', 
names_pattern = '(.*)\d+')
# A tibble: 36 x 3
#      ID Date        Fcast
#   <int> <date>      <dbl>
# 1     1 2000-01-01  0.452
# 2     1 2001-01-01  0.242
# 3     1 2002-01-01 -0.540
# 4     2 2000-02-01  1.54 
# 5     2 2001-02-01  0.178
# 6     2 2002-02-01  0.883
# 7     3 2000-03-01 -0.987
# 8     3 2001-03-01  1.40 
# 9     3 2002-03-01  0.675
#10     4 2000-04-01 -0.632
# … with 26 more rows

Answer 2

您可以执行以下操作：

library(data.table)
setDT(df)
melt(df, measure.vars=patterns("^Date", "^Fcast"), value.name=c("Date", "Fcast"))[, 
variable := NULL][]