python - 在 Pandas 中保留具有百分比重叠范围的行
问题描述
我有一个包含列的数据框:
[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'])
解决方案
您可以在所有范围之间进行笛卡尔连接,然后找到每对的长度和重叠百分比,并根据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
PS请注意,在您的示例中应该发生什么并不是很清楚。由于您的输出中没有它,我假设(希望是正确的)您对输出中的零长度范围不感兴趣
PPS 在pandas
1.2.0+ 中有一个新的笛卡尔连接语法,方法中带有how=cross
参数merge
。我在回答中使用了一个带有虚拟变量的版本k=1
,它更冗长,但与旧版本兼容
推荐阅读
- mysql - 在mysql批处理脚本中使用If语句而不创建过程
- python - 与 IDL 方法相比,在 Python 中使用 unpack 读取二进制文件
- android - android中MediaRecorder.stop()函数的运行时异常
- anylogic - Anylogic:如何将 2 个队列汇聚到一个延迟块中?
- optimization - 就变量和约束的数量而言,docplex(python 的 cplex 版本)的限制是什么?
- unix - Jenkins 预定义的环境变量
- sql-server - Azure 数据工厂处理的行数
- python - 我是否必须不断更改默认的 python 版本?
- ios - 具有 100 个或更多航点的 Google Direction API
- ios - 当 ios webview 应用程序中的 url 更改时,如何更改视图?