R 按组将列的条件平均值分配给data.table上的所有行

发布于10月27日

假设我有以下数据

DT <- data.table(id = c(1,1,1,2,2,3,3),
                bank=c("a","b","b","b","b","a","b"),
                rate =c(2,3,4,1,0.5,7,3.2),
                balance=c(10,5,11,3,20,0.5,2),
                new=c(1,0,0,1,0,0,1),
                before=c(1,0,1,1,0,1,0))

    id bank rate balance new before
1:  1    a  2.0    10.0   1      1
2:  1    b  3.0     5.0   0      0
3:  1    b  4.0    11.0   0      1
4:  2    b  1.0     3.0   1      1
5:  2    b  0.5    20.0   0      0
6:  3    a  7.0     0.5   0      1
7:  3    b  3.2     2.0   1      0

我想向所有行添加一个列，其中包含各银行的平均新贷款率(new==1).我能做的最多的就是

aft <- DT[new==1,.(mrate=mean(rate)),by=bank]
aft <-  merge(DT,aft, by="bank", all.x = T)

   bank id rate balance new before mrate
1:    a  1  2.0    10.0   1      1   2.0
2:    a  3  7.0     0.5   0      1   2.0
3:    b  1  3.0     5.0   0      0   2.1
4:    b  1  4.0    11.0   0      1   2.1
5:    b  2  1.0     3.0   1      1   2.1
6:    b  2  0.5    20.0   0      0   2.1
7:    b  3  3.2     2.0   1      0   2.1

有没有办法避免合并步骤.如有任何帮助，我们不胜感激.谢谢.

推荐答案

DT[, mrate := mean(rate[new == 1]), by = .(bank)]
#       id   bank  rate balance   new before mrate
#    <num> <char> <num>   <num> <num>  <num> <num>
# 1:     1      a   2.0    10.0     1      1   2.0
# 2:     1      b   3.0     5.0     0      0   2.1
# 3:     1      b   4.0    11.0     0      1   2.1
# 4:     2      b   1.0     3.0     1      1   2.1
# 5:     2      b   0.5    20.0     0      0   2.1
# 6:     3      a   7.0     0.5     0      1   2.0
# 7:     3      b   3.2     2.0     1      0   2.1

如果银行没有new == 1行的变化，那么你将得到NaN:

DT[1,new:=0]
DT[, mrate := mean(rate[new == 1]), by = .(bank)]
#       id   bank  rate balance   new before mrate
#    <num> <char> <num>   <num> <num>  <num> <num>
# 1:     1      a   2.0    10.0     0      1   NaN
# 2:     1      b   3.0     5.0     0      0   2.1
# 3:     1      b   4.0    11.0     0      1   2.1
# 4:     2      b   1.0     3.0     1      1   2.1
# 5:     2      b   0.5    20.0     0      0   2.1
# 6:     3      a   7.0     0.5     0      1   NaN
# 7:     3      b   3.2     2.0     1      0   2.1

如果这是一个问题，您可以使用平均值周围的fcoalesce来指定一个缺省值；我在这里使用-1只是为了清楚地说明我正在谈论的内容:

DT[, mrate := fcoalesce(mean(rate[new == 1]), -1), by = .(bank)]
#       id   bank  rate balance   new before mrate
#    <num> <char> <num>   <num> <num>  <num> <num>
# 1:     1      a   2.0    10.0     0      1  -1.0
# 2:     1      b   3.0     5.0     0      0   2.1
# 3:     1      b   4.0    11.0     0      1   2.1
# 4:     2      b   1.0     3.0     1      1   2.1
# 5:     2      b   0.5    20.0     0      0   2.1
# 6:     3      a   7.0     0.5     0      1  -1.0
# 7:     3      b   3.2     2.0     1      0   2.1