如何用Python比较两个CSV文件并将差异写入新增列？

需求说明 #

有两个结构相似的CSV文件，需要对比二者内容，当Number字段存在差异时，在对应行新增Changes列标注原文件的数值；同时需要处理文件中可能存在的新增行，通过Name字段作为唯一键进行匹配。

示例输入 #

第一个CSV文件内容：

Name,Number
AAC;2.2.3
AAF;2.4.4
ZCX;3.5.2

第二个CSV文件内容：

Name,Number
AAC;2.2.3
AAF;2.4.4
ZCX;3.5.5

期望输出 #

Name,Number,Changes
AAC;2.2.3
AAF;2.4.4
ZCX;3.5.5;change: 3.5.2

遇到的问题 #

使用Python 3.10.9尝试多段代码，要么无法新增列仅更新原有内容，要么运行报错list index out of range。

尝试代码1（键映射逻辑错误） #

import csv

# Reading the first csv and set mapping
with open('test1.csv', 'r') as csvfile:
    reader= csv.reader(csvfile)
    rows = list(reader)
    file1_dict = {row[1]: row[0] for row in rows}


# Reading the second csv and set mapping
with open('test2.csv', 'r') as csvfile:
    reader= csv.reader(csvfile)
    rows = list(reader)
    file2_dict = {row[1]: row[0] for row in rows}

# comparing the keys and find the diff
for k in test1_dict:
    if test1_dict[k] != test2:dict[k]
        test1_dict[k] = test2_dict[k]
        for row in rows:
            if row[1] == k:
                row.append(test2_dict[k])

# write the csv (not sure how to add the word "change:")
with open('test1.csv', 'w', newline ='') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerows(rows)

尝试代码2（仅提取差异行） #

with open('test1.csv') as fin1:
  with open('test2.csv') as fin2:
    read1 = csv.reader(fin1)
    read2 = csv.reader(fin2)
    diff_rows = (row1 for row1, row2 in zip(read1, read2) if row1 != row2)
    with open('test3.csv', 'w') as fout:
      writer = csv.writer(fout)
      writer.writerows(diff_rows)

尝试代码3（调整分隔符后报错） #

with open('test3.csv', 'r') as file1:
    reader = csv.reader(file1, delimiter=';')
    rows = list(reader)[1:]
    file1_dict = {row[0]: row[1] for row in rows}
with open('test4.csv', 'r') as file2:
    reader = csv.reader(file2, delimiter=';')
    rows = list(reader)[1:]
    file2_dict = {row[0]: row[1] for row in rows}
new_file = ["Name;Number;Changes\n"]
with open('output.csv', 'w') as nf:
    for key, value in file1_dict.items():
        if value != file2_dict[key]:
            new_file.append(f"{key};{file2_dict[key]};change: {value}\n")
        else:
            new_file.append(f"{key};{value}\n")
    nf.writelines(new_file)

修正方案 #

问题根源 #

1. CSV分隔符混乱：表头用逗号、内容用分号，导致csv.reader解析时无法正确拆分字段，触发索引越界错误
2. 键映射错误：错误使用Number作为键，应该用唯一标识Name
3. 未处理新增/缺失行：仅遍历其中一个文件的键，遗漏仅在单个文件存在的行

正确代码实现 #

import csv

def read_csv(file_path):
    """读取CSV文件，返回表头和以Name为键的数值字典"""
    data_dict = {}
    with open(file_path, 'r', newline='', encoding='utf-8') as f:
        reader = csv.reader(f)
        header = next(reader)  # 读取表头
        for row in reader:
            if not row:  # 跳过空行
                continue
            # 处理表头逗号、内容分号的混合分隔逻辑
            name, number = row[0].split(';')
            data_dict[name.strip()] = number.strip()
    return header, data_dict

# 读取两个目标文件
header1, file1_data = read_csv('test1.csv')
_, file2_data = read_csv('test2.csv')

# 获取所有唯一的Name，覆盖新增/缺失行场景
all_names = sorted(set(file1_data.keys()).union(file2_data.keys()))

# 构造输出内容
output_rows = []
# 生成新表头并转换为分号分隔格式
output_header = header1 + ['Changes']
output_rows.append(';'.join(output_header))

for name in all_names:
    num_old = file1_data.get(name)
    num_new = file2_data.get(name)
    
    if num_old is None:
        # 仅在新文件存在的新增行
        output_rows.append(f"{name};{num_new};new row")
    elif num_new is None:
        # 仅在旧文件存在的缺失行
        output_rows.append(f"{name};{num_old};removed row")
    elif num_old != num_new:
        # 数值有差异的行
        output_rows.append(f"{name};{num_new};change: {num_old}")
    else:
        # 无差异的行
        output_rows.append(f"{name};{num_new}")

# 写入输出文件
with open('output.csv', 'w', newline='', encoding='utf-8') as f:
    f.write('\n'.join(output_rows))

代码说明 #

1. 统一解析逻辑：针对混合分隔符问题，先读取整行再按分号拆分内容，确保字段解析正确
2. 正确键选择：用Name作为唯一匹配键，避免因数值重复导致的匹配错误
3. 全场景覆盖：合并两个文件的所有Name，标记新增、缺失以及数值变更的行
4. 输出格式统一：所有行用分号分隔，与示例输出格式一致

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