使用Pandas匹配并替换两个CSV文件中的列数据

Got it, let's sort out this CSV matching problem for you. The key issue with your current code is that it's not actually linking the two CSVs by name—you need to first extract the name from the Ancillary column, match it to the second CSV, then update the value. Here's a step-by-step solution using Python's pandas library (it's the go-to tool for this kind of data manipulation):

Step 1: Install pandas (if you haven't already) #

Open your terminal/command prompt and run:

pip install pandas

Step 2: Full Code with Explanations #

Copy this code into a .py file, then replace the file paths with your actual CSV locations:

import pandas as pd

# 1. Read both CSV files
csv1 = pd.read_csv("path/to/your/first_csv.csv")  # The one with 300 rows, needs Ancillary updated
csv2 = pd.read_csv("path/to/your/second_csv.csv") # The one with 1000-2000 rows, has BannerID

# 2. Extract the name from csv1's Ancillary column
# The format is 'EXT_TRANS_ID=xxxxxxx - 姓名' — split on ' - ' and take the last part
csv1["Extracted_Name"] = csv1["Ancillary"].str.split(" - ").str[-1].str.strip()

# 3. Clean names to avoid matching failures (case sensitivity, extra spaces)
csv1["Extracted_Name"] = csv1["Extracted_Name"].str.lower().str.strip()
csv2["Person Name"] = csv2["Person Name"].str.lower().str.strip()

# 4. Merge the two CSVs using the cleaned names as the match key
# We use a left join to keep all rows from csv1, even if there's no match (though you said all 300 are in csv2)
merged_data = pd.merge(
    csv1,
    csv2[["Person Name", "BannerID"]],  # Only keep the columns we need from csv2
    left_on="Extracted_Name",
    right_on="Person Name",
    how="left"
)

# 5. Update the Ancillary column with the matched BannerID
merged_data["Ancillary"] = merged_data["BannerID"]

# 6. Clean up temporary columns and save the result
# Keep only the original columns from csv1 (drop our temporary Extracted_Name and extra Person Name)
final_result = merged_data[csv1.columns]

# Save to a new CSV (you can overwrite the original if you're confident, but better to test first)
final_result.to_csv("path/to/your/updated_first_csv.csv", index=False)

Key Notes to Avoid Issues: #

• Verify Matches: After running, check if any BannerID values are missing (which would mean a name didn't match). Run this line to see mismatches:

  print(merged_data[merged_data["BannerID"].isna()])
  

  If you see rows here, double-check the name formatting (e.g., middle initials, hyphens, or extra spaces that weren't cleaned).
• Handle Duplicates: If there are multiple rows in csv2 for the same name, the merge will create duplicate rows in csv1. If that's a possibility, you might need to add an extra matching column (like a unique ID) or aggregate csv2 first (e.g., take the first BannerID for each name).
• Test First: Always save to a new CSV instead of overwriting the original until you confirm the results are correct.

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