r语言 - 将两个样本 ID 的相应值联接到新的单列中

Question

>我有一个数据帧sampleManifest，如下所示：

SampleName          Status          Role          Sex
AU056001_00HI1299A  unaffected      sibling       female
AU056002_00HI1301A  unaffected      proband       male  
AU0780201_00HI1775A unaffected      father        male  
AU0780202_00HI1777A unaffected      mother        female
AU0780301_00HI1778A affected        proband       male  
.
.
.

以及成对样本比较的单独数据框，kinshipEstimates：

FID    ID1                      ID2             Kinship Relationship    
AU0560 AU056001_00HI1299A  AU056002_00HI1301A   0.0283  full-sibling   
AU0780 AU0780201_00HI1775A AU0780202_00HI1777A -0.00160 unrelated   
AU0780 AU0780201_00HI1775A AU0780301_00HI1778A  0.284   parent-child
AU0780 AU0780202_00HI1777A AU0780301_00HI1778A  0.246   parent-child
.
.
.

我想构建一个新的数据帧，其中每行kinshipEstimates中两个样本中的每一个的sampleManifest$Role，使其看起来像这样：

FID    ID1                      ID2             Roles           Kinship Relationship    
AU0560 AU056001_00HI1299A  AU056002_00HI1301A   sibling-proband 0.0283  full-sibling   
AU0780 AU0780201_00HI1775A AU0780202_00HI1777A  father-mother  -0.00160 unrelated   
AU0780 AU0780201_00HI1775A AU0780301_00HI1778A  father-proband  0.284   parent-child
AU0780 AU0780202_00HI1777A AU0780301_00HI1778A  mother-proband  0.246   parent-child
.
.
.

我一直在尝试使用left_join，但不知道如何将成对中每个样本的相应Role合并为一个值。

Answer 1

解决方案是使用双left_jointidyverse包。首先加入kinshipEstimatessampleManifestID1和SampleName。再次加入sampleManifest，结果在ID2和SampleName上。最后，使用tidyr::unite合并Role.x和Role.y。

library(tidyverse)
left_join(kinshipEstimates, sampleManifest, by=c("ID1" = "SampleName")) %>%
select(-Status, -Sex) %>%
left_join(sampleManifest, by=c("ID2" = "SampleName")) %>%
unite(Roles, Role.x, Role.y, sep="-") %>%
select(-Sex, -Status)

#      FID                 ID1                 ID2 Kinship Relationship           Roles
# 1 AU0560  AU056001_00HI1299A  AU056002_00HI1301A  0.0283 full-sibling sibling-proband
# 2 AU0780 AU0780201_00HI1775A AU0780202_00HI1777A -0.0016    unrelated   father-mother
# 3 AU0780 AU0780201_00HI1775A AU0780301_00HI1778A  0.2840 parent-child  father-proband
# 4 AU0780 AU0780202_00HI1777A AU0780301_00HI1778A  0.2460 parent-child  mother-proband

数据：

sampleManifest <- read.table(text = 
"SampleName          Status          Role          Sex
AU056001_00HI1299A  unaffected      sibling       female
AU056002_00HI1301A  unaffected      proband       male  
AU0780201_00HI1775A unaffected      father        male  
AU0780202_00HI1777A unaffected      mother        female
AU0780301_00HI1778A affected        proband       male",
stringsAsFactors = FALSE, header = TRUE)
kinshipEstimates <- read.table(text = 
"FID    ID1                      ID2             Kinship Relationship    
AU0560 AU056001_00HI1299A  AU056002_00HI1301A   0.0283  full-sibling   
AU0780 AU0780201_00HI1775A AU0780202_00HI1777A -0.00160 unrelated   
AU0780 AU0780201_00HI1775A AU0780301_00HI1778A  0.284   parent-child
AU0780 AU0780202_00HI1777A AU0780301_00HI1778A  0.246   parent-child",
stringsAsFactors = FALSE, header = TRUE)

Answer 2

这是一种使用gather、 一个inner_join和group_by的方法。
添加行号可以让我们在分组时跟踪ID1/ID2对：

kinshipEstimates %>%
mutate(row_num = row_number()) %>%
gather(which_id, id, -row_num, -FID, -Kinship, -Relationship) %>%
inner_join(sampleManifest, by=c("id" = "SampleName")) %>%
group_by(FID, row_num) %>%
summarise(Roles = paste(Role, collapse="-"),
Kinship = first(Kinship),
Relationship = first(Relationship))
FID    row_num Roles            Kinship Relationship
<chr>    <int> <chr>              <dbl> <chr>       
1 AU0560       1 sibling-proband  0.0283  full-sibling
2 AU0780       2 father-mother   -0.00160 unrelated   
3 AU0780       3 father-proband   0.284   parent-child
4 AU0780       4 mother-proband   0.246   parent-child