r - 如何在 R data.table 中按行使用 ifelse？

Question

我想在 R 中创建一个新列data.table这是基于 ifelse()不同列的比较。但是，我想要 ifelse要逐行应用的语句。我试过使用组 by data.table 的功能，但它似乎适用于 test ifelse 的状况按行但评估 yes列中所有值的条件，而不是使用 by条件以行方式进行。下面是一个例子和我尝试过的一些解决方案。

我有一个 R data.table像这样:

> set.seed(45)
> DT <- data.table(date = c(rep("2018-01-01", 3), rep("2018-01-02", 3), rep("2018-01-03", 3)), 
+                  id = rep(letters[1:3], 3), 
+                  v1 = sample(x = -20:20, size = 9), 
+                  v2 = sample(x = -20:20, size = 9))
> str(DT)
Classes ‘data.table’ and 'data.frame':  9 obs. of  4 variables:
 $ date: chr  "2018-01-01" "2018-01-01" "2018-01-01" "2018-01-02" ...
 $ id  : chr  "a" "b" "c" "a" ...
 $ v1  : int  5 -8 -11 -6 -7 -10 -13 -2 -14
 $ v2  : int  -20 -6 14 -9 -3 -5 19 12 -16
 - attr(*, ".internal.selfref")=<externalptr> 
> DT
         date id  v1  v2
1: 2018-01-01  a   5 -20
2: 2018-01-01  b  -8  -6
3: 2018-01-01  c -11  14
4: 2018-01-02  a  -6  -9
5: 2018-01-02  b  -7  -3
6: 2018-01-02  c -10  -5
7: 2018-01-03  a -13  19
8: 2018-01-03  b  -2  12
9: 2018-01-03  c -14 -16

我想要以下输出:

> DT_out
         date id  v1  v2  c
1: 2018-01-01  a   5 -20  0
2: 2018-01-01  b  -8  -6  0
3: 2018-01-01  c -11  14 11
4: 2018-01-02  a  -6  -9  0
5: 2018-01-02  b  -7  -3  0
6: 2018-01-02  c -10  -5  0
7: 2018-01-03  a -13  19 13
8: 2018-01-03  b  -2  12  2
9: 2018-01-03  c -14 -16  0 

我尝试过的解决方案:

尝试 #1) 没有错误，但会评估 min跨越 v1 中的所有值和 v2 .这种行为是意料之中的；不过，它评估 test 对我来说很奇怪。即使没有 key 也可以按行进行条件处理设置或 by说:

> DT[, c := ifelse(v1 < 0 & v2 > 0, min(-v1, v2), 0)]
> DT
         date id  v1  v2   c
1: 2018-01-01  a   5 -20   0
2: 2018-01-01  b  -8  -6   0
3: 2018-01-01  c -11  14 -20
4: 2018-01-02  a  -6  -9   0
5: 2018-01-02  b  -7  -3   0
6: 2018-01-02  c -10  -5   0
7: 2018-01-03  a -13  19 -20
8: 2018-01-03  b  -2  12 -20
9: 2018-01-03  c -14 -16   0

尝试 #2) 当我设置 key并使用 by条件，没有任何变化，但我收到一条错误消息。

