如何将含列表的DataFrame写入Excel并使列表元素分占独立单元格？

问题背景 #

现有如下结构的DataFrame，其中Data列以列表形式存储重复数据，经过pivot转换后得到宽表：

import pandas as pd

d = {'Compound': ['A', 'A', 'A', 'B', 'B', 'B', 'C', 'C', 'C', 'C'],
     'Conc': [1, 0.5, 0.1, 1, 0.5, 0.1, 2, 1, 0.5, 0.1],
     'Data': [[100, 90, 80], [50, 40, 30], [10, 9.7, 8], 
              [20, 15, 10], [3, 4, 5, 6], [100, 110, 80],
              [30, 40, 50, 20], [10, 5, 9, 3], [2, 1, 2, 2], [1, 1, 0]]}

df = pd.DataFrame(data=d)
pivoted = df.pivot(index='Conc', columns='Compound', values='Data')

直接用to_excel导出时，每个单元格会显示完整的列表（如[100,90,80]），而我们需要将列表中的每个元素拆分到同一行的相邻独立单元格，同时保持Compound的表头对应并合并。

解决方案 #

步骤1：拆分列表数据为多列 #

先将每个Compound列下的列表拆分为多个子列，构建多级列索引的DataFrame：

# 拆分每个Compound列的列表为多列
expanded = []
for col in pivoted.columns:
    # 将列表拆分为多个列，用(Compound名称, 序号)作为列名
    col_expanded = pivoted[col].apply(pd.Series).rename(columns=lambda x: (col, x+1))
    expanded.append(col_expanded)

# 合并所有拆分后的列
final_df = pd.concat(expanded, axis=1)
# 转换为多级列索引
final_df.columns = pd.MultiIndex.from_tuples(final_df.columns)

步骤2：写入Excel并设置表头合并 #

使用openpyxl引擎写入Excel，同时合并Compound对应的表头单元格：

from openpyxl.styles import Alignment

with pd.ExcelWriter('output.xlsx', engine='openpyxl') as writer:
    final_df.to_excel(writer, sheet_name='Sheet1', index_label='Conc')
    worksheet = writer.sheets['Sheet1']
    
    # 遍历每个Compound，合并对应的表头单元格
    current_col = 2  # 从第二列开始（第一列为Conc列）
    for compound in final_df.columns.get_level_values(0).unique():
        # 获取当前Compound对应的子列数量
        col_num = len(final_df.columns[final_df.columns.get_level_values(0) == compound])
        # 合并第一行的表头单元格
        worksheet.merge_cells(start_row=1, start_column=current_col, 
                              end_row=1, end_column=current_col + col_num -1)
        # 设置合并后单元格的居中对齐
        header_cell = worksheet.cell(row=1, column=current_col)
        header_cell.value = compound
        header_cell.alignment = Alignment(horizontal='center')
        # 更新当前列位置
        current_col += col_num
    
    # 可选：隐藏第二行的子列序号（如果不需要显示）
    # worksheet.row_dimensions[2].hidden = True

执行上述代码后，Excel文件会呈现期望效果：每个Compound表头跨列合并，列表中的每个元素被写入同一行的相邻独立单元格。

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