Pandas操作技术问询：如何循环向Excel文件追加数据及基于名称匹配合并DataFrame

1. Appending Data to Excel in Pandas Using a Loop #

While looping to append data to Excel isn't the most efficient approach (collecting all data first and writing once is better for performance), here's how you can do it if you need to add rows incrementally:

First, you'll need to check if the target Excel file exists to decide whether to write headers or not. Here's a step-by-step example:

import pandas as pd
import os

# Define your output file path
excel_file = "output.xlsx"

# Example loop (replace with your data generation logic)
for i in range(3):
    # Create a sample DataFrame for each iteration
    new_data = pd.DataFrame({
        "name": [f"item_{i}"],
        "value": [i * 10]
    })
    
    # Check if the file exists
    if os.path.exists(excel_file):
        # Append without headers
        with pd.ExcelWriter(excel_file, mode="a", engine="openpyxl", if_sheet_exists="overlay") as writer:
            new_data.to_excel(writer, index=False, header=False, startrow=writer.sheets["Sheet1"].max_row)
    else:
        # Write the first batch with headers
        new_data.to_excel(excel_file, index=False)

Key notes:

• Use engine="openpyxl" for .xlsx files (it supports append mode).
• if_sheet_exists="overlay" ensures we add rows to the existing sheet instead of creating a new one.
• startrow calculates where to start writing to avoid overwriting existing data.

If you're working with large datasets, consider collecting all your data into a single DataFrame first and then writing it once—this will be much faster than looping and appending each time.

2. Matching Description from df2 to df1 Based on Name #

This is a classic merge operation. You can use pd.merge() to combine the two DataFrames on the name column, keeping all rows from df1 and pulling in the matching descriptions from df2.

First, let's recreate your sample DataFrames for clarity:

import pandas as pd

# Sample df1
df1 = pd.DataFrame({
    "name": ["a", "b", "c", "d", "e", "f"],
    "description": [None, None, None, None, None, None],  # Original empty column
    "author": ["Bob", "Peter", "Bob", "Carl", "Bob", "Peter"],
    "status": ["inactive", "active", "inactive", "active", "inactive", "active"]
})

# Sample df2
df2 = pd.DataFrame({
    "name": ["a", "b"],
    "description": ["this is a description", "this is another description"]
})

Now perform the merge:

# Merge df1 with df2 on 'name', keeping all rows from df1
merged_df = pd.merge(df1, df2, on="name", how="left", suffixes=("_original", ""))

# Drop the original empty description column if needed
merged_df = merged_df.drop("description_original", axis=1)

print(merged_df)

Output: #

name author    status                  description
0    a    Bob  inactive        this is a description
1    b  Peter    active  this is another description
2    c    Bob  inactive                         NaN
3    d   Carl    active                         NaN
4    e    Bob  inactive                         NaN
5    f  Peter    active                         NaN

Explanation:

• how="left" ensures every row in df1 is retained, even if there's no matching name in df2 (those will get NaN for the description).
• suffixes helps avoid column name conflicts since both DataFrames have a description column. We drop the original empty column after merging.

If you want to fill missing descriptions with a default value (like "No description available"), add this line:

merged_df["description"] = merged_df["description"].fillna("No description available")

内容的提问来源于stack exchange，提问作者Carlos Eduardo Abrantes