R语言百万级数据集高效映射：按条件匹配行索引

数据定义 #

映射表df #

df <- data.frame(L = c(1, 2, 3, 4, 5, 6, 7, 8, 9),
                 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))

待处理数据框d #

d <- data.frame(
  quantity = c(300, 368, 568, 20, 1000, 37659, 45000, 2500, 4500, 78453, 1200, 1589), 
  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里新增一列，规则是：

• 按d$fli匹配df里对应的列（比如fli.1对应df$fli.1）
• 在匹配到的列中，找到小于等于d$quantity的最大值，取这个值所在行的df$L（举个例子：quantity=37659且fli=fli.2时，对应的L是8）
• 必须能高效处理百万级数据集，之前的矩阵方法太慢，现有的data.table代码还有优化空间

原代码问题分析 #

你原来的代码是把df转成长格式后做不等连接，虽然能得到结果，但面对百万级的d时，这种方法的计算开销会很大。而且你没用到一个关键特性：df里每个fli列都是从小到大递增的，完全可以用二分查找来大幅提速。

优化方案 #

下面提供两种高效的优化方法，都是基于data.table的特性，专门适配大规模数据：

方法一：利用findInterval二分查找（最快） #

第一步：先把df转成长格式，并且按fli和阈值排序（这一步只需要做一次）

library(data.table)
setDT(df)
# 转长格式，保留L、fli列名和对应的阈值
df_long <- melt(df, id.vars = "L", measure.vars = patterns("^fli"), 
                variable.name = "fli", value.name = "threshold")
# 按fli分组，确保每个组内的阈值是递增的（原数据已经满足，这步可选，用来验证）
setorder(df_long, fli, threshold)

第二步：对d按fli分组，用findInterval快速定位对应的L值

setDT(d)
# 分组匹配，用二分查找找到每个quantity对应的最大阈值位置，再提取L
d[, L := df_long[.SD, on = .(fli), 
                 x.L[findInterval(quantity, x.threshold)], 
                 by = .EACHI]$V1]

方法二：优化后的非等值连接 #

如果不想提前处理df，也可以用data.table的非等值连接，但要先给长格式的df设置键，利用索引加速：

library(data.table)
setDT(df)
setDT(d)
# 转长格式并设置键（fli和threshold）
df_long <- melt(df, id.vars = "L", measure.vars = patterns("^fli"), 
                variable.name = "fli", value.name = "threshold")
setkey(df_long, fli, threshold)

# 非等值连接，直接取符合条件的最后一行的L值
d[, L := df_long[.SD, on = .(fli, threshold <= quantity), mult = "last", x.L]]

为什么这两种方法更快？ #

• findInterval是二分查找算法，每次查找的时间复杂度是O(log n)，百万级数据处理起来非常快
• 设置键后的非等值连接，data.table会利用索引来减少不必要的计算，比原代码的无索引连接效率高很多

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