Python如何将多个Excel工作簿读取合并为一个DataFrame？

Hey there! Let's tackle this problem step by step since you're new to Python—no worries, we'll keep it clear and straightforward. 😊

Step 1: Install Required Libraries #

First, make sure you have the necessary tools installed. Open your terminal/command prompt and run:

pip install pandas openpyxl

• pandas handles DataFrames and all your data manipulation needs
• openpyxl is the engine pandas uses to read .xlsx files (it's required for Excel 2007+ formats)

Step 2: Complete Commented Code #

Here's a ready-to-use script that does exactly what you need. I'll break down each part afterward so you understand every line:

import pandas as pd
import os

# Replace this with the actual path to your folder containing all xlsx files
folder_path = "path/to/your/excel/folder"  # Example: "C:\\MyData\\Rankings" (Windows) or "/Users/You/Rankings" (Mac/Linux)

# Get a list of all .xlsx files in the folder (filters out non-Excel files)
xlsx_files = [file for file in os.listdir(folder_path) if file.endswith(".xlsx")]

# Initialize an empty DataFrame to hold all combined data
combined_data = pd.DataFrame()

# Loop through each Excel file
for file_index, file_name in enumerate(xlsx_files):
    full_file_path = os.path.join(folder_path, file_name)
    
    # Handle the FIRST file: read from row 11 (skip first 10 rows) and keep the column headers
    if file_index == 0:
        # Skip 10 rows (Excel rows 1-10), start reading at row 11 which becomes our column headers
        df = pd.read_excel(
            full_file_path,
            sheet_name="Keywords Rankings",
            skiprows=10,
            header=0  # Treat the first row after skipping as column names
        )
        combined_data = df.copy()
    # Handle ALL OTHER files: read from row 12 (skip first 11 rows) and skip column headers
    else:
        # Skip 11 rows (Excel rows 1-11), start reading at row 12 (only data, no headers)
        df = pd.read_excel(
            full_file_path,
            sheet_name="Keywords Rankings",
            skiprows=11,
            header=None  # Tell pandas there are no column headers in this file
        )
        # Make sure the columns match our combined DataFrame before appending
        df.columns = combined_data.columns
        # Append the new data to our main DataFrame
        combined_data = pd.concat([combined_data, df], ignore_index=True)

# Optional: Save the combined DataFrame to a new Excel file (so you can check it easily)
combined_data.to_excel("combined_keywords_rankings.xlsx", index=False)

print("Done! All files combined into one DataFrame.")

Step 3: Key Explanations for Beginners #

Let's break down the most important parts so you know what's happening:

• os.listdir(folder_path): Pulls all files from your folder, and we filter only .xlsx files to avoid unwanted files
• enumerate(xlsx_files): Gives us both the file index and name, so we can check if we're dealing with the first file
• skiprows: This tells pandas how many top rows to skip. Since Excel counts rows starting at 1, skipping 10 rows means we start reading at row 11 (perfect for your first file's headers)
• header=0 vs header=None: For the first file, we use the first skipped row as column names. For other files, we skip headers entirely and reuse the column names from the first file to avoid mismatches
• pd.concat: This appends new data to our main DataFrame. ignore_index=True resets row numbers so you don't have duplicate indices

Quick Troubleshooting Tips #

• File path errors: On Windows, use double backslashes ("C:\\MyFiles") or raw strings (r"C:\MyFiles"). On Mac/Linux, use forward slashes ("/Users/You/MyFiles")
• Missing sheet errors: Double-check every file has a sheet named "Keywords Rankings" (case-sensitive!)
• Column mismatches: If any file has different columns (even though you said they're identical), the code will fail. Verify all files have the same columns in the same order

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