使用data.table在R中按条件从映射表提取值并为数据框新增列

现有数据 #

映射数据框df： #

fli.1 = c(0, 700, 1500, 2000, 3500, 5600, 11000, 20000, 50000)
fli.2 = c(0, 200, 1000, 2900, 3000, 5500, 10000, 20000, 50000)
fli.3 = c(0, 600, 1200, 2500, 3900, 5000, 10000, 22000, 58000)
fli.4 = c(0, 300, 1000, 2000, 3000, 5000, 14000, 20000, 50000)
fli.5 = c(0, 500, 1000, 2000, 3500, 5000, 10000, 20000, 50000)

df <- data.frame(xt    = c(rep("a",9),rep("b",9),rep("c",9)),
                 fli.1 = rep(fli.1,3),
                 fli.2 = rep(fli.2,3),
                 fli.3 = rep(fli.3,3),
                 fli.4 = rep(fli.4,3),
                 fli.5 = rep(fli.5,3));

目标数据框d： #

d <- data.frame(xt=rep(c("a","b","c"),4),value = c(0, 2000, 5000, 6500, 40000, 60000, 400, 200, 40, 7899, 1000, 1500), fli = c("fli.1", "fli.1", "fli.4", "fli.5", "fli.2", "fli.2", "fli.5", "fli.1", "fli.2", "fli.2", "fli.3", "fli.4"));

需求 #

为数据框d新增两列：

• 第一列：存储在df中对应xt分组、fli指定列里，小于等于d$value的最大值
• 第二列：存储该最大值在df中的行号

示例：

• d第一行(a,0,fli.1)：对应df中xt=a的fli.1列里≤0的值为0，行号1
• d第五行(b,40000,fli.2)：对应df中xt=b的fli.2列里≤40000的最大值为20000，行号17

注意：实际场景中d的数据量约200万行，需使用data.table包实现高效处理。

步骤1：转换为data.table格式 #

先将df和d转换为data.table结构，同时为df保留原始行号：

library(data.table)

# 转换df为data.table，添加原始行号列row_num
setDT(df)[, row_num := .I]

# 转换d为data.table
setDT(d)

步骤2：重塑df为长格式 #

把宽格式的df转为长格式，方便按xt和fli分组匹配：

df_long <- melt(df, id.vars = c("xt", "row_num"), variable.name = "fli", value.name = "fli_value")

步骤3：非等值连接实现高效匹配 #

利用data.table的非等值连接功能，一次性完成所有行的匹配，避免循环：

# 先按xt、fli、fli_value升序排序，确保后续能直接取到最大匹配值
setorder(df_long, xt, fli, fli_value)

# 执行非等值连接，对d的每一行匹配符合条件的记录，取每组最后一条（即最大值）
result <- df_long[d, on = .(xt, fli, fli_value <= value), 
                  .(xt, value, fli, max_fli_value = last(fli_value), row_num = last(row_num)), 
                  by = .EACHI]

结果验证 #

查看处理后的result，可以看到符合需求的新增列：

print(result)

部分输出示例：

xt value   fli max_fli_value row_num
 1:  a     0 fli.1             0       1
 2:  b  2000 fli.1          2000      12
 3:  c  5000 fli.4          5000      24
 4:  a  6500 fli.5          5000       6
 5:  b 40000 fli.2         20000      17
 6:  c 60000 fli.2         50000      27
...

性能说明 #

• 非等值连接fli_value <= value快速筛选符合条件的记录，无需遍历每一行
• by = .EACHI确保对d的每一行独立处理，逻辑清晰
• 提前排序后用last()直接取最大值，避免额外计算，适合处理200万行级别的大数据量

内容的提问来源于stack exchange，提问作者Mohit