Pandas性能警告：DataFrame高度碎片化问题排查求助

问题背景 #

通过现有DataFrame的简单计算生成新列时触发了Pandas的PerformanceWarning，代码能输出正确结果，但由于需要在优化器中多次运行，大量警告可能导致优化器分析中断。未显式使用frame.insert()方法，却触发了DataFrame碎片化警告。

核心代码 #

data_join['Ele_total'] = data_ele.sum(axis=1)
data_join['PV_total'] = data_pv.sum(axis=1)
data_join['SC'] = np.where(data_join['PV_total']>data_join['Ele_total'], data_join['Ele_total'], data_join['PV_total'])
data_join['SC%'] = np.where(data_join['PV_total']!= 0,round((data_join['SC']/data_join['PV_total'])*100,0),0)
data_join['SS%'] = np.where(data_join['Ele_total']!= 0,round((data_join['SC']/data_join['Ele_total'])*100,0),0)
data_join['LOLP'] = data_join['Ele_total']>data_join['PV_total']
data_join['E_tg'] = data_join['PV_total']-data_join['SC']
data_join['E_fg'] = data_join['Ele_total']-data_join['SC']
data_join['Ei'] = data_join['E_tg']-data_join['E_fg']
data_join['NGIP'] = data_join['Ei'].abs()<(GRID_LIM*n_build)
data_join['PAL'] = data_join['Ei'].abs()>(PEAK_LIM*n_build)
data_join['CO2'] = data_CO2['GWP']
data_join['CO2_net'] = data_CO2['GWP']*data_join['SC']
data_join['CO2_tot'] = data_CO2['GWP']*(data_join['E_tg']+data_join['SC'])

cash_flow = 0
npv = []
data_join_npv = pd.DataFrame()

for i in range (0,25):
    if i == 0:
        data_join_npv['PV_total_res_{}'.format(i)] = data_join_res['PV_total']
        data_join_npv['PV_total_ind_{}'.format(i)] = data_join_ind['PV_total']
    else:
        data_join_npv['PV_total_res_{}'.format(i)] = data_join_npv['PV_total_res_{}'.format(i-1)]*(1-d)
        data_join_npv['PV_total_ind_{}'.format(i)] = data_join_npv['PV_total_ind_{}'.format(i-1)]*(1-d)
    
    data_join_npv['SC_res_{}'.format(i)] = np.where(data_join_npv['PV_total_res_{}'.format(i)]>data_join_res['Ele_total'], data_join_res['Ele_total'], data_join_npv['PV_total_res_{}'.format(i)])
    data_join_npv['SC_ind_{}'.format(i)] = np.where(data_join_npv['PV_total_ind_{}'.format(i)]>data_join_ind['Ele_total'], data_join_ind['Ele_total'], data_join_npv['PV_total_ind_{}'.format(i)])
    data_join_npv['E_tg_res_{}'.format(i)] = data_join_npv['PV_total_res_{}'.format(i)]-data_join_npv['SC_res_{}'.format(i)]
    data_join_npv['E_tg_ind_{}'.format(i)] = data_join_npv['PV_total_ind_{}'.format(i)]-data_join_npv['SC_ind_{}'.format(i)]
    data_join_npv['E_fg_res_{}'.format(i)] = data_join_res['Ele_total']-data_join_npv['SC_res_{}'.format(i)]
    data_join_npv['E_fg_ind_{}'.format(i)] = data_join_ind['Ele_total']-data_join_npv['SC_ind_{}'.format(i)]
    cash = float(data_join_npv['SC_res_{}'.format(i)].sum())*COST_OF_ENERGY_RES + float(data_join_npv['E_tg_res_{}'.format(i)].sum())*VALUE_OF_ENERGY - float(data_join_npv['E_fg_res_{}'.format(i)].sum())*COST_OF_ENERGY_RES + float(data_join_npv['SC_ind_{}'.format(i)].sum())*COST_OF_ENERGY_IND + float(data_join_npv['E_tg_ind_{}'.format(i)].sum())*VALUE_OF_ENERGY - float(data_join_npv['E_fg_ind_{}'.format(i)].sum())*COST_OF_ENERGY_IND - OM_COST*total_pv
    cash_flow += cash/((1+DISC_RATE)**(i+1))
    npv.append(-in_inv+cash_flow)

警告信息 #

