Pandas批量合并Excel多Sheet报错AttributeError，求解决方案

问题背景 #

现有两个Sheet的DataFrame数据：

import pandas as pd

sheet_1 = pd.DataFrame({'KIFRS BS / IS': ['', 'CCY Type :', 'COA Code', '11000000000'], 
                   'Unnamed: 1': ['', 'EQU', 'COA English Name', 'ABC'],
                   'Unnamed: 2': ['', '', 'Base Currency Exchange Amt.', 1000],
                   'Unnamed: 3': ['26-11-2023', '', '', ''],
                   'Unnamed: 4': ['', 'Hanoi', '', '']})
sheet_2 = pd.DataFrame({'KIFRS BS / IS': ['', 'CCY Type :', 'COA Code', '11000000000'], 
                   'Unnamed: 1': ['', 'EQU', 'COA English Name', 'ABC'],
                   'Unnamed: 2': ['', '', 'Base Currency Exchange Amt.', 1200],
                   'Unnamed: 3': ['26-11-2023', '', '', ''],
                   'Unnamed: 4': ['', 'Hochiminh', '', '']})

数据展示：

KIFRS BS / IS   Unnamed: 1  Unnamed: 2  Unnamed: 3  Unnamed: 4
0                                           26-11-2023  
1   CCY Type :  EQU                                     Hanoi
2   COA Code    COA English Name    Base Currency Exchange Amt.     
3   11000000000 ABC                 1000

期望输出合并后的DataFrame：

output = pd.DataFrame({'COA Code': ['11000000000', '11000000000'], 
                   'COA English Name': ['ABC', 'ABC'],
                   'Base Currency Exchange Amt.': [1000, 1200],
                   'Base Date': ['26-11-2023', '26-11-2023'],
                   'Branch': ['Hanoi', 'Hochiminh']})   

输出预览：

COA Code COA English Name  Base Currency Exchange Amt.  Base Date  Branch
0  11000000000               ABC                          1000  26-11-2023   Hanoi
1  11000000000               ABC                          1200  26-11-2023   Hochiminh

手动处理单个Sheet的代码可行，但Sheet数量多时效率低：

base_date_1 = sheet_1.iloc[0,3]
branch_1 = sheet_1.iloc[1,4]
sheet_1.columns = sheet_1.iloc[2]
sheet_1 = sheet_1[3:]
sheet_1['Base Date'] = base_date_1
sheet_1['Branch'] = branch_1

base_date_2 = sheet_2.iloc[0,3]
branch_2 = sheet_2.iloc[1,4]
sheet_2.columns = sheet_2.iloc[2]
sheet_2 = sheet_2[3:]
sheet_2['Base Date'] = base_date_2
sheet_2['Branch'] = branch_2

output_2 = pd.concat([sheet_1, sheet_2], axis=0, ignore_index=True)
output_2

尝试循环批量处理时出现报错AttributeError: 'dict' object has no attribute 'iloc'，错误代码如下：

import pandas as pd

sheet_name = ['sheet_1', 'sheet_2']

for x in sheet_name:
    df = pd.read_excel('filename.xlsx', sheet_name = sheet_name)
    base_date_1 = df.iloc[0,3]
    branch_1 = df.iloc[1,4]
    df.columns = df.iloc[2]
    df = df[3:]
    df['Base Date'] = base_date_1
    df['Branch'] = branch_1

错误原因 #

pd.read_excel函数当sheet_name参数传入列表时，返回的是一个字典（key为Sheet名称，value为对应DataFrame），而非单个DataFrame。你在循环里直接对这个字典调用iloc方法，自然会报错。

修正后的批量处理代码 #

方法一：逐个读取单个Sheet #

import pandas as pd

# 定义需要处理的Sheet名称列表
sheet_names = ['sheet_1', 'sheet_2']
# 初始化空列表存储处理后的DataFrame
processed_dfs = []

for name in sheet_names:
    # 每次循环读取单个Sheet，返回单个DataFrame
    df = pd.read_excel('filename.xlsx', sheet_name=name)
    
    # 提取基础日期和分支信息
    base_date = df.iloc[0, 3]
    branch = df.iloc[1, 4]
    
    # 设置正确的列名
    df.columns = df.iloc[2]
    # 筛选有效数据行，重置索引避免混乱
    df = df[3:].reset_index(drop=True)
    
    # 添加Base Date和Branch列
    df['Base Date'] = base_date
    df['Branch'] = branch
    
    # 将处理后的DataFrame加入列表
    processed_dfs.append(df)

# 合并所有处理后的DataFrame
final_output = pd.concat(processed_dfs, axis=0, ignore_index=True)
print(final_output)

方法二：先读取所有Sheet到字典，再遍历处理 #

import pandas as pd

# 一次性读取所有指定Sheet，返回字典：{Sheet名: DataFrame}
all_sheets = pd.read_excel('filename.xlsx', sheet_name=['sheet_1', 'sheet_2'])
processed_dfs = []

# 遍历字典中的每个DataFrame
for df in all_sheets.values():
    base_date = df.iloc[0, 3]
    branch = df.iloc[1, 4]
    
    df.columns = df.iloc[2]
    df = df[3:].reset_index(drop=True)
    
    df['Base Date'] = base_date
    df['Branch'] = branch
    
    processed_dfs.append(df)

final_output = pd.concat(processed_dfs, axis=0, ignore_index=True)
print(final_output)

两种方法都能实现批量处理多Sheet并合并的需求，处理逻辑和手动方式一致，但无需逐个重复编写代码，适合Sheet数量较多的场景。

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