Pandas DataFrame区间各位置计数、percent及order总和计算问题
解决方案
你可以基于现有布尔掩码的逻辑,对percent和order列做加权求和即可,完整实现代码如下:
import pandas as pd import numpy as np # 构造原始数据 df = pd.DataFrame( { 'start': [1,1,1,2,2,2,2,3,3,3,3,3,3,3], 'stop': [9,9,10,10,10,11,11,11,11,12,11,12,11,11], 'percent' : [0.51,0.29,0.92,0.60,0.10,0.12,0.60,0.30,0.10,0.42,0.51,0.51,0.51,0.10], 'order':[3,80,3,3,4,8,89,2,3,4,5,64,82,68] } ) max_pos = df[['start', 'stop']].values.max() pos_range = np.arange(1, max_pos + 1) # 生成布尔掩码:每行代表一个区间,每列代表一个位置,值为True表示该位置属于当前区间 mask = (df[['start']].values <= pos_range) & (pos_range <= df[['stop']].values) # 计算各指标 counts = mask.sum(axis=0) sum_percent = (mask * df['percent'].values.reshape(-1, 1)).sum(axis=0) sum_order = (mask * df['order'].values.reshape(-1, 1)).sum(axis=0) # 构造结果表 res = pd.DataFrame({ 'pos': pos_range, 'count': counts, 'sum_percent': sum_percent, 'sum_order': sum_order })
运行后输出的res和你给出的预期结果完全一致。
如果要处理更大规模的数据,也可以用区间差分的方法优化性能:
# 差分法实现(适合大数量级区间场景) max_pos = df[['start', 'stop']].values.max() # 初始化差分数组 diff_count = np.zeros(max_pos + 2, dtype=int) diff_percent = np.zeros(max_pos + 2, dtype=float) diff_order = np.zeros(max_pos + 2, dtype=int) # 遍历每个区间打差分标记 for _, row in df.iterrows(): s, e, p, o = row['start'], row['stop'], row['percent'], row['order'] diff_count[s] += 1 diff_count[e+1] -= 1 diff_percent[s] += p diff_percent[e+1] -= p diff_order[s] += o diff_order[e+1] -= o # 前缀和得到结果 count = np.cumsum(diff_count)[1:max_pos+1] sum_percent = np.cumsum(diff_percent)[1:max_pos+1] sum_order = np.cumsum(diff_order)[1:max_pos+1] # 构造结果表 res = pd.DataFrame({ 'pos': np.arange(1, max_pos+1), 'count': count, 'sum_percent': sum_percent, 'sum_order': sum_order })
该方法时间复杂度为O(N + M)(N是区间数量,M是最大位置值),远优于广播法的O(N*M),适合数据量大的场景。
内容的提问来源于stack exchange,提问作者user1845261
相关产品推荐
相关产品推荐

