如何用Python对比两个CSV文件并导出差异与相似性？

Hey there! Let's work through this CSV comparison challenge together. You mentioned netscan.csv has accurate computer names, serial numbers, and device models—so I’ll use those unique identifiers as the anchor to match records between the two files. Here’s a straightforward Python script that’ll pull out similarities (matching entries) and differences (entries only in one file), then save everything to new CSVs:

We’ll use the pandas library—it’s the go-to tool for handling CSV data in Python. Install it via pip if you don’t have it yet:

pip install pandas

Copy this into your blank .py file. I’ve added comments to explain each step, and you can tweak parts to fit your exact CSV structure:

import pandas as pd

# Load your CSV files (update file paths if they're not in the same folder as your script)
netscan_df = pd.read_csv("netscan.csv")
computer_list_df = pd.read_csv("computer_list.csv")

# Choose your matching key—pick the column that's unique and consistent across both files
# Use "计算机名称" (computer name) or "序列号" (serial number) whichever is more reliable
match_key = "计算机名称"  # Swap this with "序列号" if needed

# Double-check the key exists in both files (avoids errors later)
if match_key not in netscan_df.columns or match_key not in computer_list_df.columns:
    raise ValueError(f"Oops! The column '{match_key}' isn't present in one of your CSVs. Double-check your column names!")

# Find records that exist in BOTH files (similarities)
# Suffixes help you tell apart columns with the same name from each file
matching_records = pd.merge(
    netscan_df, 
    computer_list_df, 
    on=match_key, 
    how="inner", 
    suffixes=("_netscan", "_computerlist")
)

# Find records that ONLY exist in netscan.csv
netscan_only = netscan_df[~netscan_df[match_key].isin(computer_list_df[match_key])]

# Find records that ONLY exist in computer_list.csv
computerlist_only = computer_list_df[~computer_list_df[match_key].isin(netscan_df[match_key])]

# Export all results to new CSV files (utf-8-sig ensures proper Chinese character display)
matching_records.to_csv("matching_records.csv", index=False, encoding="utf-8-sig")
netscan_only.to_csv("netscan_only_records.csv", index=False, encoding="utf-8-sig")
computerlist_only.to_csv("computerlist_only_records.csv", index=False, encoding="utf-8-sig")

# Print quick stats so you know what happened
print(f"✅ Done! Matching records saved to matching_records.csv ({len(matching_records)} entries)")
print(f"✅ Records only in netscan.csv saved to netscan_only_records.csv ({len(netscan_only)} entries)")
print(f"✅ Records only in computer_list.csv saved to computerlist_only_records.csv ({len(computerlist_only)} entries)")

Tweak the script to fit your specific needs with these tips:

• If column names don’t match: If your matching key has different names in each CSV (e.g., one uses "ComputerName" and the other "计算机名称"), use left_on and right_on in the merge instead of on:

  matching_records = pd.merge(
      netscan_df, 
      computer_list_df, 
      left_on="计算机名称",  # Key column in netscan.csv
      right_on="ComputerName",  # Key column in computer_list.csv
      how="inner", 
      suffixes=("_netscan", "_computerlist")
  )
  

• Check for mismatched details: If you want to flag cases where a matching computer has conflicting data (e.g., different device models), add this after creating matching_records:

  # Compare device models from both files
  matching_records["model_mismatch"] = matching_records["设备型号_netscan"] != matching_records["设备型号_computerlist"]
  # Save mismatches to a separate CSV
  mismatched_models = matching_records[matching_records["model_mismatch"]]
  mismatched_models.to_csv("mismatched_device_models.csv", index=False, encoding="utf-8-sig")
  print(f"✅ Mismatched device models saved to mismatched_device_models.csv ({len(mismatched_models)} entries)")
  

• Fix encoding issues: If your CSV has garbled Chinese characters, try changing the encoding parameter to "gbk" instead of "utf-8-sig".

If you run into snags (like weird data formats, missing columns, or unexpected results), just share more details about your CSV column names or sample data, and we can adjust the script further!

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