如何用Python批量读取Excel并将月份作为DataFrame列名合并？

需求概述 #

• 处理12个以Month YYYY.xlsx（如March 2021.xlsx）命名的Excel文件
• 读取规则：指定Sheet1、第5行为表头、选取A/F列、跳过末尾8行、删除空行
• 提取文件名中的月份，将每个DataFrame的第二列（原F列）重命名为对应月份
• 按A列外连接合并所有DataFrame，空值填0，输出为单个Excel文件

现有代码的问题 #

现有代码缺少从文件名提取月份并重命名第二列的核心逻辑，需补充该部分。

修改后的完整代码 #

from pathlib import Path
import pandas as pd
from functools import reduce

# 替换为你的实际Excel文件存放目录
DATA_DIR = "./your_excel_directory"

# 获取所有目标Excel文件
excel_files = list(Path(DATA_DIR).glob('*.xlsx'))

dfs = []
for excel_file in excel_files:
    # 按规则读取Excel文件
    df = pd.read_excel(
        excel_file,
        sheet_name='Sheet1',
        header=5,
        usecols='A,F',
        skipfooter=8
    )
    # 删除空行
    df.dropna(how='any', axis=0, inplace=True)
    
    # 提取文件名中的月份
    file_stem = excel_file.stem  # 获取不带后缀的文件名，如"March 2021"
    month_name = file_stem.split()[0]  # 拆分后取第一个元素，即月份名
    
    # 重命名第二列为提取的月份
    df.rename(columns={df.columns[1]: month_name}, inplace=True)
    
    dfs.append(df)

# 合并所有DataFrame，空值填充为0
df_merged = reduce(
    lambda left, right: pd.merge(left, right, on=['A'], how='outer'),
    dfs
).fillna(0)

# 输出合并后的Excel文件
df_merged.to_excel("merged_monthly_data.xlsx", index=False)

关键修改说明 #

• 月份提取逻辑：利用Path.stem获取无后缀文件名，结合固定格式Month YYYY，通过split()[0]直接提取月份名，避免字符串切片的兼容性问题
• 列名重命名：通过df.columns[1]定位第二列（原F列），无需依赖原表头的具体命名，适配不同文件的表头差异
• 合并优化：直接使用dfs列表作为合并数据源，无需手动枚举dfs[0]到dfs[11]，自动适配任意数量的目标文件

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