r - 比较两个数据帧并检索值

Question

我有两个数据框，一个看起来像这样:

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

还有一个看起来像这样

>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 列得分最高的符号。所以它看起来像这样:

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

有什么建议可以实现吗？

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

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

Answer 1

我们可以在分组后将 separate_rows 与 left_join 和 slice_max 结合起来:

library(dplyr)
library(tidyr)

df1 %>% 
  separate_rows(Symbols, sep = ",") %>% 
  left_join(df2, by=c("Symbols" = "Symbol")) %>% 
  group_by(SNP) %>% 
  slice_max(Score)

  SNP        Symbols Score
  <chr>      <chr>   <int>
1 rs11807834 GRIN1     167
2 rs3729986  MADD      164
3 rs61937595 STAC3      12