按组提取DataFrame中等于或小于指定值的对应行

现有包含ID、value、newval三列的DataFrame，需编写函数实现以下逻辑：

• 按ID分组
• 指定某个目标value值时，每组优先返回精确匹配该值的行
• 若组内无该值，则返回组内**小于目标值的最大value**对应的行

示例场景 #

• 指定目标值为0.002时，两组均存在精确匹配行，返回这两行
• 指定目标值为0.005时，两组均无精确匹配，返回每组中小于0.005的最大value对应的行
• 指定目标值为0.003时，AAHON组返回精确匹配行，AAJCQ组返回小于0.003的最大value对应的行

测试数据 #

ID = c("AAHON","AAHON","AAHON","AAHON","AAHON","AAHON","AAJCQ","AAJCQ",
       "AAJCQ","AAJCQ","AAJCQ","AAJCQ","AAJCQ","AAJCQ","AAJCQ")

value = c(0,0.001,0.002,0.003,0.004,0.006,0,0.000333,0.0005,
          0.001,0.002,0.002333,0.003667,0.023,0.0245)

newval = c(0.990295749,0.993992606,0.994454713,0.995378928,0.995841035,
           0.996303142,0.989833641,0.990295749,0.990757856,0.99676525,0.997227357,
           0.997689464,0.998151571,0.998613678,0.999075786)

df <- data.frame(ID, value, newval)

对应的DataFrame预览：

使用R语言的dplyr包实现需求，核心逻辑是分组后筛选符合条件的行：

library(dplyr)

find_target_row <- function(df, target_value) {
  df %>%
    group_by(ID) %>%
    # 筛选出组内所有小于等于目标值的行
    filter(value <= target_value) %>%
    # 按value降序排列，取第一行（优先精确匹配，否则取最大的符合条件值）
    arrange(desc(value)) %>%
    slice(1) %>%
    ungroup()
}

测试验证 #

1. 目标值0.002

find_target_row(df, 0.002)

返回结果：

# A tibble: 2 × 3
  ID    value  newval
  <chr> <dbl>   <dbl>
1 AAHON 0.002 0.994  
2 AAJCQ 0.002 0.997  

2. 目标值0.005

find_target_row(df, 0.005)

返回结果：

# A tibble: 2 × 3
  ID    value  newval
  <chr> <dbl>   <dbl>
1 AAHON 0.004 0.996  
2 AAJCQ 0.00233 0.998

3. 目标值0.003

find_target_row(df, 0.003)

返回结果：

# A tibble: 2 × 3
  ID    value  newval
  <chr> <dbl>   <dbl>
1 AAHON 0.003 0.995  
2 AAJCQ 0.00233 0.998

逻辑说明 #

• 按ID分组后，先筛选出每组中value小于等于目标值的所有行
• 对筛选结果按value降序排列，取第一行即可实现：优先拿到精确匹配行（若存在），否则拿到小于目标值的最大value对应的行
• 最后取消分组，返回标准化结果

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