如何在Pandas中基于另一个DataFrame的区间条件筛选行？

看起来你需要从df1中保留那些染色体匹配，且自身区间完全被df2中某一行区间包含的记录。这里有两种实用的方法来实现：

方法1：合并后筛选（直观易懂） #

这种方法通过合并两个DataFrame，逐对检查匹配条件，最后提取符合要求的行：

import pandas as pd

# 构建你的DataFrames
df1 = pd.DataFrame({'chr': {0: 7, 1: 7, 2: 7, 3: 7, 4: 7, 5: 7, 6: 7}, 
                    0: {0: 55241686, 1: 55242415, 2: 55248986, 3: 55259412, 4: 55260459, 5: 55266410, 6: 55268009}, 
                    1: {0: 55241736, 1: 55242513, 2: 55249171, 3: 55259567, 4: 55260534, 5: 55266556, 6: 55268064}})

df2 = pd.DataFrame({'chr': {0: 7, 1: 7, 2: 7, 3: 7, 4: 7, 5: 7, 6: 7, 7: 7, 8: 7, 9: 7, 10: 7, 11: 7, 12: 7, 13: 7, 14: 7, 15: 7, 16: 7, 17: 7, 18: 7, 19: 7}, 
                    's': {0: 55241646, 1: 55241658, 2: 55241690, 3: 55241718, 4: 55241721, 5: 55241722, 6: 55241727, 7: 55241732, 8: 55242454, 9: 55242457, 10: 55242488, 11: 55242511, 12: 55248991, 13: 55248995, 14: 55248995, 15: 55249000, 16: 55249022, 17: 55249036, 18: 55249053, 19: 55249057}, 
                    'e': {0: 55241646, 1: 55241658, 2: 55241690, 3: 55241718, 4: 55241721, 5: 55241722, 6: 55241727, 7: 55241732, 8: 55242454, 9: 55242457, 10: 55242488, 11: 55242511, 12: 55248991, 13: 55248995, 14: 55248995, 15: 55249000, 16: 55249022, 17: 55249036, 18: 55249053, 19: 55249057}, 
                    'ref': {0: 'T', 1: 'T', 2: 'A', 3: 'G', 4: 'C', 5: 'G', 6: 'G', 7: 'A', 8: 'G', 9: 'G', 10: 'C', 11: 'G', 12: 'C', 13: 'G', 14: 'G', 15: 'G', 16: 'G', 17: 'G', 18: 'C', 19: 'C'}, 
                    'alt': {0: 'C', 1: 'G', 2: 'C', 3: 'A', 4: 'T', 5: 'A', 6: 'A', 7: 'G', 8: 'A', 9: 'A', 10: 'T', 11: 'A', 12: 'G', 13: 'A', 14: 'C', 15: 'A', 16: 'C', 17: 'A', 18: 'G', 19: 'T'}})

# 按chr合并两个表，方便逐对检查条件
merged = df1.merge(df2, on='chr', how='left')

# 生成匹配条件的掩码：chr相同，且df1的起始≥df2起始，df1的结束≤df2结束
mask = (merged[0] >= merged['s']) & (merged[1] <= merged['e'])

# 提取符合条件的df1行，去重避免重复（同一df1行可能匹配多个df2行）
result = df1.loc[merged[mask].index.unique()]

print(result)

方法2：使用IntervalIndex（高效处理大数据集） #

如果你的数据量很大，全量合并会占用过多内存，这种分组+区间索引的方法更高效：

# 按chr分组，为每个chr创建df2的区间索引（闭区间）
df2_intervals = df2.groupby('chr').apply(
    lambda x: pd.IntervalIndex.from_arrays(x['s'], x['e'], closed='both')
)

def is_contained(row):
    # 获取当前行chr对应的区间集合
    intervals = df2_intervals.get(row['chr'], None)
    if intervals is None:
        return False
    # 检查当前行的区间是否被任何一个df2的区间包含
    current_interval = pd.Interval(row[0], row[1], closed='both')
    return intervals.contains(current_interval).any()

# 筛选符合条件的行
result = df1[df1.apply(is_contained, axis=1)]

print(result)

说明 #

• 两种方法都严格遵循你的匹配条件：row_df1['chr']==row_df2['chr'] 且 row_df1[0] >= row_df2['s'] 且 row_df1[1] <= row_df2['e']
• 注意你的示例df2中所有区间都是单点（s=e），所以只有当df1的起始和结束都等于这个单点时才会被保留。如果实际df2是真正的区间，代码依然适用。

内容的提问来源于stack exchange，提问作者zeissmania