解决Pandas DataFrame高度碎片化警告及替代代码失效问题

原代码触发警告 #

循环中执行以下代码向DataFrame添加列时，触发了PerformanceWarning：

row_data = dict(zip(keys, text))
df[row_data['EXAMINATION'].replace(":","")] = row_data['FINDING'].strip()
df[row_data['EXAMINATION'].replace(":","") + ' Further Comments'] = row_data['FURTHER COMMENTS'].strip()

警告信息：

C:\Users\XXX\AppData\Local\Temp\ipykernel_10588\1664916535.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()

替代代码失效情况 #

按照提示改用pd.concat后，输出结果与原代码不一致，无法将数据展平为目标格式：

df = pd.concat([df,pd.DataFrame([row_data['FINDING'].strip()],
        columns = [row_data['EXAMINATION'].replace(":","")])],axis=1,ignore_index=True)
                        
df = pd.concat([df,pd.DataFrame([row_data['FURTHER COMMENTS'].strip()],
        columns = [row_data['EXAMINATION'].replace(":","") + ' Further Comments'])],axis=1,ignore_index=True)

样例数据 #

row_data的样例如下：

{'EXAMINATION': 'PSA', 'FINDING': '1.80ug/L', 'FURTHER COMMENTS': 'Normal range 0.00-2.99ug/L'}
{'EXAMINATION': 'FIT Test', 'FINDING': 'YYY', 'FURTHER COMMENTS': 'XXX'}
{'EXAMINATION': 'Height:', 'FINDING': '1.78m', 'FURTHER COMMENTS': 'BB'}
{'EXAMINATION': 'Weight:', 'FINDING': '82kg', 'FURTHER COMMENTS': 'AA'}

完整代码 #

document = Document(f)
table = document.tables[2]

keys = None
for i, row in enumerate(table.rows):
    text = (cell.text for cell in row.cells)

# Establish the mapping based on the first row
# headers; these will become the keys of our dictionary
    if i == 0:
        keys = tuple(text)
        continue

    row_data = dict(zip(keys, text))
    df[row_data['EXAMINATION'].replace(":","")] = row_data['FINDING'].strip()
    df[row_data['EXAMINATION'].replace(":","") + ' Further Comments'] = row_data['FURTHER COMMENTS'].strip()
    #df = pd.concat([df,pd.DataFrame([row_data['FINDING'].strip()],
     #                               columns = [row_data['EXAMINATION'].replace(":","")])],axis=1,ignore_index=True)
                    
    #df = pd.concat([df,pd.DataFrame([row_data['FURTHER COMMENTS'].strip()],
     #                               columns = [row_data['EXAMINATION'].replace(":","") + ' Further Comments'])],axis=1,ignore_index=True)
df1 = pd.concat([df1,df], axis=0, ignore_index=True)

失效原因分析 #

1. 索引与行数不匹配：原代码是直接给df新增列，新列会自动填充到df的所有行；而你用pd.concat时，新创建的DataFrame只有1行，和原df行数不一致，合并后会出现缺失值，导致结构错乱。
2. ignore_index的副作用：设置ignore_index=True会重置列索引，把你自定义的列名（比如PSA、PSA Further Comments）覆盖成数字索引，完全破坏了原有的列命名逻辑。
3. 违背一次性合并的初衷：警告提示的核心是避免循环内多次修改DataFrame，而你只是把df[col] = val换成了循环内pd.concat，依然是逐次修改，既没解决性能问题，还引入了格式错误。

正确解决方案 #

先在循环内收集所有要添加的列数据，最后一次性合并到原DataFrame：

document = Document(f)
table = document.tables[2]

keys = None
cols_dict = {}
for i, row in enumerate(table.rows):
    text = (cell.text for cell in row.cells)
    if i == 0:
        keys = tuple(text)
        continue
    row_data = dict(zip(keys, text))
    col_base = row_data['EXAMINATION'].replace(":","")
    cols_dict[col_base] = row_data['FINDING'].strip()
    cols_dict[f"{col_base} Further Comments"] = row_data['FURTHER COMMENTS'].strip()

# 将收集的列转为DataFrame，和原df合并
new_cols_df = pd.DataFrame([cols_dict])
# 如果原df有多行，将新列广播到所有行
if len(df) > 1:
    new_cols_df = new_cols_df.loc[df.index].reset_index(drop=True)

df = pd.concat([df, new_cols_df], axis=1)
df1 = pd.concat([df1, df], axis=0, ignore_index=True)

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