C:\Users\Giacomo\Desktop\150\insert_data.py:342: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`
  data_join_npv['E_tg_res_{}'.format(i)] = data_join_npv['PV_total_res_{}'.format(i)]-data_join_npv['SC_res_{}'.format(i)]
C:\Users\Giacomo\Desktop\150\insert_data.py:343: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`
  data_join_npv['E_tg_ind_{}'.format(i)] = data_join_npv['PV_total_ind_{}'.format(i)]-data_join_npv['SC_ind_{}'.format(i)]
C:\Users\Giacomo\Desktop\150\insert_data.py:344: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`
  data_join_npv['E_fg_res_{}'.format(i)] = data_join_res['Ele_total']-data_join_npv['SC_res_{}'.format(i)]
C:\Users\Giacomo\Desktop\150\insert_data.py:345: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`
  data_join_npv['E_fg_ind_{}'.format(i)] = data_join_ind['Ele_total']-data_join_npv['SC_ind_{}'.format(i)]
C:\Users\Giacomo\Desktop\150\insert_data.py:337: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`
  data_join_npv['PV_total_res_{}'.format(i)] = data_join_npv['PV_total_res_{}'.format(i-1)]*(1-d)
C:\Users\Giacomo\Desktop\150\insert_data.py:338: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`
  data_join_npv['PV_total_ind_{}'.format(i)] = data_join_npv['PV_total_ind_{}'.format(i-1)]*(1-d)
C:\Users\Giacomo\Desktop\150\insert_data.py:340: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`
  data_join_npv['SC_res_{}'.format(i)] = np.where(data_join_npv['PV_total_res_{}'.format(i)]>data_join_res['Ele_total'], data_join_res['Ele_total'], data_join_npv['PV_total_res_{}'.format(i)])
C:\Users\Giacomo\Desktop\150\insert_data.py:341: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()`
  data_join_npv['SC_ind_{}'.format(i)] = np.where(data_join_npv['PV_total_ind_{}'.format(i)]>data_join_ind['Ele_total'], data_join_ind['Ele_total'], data_join_npv['PV_total_ind_{}'.format(i)])
