r - 比较拆分和合并两个数据框

Question

如何通过基因名称比较两个数据集df1和df2，并从df2中提取每个基因名称的对应值并将其插入df1

df1 <-

Genes    sample.ID  chrom   loc.start   loc.end num.mark
Klri2     LO.WGS      1   3010000 173490000     8430
Rrs1      LO.WGS      1   3010000 173490000     8430
Serpin    LO.WGS      1   3010000 173490000     8430
Myoc      LO.WGS          1   3010000 173490000     8430
St18      LO.WGS          1   3010000 173490000     8430


df2 <-

    RL  pValue.   chr   start            end    CNA     Genes
    2   2.594433   1    129740006   129780779   gain    Klri2   
    2   3.941399   1    130080653   130380997   gain    Serpin,St18,Myoc

df3<-

Genes   sample.ID  chrom  loc.start  loc.end num.mark   RL  pValue      CNA
Klri2    LO.WGS     1   3010000   173490000     8430    2   2.594433    gain
Rrs1     LO.WGS     1   3010000   173490000     8430    0     0          0
Serpin   LO.WGS     1   3010000   173490000     8430    2   3.941399    gain
Myoc     LO.WGS     1   3010000   173490000     8430    2   3.941399    gain
St18     LO.WGS     1   3010000   173490000     8430    2   3.941399    gain

Answer 1

你可以试试:

library(splitstackshape)   
out <- cSplit(df2, "Genes", sep = ",", "long")

这将以正确的格式 reshape df2(每个基因一行):

#   RL  pValue. chr     start       end  CNA  Genes
#1:  2 2.594433   1 129740006 129780779 gain  Klri2
#2:  2 3.941399   1 130080653 130380997 gain Serpin
#3:  2 3.941399   1 130080653 130380997 gain   St18
#4:  2 3.941399   1 130080653 130380997 gain   Myoc

然后你只需要使用 dplyr 中的 merge() 或 left_join():

library(dplyr)
df3 <- left_join(df1, out)

如果你想用 0 替换 NAs，你可以这样做:

df3 <- left_join(df1, out) %>% mutate_each(funs(ifelse(is.na(.), 0, .)))

或者，如果您更喜欢子集:

df3 <- left_join(df1, out) %>% (function(x) { x[is.na(x)] <- 0; x })