如何将PROGRAMS字段拆分生成一对一关联的账户-项目数据表？

需求说明 #

现有一份竖线分隔的CSV文件，包含ACCOUNTID和PROGRAMS两个字段：

• PROGRAMS字段由PROGRAM CODE和PROGRAM AMT成对通过分号;拼接而成，与ACCOUNTID是多对一关系。
  需要将其转换为ACCOUNTID、PROGRAM_CD、PROGRAM_AMT的一对一关联数据表。

原始示例数据

ACCOUNTID|PROGRAMS
AC30400290|01;25000;02;40411;04;30730;17;30730
AC01410304|02;65100;22;48401

目标结果示例

ACCOUNTID|PROGRAM_CD|PROGRAM_AMT
AC30400290|01|25000
AC30400290|02|40411
AC30400290|04|30730
AC30400290|17|30730
AC01410304|02|65100
AC01410304|22|48401

解决方案1：Python脚本处理 #

适合批量处理或自动化场景，逻辑清晰可适配复杂数据：

# 配置输入输出文件路径
input_path = "input.csv"
output_path = "output.csv"

with open(input_path, 'r', encoding='utf-8') as infile, open(output_path, 'w', encoding='utf-8') as outfile:
    # 写入目标表头
    outfile.write("ACCOUNTID|PROGRAM_CD|PROGRAM_AMT\n")
    # 跳过原始文件表头
    next(infile)
    
    for line in infile:
        line = line.strip()
        if not line:
            continue  # 跳过空行
        # 拆分ACCOUNTID和PROGRAMS字段
        account_id, programs_str = line.split('|', maxsplit=1)
        # 拆分PROGRAMS为单个元素列表
        programs = programs_str.split(';')
        # 按每两个元素为一组遍历
        for idx in range(0, len(programs), 2):
            # 检查是否有配对的金额值
            if idx + 1 >= len(programs):
                continue
            prog_cd = programs[idx]
            prog_amt = programs[idx + 1]
            # 写入拆分后的行
            outfile.write(f"{account_id}|{prog_cd}|{prog_amt}\n")

使用说明：将原始CSV文件命名为input.csv放在同目录下，运行脚本后会生成output.csv。

解决方案2：Excel/Power Query可视化处理 #

适合非编程用户，无需写代码即可完成：

1. 导入并分列原始数据

   • 打开Excel，导入CSV文件时选择分隔符为|，将ACCOUNTID和PROGRAMS分成独立两列。
   • 选中PROGRAMS列，点击【数据】→【分列】，选择分隔符为;，将所有CODE和AMT拆分为单独列。

2. 用Power Query逆透视并分组配对

   • 选中所有数据区域，点击【数据】→【从表格/区域】（Excel 2016及以上默认支持Power Query）。
   • 在Power Query编辑器中，选中ACCOUNTID列，点击【转换】→【逆透视其他列】，将所有CODE和AMT转为两行一组的结构。
   • 添加索引列：点击【添加列】→【索引列】→【从0开始】。
   • 添加分组列：点击【添加列】→【自定义列】，输入公式 = Number.IntegerDivide([Index], 2)，将每两行标记为同一组。
   • 选中分组列，点击【转换】→【透视列】，将值列分为PROGRAM_CD和PROGRAM_AMT（聚合方式选"不要聚合"）。
   • 删除索引和分组列，点击【关闭并上载】，将处理后的数据导出为CSV即可。

解决方案3：Linux/macOS命令行（awk） #

适合熟悉命令行的用户，快速完成处理：

创建脚本文件split_programs.awk，内容如下：

BEGIN {
    FS = "|"       # 设置输入分隔符为竖线
    OFS = "|"      # 设置输出分隔符为竖线
    print "ACCOUNTID|PROGRAM_CD|PROGRAM_AMT"  # 输出目标表头
}
NR > 1 {          # 跳过原始文件表头行
    split($2, prog_arr, ";")  # 拆分PROGRAMS字段为数组
    # 遍历数组，每两个元素为一组
    for(i = 1; i <= length(prog_arr); i += 2) {
        if(i + 1 <= length(prog_arr)) {
            print $1, prog_arr[i], prog_arr[i+1]
        }
    }
}

运行命令：

awk -f split_programs.awk input.csv > output.csv

也可以直接用单行命令执行：

awk 'BEGIN{FS="|";OFS="|";print"ACCOUNTID|PROGRAM_CD|PROGRAM_AMT"} NR>1{split($2,a,";");for(i=1;i<=length(a);i+=2){if(i+1<=length(a))print $1,a[i],a[i+1]}}' input.csv > output.csv

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