使用pandas drop删除Excel列后保存文件未生效的技术求助

问题根因 #

你使用pandas执行的列删除操作仅作用于内存中的DataFrame对象，没有将处理后的结果写回你最终保存的wb2工作簿对象，所以生成的Excel文件看不到任何列删除效果。

修复方法 #

你需要在得到删除列后的DataFrame后，清空对应工作表的原有内容，再将处理后的数据写入对应工作表，完整修正代码如下：

#Filter xls and create worksheets
import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
import os

# 请自行补充定义这两个变量
readFileName = "你的原始文件路径.xlsx"
writer = "你的输出文件路径.xlsx"

print ("importing modules")
print ("copy data")

wb1 = load_workbook(filename=readFileName)
ws1 = wb1.worksheets[1]

wb2 = load_workbook(filename=writer)
ws2 = wb2.create_sheet(ws1.title)

for row in ws1:
    for cell in row:
        ws2[cell.coordinate].value = cell.value

wb2.save(writer)
print ("copied sheet2")

# 处理level1工作表
for t in range(len(wb2.sheetnames)):
    if wb2.sheetnames[t] == 'level1':
        break
source_df = pd.read_excel(writer, t)
result_df = source_df.drop(['ObjectID', 'GlobalID', 'WSSN.1', 'The Quarter you are Submitting for Review'], axis=1)
# 新增：将处理后的结果写回level1工作表
with pd.ExcelWriter(writer, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer_obj:
    result_df.to_excel(writer_obj, sheet_name='level1', index=False)

# 处理treatment工作表
for u in range(len(wb2.sheetnames)):
    if wb2.sheetnames[u] == 'treatment':
        break
source_df2 = pd.read_excel(writer, u)
result_df2 = source_df2.drop(['ObjectID', 'GlobalID', 'WSSN.1', 'The Quarter you are Submitting for Review', 'Enter in which Fiscal Year you are submitting for review:'], axis=1)
# 新增：将处理后的结果写回treatment工作表
with pd.ExcelWriter(writer, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer_obj:
    result_df2.to_excel(writer_obj, sheet_name='treatment', index=False)

print ("removed level1 fields")
print ("removed treatment fields")
wb2.close()
wb1.close()

补充说明 #

• 代码中新增了ExcelWriter写入逻辑，if_sheet_exists='replace'参数会直接覆盖原有工作表的内容，避免旧数据残留
• 如果你的pandas版本低于1.4.0，不支持if_sheet_exists参数，可以先删除原有工作表再新增同名工作表写入数据

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