r data.table中所有行的输出的条件求和 [英] Conditional sum with output for all rows in r data.table

问题描述

我有一个编码问题，我认为应该很容易。我创建了一个简化的数据集：

I have a coding issue what I think should be very easy. I have created a simplified dataset:

DT <- data.table(Bank=rep(c("a","b","c"),4),
                 Type=rep(c("Ass","Liab"),6),
                 Amount=c(100,200,300,400,200,300,400,500,200,100,300,100))
# Bank Type Amount SumLiab
# 1:    a  Ass    100      NA
# 2:    b Liab    200     700
# 3:    c  Ass    300      NA
# 4:    a Liab    400     500
# 5:    b  Ass    200      NA
# 6:    c Liab    300     400
# 7:    a  Ass    400      NA
# 8:    b Liab    500     700
# 9:    c  Ass    200      NA
# 10:    a Liab    100     500
# 11:    b  Ass    300      NA
# 12:    c Liab    100     400

我想创建一个变量，它是每个银行在Type =Liab时金额的总和。所以这没有问题：

I want to create a variable that is the sum of amount when Type = "Liab" per bank. So this is no problem:

DT[Type=='Liab',SumLiab:=sum(Amount),by=Bank]
# Bank Type Amount SumLiab
# 1:    a  Ass    100      NA
# 2:    b Liab    200     700
# 3:    c  Ass    300      NA
# 4:    a Liab    400     500
# 5:    b  Ass    200      NA
# 6:    c Liab    300     400
# 7:    a  Ass    400      NA
# 8:    b Liab    500     700
# 9:    c  Ass    200      NA
# 10:    a Liab    100     500
# 11:    b  Ass    300      NA
# 12:    c Liab    100     400

但是我想要所有行的值，即使Type =='Ass'。我明白，由于 DT [Type =='Liab'，..] 限制，我现在得到NA。有没有一种聪明的编码方式来获得所有行的SumLiab值？ （所以row1，目前为SumLiab的NA为500）

But I want this value for all rows, even when Type =='Ass'. I understand that I now get NA due to the DT[Type=='Liab',..] restriction. Is there a clever way of coding to get the value SumLiab for all rows? (So row1 that currently is NA for SumLiab gets the value 500)

谢谢！
Tim

Thanks! Tim

推荐答案

当我们使用 Type =='Liab'在'i'中，它只将值插入由i索引的行。我们可以基于 Type =='Liab'在'j'中的'Amount'子集并分配（：= ）它是新的变量。

When we are using Type=='Liab' in 'i', it is inserting the values only to that rows indexed by 'i'. We can subset the 'Amount' based on Type=='Liab' in 'j' and assign (:=) it to be new variable.

 DT[, SumLiab:= sum(Amount[Type=='Liab']), by =Bank]
 DT
 #   Bank Type Amount SumLiab
 #1:    a  Ass    100     500
 #2:    b Liab    200     700
 #3:    c  Ass    300     400
 #4:    a Liab    400     500
 #5:    b  Ass    200     700
 #6:    c Liab    300     400
 #7:    a  Ass    400     500
 #8:    b Liab    500     700
 #9:    c  Ass    200     400
 #10:   a Liab    100     500
 #11:   b  Ass    300     700
 #12:   c Liab    100     400

这篇关于r data.table中所有行的输出的条件求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！