不使用pandas 仅用Python标准库实现CSV透视并保留指定列

修改后完整代码 #

import csv
import re

# 固定要透视的key列
PIVOT_COLS = ["x", "y", "z", "p", "q"]

def main():
    # 存储按dont_pivot分组的透视数据、dont_pivot拆分后的信息
    pivot_groups = dict()
    dp_extra_info = dict()

    # 读取输入CSV
    with open("myfile.csv", "r", encoding="utf-8") as f:
        reader = csv.reader(f)
        next(reader) # 跳过表头
        for row in reader:
            dp_val, key, value = row[0], row[1], row[2].strip()
            # 首次遇到该dont_pivot值时初始化
            if dp_val not in pivot_groups:
                pivot_groups[dp_val] = {k: "" for k in PIVOT_COLS}
                # 提前拆分dont_pivot的信息，单独存储
                first_char = re.search(r"^(\w)", dp_val).group(1)
                mid_num = re.search(r"_(\d+)_", dp_val).group(1)
                dp_extra_info[dp_val] = (first_char, mid_num)
            # 填充对应key的value
            if key in pivot_groups[dp_val]:
                pivot_groups[dp_val][key] = value

    # ========== 输出格式1：保留原始dont_pivot列 ==========
    with open("output1.csv", "w", encoding="utf-8", newline="") as f:
        writer = csv.writer(f)
        # 写表头
        writer.writerow(["dont_pivot"] + PIVOT_COLS)
        # 逐行写数据
        for dp_val, val_map in pivot_groups.items():
            row = [dp_val] + [val_map[k] for k in PIVOT_COLS]
            writer.writerow(row)

    # ========== 输出格式2：拆分dont_pivot为两个字段 ==========
    with open("output2.csv", "w", encoding="utf-8", newline="") as f:
        writer = csv.writer(f)
        # 写表头
        writer.writerow(["dont_pivot_letter", "dont_pivot_num"] + PIVOT_COLS)
        # 逐行写数据
        for dp_val, val_map in pivot_groups.items():
            first_char, mid_num = dp_extra_info[dp_val]
            row = [first_char, mid_num] + [val_map[k] for k in PIVOT_COLS]
            writer.writerow(row)

if __name__ == "__main__":
    main()

核心修改说明 #

1. 重构了透视逻辑：新增按dont_pivot字段分组的逻辑，避免不同组的透视值混淆，自然保留了非透视列的信息
2. 单独存储dont_pivot字段的拆分结果，和透视逻辑完全解耦，符合你后续单独处理的要求
3. 直接使用Python标准库csv模块的writer写入文件，避免手动拼接CSV字符串可能带来的转义错误
4. 修复了原代码中dont_pivot_char变量未定义的问题，调整了正则匹配规则，确保能正确提取两个下划线中间的数字

运行后会在同级目录生成两个输出文件，分别对应你要求的两种输出格式，直接打开即可使用。

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