基于匹配索引列与深度范围条件，向R数据框添加另一数据框列

需求说明 #

需要将数据框df2中的text_description列匹配添加到df1中，匹配规则如下：

• 匹配相同的location_ID
• 深度区间规则：
  1. 若df1的深度区间[start_1, end_1]完全包含在df2的[start_2, end_2]区间内，直接匹配对应text_description；
  2. 若df1的深度区间跨df2多个区间，按df1区间在df2各区间中的重叠占比最大者分配text_description。

示例数据 #

df1 <- data.frame(
  location_ID = c("Location_01", "Location_01", "Location_01", "Location_02", "Location_02", "Location_02"),
  start_1 = c(0,5, 15, 0, 2.5, 5),
  end_1 = c(5,15, 25, 2.5,5, 20),
  value = c(3.00, 3.75, 3.30, 3.25, 4.15, 4.25)
)

df2 <- data.frame(
  location_ID = c("Location_01", "Location_01", "Location_02", "Location_02"),
  start_2 = c(0, 10, 0, 5),
  end_2 = c(10, 25, 5, 20),
  text_description = c("First Description (Location 1)", "Second Description (Location 1)", 
                       "First Description (Location 2)", "Second Description (Location 2)")
)

尝试过的代码（无法运行） #

由于df1和df2行数不一致，直接按列匹配的方式报错：

test_df <- df1 %>%
  mutate(text = case_when(df2$start_2 <= start_1 & df2$end_2 >= end_1 ~ df2$text_description ))

解决方案 #

可以通过按location_ID分组连接，计算每个df1区间与对应df2区间的重叠长度，再筛选占比最大的记录来实现：

library(dplyr)

# 1. 按location_ID将两个数据框做全连接，得到同地点下的所有区间组合
joined_df <- df1 %>%
  inner_join(df2, by = "location_ID") %>%
  # 2. 计算两个区间的重叠起始和结束位置
  mutate(
    overlap_start = pmax(start_1, start_2),
    overlap_end = pmin(end_1, end_2),
    # 3. 计算重叠长度（若没有重叠则为0）
    overlap_length = ifelse(overlap_end > overlap_start, overlap_end - overlap_start, 0),
    # 4. 计算df1区间的总长度
    df1_interval_length = end_1 - start_1,
    # 5. 计算重叠占比
    overlap_ratio = overlap_length / df1_interval_length
  ) %>%
  # 6. 过滤掉无重叠的记录
  filter(overlap_ratio > 0) %>%
  # 7. 按df1的每一行分组，筛选占比最大的记录
  group_by(location_ID, start_1, end_1, value) %>%
  filter(overlap_ratio == max(overlap_ratio)) %>%
  # 8. 保留需要的列
  select(location_ID, start_1, end_1, value, text_description) %>%
  ungroup()

# 查看结果
print(joined_df)

结果解释 #

• 对于df1中Location_01的[5,15]区间：
  • 与df2的[0,10]重叠长度为5（5-10），占比5/10=0.5；
  • 与df2的[10,25]重叠长度为5（10-15），占比5/10=0.5；
  • 若出现占比相同的情况，会保留多条记录，可根据需求调整。

优化：处理占比相同的情况 #

如果需要在占比相同时只保留一条记录，可以在filter后添加slice_head(n=1)：

joined_df <- df1 %>%
  inner_join(df2, by = "location_ID") %>%
  mutate(
    overlap_start = pmax(start_1, start_2),
    overlap_end = pmin(end_1, end_2),
    overlap_length = ifelse(overlap_end > overlap_start, overlap_end - overlap_start, 0),
    df1_interval_length = end_1 - start_1,
    overlap_ratio = overlap_length / df1_interval_length
  ) %>%
  filter(overlap_ratio > 0) %>%
  group_by(location_ID, start_1, end_1, value) %>%
  filter(overlap_ratio == max(overlap_ratio)) %>%
  slice_head(n=1) %>% # 取第一条占比最大的记录
  select(location_ID, start_1, end_1, value, text_description) %>%
  ungroup()

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