Python实现从Detail CSV提取数据插入Master CSV指定列求助

核心思路 #

用字典存储Detail文件的匹配数据（通过共同标识，比如姓名/ID），遍历Master文件的每一行，通过匹配键快速找到对应Detail数据，插入到指定列后写入新文件。

完整实现代码 #

from csv import DictReader, DictWriter
from collections import defaultdict

def load_detail_data(detail_path, match_key="姓名", payment_cols=["一月支付", "二月支付", "三月支付"]):
    # 存储Detail数据：键为匹配标识，值为对应需插入的列数据
    detail_data = defaultdict(dict)
    with open(detail_path, "r", encoding="utf-8") as f:
        reader = DictReader(f)
        for row in reader:
            key = row[match_key]
            # 提取需要插入的列数据
            payment_info = {col: row[col] for col in payment_cols}
            detail_data[key] = payment_info
    return detail_data

def merge_master_detail(master_path, detail_data, output_path, match_key="姓名", insert_cols=["一月支付", "二月支付", "三月支付"]):
    # 获取Master原表头，并添加要插入的列（这里默认追加到末尾，可调整位置）
    with open(master_path, "r", encoding="utf-8") as f:
        reader = DictReader(f)
        master_fields = reader.fieldnames.copy()
        master_fields.extend(insert_cols)
    
    # 写入合并后的文件
    with open(output_path, "w", encoding="utf-8", newline="") as f:
        writer = DictWriter(f, fieldnames=master_fields)
        writer.writeheader()
        
        # 逐行处理Master数据
        with open(master_path, "r", encoding="utf-8") as master_f:
            master_reader = DictReader(master_f)
            for row in master_reader:
                key = row[match_key]
                # 匹配Detail数据，无匹配则用空字符串填充
                payment_info = detail_data.get(key, {col: "" for col in insert_cols})
                # 合并原行数据与插入数据
                merged_row = {**row, **payment_info}
                writer.writerow(merged_row)

# 配置文件路径与参数（根据实际情况修改）
DETAIL_FILE = "csv/detail.csv"
MASTER_FILE = "csv/master.csv"
OUTPUT_FILE = "csv/merged_master.csv"

# 执行流程
detail_data = load_detail_data(DETAIL_FILE)
merge_master_detail(MASTER_FILE, detail_data, OUTPUT_FILE)

print(f"数据合并完成，结果已保存到 {OUTPUT_FILE}")

代码说明 #

1. load_detail_data函数：

   • 读取Detail文件，用字典存储每个匹配标识（如姓名）对应的需插入列数据
   • 支持自定义匹配键和要提取的列名

2. merge_master_detail函数：

   • 先获取Master原表头，将需插入的列添加到表头中（可自行调整插入位置）
   • 逐行读取Master数据，通过匹配键快速查找Detail中的对应数据
   • 合并数据后写入新文件，无匹配数据的行用空字符串填充

3. 适配调整：

   • 如果你的匹配键不是"姓名"，或者要插入的列名不同，修改函数调用时的参数即可
   • 用with语句自动管理文件，避免手动关闭文件的风险

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