C:\Users\Giacomo\Desktop\150\insert_data.py:342: PerformanceWarning: DataFrame is highly fragmented.  This is usually the result of calling `frame.insert` many times, which has poor performance.  Consider joining all columns at once using pd.concat(axis=1) instead. To get a de-fragmented frame, use `newframe = frame.copy()

解决方案 #

1. 预先用字典存储列，最后一次性合并（推荐） #

循环中逐列添加到DataFrame会导致内部存储块碎片化，即使没显式调用insert，赋值新列的操作底层也会触发类似逻辑。改用字典存储每一列的计算结果，最后用pd.concat合并成DataFrame，能从根源避免碎片化。

修改后的代码示例：

cash_flow = 0
npv = []
# 用字典存储所有需要的列
cols_dict = {}

for i in range(0,25):
    # 计算PV_total列
    if i == 0:
        cols_dict['PV_total_res_{}'.format(i)] = data_join_res['PV_total']
        cols_dict['PV_total_ind_{}'.format(i)] = data_join_ind['PV_total']
    else:
        cols_dict['PV_total_res_{}'.format(i)] = cols_dict['PV_total_res_{}'.format(i-1)]*(1-d)
        cols_dict['PV_total_ind_{}'.format(i)] = cols_dict['PV_total_ind_{}'.format(i-1)]*(1-d)
    
    # 计算SC列
    cols_dict['SC_res_{}'.format(i)] = np.where(cols_dict['PV_total_res_{}'.format(i)]>data_join_res['Ele_total'], data_join_res['Ele_total'], cols_dict['PV_total_res_{}'.format(i)])
    cols_dict['SC_ind_{}'.format(i)] = np.where(cols_dict['PV_total_ind_{}'.format(i)]>data_join_ind['Ele_total'], data_join_ind['Ele_total'], cols_dict['PV_total_ind_{}'.format(i)])
    
    # 计算E_tg和E_fg列
    cols_dict['E_tg_res_{}'.format(i)] = cols_dict['PV_total_res_{}'.format(i)] - cols_dict['SC_res_{}'.format(i)]
    cols_dict['E_tg_ind_{}'.format(i)] = cols_dict['PV_total_ind_{}'.format(i)] - cols_dict['SC_ind_{}'.format(i)]
    cols_dict['E_fg_res_{}'.format(i)] = data_join_res['Ele_total'] - cols_dict['SC_res_{}'.format(i)]
    cols_dict['E_fg_ind_{}'.format(i)] = data_join_ind['Ele_total'] - cols_dict['SC_ind_{}'.format(i)]
    
    # 计算现金流和NPV
    cash = float(cols_dict['SC_res_{}'.format(i)].sum())*COST_OF_ENERGY_RES + float(cols_dict['E_tg_res_{}'.format(i)].sum())*VALUE_OF_ENERGY - float(cols_dict['E_fg_res_{}'.format(i)].sum())*COST_OF_ENERGY_RES + float(cols_dict['SC_ind_{}'.format(i)].sum())*COST_OF_ENERGY_IND + float(cols_dict['E_tg_ind_{}'.format(i)].sum())*VALUE_OF_ENERGY - float(cols_dict['E_fg_ind_{}'.format(i)].sum())*COST_OF_ENERGY_IND - OM_COST*total_pv
    cash_flow += cash/((1+DISC_RATE)**(i+1))
    npv.append(-in_inv+cash_flow)

# 最后一次性合并所有列到DataFrame
data_join_npv = pd.concat(cols_dict.values(), axis=1, keys=cols_dict.keys())

2. 定期复制DataFrame消除碎片化 #

按照警告提示，在循环中定期调用copy()方法重新生成一个连续存储的DataFrame，能临时解决碎片化问题。比如在每轮循环结束后复制：

cash_flow = 0
npv = []
data_join_npv = pd.DataFrame()

for i in range (0,25):
    if i == 0:
        data_join_npv['PV_total_res_{}'.format(i)] = data_join_res['PV_total']
        data_join_npv['PV_total_ind_{}'.format(i)] = data_join_ind['PV_total']
    else:
        data_join_npv['PV_total_res_{}'.format(i)] = data_join_npv['PV_total_res_{}'.format(i-1)]*(1-d)
        data_join_npv['PV_total_ind_{}'.format(i)] = data_join_npv['PV_total_ind_{}'.format(i-1)]*(1-d)
    
    data_join_npv['SC_res_{}'.format(i)] = np.where(data_join_npv['PV_total_res_{}'.format(i)]>data_join_res['Ele_total'], data_join_res['Ele_total'], data_join_npv['PV_total_res_{}'.format(i)])
    data_join_npv['SC_ind_{}'.format(i)] = np.where(data_join_npv['PV_total_ind_{}'.format(i)]>data_join_ind['Ele_total'], data_join_ind['Ele_total'], data_join_npv['PV_total_ind_{}'.format(i)])
    data_join_npv['E_tg_res_{}'.format(i)] = data_join_npv['PV_total_res_{}'.format(i)]-data_join_npv['SC_res_{}'.format(i)]
    data_join_npv['E_tg_ind_{}'.format(i)] = data_join_npv['PV_total_ind_{}'.format(i)]-data_join_npv['SC_ind_{}'.format(i)]
    data_join_npv['E_fg_res_{}'.format(i)] = data_join_res['Ele_total']-data_join_npv['SC_res_{}'.format(i)]
    data_join_npv['E_fg_ind_{}'.format(i)] = data_join_ind['Ele_total']-data_join_npv['SC_ind_{}'.format(i)]
    
    # 复制DataFrame消除碎片化
    data_join_npv = data_join_npv.copy()
    
    cash = float(data_join_npv['SC_res_{}'.format(i)].sum())*COST_OF_ENERGY_RES + float(data_join_npv['E_tg_res_{}'.format(i)].sum())*VALUE_OF_ENERGY - float(data_join_npv['E_fg_res_{}'.format(i)].sum())*COST_OF_ENERGY_RES + float(data_join_npv['SC_ind_{}'.format(i)].sum())*COST_OF_ENERGY_IND + float(data_join_npv['E_tg_ind_{}'.format(i)].sum())*VALUE_OF_ENERGY - float(data_join_npv['E_fg_ind_{}'.format(i)].sum())*COST_OF_ENERGY_IND - OM_COST*total_pv
    cash_flow += cash/((1+DISC_RATE)**(i+1))
    npv.append(-in_inv+cash_flow)

3. 临时禁用该警告（不推荐，仅临时应急） #

如果确认性能不受影响，只是想避免警告干扰，可以用warnings模块禁用特定警告：

import warnings
from pandas.errors import PerformanceWarning

# 禁用PerformanceWarning
warnings.filterwarnings("ignore", category=PerformanceWarning)

# 你的原有代码...

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