向DataFrame添加新列触发PerformanceWarning的优化咨询

我有一个列格式为YYYY-WW的数据透视表，因为不是所有年周组合都存在，需要计算每周价格与上周价格的差值及百分比。功能已经实现，但触发了如下PerformanceWarning：

/var/folders/b6/jndhzshn3hlbwyrdsjzj2znw0000gn/T/ipykernel_28918/446450422.py:38: 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()`
  df_pivot[f"% {year} [{week:02d}-{prev_week:02d}]"] = (df_pivot[current_week_colname] / df_pivot[prev_week_colname]) - 1
/var/folders/b6/jndhzshn3hlbwyrdsjzj2znw0000gn/T/ipykernel_28918/446450422.py:37: 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()`
  df_pivot[f"$ {year} [{week:02d}-{prev_week:02d}]"] = df_pivot[current_week_colname] - df_pivot[prev_week_colname]
/var/folders/b6/jndhzshn3hlbwyrdsjzj2znw0000gn/T/ipykernel_28918/446450422.py:38: 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()`
  df_pivot[f"% {year} [{week:02d}-{prev_week:02d}]"] = (df_pivot[current_week_colname] / df_pivot[prev_week_colname]) - 1

原运行代码如下：

...
years = ["2020", "2021", "2022"]
df_pivot_colnames = tuple(df_pivot.columns)
...
for year in years:
    for week in range(2, 53):
        prev_week = week - 1

        current_week_colname = f"{year}-{week:02d}"
        prev_week_colname = f"{year}-{prev_week:02d}"
        new_week_colname = f"{year} [{week:02d}-{prev_week:02d}]"

        if (
            current_week_colname in df_pivot_colnames
            and prev_week_colname in df_pivot_colnames
        ):
            df_pivot[f"$ {new_week_colname}"] = (
                df_pivot[current_week_colname] - df_pivot[prev_week_colname]
            )
            df_pivot[f"% {new_week_colname}"] = (
                df_pivot[current_week_colname] / df_pivot[prev_week_colname]
            ) - 1

df_pivot.to_csv(source_csv_path + "output_" + csv)

我理解警告的意思，但因为列名随加载的DataFrame变化，不知道怎么用pd.concat来优化。

核心思路是先把所有要新增的计算列存到一个列表里，最后一次性用pd.concat合并到原DataFrame，避免循环中反复插入列导致的碎片化问题。

优化后的代码 #

...
years = ["2020", "2021", "2022"]
df_pivot_colnames = tuple(df_pivot.columns)
# 初始化空列表存储新生成的列
new_columns = []

for year in years:
    for week in range(2, 53):
        prev_week = week - 1

        current_week_colname = f"{year}-{week:02d}"
        prev_week_colname = f"{year}-{prev_week:02d}"
        new_week_colname = f"{year} [{week:02d}-{prev_week:02d}]"

        if (
            current_week_colname in df_pivot_colnames
            and prev_week_colname in df_pivot_colnames
        ):
            # 计算价格差值列，设置列名后加入列表
            diff_col = df_pivot[current_week_colname] - df_pivot[prev_week_colname]
            diff_col.name = f"$ {new_week_colname}"
            new_columns.append(diff_col)
            
            # 计算百分比变化列，设置列名后加入列表
            pct_col = (df_pivot[current_week_colname] / df_pivot[prev_week_colname]) - 1
            pct_col.name = f"% {new_week_colname}"
            new_columns.append(pct_col)

# 一次性合并所有新列到原DataFrame
df_pivot = pd.concat([df_pivot] + new_columns, axis=1)

df_pivot.to_csv(source_csv_path + "output_" + csv)

优化说明 #

• 循环中仅生成计算后的Series并添加到列表，不修改原DataFrame，彻底避免反复插入列导致的内存碎片化。
• pd.concat一次性合并所有列，性能远高于循环插入，同时消除了PerformanceWarning。
• 保留了原有的动态列名判断逻辑，完全适配不同DataFrame的列变化情况。

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