连接data.table时如何查找最接近的最大值 [英] How to find nearest highest value when join data.table

问题描述

我有以下2个数据表:

DT1 <- data.table(A = c(100,50,10), B = c("Good","Ok","Bad"))
DT1
     A    B
1: 100 Good
2:  50   Ok
3:  10  Bad

和

DT2 <- data.table(A = c(99,34,5,"",24,86))
DT2
    A
1: 99
2: 34
3:  5
4:   
5: 24
6: 86   

加入DT1和DT2时我想返回的内容是

What I would like to return when joining DT1 and DT2 is

DT2
    A       B
1: 99    Good
2: 34    Ok
3:  5    Bad
4:       NA
5: 24    Ok
6: 86    Good

data.table中的"roll"选项仅适用于最近"匹配，因此在我的情况下不起作用.有什么办法可以对data.table进行这种查找吗?

The "roll" option in data.table is only for "nearest" match so it doesnt work in my case. Is there any way I can do such lookup with data.table?

推荐答案

滚动连接如果向后滚动(NOCB =下一个礼拜倒退)对我有用:

The rolling join does work for me if rolled backwards (NOCB = next obervation carried backwards):

library(data.table)
DT1 <- data.table(A = c(100, 50, 10), B = c("Good", "Ok", "Bad"))
DT2 <- data.table(A = c(99, 34, 5, "", 24, 86))

DT2[, A := as.numeric(A)]
DT1[DT2, on = "A", roll = -Inf]

    A    B
1: 99 Good
2: 34   Ok
3:  5  Bad
4: NA <NA>
5: 24   Ok
6: 86 Good

请注意，这仅在两列A均为数字(或整数)时才有效.通过使用""，OP将DT2$A转换为字符列.

Note that this does only work if both columns A are numeric (or integer). By using "", the OP has turned DT2$A into a character column.

这篇关于连接data.table时如何查找最接近的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！