在Python中对比两个CSV文件，提取无重复匹配结果至新CSV

问题说明 #

我有两个CSV文件：

• web_file：包含25000行数据
• inv_file：包含320000行数据

需求：读取web_file第一列的所有值，找出inv_file第一列中与之匹配的行，将这些匹配行写入新的CSV文件。

示例数据 #

web_file示例

Inv_SKU,Web_SKU,Brand,Barcode
225481-34,225481-34,brand1,987654321
0486592,0486592,brand2,654871233
AB56412,AB56412,brand2,651273214
LL-123456,LL-123456,brand3,748912349
JLPD-65,JLPD-65,brand6,341541648
20143966,20143966,brand3,82193714
39585824,39585824,brand5,36837329
78066099,78066099,brand4,98398987
44381051,44381051,brand1,9090428
86529443,86529443,brand4,6861670
DF 5645 12,DF 5645 12,brand1,489456138
9845671325,9845671325,brand4,498451315
59634923,59634923,brand4,35828574
85290760,85290760,brand2,64562216
41217184,41217184,brand4,12816236
AE48915,AE48915,brand1,342536125
93981723,93981723,brand2,58155601

inv_file示例

Inv_SKU,Web_SKU,Brand,Barcode
0486592,0486592,brand2,654871233
LL-123456,LL-123456,brand3,748912349
9845671325,9845671325,brand4,498451315
OI3248967,OI3248967,brand2,891513211
AB56412,AB56412,brand2,651273214
DF 5645 12,DF 5645 12,brand1,489456138
225481-34,225481-34,brand1,987654321
123456789,123456789,brand5,654986413
9841531,9841531,brand3,543254512
AE48915,AE48915,brand1,342536125
JLPD-65,JLPD-65,brand6,341541648
MMMM,MMMM,brand7,384941542
23481-4323,23481-4323,brand3,489123157
98451321,98451321,brand4,498121354
23454152,23454152,brand2,894165123
10275690,10275690,brand2,25612670
20143966,20143966,brand3,82193714
59634923,59634923,brand4,35828574
65800253,65800253,brand5,72318134
67722613,67722613,brand6,93290033
92617199,92617199,brand7,95078073
15379652,15379652,brand1,56281224
85290760,85290760,brand2,64562216
78066099,78066099,brand4,98398987
41217184,41217184,brand4,12816236
87152990,87152990,brand4,95058925
73813369,73813369,brand1,2395994
50201544,50201544,brand1,9167830
93981723,93981723,brand2,58155601
39585824,39585824,brand5,36837329
29082963,29082963,brand3,23393947
23856043,23856043,brand8,57295562
74249006,74249006,brand8,83219065
94376071,94376071,brand8,94887004
14553763,14553763,brand8,14223230
44381051,44381051,brand1,9090428
7598085,7598085,brand1,48967969
56383025,56383025,brand2,68864452
44338055,44338055,brand4,47043853
86529443,86529443,brand4,6861670

原代码问题分析 #

我尝试的代码出现大量重复行，核心问题有两个：

• 嵌套循环遍历inv_file和web_file的所有行，每匹配一次就写入一次，同一个匹配行会被多次写入
• 使用row[0] in row1[0]进行字符包含判断，而非精确匹配，会导致误匹配（比如SKU"123"会匹配"1234"）

原代码：

with open('inv_file.csv', 'r') as f1, open('web_file.csv', 'r') as f2:
    inv_file = f1.readlines()
    web_file = f2.readlines()


with open('result.csv', 'r+') as f3:
    result_file = f3.readlines()

    while len(result_file) < len(web_file):
        for row in inv_file:
            for row1 in web_file:
                if row[0] in row1[0]:
                    f3.write(row1)
        break

正确解决方案 #

方案一：使用csv模块+集合（高效低内存） #

利用集合的O(1)查询特性，避免重复匹配，同时用csv模块处理表头和行数据：

import csv

# 读取web_file的Inv_SKU到集合，自动去重
web_skus = set()
with open('web_file.csv', 'r', newline='', encoding='utf-8') as web_f:
    reader = csv.DictReader(web_f)
    for row in reader:
        web_skus.add(row['Inv_SKU'].strip())  # 去除SKU前后可能的空格

# 遍历inv_file，匹配并写入结果
with open('inv_file.csv', 'r', newline='', encoding='utf-8') as inv_f, \
     open('result.csv', 'w', newline='', encoding='utf-8') as res_f:
    reader = csv.DictReader(inv_f)
    writer = csv.DictWriter(res_f, fieldnames=reader.fieldnames)
    writer.writeheader()  # 写入表头

    for row in reader:
        if row['Inv_SKU'].strip() in web_skus:
            writer.writerow(row)

方案二：极简版（无需csv模块） #

如果确认两个文件列顺序完全一致，可直接分割行处理：

# 读取web_file第一列（跳过表头）
web_skus = set()
with open('web_file.csv', 'r', encoding='utf-8') as web_f:
    next(web_f)  # 跳过表头行
    for line in web_f:
        sku = line.split(',')[0].strip()
        web_skus.add(sku)

# 处理inv_file，写入匹配行
with open('inv_file.csv', 'r', encoding='utf-8') as inv_f, \
     open('result.csv', 'w', encoding='utf-8') as res_f:
    header = next(inv_f)
    res_f.write(header)  # 写入表头
    for line in inv_f:
        sku = line.split(',')[0].strip()
        if sku in web_skus:
            res_f.write(line)

方案优势 #

• 集合存储SKU，自动去重，避免重复判断
• 遍历inv_file仅一次，每匹配成功的行只写入一次，不会产生重复
• 精确匹配SKU，避免字符包含导致的误匹配
• 逐行读取文件，不会一次性加载全部数据到内存，适合处理大文件

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