用 R 数据框中的匹配 ID 替换单元格

Question

我有以下形式的数据:

Input_SNP       Set_1     Set_2     Set_3     Set_4     Set_5    Set_6     Set_7
rs70812    4:12309   7:189029   2:2134   17:43232  12:51123  11:15123  19:4312
rs34812    5:61233   2:571022  1:57012   3:537012  14:57123  4:57129   1:61507
rs15602    1:571209  12:34120  9:41236   12:32417  3:57120   9:34123   3:41235
rs90143    7:83541   9:659123  5:23412   16:98234  18:472351 20:12357  1:13421
rs70823    14:89023  13:42081  8:32098   5:431332  9:234134  13:7831   2:74012
rs100980   11:51003  1:100098  10:409123 12:412309 13:34123  16:431098 3:58023
rs10341    18:90312  15:609123 1:70923   2:102358  5:019824  17:120394 9:80123

我实际上有 10,000 个集合和大约 4,000 行。但这是一个很好的例子。我还有一个文件是:

set snpID     rsMatch
1   4:12309   rs241984
2   7:189029  rs104141
3   2:2134    rs485506
4   17:43232  rs345180
5   12:51123  rs129819
6   11:15123  rs757492
7   19:4312   rs711403
1   5:61233   rs341098
2   2:571022  rs512309
3   1:57012   rs120394
4   3:537012  rs510293
5   14:571234 rs234098
6   4:57129   rs71302
7   1:61507   rs234109
1   1:571209  rs09384
... ...       ...

我想将我的 Set_1、Set_2、Set_3 等的数字格式替换为其 rsMatch 格式，如下所示:

    Input_SNP  Set_1     Set_2     Set_3     Set_4     Set_5     Set_6     Set_7
    rs70812    rs241984 rs104141 rs485506 rs345180 rs129819 rs757492 rs711403
    rs34812    rs341098 rs512309 rs120394 rs510293 rs234098 rs71302  rs234109
    rs15602    rs098384 ...       ...       ...       ...       ...
...        ...       ...       ...       ...       ...       ...

你们对如何做到这一点有什么建议吗？我在考虑 R 数据框，但我对任何事情都持开放态度...

Answer 1

您可以在适当的转换后使用merge 来解决这个问题。我正在使用 library(reshape2) 以正确的形状获取数据以进行合并并返回输出。

#read in files
df1<-read.table("file1",header=TRUE,stringsAsFactors=FALSE)   
df2<-read.table("file2",header=TRUE,stringsAsFactors=FALSE)

library(reshape2)
m1<-melt(df1,id.vars="Input_SNP")
m2<-transform(df2,variable=paste0("Set_",set),value=snpID)
m<-merge(m1,m2)
out<-dcast(m,Input_SNP~variable,value.var="rsMatch")

print(out)

  Input_SNP    Set_1    Set_2    Set_3    Set_4    Set_5    Set_6    Set_7
1   rs15602  rs09384     <NA>     <NA>     <NA>     <NA>     <NA>     <NA>
2   rs34812 rs341098 rs512309 rs120394 rs510293     <NA>  rs71302 rs234109
3   rs70812 rs241984 rs104141 rs485506 rs345180 rs129819 rs757492 rs711403