如何对比两个DataFrame并为每个SNP获取最高得分的Symbol？

数据结构 #

现有两个DataFrame，结构如下：

df1 #

> df1
             SNP                Symbols
1 rs11807834          GRIN1,SETD1A
2  rs3729986      MADD,STAC3,SPI1
3 rs61937595 NDUFA4L2,STAC3,CAMK2N1

df2 #

> df2
   Symbol    Score
1   GRIN1      167
2  SETD1A      160
3    MADD      164
4   STAC3       12
5 CAMK2N1        3
6 NDUFA4L2       0
7   SPI1         0

需求 #

为每个SNP对应的Symbols列表，找出其中Score最高的Symbol，期望结果如下：

> result
          SNP                 Symbols        Highest.Score   
rs11807834            GRIN1,SETD1A                GRIN1                                
rs3729986          MADD,STAC3,SPI1                MADD                           
rs61937595   NDUFA4L2,STAC3,CAMK2N1               STAC3                        

实现方案 #

首先加载示例数据（注意添加stringsAsFactors = FALSE避免字符自动转因子）：

df1 <- data.frame(
  "SNP" = c("rs11807834", "rs3729986", "rs61937595" ), 
  "Symbols" = c("GRIN1,SETD1A", "MADD,STAC3,SPI1", "NDUFA4L2,STAC3,CAMK2N1"),
  stringsAsFactors = FALSE
)

df2 <- data.frame(
  "Symbol" = c("GRIN1", "SETD1A", "MADD", "STAC3", "CAMK2N1", "NDUFA4L2", "SPI1"), 
  "Score" = c(167, 160, 164,12,3,0,0),
  stringsAsFactors = FALSE
)

方法1：使用tidyverse工具链 #

适合习惯管道操作的场景，步骤清晰直观：

library(tidyverse)

result <- df1 %>%
  # 按逗号拆分Symbols为多行
  separate_rows(Symbols, sep = ",") %>%
  # 关联df2获取对应得分
  left_join(df2, by = c("Symbols" = "Symbol")) %>%
  # 按SNP分组，筛选每组得分最高的行
  group_by(SNP) %>%
  slice_max(Score, n = 1) %>%
  # 合并回原始的Symbols字符串格式
  summarise(
    Symbols = paste(unique(Symbols), collapse = ","),
    Highest.Score = first(Symbols)
  ) %>%
  ungroup()

print(result)

方法2：基础R实现 #

无需额外加载包，适合轻量处理场景：

# 定义函数：输入符号字符串，返回得分最高的符号
get_top_symbol <- function(sym_str) {
  syms <- strsplit(sym_str, ",")[[1]]
  # 匹配对应得分，找出最大值对应的符号
  scores <- df2$Score[match(syms, df2$Symbol)]
  syms[which.max(scores)]
}

# 对df1每行应用函数生成结果列
df1$Highest.Score <- sapply(df1$Symbols, get_top_symbol)

# 整理成目标格式
result <- df1[, c("SNP", "Symbols", "Highest.Score")]
print(result)

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