基于多列关系合并pandas数据框

Question

假设您有一个区域（起始，结束）坐标的DataFrame和另一个位置的DataFrame，这些位置可能或可能不在给定区域内。例如：

region = pd.DataFrame({'chromosome': [1, 1, 1, 1, 2, 2, 2, 2], 'start': [1000, 2000, 3000, 4000, 1000, 2000, 3000, 4000], 'end': [2000, 3000, 4000, 5000, 2000, 3000, 4000, 5000]})
position = pd.DataFrame({'chromosome': [1, 2, 1, 3, 2, 1, 1], 'BP': [1500, 1100, 10000, 2200, 3300, 400, 5000]})
print region
print position


   chromosome   end  start
0           1  2000   1000
1           1  3000   2000
2           1  4000   3000
3           1  5000   4000
4           2  2000   1000
5           2  3000   2000
6           2  4000   3000
7           2  5000   4000

      BP  chromosome
0   1500           1
1   1100           2
2  10000           1
3   2200           3
4   3300           2
5    400           1
6   5000           1

如果一个位置满足以下条件，则属于该地区：

position['BP'] >= region['start'] &
position['BP'] <= region['end'] &
position['chromosome'] == region['chromosome']

每个位置都保证最多只落在一个区域内，但有可能不属于任何区域。

最佳方式是如何合并这两个数据框，以便在位置处添加附加列，如果它位于任何区域内，则添加该区域。在这种情况下，大致输出如下：

      BP  chromosome  start  end
0   1500           1  1000   2000
1   1100           2  1000   2000
2  10000           1  NA     NA
3   2200           3  NA     NA
4   3300           2  3000   4000
5    400           1  NA     NA
6   5000           1  4000   5000

一种方法是编写一个函数来计算所需的关系，然后使用DataFrame.apply方法来实现：

def within(pos, regs):
    istrue = (pos.loc['chromosome'] == regs['chromosome']) & (pos.loc['BP'] >= regs['start']) &  (pos.loc['BP'] <= regs['end'])
    if istrue.any():
        ind = regs.index[istrue].values[0]
        return(regs.loc[ind ,['start', 'end']])
    else:
        return(pd.Series([None, None], index=['start', 'end']))

position[['start', 'end']] = position.apply(lambda x: within(x, region), axis=1)
print position

      BP  chromosome  start   end
0   1500           1   1000  2000
1   1100           2   1000  2000
2  10000           1    NaN   NaN
3   2200           3    NaN   NaN
4   3300           2   3000  4000
5    400           1    NaN   NaN
6   5000           1   4000  5000

但我希望有一种比每次O(N)时间的比较更优化的方法。谢谢！

- dylkot

Answer 1

一种解决方法是在chromosome上进行内连接，排除违规行，然后在position上进行左连接：

>>> df = pd.merge(position, region, on='chromosome', how='inner')
>>> idx = (df['BP'] < df['start']) | (df['end'] < df['BP'])  # violating rows
>>> pd.merge(position, df[~idx], on=['BP', 'chromosome'], how='left')
      BP  chromosome   end  start
0   1500           1  2000   1000
1   1100           2  2000   1000
2  10000           1   NaN    NaN
3   2200           3   NaN    NaN
4   3300           2  4000   3000
5    400           1   NaN    NaN
6   5000           1  5000   4000

- behzad.nouri

---

很遗憾，我的数据文件太大了，无法在内存中容纳df。我正在尝试找到一种替代方案，在其中使用多索引将位置和区域加载到数据帧中，以染色体作为外部索引，然后独立地对每个染色体进行合并。我现在正在编写这段代码，但如果有人知道更好的方法，请告诉我。谢谢。 - dylkot

Answer 2

我在处理自己的大型数据集时发现解决这个问题的最佳方法是使用bedtools的intersect方法，由pybedtools（http://pythonhosted.org/pybedtools/）封装为python。因为问题实际上归结为交集两个区域集（其中之一在本例中仅长度为1）。

- dylkot