如何通过比较值的范围来合并两个 pandas 数据框(或传输值) [英] How to merge two pandas dataframes (or transfer values) by comparing ranges of values
本文介绍了如何通过比较值的范围来合并两个 pandas 数据框(或传输值)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在以下数据中:
data01 =
contig start end haplotype_block
2 5207 5867 1856
2 155667 155670 2816
2 67910 68022 2
2 68464 68483 3
2 525 775 132
2 118938 119559 1157
data02 =
contig start last feature gene_id gene_name transcript_id
2 5262 5496 exon scaffold_200003.1 CP5 scaffold_200003.1
2 5579 5750 exon scaffold_200003.1 CP5 scaffold_200003.1
2 5856 6032 exon scaffold_200003.1 CP5 scaffold_200003.1
2 6115 6198 exon scaffold_200003.1 CP5 scaffold_200003.1
2 916 1201 exon scaffold_200001.1 NA scaffold_200001.1
2 614 789 exon scaffold_200001.1 NA scaffold_200001.1
2 171 435 exon scaffold_200001.1 NA scaffold_200001.1
2 2677 2806 exon scaffold_200002.1 NA scaffold_200002.1
2 2899 3125 exon scaffold_200002.1 NA scaffold_200002.1
问题:
- 我想比较这两个数据帧的范围(开始-结束).
- 如果范围重叠,我想将
gene_id
和gene_name
值从data02传输到data01中的新列.
- I want to compare the ranges (start - end) from these two data frames.
- If the ranges overlap I want to transfer the
gene_id
andgene_name
values from data02 to to a new column in the data01.
我尝试过(使用熊猫):
I tried (using pandas):
data01['gene_id'] = ""
data01['gene_name'] = ""
data01['gene_id'] = data01['gene_id'].\
apply(lambda x: data02['gene_id']\
if range(data01['start'], data01['end'])\
<= range(data02['start'], data02['last']) else 'NA')
如何改进此代码?我目前坚持使用熊猫,但是如果使用字典可以更好地解决问题,那么我可以接受.但是,请解释一下过程,我乐于学习,而不仅仅是获得答案.
谢谢
所需的输出:
contig start end haplotype_block gene_id gene_name
2 5207 5867 1856 scaffold_200003.1,scaffold_200003.1,scaffold_200003.1 CP5,CP5,CP5
# the gene_id and gene_name are repeated 3 times because three intervals (i.e 5262-5496, 5579-5750, 5856-6032) from data02 overlap(or touch) the interval ranges from data01 (5207-5867)
# So, whenever there is overlap of the ranges between two dataframe, copy the gene_id and gene_name.
# and simply NA on gene_id and gene_name for non overlapping ranges
2 155667 155670 2816 NA NA
2 67910 68022 2 NA NA
2 68464 68483 3 NA NA
2 525 775 132 scaffold_200001.1 NA
2 118938 119559 1157 NA NA
推荐答案
s1 = data01.start.values
e1 = data01.end.values
s2 = data02.start.values
e2 = data02['last'].values
overlap = (
(s1[:, None] <= s2) & (e1[:, None] >= s2)
) | (
(s1[:, None] <= e2) & (e1[:, None] >= e2)
)
g = data02.gene_id.values
n = data02.gene_name.values
i, j = np.where(overlap)
idx_map = {i_: data01.index[i_] for i_ in pd.unique(i)}
def make_series(m):
s = pd.Series(m[j]).fillna('').groupby(i).agg(','.join)
return s.rename_axis(idx_map).replace('', np.nan)
data01.assign(
gene_id=make_series(g),
gene_name=make_series(n),
)
这篇关于如何通过比较值的范围来合并两个 pandas 数据框(或传输值)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文