如何将Pandas DataFrame按索引拆分输出为Excel单工作表多表格

现有如下格式的Pandas DataFrame：

'index' |   'path'  | 'Key'  |   'Value'  

    1        L1         A          1
   1.1     L1/L11       B          2      
   1.1     L1/L11       C          2 
  1.1.2  L1/L11/L111    CA         4
   1.2     L1/L12                    
  1.2.1  L1/L12/L121    CC         6 
   ...       ...        ..        ...

需要将其输出到Excel单个工作表中，要求：

• Excel列A：索引（index）
• Excel列B：路径（path）
• Excel列C、D：由Key和Value组成的表格，每个新索引对应生成一个表格；若Key或Value为空，则仅写入索引和路径，不生成表格。

预期输出格式如下：

A     |    B    |    C    |    D    |
     1          L1
                         Key      Value 
                          A         1
    1.1       L1/L11   
                         Key      Value
                          B         2
                          C         2
  1.1.2    L1/L11/L111
                         Key      Value
                         CA         4
   1.2       L1/L12 
  1.2.1   L1/L12/L121
                         Key      Value
                         CC         6 
   ...       ...         ...       ...

实现方法 #

直接用Pandas原生的to_excel无法实现这种自定义排版，需要结合openpyxl库精细控制单元格写入，具体步骤如下：

1. 安装依赖库 #

确保环境中安装了所需工具：

pip install pandas openpyxl

2. 代码实现 #

import pandas as pd
from openpyxl import Workbook

# 模拟原始数据（实际使用时替换为你的DataFrame）
data = {
    'index': ['1', '1.1', '1.1', '1.1.2', '1.2', '1.2.1'],
    'path': ['L1', 'L1/L11', 'L1/L11', 'L1/L11/L111', 'L1/L12', 'L1/L12/L121'],
    'Key': ['A', 'B', 'C', 'CA', None, 'CC'],
    'Value': [1, 2, 2, 4, None, 6]
}
df = pd.DataFrame(data)

# 初始化Excel工作簿和工作表
wb = Workbook()
ws = wb.active
current_row = 1

# 按索引分组处理每组数据
for idx, group in df.groupby('index'):
    # 写入当前组的索引和路径到A、B列
    ws.cell(row=current_row, column=1, value=idx)
    ws.cell(row=current_row, column=2, value=group['path'].iloc[0])
    
    # 筛选出Key和Value均不为空的有效行
    valid_rows = group.dropna(subset=['Key', 'Value'])
    
    if not valid_rows.empty:
        # 写入Key-Value表头到C、D列
        current_row += 1
        ws.cell(row=current_row, column=3, value='Key')
        ws.cell(row=current_row, column=4, value='Value')
        
        # 逐行写入有效Key-Value对
        for _, row in valid_rows.iterrows():
            current_row += 1
            ws.cell(row=current_row, column=3, value=row['Key'])
            ws.cell(row=current_row, column=4, value=row['Value'])
    
    # 每组结束后换行，留出空白分隔（不需要可删除此行）
    current_row += 1

# 保存结果文件
wb.save('custom_output.xlsx')

代码说明 #

• 分组处理：通过groupby('index')将同索引的行归为一组，保证每个索引对应独立的输出模块。
• 空值过滤：用dropna剔除Key或Value为空的行，避免生成无效空表格。
• 精准写入：借助openpyxl的cell方法直接控制单元格内容和位置，实现自定义排版需求。

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