如何基于二级索引管理DataFrame行合并与列合并，适配动态数据类

问题背景 #

现有如下课程与课时一对多映射的DataFrame：

course_id       course_name  lesson_id     lesson_title
0          0          Learn C#          1              foo
1          0          Learn C#          2              bar
2          0          Learn C#          3              baz
3          1  Origami together          1        the crane
4          1  Origami together          2  crease patterns
5          2        WIP course          1        the first

需要实现以下效果：

• 课程列（course_id、course_name）跨行显示，同一课程的多行课时仅保留第一行的课程信息
• lesson_id和lesson_title归入统一的lessons二级列下
• 导出Excel后呈现单元格合并的排版效果

且要求方案具备扩展性：后续新增Course类属性时，无需大幅修改代码结构。

解决方案 #

步骤1：构建多级列索引 #

先将课时相关列归入lessons二级索引下，课程列保持一级索引，既满足列分组需求，又能灵活扩展课程属性。

import pandas as pd

# 初始DataFrame（示例）
df = pd.DataFrame({
    'course_id': [0, 0, 0, 1, 1, 2],
    'course_name': ['Learn C#', 'Learn C#', 'Learn C#', 'Origami together', 'Origami together', 'WIP course'],
    'lesson_id': [1, 2, 3, 1, 2, 1],
    'lesson_title': ['foo', 'bar', 'baz', 'the crane', 'crease patterns', 'the first']
})

# 定义多级列结构：课程列对应('', '')，课时列对应('lessons', 子列名)
new_columns = [
    ('course_id', ''),
    ('course_name', ''),
    ('lessons', 'id'),
    ('lessons', 'title')
]
df.columns = pd.MultiIndex.from_tuples(new_columns)

步骤2：清空重复课程行的信息 #

通过duplicated标记同一课程的后续行，将这些行的课程列值设为空，为后续Excel合并做准备：

# 标记course_id重复的行
mask = df[('course_id', '')].duplicated()
# 清空重复行的课程列值
df.loc[mask, ('course_id', '')] = ''
df.loc[mask, ('course_name', '')] = ''

处理后的DataFrame结构如下：

lessons
   course_id       course_name         id            title
0          0          Learn C#          1              foo
1                                       2              bar
2                                       3              baz
3          1  Origami together          1        the crane
4                                       2  crease patterns
5          2        WIP course          1        the first

步骤3：导出Excel并实现单元格合并 #

pandas默认导出不会自动合并空值单元格，需要借助xlsxwriter引擎手动处理跨行合并：

with pd.ExcelWriter('courses_lessons.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, index=False, sheet_name='Courses')
    workbook = writer.book
    worksheet = writer.sheets['Courses']
    
    # 设置合并单元格的格式（可选，优化视觉效果）
    merge_format = workbook.add_format({
        'align': 'center',
        'valign': 'vcenter',
        'border': 1
    })
    
    # 按课程分组，获取每个课程的行范围
    course_ids = df[('course_id', '')].replace('', pd.NA).ffill()
    course_groups = course_ids.groupby(course_ids).indices
    
    # 遍历每个课程，合并对应的单元格
    for course_val, row_indices in course_groups.items():
        start_row = row_indices[0] + 1  # 表头占1行，行号从1开始
        end_row = row_indices[-1] + 1
        
        # 合并course_id列
        worksheet.merge_range(start_row, 0, end_row, 0, course_val, merge_format)
        # 合并course_name列，取该课程第一行的名称
        course_name = df.loc[row_indices[0], ('course_name', '')]
        worksheet.merge_range(start_row, 1, end_row, 1, course_name, merge_format)
    
    # 调整列宽（可选）
    worksheet.set_column(0, 3, 20)

方案扩展性说明 #

后续新增Course类属性（如course_description）时，仅需两步修改：

1. 在new_columns列表中新增对应项：('course_description', '')
2. 在清空重复行的代码中，新增该行：df.loc[mask, ('course_description', '')] = ''
   无需调整多级索引的核心逻辑，完全适配动态扩展需求。

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