如何用Pandas动态将DataFrame列表列转为无重复多单元格Excel格式？

需求说明 #

输入数据：

[{"id": 1, "name": "Alex", "projects": ["A", "B", "C"]}, {"id": 2, "name": "Bob", "projects": None}]

需要生成的Excel文件满足：

• projects列的每个列表元素单独占一行
• id和name列对应行自动合并，无重复内容
• 动态实现，无需手动指定单元格索引

实现步骤及代码 #

1. 数据预处理与展开 #

先将原始数据中的projects字段展开为多行，同时保留对应id和name，处理None值为空白行：

import pandas as pd
from itertools import groupby

# 原始数据
data = [
    {"id": 1, "name": "Alex", "projects": ["A", "B", "C"]},
    {"id": 2, "name": "Bob", "projects": None}
]

# 展开projects字段，处理空值
expanded_data = []
for item in data:
    projects = item["projects"] if item["projects"] is not None else []
    # 无项目时保留一行空值
    if not projects:
        expanded_data.append({
            "id": item["id"],
            "name": item["name"],
            "projects": ""
        })
    else:
        for proj in projects:
            expanded_data.append({
                "id": item["id"],
                "name": item["name"],
                "projects": proj
            })

# 生成DataFrame
df = pd.DataFrame(expanded_data)

2. 写入Excel并动态合并单元格 #

使用xlsxwriter作为引擎，按id分组计算合并范围，自动完成单元格合并：

# 创建Excel写入器
writer = pd.ExcelWriter("output.xlsx", engine="xlsxwriter")
df.to_excel(writer, index=False, sheet_name="Sheet1")
workbook = writer.book
worksheet = writer.sheets["Sheet1"]

# 定义格式：合并单元格居中+边框，普通单元格边框
merge_format = workbook.add_format({
    "align": "center",
    "valign": "vcenter",
    "border": 1
})
cell_format = workbook.add_format({"border": 1})

# 设置列宽并应用基础格式
worksheet.set_column(0, len(df.columns)-1, 15)
for row in range(len(df)):
    for col in range(len(df.columns)):
        worksheet.write(row+1, col, df.iloc[row, col], cell_format)

# 按id分组，动态计算合并范围
for _, group in groupby(enumerate(df["id"]), key=lambda x: x[1]):
    row_indices = [idx for idx, _ in group]
    start_row = row_indices[0] + 1  # 表头占第1行，数据从第2行开始
    end_row = row_indices[-1] + 1

    # 合并id列
    if start_row != end_row:
        worksheet.merge_range(start_row, 0, end_row, 0, df.iloc[start_row-1, 0], merge_format)
    else:
        worksheet.write(start_row, 0, df.iloc[start_row-1, 0], merge_format)
    
    # 合并name列
    if start_row != end_row:
        worksheet.merge_range(start_row, 1, end_row, 1, df.iloc[start_row-1, 1], merge_format)
    else:
        worksheet.write(start_row, 1, df.iloc[start_row-1, 1], merge_format)

# 保存文件
writer.close()

关键说明 #

• 数据展开：确保每个项目对应一行，无项目的用户保留一行空白，避免丢失数据
• 动态合并：通过groupby按id分组，自动获取每个用户的行范围，无需手动指定单元格索引
• 格式优化：统一设置边框和居中格式，提升表格可读性

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