合并多Excel文件特定动态列至DataFrame问题求助

我需要将14个Excel文件中的数据合并为单个DataFrame并导出为CSV，目前已实现文件遍历，但无法成功合并数据。推测问题出在动态获取每个Excel文件最后一行的代码部分。需合并的数据位于CB:DL列，起始行是第6行，总数据量约10万行，且每个文件的结束行号各不相同。

测试代码 #

#import modules
import pandas as pd
import glob
from openpyxl import Workbook
from openpyxl import load_workbook as xw
from openpyxl.utils import get_column_letter


# path of the folder
path = r'C:\\All Raw Data\\'
  
# reading all the excel files
filenames = glob.glob(path + "\\*.xlsx")

 
# to iterate excel file one by one 
# inside the folder
for file in filenames:
    print(file)
    
    #print('File names:', filenames)
      
    # initializing empty data frame
    finalexcelsheet = pd.DataFrame()

    wb = Workbook(file)
    print(wb)
    for sheet in wb:
        ws = wb.sheet["Speech"]
        print(ws)
    
        for col in range(1, ws.max_column + 1):
            col_letter = get_column_letter(col)
            max_col_row = len([cell for cell in ws[col_letter] if cell.value])
            print("Column: {}, Row numbers: {}".format(col_letter, max_col_row))
                
            
            # combining multiple excel worksheets into single data frames
            df = pd.concat(pd.read_excel(file, sheet_name=None, header=6, usecols='CB'+max_col_row+':DL'+max_col_row), ignore_index=True, sort=False)
            print(df.shape)
            
            # appending excel files one by one
            merged= finalexcelsheet.append(df, ignore_index=True)
  
# to print the combined data
print(merged.shape)

merged.to_csv('C:\\All Raw Data\\merged.csv')

问题分析与修正方案 #

原代码核心问题 #

1. 累积DataFrame初始化位置错误：finalexcelsheet在循环内部初始化，每次遍历文件都会重置为空，无法保存之前合并的数据
2. 文件读取方式错误：用Workbook(file)创建新工作簿，而非读取现有文件，应使用load_workbook
3. 无效列遍历：遍历所有列来获取行数完全冗余，我们只需要目标列CB:DL的有效行数
4. read_excel参数错误：
   • sheet_name=None会读取所有工作表，但需求仅针对"Speech"工作表
   • usecols格式错误，不应拼接行号，该参数只指定列范围
5. 弃用方法使用：append已被Pandas弃用，应使用pd.concat来合并数据

修正后的代码 #

import pandas as pd
import glob
from openpyxl import load_workbook

# 文件夹路径
path = r'C:\All Raw Data\\'
# 获取所有xlsx文件路径
filenames = glob.glob(path + "*.xlsx")

# 初始化用于累积数据的空列表
merged_data = []

for file in filenames:
    print(f"正在处理文件: {file}")
    
    # 加载工作簿，只读取数据不加载样式提升效率
    wb = load_workbook(file, read_only=True, data_only=True)
    # 获取目标工作表
    ws = wb["Speech"]
    
    # 获取CB列的有效行数（从第6行开始）
    cb_col = ws['CB']
    # 过滤空值，计算有效行数，减去前5行（因为起始行是第6行）
    max_row = len([cell for cell in cb_col if cell.value is not None])
    # 确保起始行是第6行，若有效行数小于6则跳过该文件
    if max_row < 6:
        print(f"文件{file}有效数据不足，跳过")
        continue
    
    # 读取指定范围的数据：行6到max_row，列CB到DL
    df = pd.read_excel(
        file,
        sheet_name="Speech",
        header=5,  # 第6行作为表头，所以header是5（索引从0开始）
        usecols="CB:DL",
        nrows=max_row - 5  # 读取从第6行到最后一行的行数
    )
    
    print(f"读取到{df.shape[0]}行数据")
    # 将当前文件的数据添加到累积列表
    merged_data.append(df)

# 合并所有数据
final_df = pd.concat(merged_data, ignore_index=True)
print(f"合并完成，总数据量: {final_df.shape}")

# 保存为CSV
final_df.to_csv(r'C:\All Raw Data\merged.csv', index=False)

关键优化点 #

• 使用read_only=True加载工作簿，处理大文件时大幅提升效率
• 直接定位目标列CB计算有效行数，避免无效遍历
• 用列表累积DataFrame后一次性合并，比逐次合并更高效
• 明确指定sheet_name和列范围，避免读取冗余数据
• 加入数据有效性判断，跳过无有效数据的文件

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