Pandas处理Excel时如何删除含Unnamed的行并合并单元格保留数值

问题核心 #

• 导出Excel时出现大量Unnamed开头的无效列
• 无法自动合并同级表头单元格并保留原有数值

现有代码问题梳理 #

1. transpon函数没有返回值，导致后续拿到的new_array为空
2. 多余的中间存储读取流程，破坏了原有的多级索引结构，额外生成Unnamed列
3. 导出时未开启多级表头合并参数

修改后完整代码 #

import pandas as pd

def write_xls(df, file_name='test.xlsx'):
    writer = pd.ExcelWriter(file_name, engine='xlsxwriter')
    # 开启多级表头自动合并
    df.to_excel(writer, sheet_name='Worktest', merge_cells=True)
    writer.save()
    writer.close()

def transpon(array):
    df = pd.DataFrame.from_dict(array)
    # 直接构建符合要求的多层列索引结构
    new_df = df.unstack().to_frame().T
    # 给多级索引命名，对应目标表头层级
    new_df.columns.names = ['分类', '年份']
    return new_df

# 原始数据
array = {
    1: {2016: 22818888925.021294, 2017: 23215791927.55883, 2018: 24241415053.75489, 2019: 25526831987.89783, 2007: 18183059018.027966, 2008: 16868527277.51174, 2009: 18331809529.53519, 2010: 19724006864.797657, 2011: 20392912788.825798, 2012: 19580849153.840134, 2013: 22084393933.539394, 2014: 22030084567.47173, 2015: 21700822555.267574},
    2: {2016: 15081873888.10038, 2017: 15377071873.462284, 2018: 16253879799.12362, 2019: 16517025291.019705, 2007: 10038529774.615318, 2008: 11611749842.042978, 2009: 12040185617.510305, 2010: 12508816879.258118, 2011: 13711111085.100977, 2012: 13219008951.675236, 2013: 14209627046.021126, 2014: 15371996764.61913, 2015: 14417938720.997215},
    6: {2016: 0.6609381349660216, 2017: 0.6623539667069717, 2018: 0.6705004539991144, 2019: 0.6470456380505956, 2007: 0.5520814602571774, 2008: 0.6883677306864346, 2009: 0.6567919876164886, 2010: 0.6341924825418297, 2011: 0.6723468700662476, 2012: 0.6750988605150847, 2013: 0.6434239077958611, 2014: 0.6977729348945159, 2015: 0.6643959547744175}
}

new_array = transpon(array)
# 直接导出最终结果，无中间冗余读写流程
write_xls(new_array, 'test_new.xlsx')

修改说明 #

• 补全了transpon函数的返回值，修正了函数拼写错误
• 去掉冗余的中间Excel读写步骤，保留原始多级索引结构，从根源避免Unnamed列生成
• 导出时添加merge_cells=True参数，xlsxwriter会自动合并多级表头的相同层级单元格，完全匹配预期效果
• 给多级列索引添加命名，表头结构更清晰

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