时间戳

首页 > 解决方案 > 数据表，与不寻常的格式化表融为一体

r - 数据表，与不寻常的格式化表融为一体

问题描述

我有一个类似于下面的data.table：

data.table(`COLOUR` = c("red", "blue", "green"), 
           `COUNT(1:1)` = 5:7, 
           `COUNT(2:1)` = 1:3, 
           `COUNT(1:2)` = 1:3, 
           `COUNT(2:2)` = 6:8, 
           `RATE(1:1)` = 1/(1:3), 
           `RATE(2:1)` = 2/(2:4), 
           `RATE(1:2)` = 3/(4:6), 
           `RATE(2:2)` = 1,
           `INDICATOR(2)` = c("left", "lefter", "leftest")
           `INDICATOR(1)` = c("right", "righter", "more right"))

我需要melt它来提供类似于以下的长结果：

structure(list(COLOUR = c("red", "red", "red", "red", "blue", 
"blue", "blue", "blue", "green", "green", "green", "green"), 
    x = c(1, 1, 2, 2, 1, 1, 2, 2, 1, 1, 2, 2), y = c(1, 2, 1, 
    2, 1, 2, 1, 2, 1, 2, 1, 2), INDICATOR = c("right", "left", 
    "right", "left", "righter", "lefter", "righter", "lefter", 
    "more right", "leftest", "more right", "leftest"), COUNT = c(5, 
    2, 1, 6, 6, 2, 2, 7, 7, 3, 3, 8), RATE = c(1, 0.75, 1, 1, 
    0.5, 0.6667, 0.6, 1, 0.333, 0.5, 0.5, 1)), row.names = c(NA, 
-12L), class = c("data.table", "data.frame"), .internal.selfref = <pointer: 0x0000014d26c41ef0>)

您会注意到，原始日期集中的列在其列名中存储了两个变量：COUNT和RATE：x和y值，INDICATOR列将y值存储在其列名中。

我希望使用melt它，我怀疑它需要多个应用程序，但是我不太确定最好的方法是什么。

标签： rdata.tablemelt

解决方案

我们可以分两步完成

library(data.table)
nm1 <- names(dt1)[!grepl("^INDICATOR", names(dt1))]
dt2 <- melt(dt1[, ..nm1], id.var = c("COLOUR"))[, 
         c('variable', 'x', 'y') := tstrsplit(variable, "[():]")][]       

dt3 <- melt(dt1[, .SD, .SDcols = patterns("^(COLOUR|INDICATOR)")], 
    id.var = 'COLOUR', value.name = 'INDICATOR')[,
          y := sub(".*\\((\\d+)\\)", "\\1", variable)][]
dcast(dt2[dt3, on = .(COLOUR, y)], COLOUR + INDICATOR + x + y ~ 
         variable, value.var = 'value')[
   order(factor(COLOUR, levels = c('red', 'blue', 'green')), x, y)]
#   COLOUR  INDICATOR x y COUNT      RATE
# 1:    red      right 1 1     5 1.0000000
# 2:    red       left 1 2     1 0.7500000
# 3:    red      right 2 1     1 1.0000000
# 4:    red       left 2 2     6 1.0000000
# 5:   blue    righter 1 1     6 0.5000000
# 6:   blue     lefter 1 2     2 0.6000000
# 7:   blue    righter 2 1     2 0.6666667
# 8:   blue     lefter 2 2     7 1.0000000
# 9:  green more right 1 1     7 0.3333333
#10:  green    leftest 1 2     3 0.5000000
#11:  green more right 2 1     3 0.5000000
#12:  green    leftest 2 2     8 1.0000000

推荐阅读