时间戳

首页 > 解决方案 > 聚合每个观察是否可以属于多个组

r - 聚合每个观察是否可以属于多个组

问题描述

我想按组聚合日期。然而，每个观察可以属于几个组（例如观察 1 属于组 A 和 B）。我找不到一个很好的方法来实现这一点data.table。TRUE目前，我为每个可能的组创建了一个逻辑变量，如果观察属于该组，则该变量取值。我正在寻找比下面介绍的更好的方法来做到这一点。我也想知道如何使用tidyverse. 

library(data.table)
# Data
set.seed(1)
TF <- c(TRUE, FALSE)
time <- rep(1:4, each = 5)
df <- data.table(time = time, x = rnorm(20), groupA = sample(TF, size = 20, replace = TRUE),
                                             groupB = sample(TF, size = 20, replace = TRUE),
                                             groupC = sample(TF, size = 20, replace = TRUE))

# This should be nicer and less repetitive
df[groupA == TRUE, .(A = sum(x)), by = time][
  df[groupB == TRUE, .(B = sum(x)), by = time], on = "time"][
    df[groupC == TRUE, .(C = sum(x)), by = time], on = "time"]

# desired output
time          A          B         C
1:    1         NA  0.9432955 0.1331984
2:    2  1.2257538  0.2427420 0.1882493
3:    3 -0.1992284 -0.1992284 1.9016244
4:    4  0.5327774  0.9438362 0.9276459

标签： rdplyrdata.tabletidyverse

解决方案

这是一个解决方案data.table：

df[, lapply(.SD[, .(groupA, groupB, groupC)]*x, sum), time]
# > df[, lapply(.SD[, .(groupA, groupB, groupC)]*x, sum), time]
#    time     groupA     groupB    groupC
# 1:    1  0.0000000  0.9432955 0.1331984
# 2:    2  1.2257538  0.2427420 0.1882493
# 3:    3 -0.1992284 -0.1992284 1.9016244
# 4:    4  0.5327774  0.9438362 0.9276459

或者（感谢@chinsoon12 的评论）以编程方式：

df[, lapply(.SD*x, sum), by=.(time), .SDcols=paste0("group", c("A","B","C"))]

如果您想要长格式的结果，您可以执行以下操作：

df[, colSums(.SD*x), by=.(time), .SDcols=paste0("group", c("A","B","C"))]
### with indicator for the group:
df[, .(colSums(.SD*x), c("A","B","C")), by=.(time), .SDcols=paste0("group", c("A","B","C"))]

推荐阅读