如何对比新旧股票CSV列表并提取增删项且保留数据格式？

Hey there! Let's fix your CSV comparison problem—handling 5000 rows efficiently while preserving your original data structure is totally achievable. Your initial nested loop approach has a key issue: once you iterate through old_reader once, it's exhausted, so subsequent rows from new_reader can't re-check the old data. Here's a better solution:

Step-by-Step Approach #

We'll use sets for O(1) lookups (way faster than nested loops for large datasets) and csv.DictReader/DictWriter to keep your original column structure intact.

1. Extract Stock Codes for Fast Lookup #

First, we'll pull all stock codes from both CSVs into sets—this lets us quickly check if a code exists in the other file.

import csv

# Define file paths
new_path = "new.csv"
old_path = "old.csv"
add_path = "add.csv"
remove_path = "remove.csv"

# Extract stock codes and field names from old.csv
old_codes = set()
old_fields = []
with open(old_path, encoding="utf8") as old_file:
    old_reader = csv.DictReader(old_file)
    old_fields = old_reader.fieldnames  # Save original columns
    for row in old_reader:
        old_codes.add(row["STOCK CODE"])

# Extract stock codes and field names from new.csv
new_codes = set()
new_fields = []
with open(new_path, encoding="utf8") as new_file:
    new_reader = csv.DictReader(new_file)
    new_fields = new_reader.fieldnames
    for row in new_reader:
        new_codes.add(row["STOCK CODE"])

2. Write Newly Added Stocks to add.csv #

We'll iterate through new.csv and write any row whose stock code isn't in old_codes:

# Write added stocks
with open(new_path, encoding="utf8") as new_file, open(add_path, "w", encoding="utf8", newline="") as add_file:
    new_reader = csv.DictReader(new_file)
    add_writer = csv.DictWriter(add_file, fieldnames=new_fields)
    add_writer.writeheader()  # Include original header
    for row in new_reader:
        if row["STOCK CODE"] not in old_codes:
            add_writer.writerow(row)

3. Write Removed Stocks to remove.csv #

Similarly, iterate through old.csv and write rows not present in new_codes:

# Write removed stocks
with open(old_path, encoding="utf8") as old_file, open(remove_path, "w", encoding="utf8", newline="") as remove_file:
    old_reader = csv.DictReader(old_file)
    remove_writer = csv.DictWriter(remove_file, fieldnames=old_fields)
    remove_writer.writeheader()
    for row in old_reader:
        if row["STOCK CODE"] not in new_codes:
            remove_writer.writerow(row)

4. Track Position Changes (Optional) #

If you want to identify stocks that exist in both files but have changed positions, we can map each stock code to its row number:

# Track row positions for old.csv
old_code_pos = {}
with open(old_path, encoding="utf8") as old_file:
    old_reader = csv.DictReader(old_file)
    for row_num, row in enumerate(old_reader, start=1):  # Start at 1 to skip header
        old_code_pos[row["STOCK CODE"]] = row_num

# Track row positions for new.csv
new_code_pos = {}
with open(new_path, encoding="utf8") as new_file:
    new_reader = csv.DictReader(new_file)
    for row_num, row in enumerate(new_reader, start=1):
        new_code_pos[row["STOCK CODE"]] = row_num

# Find stocks with changed positions
moved_stocks = [
    code for code in old_codes.intersection(new_codes)
    if old_code_pos[code] != new_code_pos[code]
]

print("Stocks that changed position:", moved_stocks)

Why This Works #

• Efficiency: Sets give us near-instant lookups, so this runs in O(n + m) time (vs. O(n*m) for nested loops)—perfect for 5000 rows.
• Structure Preservation: Using DictWriter ensures the output CSVs have the exact same columns as your original files.
• Flexibility: The optional position tracking adds the extra functionality you mentioned was missing from your referenced solution.

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