大数据集统计特征计算值错误,求基于pandas loc/iloc的实现方案
问题根源分析
你在大数据集下的异常和列表转Series赋值无关,核心问题是全局变量ind_maximum、ind_minimum没有在遇到新ID时重置,前一个ID的最大最小值状态会污染后一个ID的计算结果。你提供的小样本里第二个ID的首条记录刚好落在前一个ID的最值区间内,所以没暴露出问题,大数据集多ID场景下必然会出现特征异常。另外你用df.T.iteritems()遍历行的写法效率极低,也容易出现索引错位问题。
基于.loc的实现方案
使用.loc直接操作DataFrame确实更安全稳定,它会直接根据行/列标签定位赋值,不会出现外置列表和DataFrame索引不匹配、长度不一致导致的错位问题,示例代码如下:
import pandas as pd import numpy as np df = pd.DataFrame([[58685991,'2020-06-01',2], [58685991,'2020-06-01',1], [58685991,'2020-06-01',0], [58685991,'2020-12-05',7], [57839709,'2020-12-01',5], [57839709,'2021-01-08',3]],columns=['ID','DATE','QTD']) def monthdelta(a,b): a1,a2,a3 = (int(k) for k in a.split('-')) b1,b2,b3 = (int(k) for k in b.split('-')) return (a1*12+a2) - (b1*12+b2) # 先初始化目标列,避免链式索引风险 df['MEAN'] = 0.0 df['STDEV'] = 0.0 df['TOTAL'] = 0 df['MAX'] = 0 df['MIN'] = 0 # 状态字典仅保存跨ID不共享的统计量 startdate = {} sums = {} sumsqs = {} for idx, row in df.iterrows(): current_id = row['ID'] current_qtd = row['QTD'] current_date = row['DATE'] if current_id not in startdate: # 新ID初始化所有状态,完全隔离不同ID的计算 startdate[current_id] = current_date sums[current_id] = current_qtd sumsqs[current_id] = current_qtd ** 2 current_max = current_qtd current_min = current_qtd # 直接通过行索引idx给当前行赋值 df.loc[idx, 'MEAN'] = current_qtd df.loc[idx, 'STDEV'] = 0.0 df.loc[idx, 'TOTAL'] = current_qtd df.loc[idx, 'MAX'] = current_max df.loc[idx, 'MIN'] = current_min else: sums[current_id] += current_qtd sumsqs[current_id] += current_qtd ** 2 delta = monthdelta(current_date, startdate[current_id]) + 1 # 仅在当前ID范围内计算最值,避免跨ID污染 current_max = max(df.loc[df['ID'] == current_id, 'MAX'].max(), current_qtd) current_min = min(df.loc[df['ID'] == current_id, 'MIN'].min(), current_qtd) # 赋值 df.loc[idx, 'MEAN'] = sums[current_id] / delta df.loc[idx, 'TOTAL'] = sums[current_id] df.loc[idx, 'STDEV'] = 0.0 if delta == 1 else np.sqrt((delta * sumsqs[current_id] - sums[current_id] ** 2) / delta) df.loc[idx, 'MAX'] = current_max df.loc[idx, 'MIN'] = current_min
大数据集优化建议
如果数据量超过10万行,逐行遍历的效率会非常低,建议改用pandas分组滚动计算方案:先把DATE列转为datetime格式、按ID和月份排序后,用groupby('ID').rolling()实现分组增量统计,不需要手动维护状态变量,性能和稳定性都会大幅提升。
内容的提问来源于stack exchange,提问作者Peter
相关产品推荐
相关产品推荐

