CSV列特定行转JSON及字段提取与多值处理技术问询

一、修复不规范的JSON格式 #

你遇到的JSONDecodeError主要是因为id值为未加引号的数字，再加上键名/值的多余空格、格式冗余字符（比如多余的]）导致JSON解析失败。可以用正则表达式预处理字符串，将其转为合法JSON：

预处理函数示例 #

import re

def fix_invalid_json(s):
    if not s or s.strip() == "":
        return ""
    # 1. 去掉首尾多余的括号、换行和空格
    cleaned = s.strip().strip("[]{}")
    # 2. 把id后的数字转为带引号的字符串
    cleaned = re.sub(r'("id"\s*:\s*)(\d+)', r'\1"\2"', cleaned)
    # 3. 去掉键名后的多余空格（比如"label " → "label"）
    cleaned = re.sub(r'("\w+)\s*"', r'\1"', cleaned)
    # 4. 去掉字符串值前后的多余空格（比如"Fruit " → "Fruit"）
    cleaned = re.sub(r'"\s*(.*?)\s*"', r'"\1"', cleaned)
    # 5. 拼接成合法的JSON对象格式
    return f"{{{cleaned}}}"

二、提取字段并生成新CSV #

结合预处理步骤，读取原CSV并提取label、id、dataType字段，生成符合要求的新CSV：

完整代码示例 #

import csv
import re
import json

def fix_invalid_json(s):
    if not s or s.strip() == "":
        return ""
    cleaned = s.strip().strip("[]{}")
    cleaned = re.sub(r'("id"\s*:\s*)(\d+)', r'\1"\2"', cleaned)
    cleaned = re.sub(r'("\w+)\s*"', r'\1"', cleaned)
    cleaned = re.sub(r'"\s*(.*?)\s*"', r'"\1"', cleaned)
    return f"{{{cleaned}}}"

def extract_fields(json_str):
    if not json_str:
        return ("", "", "")
    try:
        data = json.loads(json_str)
        return (
            data.get("label", ""),
            data.get("id", ""),
            data.get("dataType", "")
        )
    except json.JSONDecodeError:
        return ("", "", "")

# 读取原CSV并生成新CSV
with open("input.csv", "r", encoding="utf-8") as infile, open("output.csv", "w", encoding="utf-8", newline="") as outfile:
    reader = csv.DictReader(infile)
    writer = csv.writer(outfile)
    # 写入新表头
    writer.writerow(["label", "id", "dataType"])
    
    for row in reader:
        children_str = row.get("Children:", "")  # 匹配原CSV的"Children:"表头
        fixed_json = fix_invalid_json(children_str)
        label, id_val, data_type = extract_fields(fixed_json)
        writer.writerow([label, id_val, data_type])

三、处理同一单元格的多组键值对 #

如果单元格内包含重复的label/id/dataType键（比如{"label": "val1", "id":2, ..., "label": "val2", ...}），这种不合法的JSON无法直接解析，需要先拆分成多个独立对象再提取字段：

适配多组键值对的修改代码 #

def split_multiple_entries(s):
    if not s or s.strip() == "":
        return []
    # 匹配每个包含label、id、dataType的完整键值块
    pattern = re.compile(r'"label"\s*:\s*".*?",\s*"id"\s*:\s*\d+,\s*"dataType"\s*:\s*".*?"')
    matches = pattern.findall(s)
    entries = []
    for match in matches:
        # 对每个片段单独修复格式并解析
        fixed = fix_invalid_json(match)
        if fixed:
            data = json.loads(fixed)
            entries.append((
                data.get("label", ""),
                data.get("id", ""),
                data.get("dataType", "")
            ))
    return entries

# 处理多组键值对的场景
with open("input.csv", "r", encoding="utf-8") as infile, open("output_multi.csv", "w", encoding="utf-8", newline="") as outfile:
    reader = csv.DictReader(infile)
    writer = csv.writer(outfile)
    writer.writerow(["label", "id", "dataType"])
    
    for row in reader:
        children_str = row.get("Children:", "")
        entries = split_multiple_entries(children_str)
        if entries:
            # 每个键值组单独占一行
            for entry in entries:
                writer.writerow(entry)
        else:
            # 空行填充空值
            writer.writerow(["", "", ""])

可选调整 #

如果需要将同一行的多组值合并到同一单元格（比如用逗号分隔），可以修改extract_fields函数，将多个对应字段的值用分隔符拼接后写入。

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