如何将多个DataFrame整理并追加至Excel，同时调整列结构？

Got it, let's walk through how to solve this problem step by step—converting those DataFrames to your desired structure and appending multiple files to a single target Excel.

Step 1: Convert a Single DataFrame to the Column-Based Structure #

First, let's take your sample DataFrame and reshape it so that Questions become column names, with Answer as the corresponding values. Since your Item column uses a hierarchical format (like 1, 1.1, 1.2) for a single record, we'll first group by the main item identifier before pivoting.

Here's the code:

import pandas as pd

# Read the Excel file into a DataFrame
df = pd.read_excel("your_single_file.xlsx")

# Extract the main Item identifier (e.g., "1" from "1.1")
df["Main_Item"] = df["Item"].astype(str).str.split(".").str[0]

# Pivot the DataFrame to get Questions as columns
converted_df = df.pivot(
    index="Main_Item",
    columns="Questions",
    values="Answer"
).reset_index(drop=True)

# Result will look like this:
#   First name  Age Nationality
# 0       Alex   43    English

Note: If your Item column doesn't use a hierarchical structure (each Item is a unique, standalone record), skip the Main_Item step and use Item directly as the pivot index.

Step 2: Process Multiple Files and Append to Target Excel #

Now, let's scale this to multiple files. You have two main options: either combine all converted DataFrames first then write to Excel, or append each converted DataFrame directly to the target file as you process it.

Option 1: Combine First, Write Once (Cleaner for Small/Medium Files) #

This method collects all converted data into one DataFrame before exporting, which is easier to debug:

import pandas as pd
import glob

# Get a list of all Excel files you want to process (adjust the path as needed)
file_list = glob.glob("path/to/your/files/*.xlsx")

# Initialize an empty DataFrame to store all results
all_converted_data = pd.DataFrame()

for file in file_list:
    # Read and convert each file
    df = pd.read_excel(file)
    df["Main_Item"] = df["Item"].astype(str).str.split(".").str[0]
    converted_df = df.pivot(index="Main_Item", columns="Questions", values="Answer").reset_index(drop=True)
    
    # Add to the combined DataFrame
    all_converted_data = pd.concat([all_converted_data, converted_df], ignore_index=True)

# Write the combined data to your target Excel file
all_converted_data.to_excel("target_summary.xlsx", index=False)

Option 2: Append Directly to Excel (Better for Large Files) #

If you're working with large files and want to avoid loading all data into memory at once, append each converted DataFrame directly to the target file:

import pandas as pd
import glob
import os

target_file = "target_summary.xlsx"
file_list = glob.glob("path/to/your/files/*.xlsx")

for file in file_list:
    # Read and convert the file
    df = pd.read_excel(file)
    df["Main_Item"] = df["Item"].astype(str).str.split(".").str[0]
    converted_df = df.pivot(index="Main_Item", columns="Questions", values="Answer").reset_index(drop=True)
    
    # Check if target file exists to handle headers correctly
    if not os.path.exists(target_file):
        # First write: include headers
        converted_df.to_excel(target_file, index=False)
    else:
        # Append mode: skip headers, add to the next empty row
        with pd.ExcelWriter(target_file, mode="a", if_sheet_exists="overlay") as writer:
            start_row = writer.sheets["Sheet1"].max_row
            converted_df.to_excel(writer, index=False, header=False, startrow=start_row)

Key Notes #

• If different files have different Questions, the final DataFrame will include all unique question columns, with NaN for missing answers in specific records.
• Make sure to adjust file paths in glob.glob() to match where your Excel files are stored.
• The append() method you thought of is deprecated in pandas—we use pd.concat() instead for combining DataFrames, or ExcelWriter in append mode for writing directly to Excel.

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