Python CSV转JSON时处理双列非空逻辑与优惠券码前导零保留问题

现有如下|分隔的CSV文件：

Promotion Code|Promotion Name|Coupon Code|Coupon Name
E00000002|Gift Free Coin||
||000420|new join $300
E000000001|VIP -10%|006969|new join $300

需要转换为目标JSON格式：

[{"New_column": [{"type": "promo_code", "description": "Gift Free Coin", "redeem": "false"}]}, {"New_column": [{"coupon_code": 000420, "type": "coupon_code", "description": "new join $300", "redeem": "false"}]},{"New_column":[{"type":"promo_code","description":"VIP -10%","redeem":"false"},{"type":"coupon","description":"new join $300","redeem":"false","coupon_code":006969}]}]

原有实现代码：

import csv
import json
from os import sep

with open('tran_test.csv') as file:
    dict_input = csv.reader(file, delimiter='|')
    next(dict_input, None)
    output = []
    for line in dict_input:
        promotion_code = line[0]
        promotion_name = line[1]
        coupon_code = line[2]
        coupon_name = line[3]
        print(line)
        coupon = {}
        if coupon_code == '' and promotion_code !='':
            coupon['type'] = 'promo_code'
            coupon['description'] = promotion_name
            coupon['redeem'] = 'false'
        elif coupon_code != '' and promotion_code == '':
            coupon['coupon_code'] = coupon_code
            coupon['type'] = 'coupon_code'
            coupon['description'] = coupon_name
            coupon['redeem'] = 'false'
        # elif (coupon_code != '' and promotion_code != ''):
        # 
        else:
            None
        output_obj = {}
        output_obj['New_column'] = [coupon]
        output.append(output_obj)

print(json.dumps(output))

当前运行输出：

[{"New_column": [{"type": "promo_code", "description": "Gift Free Coin", "redeem": "false"}]}, {"New_column": [{"coupon_code": "000420", "type": "coupon_code", "description": "new join $300", "redeem": "false"}]}, {"New_column": [{}]}]

• 无法处理促销码和优惠券码都非空的行，无法生成包含两个元素的New_column数组
• coupon_code需要保留前导零，但转为int类型会丢失前导零

1. 多字段非空场景处理 #

不要用互斥的if-elif分支判断，改为分别判断促销码、优惠券码是否非空，各自生成对应的对象加入New_column数组即可，自然覆盖三种场景：仅促销码非空、仅优惠券码非空、两者都非空。

2. 前导零保留问题 #

首先明确：标准JSON规范不支持带前导零的数字类型，你给出的目标JSON中000420作为数字是非法格式，所有标准JSON解析器都会识别为语法错误。如果必须保留前导零，唯一合法的方案是将coupon_code存为字符串类型。如果下游系统要求必须为数字类型，建议和下游确认格式要求，大概率是目标示例的写法不严谨。

import csv
import json

with open('tran_test.csv') as file:
    dict_input = csv.reader(file, delimiter='|')
    next(dict_input, None)
    output = []
    for line in dict_input:
        promotion_code = line[0].strip()
        promotion_name = line[1].strip()
        coupon_code = line[2].strip()
        coupon_name = line[3].strip()
        # 初始化New_column数组
        new_column = []
        # 处理促销码
        if promotion_code:
            promo_item = {
                "type": "promo_code",
                "description": promotion_name,
                "redeem": "false"
            }
            new_column.append(promo_item)
        # 处理优惠券码
        if coupon_code:
            coupon_item = {
                "coupon_code": coupon_code, # 字符串类型保留前导零
                "type": "coupon_code",
                "description": coupon_name,
                "redeem": "false"
            }
            new_column.append(coupon_item)
        # 组装行对象
        output.append({"New_column": new_column})

print(json.dumps(output, indent=2))

[
  {
    "New_column": [
      {
        "type": "promo_code",
        "description": "Gift Free Coin",
        "redeem": "false"
      }
    ]
  },
  {
    "New_column": [
      {
        "coupon_code": "000420",
        "type": "coupon_code",
        "description": "new join $300",
        "redeem": "false"
      }
    ]
  },
  {
    "New_column": [
      {
        "type": "promo_code",
        "description": "VIP -10%",
        "redeem": "false"
      },
      {
        "coupon_code": "006969",
        "type": "coupon_code",
        "description": "new join $300",
        "redeem": "false"
      }
    ]
  }
]

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