分组变量是否可以.SDcols
变化by
?我遇到以下情况,我想.SDcols
每年更改为不同的列。的值.SDcols
位于一个data.table中,而我试图.SD
使用这些值将函数应用于另一个表中的。
很可能我错过了明显的方法并做错了,但这就是我正在尝试的方法,
## Contains the .SDcols applicable to each year
dat1 <- data.table(
year = 1:4,
vals = lapply(1:4, function(i) letters[1:i])
)
## Make the sample data (with NAs)
set.seed(1775)
dat2 <- data.table( year = sample(1:4, 10, TRUE) )
dat2[, letters[1:4] := replicate(4, sample(c(NA, 1:5), 10, TRUE), simplify=FALSE)]
## Goal: Sum up the columns in the corresponding .SDcols for each year
## Attempt, doesn't work -- I think b/c .SDcols must be fixed?
dat2[, SUM := rowSums(.SD, na.rm=TRUE), by=year,
.SDcols=unlist(dat1[year == .BY[[1]], vals])]
## Desired result, by simply iterating through each possible year
for (i in 1:4) {
dat2[year==i, SUM := rowSums(.SD, na.rm=TRUE),
.SDcols=unlist(dat1[year == i, vals])]
}
dat2[]
# year a b c d SUM
# 1: 1 3 1 5 1 3
# 2: 2 1 3 3 1 4
# 3: 1 5 4 3 NA 5
# 4: 4 1 NA 4 5 10
# 5: 2 2 2 2 NA 4
# 6: 2 NA 3 3 NA 3
# 7: 4 2 3 2 NA 7
# 8: 1 2 NA 5 4 2
# 9: 2 3 3 5 1 6
# 10: 3 NA 4 2 NA 6
在我看来,你只是在寻找一个简单连接,同时在每个值更新值(参考)dat1
(by = .EACHI)
。无论哪种方式,rowSums
在这两个这种解决方案,你的尝试(因为矩阵变换)的瓶颈。如果我就是你,我会将所有NA
s都转换为零,然后运行Reduce(`+`,...)
(不确定是否要更改原始数据中的值)
dat2[dat1,
SUM := rowSums(.SD[, unlist(i.vals), with = FALSE], na.rm = TRUE),
on = "year",
by = .EACHI]
dat2
# year a b c d SUM
# 1: 1 3 1 5 1 3
# 2: 2 1 3 3 1 4
# 3: 1 5 4 3 NA 5
# 4: 4 1 NA 4 5 10
# 5: 2 2 2 2 NA 4
# 6: 2 NA 3 3 NA 3
# 7: 4 2 3 2 NA 7
# 8: 1 2 NA 5 4 2
# 9: 2 3 3 5 1 6
# 10: 3 NA 4 2 NA 6
如前所述,虽然如果我是你,我会将NA
s转换为零,然后Reduce
改用
for(j in 2:ncol(dat2)) set(dat2, i = which(is.na(dat2[[j]])), j = j, value = 0L)
dat2[dat1,
SUM := Reduce(`+`, .SD[, unlist(i.vals), with = FALSE]),
on = "year",
by = .EACHI]
dat2
# year a b c d SUM
# 1: 1 3 1 5 1 3
# 2: 2 1 3 3 1 4
# 3: 1 5 4 3 0 5
# 4: 4 1 0 4 5 10
# 5: 2 2 2 2 0 4
# 6: 2 0 3 3 0 3
# 7: 4 2 3 2 0 7
# 8: 1 2 0 5 4 2
# 9: 2 3 3 5 1 6
# 10: 3 0 4 2 0 6
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句