如何按df1的col1分组匹配df3的snp，为df2新增yes/no判断列

核心逻辑 #

1. 提前提取df3中snp列的所有唯一值作为匹配池
2. 对df1按col1分组，判断每组内的CAND_SNP是否有任意一个存在于匹配池中，生成每个col1对应的匹配结果
3. 将匹配结果合并到df2中，得到新增的标识列

R 实现（tidyverse 版本） #

library(tidyverse)

# 示例数据构造
df1 <- tribble(
  ~col1, ~CAND_SNP,
  1, "a1", 1, "a2", 1, "a3", 1, "a4",
  2, "b1",
  3, "c1", 3, "c2", 3, "c3"
)
df2 <- tribble(
  ~col1, ~LEAD_SNP,
  1, "a1", 2, "b1", 3, "c1"
)
df3 <- tribble(
  ~snp, ~col2,
  "a3", "x1", "a21", "x2", "a31", "x3", "a41", "x4",
  "b11", "x5", "c11", "x6", "c21", "x7", "c31", "x8"
)

# 核心计算
snp_pool <- unique(df3$snp)
group_match <- df1 %>%
  group_by(col1) %>%
  summarise(col3 = if_else(any(CAND_SNP %in% snp_pool), "Yes", "No"))

# 合并结果到df2
df2 <- df2 %>% left_join(group_match, by = "col1")

R 实现（base R 版本） #

snp_pool <- unique(df3$snp)
# 分组计算匹配结果
group_match <- aggregate(CAND_SNP ~ col1, df1, function(x) any(x %in% snp_pool))
group_match$col3 <- ifelse(group_match$CAND_SNP, "Yes", "No")
# 合并到df2
df2$col3 <- group_match$col3[match(df2$col1, group_match$col1)]

Python 实现（pandas 版本） #

import pandas as pd

# 示例数据构造
df1 = pd.DataFrame({
    "col1": [1,1,1,1,2,3,3,3],
    "CAND_SNP": ["a1", "a2", "a3", "a4", "b1", "c1", "c2", "c3"]
})
df2 = pd.DataFrame({
    "col1": [1,2,3],
    "LEAD_SNP": ["a1", "b1", "c1"]
})
df3 = pd.DataFrame({
    "snp": ["a3", "a21", "a31", "a41", "b11", "c11", "c21", "c31"],
    "col2": ["x1", "x2", "x3", "x4", "x5", "x6", "x7", "x8"]
})

# 核心计算
snp_pool = df3["snp"].unique()
group_match = df1.groupby("col1")["CAND_SNP"].apply(lambda x: x.isin(snp_pool).any()).reset_index(name="col3")
group_match["col3"] = group_match["col3"].map({True: "Yes", False: "No"})

# 合并结果到df2
df2 = df2.merge(group_match, on="col1", how="left")

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