重新格式化数据框，使用月份展开并按 R 中的日历顺序排序

Question

这个问题在这里已经有了答案:





Convert data from long format to wide format with multiple measure columns

(5 个回答)



Transpose / reshape dataframe without "timevar" from long to wide format

(8 个回答)


3年前关闭。




我有一个下面给出的 data.frame。我正在尝试将其从长格式移动到宽格式。使用作为日期的传播列。使用来自 tidyr 的扩展函数包存在两个问题:

数据填NA

月份按字母顺序排列

那我怎么去

30-Apr-2015 632.95
28-May-2015 532.95
25-Jun-2015 232.95

到

30-Apr-2015 28-May-2015 25-Jun-2015
632.95      532.95      232.95

相反，我最终在

30-Apr-2015 25-Jun-2015 28-May-2015 
632.95      NA      232.95
NA          232.95  NA
NA          NA      532.95

实际日期无关紧要，但它们的相对顺序很重要，即最近的月份数据应按顺序排在第一列，然后是其他两个月的数据。这是必要的，因为我正在使用 rbind结果

我试过的代码

data = tidyr::spread(data, key = EXPIRY_DT, value = CHG_IN_OI)
colnames(data)[3:5] = c('Month1', 'Month2', 'Month3')

data.frame 如下所示:

data = structure(list(SYMBOL = c("A", "A", "A", "B", "B", "B", "C", 
"C", "C", "D", "D", "D"), EXPIRY_DT = c("30-Apr-2015", "28-May-2015", 
"25-Jun-2015", "30-Apr-2015", "28-May-2015", "25-Jun-2015", "30-Apr-2015", 
"28-May-2015", "25-Jun-2015", "30-Apr-2015", "28-May-2015", "25-Jun-2015"
), OPEN = c(1750, 1789, 0, 1627.5, 1653.3, 0, 632.95, 644.1, 
0, 317.8, 319.5, 0), HIGH = c(1788.05, 1795, 0, 1656.5, 1653.3, 
0, 646.4, 650.5, 0, 324.6, 326.65, 0), LOW = c(1746, 1760, 0, 
1627.5, 1645.45, 0, 629.65, 635, 0, 315.85, 318.4, 0), CLOSE = c(1782.3, 
1791.85, 1695.1, 1642.95, 1646.75, 1613.9, 640.85, 644.35, 614.6, 
320.55, 322.35, 310.85), SETTLE_PR = c(1782.3, 1791.85, 1804.8, 
1642.95, 1653.85, 1664.35, 640.85, 644.35, 649.1, 320.55, 322.35, 
325.35), CONTRACTS = c(1469L, 78L, 0L, 2638L, 14L, 0L, 4964L, 
181L, 0L, 3416L, 82L, 0L), VALUE = c(6496.96, 347.91, 0, 10830.05, 
57.68, 0, 15869.41, 583.38, 0, 10969.31, 264.93, 0), OPEN_INT = c(1353750L, 
8500L, 0L, 1377250L, 17000L, 0L, 6264000L, 98000L, 0L, 8228000L, 
216000L, 0L), CHG_IN_OI = c(15250L, 1250L, 0L, -21000L, 1500L, 
0L, 73500L, 6000L, 0L, -192000L, 13000L, 0L), TIMESTAMP = c("10-APR-2015", 
"10-APR-2015", "10-APR-2015", "10-APR-2015", "10-APR-2015", "10-APR-2015", 
"10-APR-2015", "10-APR-2015", "10-APR-2015", "10-APR-2015", "10-APR-2015", 
"10-APR-2015")), .Names = c("SYMBOL", "EXPIRY_DT", "OPEN", "HIGH", 
"LOW", "CLOSE", "SETTLE_PR", "CONTRACTS", "VALUE", "OPEN_INT", 
"CHG_IN_OI", "TIMESTAMP"), row.names = 40:51, class = "data.frame")

谢谢阅读。

Edit:

在@akrun 发表评论后添加了预期的输出。因为每个日期的值不同，即需要一个接一个地放置每个月的数据，列名附加字符串“Month1/2/3”而不是实际日期。希望有帮助。

