如何按匹配条件合并两个CSV并生成新CSV？代码问题排查

核心问题分析 #

• CSV迭代器耗尽：csv.DictReader返回的是一次性迭代器，第一次遍历target_reader后，后续循环无法再读取target.csv的数据，导致只有第一行source数据可能匹配成功。
• 匹配逻辑过于严格：source中Brusenka Restaurant a Pension与target中Pension Brusenka无法直接通过相等判断匹配，导致该行数据无法合并。
• 未匹配字段未初始化：未匹配到的行缺少hotel相关字段的默认空值，可能导致写入CSV时出现字段缺失。

修正后的代码 #

import csv

columns = [
    "query",
    "name",
    "hotel_name",
    "hotel_address",
    "hotel_star_ratings",
    "number_of_rooms",
    "number_of_reviews",
    "hotel_user_rating",
]

# 预加载target数据到字典，以hotel_name为键实现快速查找
target_data = {}
with open("target.csv", "r") as target:
    target_reader = csv.DictReader(target)
    for row in target_reader:
        target_data[row["hotel_name"]] = row

data = []
with open("source.csv", "r") as source:
    source_reader = csv.DictReader(source)
    for source_row in source_reader:
        # 初始化所有字段，确保未匹配行输出空值
        merged_row = {
            "query": source_row["query"],
            "name": source_row["name"],
            "hotel_name": "",
            "hotel_address": "",
            "hotel_star_ratings": "",
            "number_of_rooms": "",
            "number_of_reviews": "",
            "hotel_user_rating": "",
        }
        
        # 尝试精确匹配+变体匹配，覆盖名称不一致的场景
        for hotel_name, target_row in target_data.items():
            if source_row["name"] == hotel_name or hotel_name in source_row["name"]:
                merged_row.update({
                    "hotel_name": target_row["hotel_name"],
                    "hotel_address": target_row["hotel_address"],
                    "hotel_star_ratings": target_row["hotel_star_ratings"],
                    "number_of_rooms": target_row["number_of_rooms"],
                    "number_of_reviews": target_row["number_of_reviews"],
                    "hotel_user_rating": target_row["hotel_user_rating"],
                })
                break
        
        data.append(merged_row)

# 写入输出文件，newline参数避免空行问题
with open("output.csv", "w", newline="") as output:
    writer = csv.DictWriter(output, fieldnames=columns)
    writer.writeheader()
    writer.writerows(data)

关键优化点 #

• 预加载数据：将target.csv内容一次性存入字典，彻底解决迭代器耗尽问题，同时将匹配效率从O(n²)优化为O(n)。
• 灵活匹配规则：增加包含判断，处理名称变体的匹配场景，覆盖Brusenka Restaurant a Pension与Pension Brusenka这类情况。
• 字段默认值初始化：提前为所有字段设置空值，确保未匹配行的输出格式完全符合预期。

内容的提问来源于stack exchange，提问作者X-something