将两个sampleID的相应值连接到新的单个列中

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

一种解决方案是通过tidyverse包使用双left_join。首先在ID1和SampleName上加入kinshipEstimates和sampleManifest。再次在ID2和SampleName上使用result加入sampleManifest。最后，使用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、one 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