多Excel合并：除首文件外删前21行+清除E列空行求助

Hey there! Let's get your Excel merging code working exactly as you need it. I'll walk through fixing the two main issues you mentioned—removing the first 21 rows from all files except the first, and deleting rows with empty cells in column E.

Here's the corrected code: #

import pandas as pd

# List of your target Excel files
excel_names = ["file1.xlsx", "file2.xlsx", "file3.xlsx"]

# Load each Excel file into an ExcelFile object
excels = [pd.ExcelFile(name) for name in excel_names]

# Convert the first sheet of each file to a DataFrame (no header defined)
frames = [x.parse(x.sheet_names[0], header=None, index_col=None) for x in excels]

# Remove first 21 rows from all files except the first one
for i in range(1, len(frames)):
    frames[i] = frames[i][21:]

# Combine all DataFrames into one (reset index to avoid duplicate row numbers)
combined = pd.concat(frames, ignore_index=True)

# Delete rows where column E (4th column, 0-indexed) has empty values
combined = combined.dropna(subset=[4])

# Export the final merged data to a new Excel file
combined.to_excel("c.xlsx", header=False, index=False)

Let's break down the key fixes: #

• Fixing the first 21 rows removal: Your original code used frames[21:], which tried to access elements starting at index 21 in your list of DataFrames—but you only have 3 files! Instead, we loop through the list starting from the second file (index 1) and slice each DataFrame to keep only rows from index 21 onwards (this skips the first 21 rows entirely).

• Removing empty E-column rows: Column E corresponds to index 4 in your DataFrame (since pandas uses 0-based indexing for columns: A=0, B=1, ..., E=4). The dropna(subset=[4]) method removes any row where this column has a missing or empty value.

• Extra quality-of-life tweak: Added ignore_index=True to pd.concat—this resets the row numbers in the merged file so you don't end up with duplicate indices carried over from the original files.

内容的提问来源于stack exchange，提问作者P.Costa