> setkey(DT, date, id)
> DT[, c := ifelse(v1 < 0 & v2 > 0, min(-v1, v2), 0), by = list(date, id)]
Error in `[.data.table`(DT, , `:=`(c, ifelse(v1 < 0 & v2 > 0, min(-v1,  : 
  Type of RHS ('integer') must match LHS ('double'). To check and coerce would impact performance too much for the fastest cases. Either change the type of the target column, or coerce the RHS of := yourself (e.g. by using 1L instead of 1)
> DT
         date id  v1  v2   c
1: 2018-01-01  a   5 -20   0
2: 2018-01-01  b  -8  -6   0
3: 2018-01-01  c -11  14 -20
4: 2018-01-02  a  -6  -9   0
5: 2018-01-02  b  -7  -3   0
6: 2018-01-02  c -10  -5   0
7: 2018-01-03  a -13  19 -20
8: 2018-01-03  b  -2  12 -20
9: 2018-01-03  c -14 -16   0

自组合 date和 id每一行都是唯一的，我更难理解为什么不对每个 group 进行评估。 ，在这种情况下，即每一行。

也许我需要使用 .SDcols = .(date, id)和 .SD在 ifelse ，但我不知道如何使用 .SD在 ifelse .

Answer 1

您需要使用 pmin而不是 min :

DT[, c := ifelse(v1 < 0 & v2 > 0, pmin(-v1, v2), 0)]

> DT
         date id  v1  v2  c
1: 2018-01-01  a   5 -20  0
2: 2018-01-01  b  -8  -6  0
3: 2018-01-01  c -11  14 11
4: 2018-01-02  a  -6  -9  0
5: 2018-01-02  b  -7  -3  0
6: 2018-01-02  c -10  -5  0
7: 2018-01-03  a -13  19 13
8: 2018-01-03  b  -2  12  2
9: 2018-01-03  c -14 -16  0

# see also:
?pmin

pmax*() and pmin*() take one or more vectors as arguments, recycle them to common length and return a single vector giving the ‘parallel’ maxima (or minima) of the argument vectors.

[稍后补充]

如果您首先更改列类型，您的原始代码也能正常工作:

  DT[, v1:= as.numeric(v1)]   # was integer, converting to 'double'
  DT[, v2:= as.numeric(v2)]   # ---,,---
  DT[, c := ifelse(v1 < 0 & v2 > 0, min(-v1, v2), 0), by = list(date, id)]

据我了解，data.table 的理念是不让 R “隐式”更改列类型，而是该类型将保留直到显式更改。

手册上说:

Unlike <- for data.frame, the (potentially large) LHS is not coerced to match the type of the (often small) RHS. Instead the RHS is coerced to match the type of the LHS, if necessary. Where this involves double precision values being coerced to an integer column, a warning is given (whether or not fractional data is truncated). The motivation for this is efficiency. It is best to get the column types correct up front and stick to them. Changing a column type is possible but deliberately harder: provide a whole column as the RHS. This RHS is then plonked into that column slot and we call this plonk syntax, or replace column syntax if you prefer. By needing to construct a full length vector of a new type, you as the user are more aware of what is happening, and it’s clearer to readers of your code that you really do intend to change the column type.

到现在为止还挺好。但当然，原始错误信息令人困惑。

 # To check and coerce would impact performance too much for the fastest cases. 

“对于最快的情况？”。这一定是最快的情况之一，因为数据集很小，我敢打赌，如果 data.table，没有人会注意到这种情况下的性能影响。将允许隐式类型转换。所以这个错误消息的主要动机似乎是包作者想要强制执行他认为是好的做法。

这也将起作用(没有类型转换):

 DT[, c := ifelse(v1 < 0 & v2 > 0, as.numeric(min(-v1, v2)), 0), by = list(date, id)]  # 1

或者:

 DT[, c := ifelse(v1 < 0 & v2 > 0, min(-v1, v2), 0L), by = list(date, id)] # 2

但是你不能运行最后两行——#1 和#2——一个接一个， c必须先删除列。 DT$c在第一种情况下将是数字，在第二种情况下将是整数。

一些额外的实验

DT[, c:= NULL] 
DT[, c := ifelse(v1 < 0, v1, 0), by = list(date, id)] 
# error but DT$c col created with first element as NA
# the condition was FALSE for the first element, so numeric 0 became the first element of c
# ... but the next element would be integer, hence the error
DT$c # [1]  0 NA NA NA NA NA NA NA NA
DT[, c:= NULL] 
DT[, c := ifelse(v1 > 0, v1, 0), by = list(date, id)]
# error; DT$c column is integer, with 5 as first element and the rest as NA 
DT$c # [1]  5 NA NA NA NA NA NA NA NA
DT[, c:= NULL] 
DT[, c := ifelse(v1 < 0, as.numeric(v1), 0), by = list(date, id)] 
# works without error but results in numeric DT$c
is.numeric(DT$c) # TRUE
DT[, c := ifelse(v1 < 0, v1, 0L), by = list(date, id)]
# type error, DT$c was numeric and we are trying to add an integer column
DT[, c:= NULL] # deleting the c column again
DT[, c := ifelse(v1 < 0, v1, 0L), by = list(date, id)]
# no error now
is.integer(DT$c) # TRUE