如何清洗从Excel提取的畸形JSON数据并转换为标准Python字典

首先明确你提供的样本存在的可复现异常点：

• HTML转义符"替代了JSON标准双引号
• JSON结构的逗号分隔符全被替换为/
• 字符串首尾存在多余的转义反斜杠、冗余双引号
• 部分字段值（如IP字段）内部存在合法的带空格斜杠/ ，需要避免被误替换

清洗实现（Python） #

核心思路是先做转义解码，再通过占位符区分合法斜杠和分隔符斜杠，替换后直接用标准JSON库解析即可：

import json
import html
import pandas as pd

def clean_malformed_json(raw_str):
    # 1. 解码HTML转义字符，将"转换为标准双引号
    cleaned = html.unescape(raw_str)
    # 2. 清理首尾冗余字符
    cleaned = cleaned.strip()
    # 处理开头多余的反斜杠
    if cleaned.startswith('{\\'):
        cleaned = '{' + cleaned[2:]
    # 处理结尾多余的双引号
    if cleaned.endswith('"'):
        cleaned = cleaned[:-1]
    # 3. 区分合法斜杠和分隔符斜杠，避免误改字段值
    # 先把值内部带空格的斜杠替换为临时占位符
    cleaned = cleaned.replace('/ ', '###TEMP_SLASH###')
    # 替换所有剩余的分隔符斜杠为JSON标准逗号
    cleaned = cleaned.replace('/', ',')
    # 把占位符换回原合法斜杠
    cleaned = cleaned.replace('###TEMP_SLASH###', '/ ')
    # 4. 解析为标准Python字典
    return json.loads(cleaned)

# 调用示例：清洗Excel指定列
df = pd.read_excel("你的Excel文件路径.xlsx")
# 替换为你实际存储异常JSON的列名
df['parsed_result'] = df['目标列名'].apply(clean_malformed_json)

异常兼容说明 #

如果少量单元格存在其他异常导致解析失败，可在函数内增加异常捕获，输出对应原始字符串后针对性补充替换规则即可，比如：

def clean_malformed_json(raw_str):
    try:
        # 上述清洗逻辑
        cleaned = html.unescape(raw_str)
        cleaned = cleaned.strip()
        if cleaned.startswith('{\\'):
            cleaned = '{' + cleaned[2:]
        if cleaned.endswith('"'):
            cleaned = cleaned[:-1]
        cleaned = cleaned.replace('/ ', '###TEMP_SLASH###')
        cleaned = cleaned.replace('/', ',')
        cleaned = cleaned.replace('###TEMP_SLASH###', '/ ')
        return json.loads(cleaned)
    except Exception as e:
        print(f"解析失败，原始字符串：{raw_str}，错误信息：{e}")
        return None

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