如何将Excel中类字典的损坏长字符串转换为Pandas DataFrame

核心思路 #

你手上的数据属于非严格JSON格式，存在分隔符错误、键名缺失双引号、数组格式易被误替换的问题，优先用容错性高的JSON解析工具处理，避免手动正则覆盖不全的问题。

步骤1：安装依赖 #

执行命令安装需要的库：

pip install demjson3 pandas openpyxl

步骤2：完整处理代码 #

import pandas as pd
import demjson3
import re

# 1. 读取Excel文件，假设存储JSON的列名为json_col，替换成你实际的列名
df_raw = pd.read_excel("你的文件路径.xlsx")
result_list = []

for raw_str in df_raw["json_col"]:
    # 2. 字符串预处理
    # 去掉首尾多余的引号、前缀（如示例中的1L:）、空白字符
    clean_str = re.sub(r"^.*?'{", "{", str(raw_str).strip())
    clean_str = re.sub(r"}'?$", "}", clean_str)
    # 把所有/替换为,
    clean_str = clean_str.replace("/", ",")
    # 修复infusions字段丢失方括号的问题（你之前替换时可能误删了括号）
    clean_str = re.sub(r"infusions:([\d,]+)", r"infusions:[\1]", clean_str)
    
    # 3. 非严格解析JSON为字典
    try:
        data_dict = demjson3.decode(clean_str)
        result_list.append(data_dict)
    except Exception as e:
        print(f"解析失败的字符串: {clean_str}, 错误: {e}")

# 4. 转换为DataFrame，可只保留你需要的字段
df_result = pd.DataFrame(result_list)[["riskProfile", "initialInvestment", "cashflowDate", "infusions"]]
print(df_result)

可选：不用第三方解析库的正则方案 #

如果不想安装demjson3，可以用正则给所有键名补双引号，再用标准json库解析：

import json

def add_quotes_to_keys(s):
    # 给键名加双引号
    s = re.sub(r'([\{,])\s*([a-zA-Z0-9_]+)\s*:', r'\1"\2":', s)
    # 给字符串值加双引号（排除数字、true、false、null）
    s = re.sub(r':\s*([a-zA-Z][a-zA-Z0-9_\-\s:.]*)\s*([,\}])', r':"\1"\2', s)
    return s

# 预处理后调用该方法，再用json.loads解析
clean_str = add_quotes_to_keys(clean_str)
data_dict = json.loads(clean_str)

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