R语言按分组处理dataframe：符合条件行对应overlap行value置0

1. 按group字段分组处理数据
2. 筛选每组中value > 10的行，拆分这些行逗号分隔的overlaps字段，收集所有需要置0的row_name列表
3. 匹配原数据的row_name，将对应行的value修改为0

注：你提供的示例数据中overlaps列多写了一个元素，修正后的可运行示例数据如下：

data <- data.frame(group = c("group_a", "group_a", "group_a", "group_a",
                             "group_a", "group_b", "group_b"),
                   value = c(4, 5 , 48, 54, 12, 12, 1),
                   row_name = c("1", "2", "3", "4", "5", "6", "7"),
                   overlaps = c("2", "3, 5", "4, 5", "5", "", "7"))

dplyr 实现 #

需要搭配tidyr的拆分函数使用，适合常规数据量场景：

library(dplyr)
library(tidyr)

result <- data %>%
  group_by(group) %>%
  mutate(
    # 提取当前组所有需要置0的行名
    to_zero = list(
      filter(cur_data(), value > 10) %>%
        separate_longer_delim(overlaps, delim = ", ") %>%
        pull(overlaps) %>%
        na.omit() %>%
        unique()
    )
  ) %>%
  ungroup() %>%
  # 匹配行名置0
  mutate(value = ifelse(row_name %in% unlist(to_zero), 0, value)) %>%
  select(-to_zero)

data.table 实现 #

使用更新连接逻辑，运算效率更高，适合百万级以上的大数据量场景：

library(data.table)
setDT(data)

# 按组提取所有待置0的行名
to_zero_dt <- data[value > 10, .(overlap = unlist(strsplit(overlaps, ", "))), by = group]
# 过滤空值并去重
to_zero_dt <- unique(to_zero_dt[overlap != ""])
# 匹配批量置0
data[to_zero_dt, value := 0, on = .(group, row_name = overlap)]

输出结果 #

两种方法输出的结果完全一致，符合预期：

group value row_name overlaps
1 group_a     4        1        2
2 group_a     5        2     3, 5
3 group_a    48        3     4, 5
4 group_a     0        4        5
5 group_a     0        5         
6 group_b    12        6        7
7 group_b     0        7         

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