如何在R中基于最邻近值对DataFrame进行Left Join？

原始数据 #

定义两个目标DataFrame：

t1 <- data.frame(
    team = c('a', 'b', 'c', 'd', 'e'),
    value1 = c(0.285, 0.37, 0.45, 0.42, 0.385),
    value2 = c(41, 51, 55, 61, 64)
)  

pctiles = data.frame(
    pctile = c(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10),
    value1 = c(0.253, 0.291, 0.325, 0.336, 0.345, 0.35, 0.367, 0.39, 0.4, 0.41, 0.435),
    value2 = c(35, 50, 54, 57, 59, 61, 62, 65, 71, 81, 95)
)

需求 #

将pctiles中的pctile值，通过t1中value1、value2与pctiles对应列的最邻近值匹配，左连接到t1，最终输出如下结构：

output_df <- data.frame(
    team = c('a', 'b', 'c', 'd', 'e'),
    value1 = c(0.285, 0.37, 0.45, 0.42, 0.385),
    value2 = c(41, 51, 55, 61, 64),
    value1pctile = c(1, 6, 10, 9, 7),
    value2pctile = c(0, 1, 3, 5, 7)
)

错误原因 #

你使用fuzzy_left_join时触发错误，核心问题：

• match_fun要求传入逻辑判断函数，"min_diff"不是合法参数；
• distance_col并非fuzzy_left_join的参数，该参数属于fuzzyjoin包中的distance_join系列函数。

正确实现方案 #

方案1：使用fuzzyjoin::distance_left_join #

专门针对距离匹配的连接函数，适合最邻近值场景：

library(fuzzyjoin)

# 匹配value1对应的分位数
t1_with_v1_pct <- distance_left_join(
    t1, pctiles,
    by = "value1",
    max_dist = Inf,  # 不限制最大匹配距离
    distance_col = "dist"
) %>%
    group_by(team, value1.x, value2) %>%
    slice_min(dist, n = 1) %>%  # 筛选距离最小的匹配项
    ungroup() %>%
    select(team, value1 = value1.x, value2, value1pctile = pctile)

# 匹配value2对应的分位数并整合结果
final_df <- distance_left_join(
    t1_with_v1_pct, pctiles,
    by = "value2",
    max_dist = Inf,
    distance_col = "dist"
) %>%
    group_by(team, value1, value2) %>%
    slice_min(dist, n = 1) %>%
    ungroup() %>%
    select(team, value1, value2, value1pctile, value2pctile = pctile)

print(final_df)

方案2：使用dplyr+findInterval（高效轻量） #

利用基础函数找到最邻近值的位置，无需额外依赖：

library(dplyr)

t1 <- t1 %>%
    mutate(
        # 匹配value1对应的pctile
        value1pctile = pctiles$pctile[
            sapply(value1, function(x) {
                pos <- findInterval(x, pctiles$value1, all.inside = TRUE)
                # 比较当前位置与下一个位置的距离，取更近的
                if (pos == length(pctiles$value1)) pos
                else if (abs(x - pctiles$value1[pos]) <= abs(x - pctiles$value1[pos+1])) pos
                else pos + 1
            })
        ],
        # 匹配value2对应的pctile
        value2pctile = pctiles$pctile[
            sapply(value2, function(x) {
                pos <- findInterval(x, pctiles$value2, all.inside = TRUE)
                if (pos == length(pctiles$value2)) pos
                else if (abs(x - pctiles$value2[pos]) <= abs(x - pctiles$value2[pos+1])) pos
                else pos + 1
            })
        ]
    )

print(t1)

两种方案均能输出符合预期的结果，方案2在大数据量场景下效率更优。

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