output = structure(list(SYMBOL = c("A", "B", "C", "D"), TIMESTAMP = c("10-Apr-15", 
"10-Apr-15", "10-Apr-15", "10-Apr-15"), OPEN.Month1 = c(1750, 
1627.5, 632.95, 317.8), HIGH.Month1 = c(1788.05, 1656.5, 646.4, 
324.6), LOW.Month1 = c(1746, 1627.5, 629.65, 315.85), CLOSE.Month1 = c(1782.3, 
1642.95, 640.85, 320.55), SETTLE_PR.Month1 = c(1782.3, 1642.95, 
640.85, 320.55), CONTRACTS.Month1 = c(1469L, 2638L, 4964L, 3416L
), VALUE.Month1 = c(6496.96, 10830.05, 15869.41, 10969.31), OPEN_INT.Month1 = c(1353750L, 
1377250L, 6264000L, 8228000L), CHG_IN_OI.Month1 = c(15250L, -21000L, 
73500L, -192000L), OPEN.Month2 = c(1789, 1653.3, 644.1, 319.5
), HIGH.Month2 = c(1795, 1653.3, 650.5, 326.65), LOW.Month2 = c(1760, 
1645.45, 635, 318.4), CLOSE.Month2 = c(1791.85, 1646.75, 644.35, 
322.35), SETTLE_PR.Month2 = c(1791.85, 1653.85, 644.35, 322.35
), CONTRACTS.Month2 = c(78L, 14L, 181L, 82L), VALUE.Month2 = c(347.91, 
57.68, 583.38, 264.93), OPEN_INT.Month2 = c(8500L, 17000L, 98000L, 
216000L), CHG_IN_OI.Month2 = c(1250L, 1500L, 6000L, 13000L), 
    OPEN.Month3 = c(0L, 0L, 0L, 0L), HIGH.Month3 = c(0L, 0L, 
    0L, 0L), LOW.Month3 = c(0L, 0L, 0L, 0L), CLOSE.Month3 = c(1695.1, 
    1613.9, 614.6, 310.85), SETTLE_PR.Month3 = c(1804.8, 1664.35, 
    649.1, 325.35), CONTRACTS.Month3 = c(0L, 0L, 0L, 0L), VALUE.Month3 = c(0L, 
    0L, 0L, 0L), OPEN_INT.Month3 = c(0L, 0L, 0L, 0L), CHG_IN_OI.Month3 = c(0L, 
    0L, 0L, 0L)), .Names = c("SYMBOL", "TIMESTAMP", "OPEN.Month1", 
"HIGH.Month1", "LOW.Month1", "CLOSE.Month1", "SETTLE_PR.Month1", 
"CONTRACTS.Month1", "VALUE.Month1", "OPEN_INT.Month1", "CHG_IN_OI.Month1", 
"OPEN.Month2", "HIGH.Month2", "LOW.Month2", "CLOSE.Month2", "SETTLE_PR.Month2", 
"CONTRACTS.Month2", "VALUE.Month2", "OPEN_INT.Month2", "CHG_IN_OI.Month2", 
"OPEN.Month3", "HIGH.Month3", "LOW.Month3", "CLOSE.Month3", "SETTLE_PR.Month3", 
"CONTRACTS.Month3", "VALUE.Month3", "OPEN_INT.Month3", "CHG_IN_OI.Month3"
), class = "data.frame", row.names = c(NA, -4L))

Answer 1

我们可以使用 devel data.table 的版本IE。 'v1.9.5' 可以采用多个“value.vars”。安装devel版本的说明是 here .

将“data.frame”更改为“data.table”( setDT(data) )。通过将“月份”与每个“符号”的行号粘贴在一起，创建一个“月份”列。然后，我们可以使用 dcast ，指定 value.var作为列'3:11'。

library(data.table)
res <- dcast(setDT(data)[, Month:=paste0('Month', 1:.N), by=SYMBOL],
                 SYMBOL+TIMESTAMP~Month, value.var=names(data)[3:11])

如果我们需要将列名更改为“输出”中的特定格式，请使用 setnames .我按照预期的结果(“输出”)重新排列了列的顺序，并将 data.table 更改为 data.frame( setDF )

setnames(res, sub('([^_]+)_(.*)', '\\2.\\1', colnames(res)))
res1 <- setDF(res[,names(output), with=FALSE])
res1
#  SYMBOL   TIMESTAMP OPEN.Month1 HIGH.Month1 LOW.Month1 CLOSE.Month1
#1      A 10-APR-2015     1750.00     1788.05    1746.00      1782.30
#2      B 10-APR-2015     1627.50     1656.50    1627.50      1642.95
#3      C 10-APR-2015      632.95      646.40     629.65       640.85
#4      D 10-APR-2015      317.80      324.60     315.85       320.55
#  SETTLE_PR.Month1 CONTRACTS.Month1 VALUE.Month1 OPEN_INT.Month1
#1          1782.30             1469      6496.96         1353750
#2          1642.95             2638     10830.05         1377250
#3           640.85             4964     15869.41         6264000
#4           320.55             3416     10969.31         8228000
#  CHG_IN_OI.Month1 OPEN.Month2 HIGH.Month2 LOW.Month2 CLOSE.Month2
#1            15250      1789.0     1795.00    1760.00      1791.85
#2           -21000      1653.3     1653.30    1645.45      1646.75
#3            73500       644.1      650.50     635.00       644.35
#4          -192000       319.5      326.65     318.40       322.35
#  SETTLE_PR.Month2 CONTRACTS.Month2 VALUE.Month2 OPEN_INT.Month2
#1          1791.85               78       347.91            8500
#2          1653.85               14        57.68           17000
#3           644.35              181       583.38           98000
#4           322.35               82       264.93          216000
#  CHG_IN_OI.Month2 OPEN.Month3 HIGH.Month3 LOW.Month3 CLOSE.Month3  
#1             1250           0           0          0      1695.10
#2             1500           0           0          0      1613.90
#3             6000           0           0          0       614.60
#4            13000           0           0          0       310.85
#  SETTLE_PR.Month3 CONTRACTS.Month3 VALUE.Month3 OPEN_INT.Month3
#1          1804.80                0            0               0
#2          1664.35                0            0               0
#3           649.10                0            0               0
#4           325.35                0            0               0
#  CHG_IN_OI.Month3
#1                0
#2                0
#3                0
#4                0

TIMESTAMP “输出”中的列采用不同的格式。更改了“res1”中的格式，它与预期的输出相同。

res1$TIMESTAMP <- format(as.Date(res1$TIMESTAMP, '%d-%b-%Y'), '%d-%b-%y')
all.equal(output, res1)
#[1] TRUE

或者我们可以使用 reshape来自 base R ，它确实需要多个值列。就像我们之前创建的序列一样，这里我们可以使用 ave创建“MONTH”列并将其用作 timevar内 reshape .

 data$MONTH <- with(data, paste0('MONTH', ave(seq_along(SYMBOL), 
                    SYMBOL, FUN=seq_along)))
 res2 <- reshape(data[-2], idvar=c('SYMBOL', 'TIMESTAMP'), 
                          timevar='MONTH', direction='wide')