python - 在 Pandas 中保留具有百分比重叠范围的行

Question

我有一个包含列的数据框:

[id, range_start, range_stop, score] 

如果两行的范围重叠 x我保留得分较高的行的百分比。但是，我很困惑如何拉出与其他范围没有重叠的行。我正在使用嵌套循环和递归将重叠范围压缩到一个新的数据帧中。但是，当我寻找非重叠行时，这种结构会导致保留所有行。

## This is my function to recursively select the highest scoring overlapping regions

def overlap_retention(df_overlap, threshold, df_nonoverlap=None):
     if df_nonoverlap != None:
          df_nonoverlap = pd.DataFrame()
     
     df_overlap = pd.DataFrame() 
    
     for index, row in x.iterrows():
          rs = row['range_start']
          re = row['range_end']

          ## Silly nested loop to compare ranges between all rows 
          for index2, row2 in x.drop(index).iterrows():   
               rs2 = row2['range_start']
               re2 = row2['range_end']
               readRegion=[*range(rs,re,1)]
               refRegion=[*range(rs2,re2,1)]
               regionUnion = set(readRegion).intersection(set(refRegion))
               overlap_length = len(regionUnion)
            
               overlap_min = min(rs, rs2)
               overlap_max = max(re, re2)
               overlap_full_range = overlap_max-overlap_min

               overlap_percentage = (overlap_length/overlap_full_range)*100

               ## Check if they overlap by x_percentage and retain the higher score
               if overlap_percentage>x_percentage:
                    evalue = row['score']
                    evalue_2 = row2['score']
            
                    if evalue_2 > evalue:
                          df_overlap = df_overlap.append(row2)
                    else:
                         df_overlap = df_overlap.append(row)
#----------------------------------------------------------
                ## How to find non-overlapping rows without pulling everything?
               else:
                    df_nonoverlap = df_nonoverlap.append(row)
# ---------------------------------------------

          ### Recursion here to condense overlapped list further
          if len(df_overlap)>1:
              overlap_retention(df_overlap, threshold, df_nonoverlap)
          else:
              return(df_nonoverlap)

示例输入如下:

data = {'id':['id1', 'id2', 'id3', 'id4', 'id5', 'id6'],
       'range_start':[1,12,11,1,20, 10],
       'range_end':[4,15,15,6,23,16],
       'score':[3,1,8,2,5,1]}
input = pd.DataFrame(data, columns=['id', 'range_start', 'range_end', 'score'])

所需的输出可以根据重叠阈值而改变。在上面的例子中 id1和 id4既可以保留也可以简单地保留 id1取决于重叠阈值:

data = {'id':['id1', 'id3', 'id5'],
       'range_start':[1,11,20],
       'range_end':[4,15,23],
       'score':[3,8,5]}
output = pd.DataFrame(data, columns=['id', 'range_start', 'range_end', 'score'])

Answer 1

您可以在所有范围之间进行笛卡尔连接，然后找到每对重叠的长度和百分比，并根据 x_overlap 对其进行过滤。临界点。
之后，对于每个范围，我们可以找到得分最高的重叠范围(可能是范围本身，重叠率为 100%):

# set min overlap parameter
x_overlap = 0.5

# cartesian join all ranges
z = df.assign(k=1).merge(
    df.assign(k=1), on='k', suffixes=['_1', '_2'])

# find lengths of overlaps
z['len_overlap'] = (
    z[['range_end_1', 'range_end_2']].min(axis=1) -
    z[['range_start_1', 'range_start_2']].max(axis=1)).clip(0)

# we're only interested in cases where ranges overlap, so the total
# range is the range between min(start1, start2) and max(end1, end2)
z['len_total'] = (
    z[['range_end_1', 'range_end_2']].max(axis=1) -
    z[['range_start_1', 'range_start_2']].min(axis=1)).clip(0)

# find % overlap and filter out pairs above threshold
# these include 'pairs' where a range is paired to itself
z['pct_overlap'] = z['len_overlap'] / z['len_total']
z = z[z['pct_overlap'] > x_overlap]

# for each range find an overlapping range with the highest score
# (could be the range itself)
z = z.sort_values('score_2').groupby('id_1')['id_2'].last()

# filter the inputs
df_out = df[df['id'].isin(z)]

df_out

输出:

    id  range_start  range_end  score
0  id1            1          4      3
2  id3           11         15      8
4  id5           20         23      5

附言请注意，目前还不清楚 id4 会发生什么情况。在你的例子中。由于您的输出中没有它，我假设(希望正确)您对输出中的零长度范围不感兴趣
P.P.S. pandas 中有笛卡尔连接的新语法1.2.0+ 与 how=cross merge 中的参数方法。我在回答中使用了一个带有虚拟变量的版本 k=1 , 更冗长，但与旧版本兼容