我需要通过提取 df1$Id 中“gene_id”之后的单词并在 df2 的 2 个不同列(df2$Gene.id、df2$Gene.name)中搜索它们来找到 df2 中存在哪些 df1 行。
这是我的数据的样子:
df1 <- data_frame(
Chr = c("NC_035077.1", "NC_035078.1", "NC_035083.1", "NC_035083.1", "NC_035084.1", "NC_035084.1", "NC_035088.1"),
Pos = c("61344375", "78462810", "24378412", "24387264","66360216", "66360385","40131947"),
Var=c("tco","born", "tco","tco", "born","tco","tco"),
Id=c("gene_id calm2", "gene_id LOC110500174", "gene_id ahcy", "gene_id ahcy", "gene_id cebpd", "gene_id cebpd", "gene_id LOC110537636, gene_id hsc70a")
)
df1
Chr Pos Var Id
<chr> <chr> <chr> <chr>
1 NC_035077.1 61344375 tco gene_id calm2
2 NC_035078.1 78462810 born gene_id LOC110500174
3 NC_035083.1 24378412 tco gene_id ahcy
4 NC_035083.1 24387264 tco gene_id ahcy
5 NC_035084.1 66360216 born gene_id cebpd
6 NC_035084.1 66360385 tco gene_id cebpd
7 NC_035088.1 40131947 tco gene_id LOC110537636, gene_id hsc70a
df2 <- data_frame(
Gene.id = c("LOC110488122", "NA", "LOC110490243", "LOC110537256", "LOC100136165", "LOC100379112", "LOC100379114", "LOC110527949", "LOC110537636"),
Gene.name = c("agr2", "agrn", "ahcy", "akap1","cebpb", "cebpb","cebpd", "ddost","slc6a13")
)
df2
Gene.id Gene.name
<chr> <chr>
1 LOC110488122 agr2
2 NA agrn
3 LOC110490243 ahcy
4 LOC110537256 akap1
5 LOC100136165 cebpb
6 LOC100379112 cebpb
7 LOC100379114 cebpd
8 LOC110527949 ddost
9 LOC110537636 slc6a13
正如您所看到的,一些 df1$Id 有两个基因 ID,我需要在 df2 中检查它们,无论它们与 df2$Gene.id 还是 df2$Gene.name 匹配,我都需要将该行包含在输出文件。
df2 中也有一些 NA。
我的输出应该是这样的:
Chr Pos Var Id Gene.id Gene.name
NC_035083.1 24378412 tco gene_id ahcy LOC110490243 ahcy
NC_035083.1 24387264 tco gene_id ahcy LOC110490243 ahcy
NC_035084.1 66360216 born gene_id cebpd LOC100379114 cebpd
NC_035084.1 66360385 tco gene_id cebpd LOC100379114 cebpd
NC_035088.1 40131947 tco gene_id LOC110537636, gene_id hsc70a LOC110537636 slc6a13
任何有关如何实现这一目标的帮助将不胜感激。
这使用你的例子做到了。我假设您的所有数据都遵循相同的格式。
你需要创建一个干净的“一切”一栏加入df1
用df2
。完成两次连接后,清理无用的行并NA
使用“all in”列更新s的值。
NA
的new_col
因为您不再需要它library(stringr) # for str_remove()
library(tidyr) # for separate_rows()
library(dplyr) # for everything else
df1 %>%
mutate(new_col = Id) %>%
separate_rows(new_col, sep = ", ") %>%
mutate(new_col = str_remove(new_col, "gene_id ")) %>%
left_join(df2, by = c("new_col" = "Gene.name")) %>%
left_join(df2, by = c("new_col" = "Gene.id")) %>%
filter(!is.na(Gene.name) | !is.na(Gene.id)) %>%
mutate(Gene.name = if_else(is.na(Gene.name), new_col, Gene.name),
Gene.id = if_else(is.na(Gene.id), new_col, Gene.id)) %>%
select(-new_col)
Chr Pos Var Id Gene.id Gene.name
<chr> <chr> <chr> <chr> <chr> <chr>
1 NC_035083.1 24378412 tco gene_id ahcy LOC110490243 ahcy
2 NC_035083.1 24387264 tco gene_id ahcy LOC110490243 ahcy
3 NC_035084.1 66360216 born gene_id cebpd LOC100379114 cebpd
4 NC_035084.1 66360385 tco gene_id cebpd LOC100379114 cebpd
5 NC_035088.1 40131947 tco gene_id LOC110537636, gene_id hsc70a LOC110537636 slc6a13
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句