含嵌套字典的txt文件转Pandas DataFrame报错及处理方案问询

问题背景 #

读取包含字典结构的store.txt文件时，使用pd.read_csv报错：Error tokenizing data. C error: Expected 4 fields in line 2, saw 11，核心原因是字典内部的逗号与文件的列分隔符逗号冲突，导致解析时字段数不匹配。

数据示例 #

id,name,storeid,report
11,JohnSmith,3221-123-555,{"Source":"online","FileFormat":0,"Isonline":true,"comment":"NAN","itemtrack":"110", "info": {"haircolor":"black", "age":53}, "itemsboughtid":[],"stolenitem":[{"item":"candy","code":1},{"item":"candy","code":1}]}
35,BillyDan,3221-123-555,{"Source":"letter","FileFormat":0,"Isonline":false,"comment":"this is the best store, hands down and i will surely be back...","itemtrack":"110", "info": {"haircolor":"black", "age":21},"itemsboughtid":[1,42,465,5],"stolenitem":[{"item":"shoe","code":2}]}
64,NickWalker,3221-123-555, {"Source":"letter","FileFormat":0,"Isonline":false, "comment":"we need this area to be fixed, so much stuff is everywhere and i     do not like this one bit at all, never again...","itemtrack":"110", "info": {"haircolor":"red", "age":22},"itemsboughtid":[1,2],"stolenitem":[{"item":"sweater","code":11},{"item":"mask","code":221},{"item":"jack,jill","code":001}]}

解决方案 #

方法1：固定分隔次数拆分（高效适配任意字典键数量） #

利用前3列不含逗号的特性，按逗号拆分3次，剩余部分直接作为JSON字符串解析，自动展开所有字典键为DataFrame列：

import pandas as pd
import json

data = []
with open('store.txt', 'r', encoding='utf-8') as f:
    # 读取表头
    header = f.readline().strip().split(',')
    for line in f:
        line = line.strip()
        if not line:
            continue
        # 仅拆分前3个字段，剩余部分作为report的JSON内容
        id_val, name, storeid, report_str = line.split(',', 3)
        # 解析JSON字符串（去除开头可能的空格）
        report_dict = json.loads(report_str.strip())
        # 合并基础字段与字典内容
        row = {header[0]: id_val, header[1]: name, header[2]: storeid, **report_dict}
        data.append(row)

df = pd.DataFrame(data)

方法2：预处理文件给JSON部分加引号 #

自动给每行的JSON内容包裹双引号，让pd.read_csv识别为单个字段，再解析JSON并展开列：

import pandas as pd
import json

# 预处理文件，给JSON部分加双引号
with open('store.txt', 'r', encoding='utf-8') as f_in, open('store_processed.txt', 'w', encoding='utf-8') as f_out:
    f_out.write(f_in.readline())  # 写入表头
    for line in f_in:
        line = line.strip()
        if not line:
            continue
        # 定位JSON起始的{位置
        brace_pos = line.find('{')
        # 前半部分保留，后半部分用双引号包裹
        processed_line = f"{line[:brace_pos]}\"{line[brace_pos:]}\"\n"
        f_out.write(processed_line)

# 读取预处理后的文件，指定quotechar忽略内部逗号
df = pd.read_csv('store_processed.txt', quotechar='"')
# 解析JSON并展开为新列
df = df.join(df['report'].apply(json.loads).apply(pd.Series))
# 可选：删除原report列
df = df.drop('report', axis=1)

方法3：处理嵌套字典（如info字段） #

如果字典包含嵌套结构，可单独展开嵌套字段：

# 展开info子字典，并重命名列避免冲突
df = df.join(df['info'].apply(pd.Series).rename(columns=lambda x: f'info_{x}'))
# 删除原info列
df = df.drop('info', axis=1)

关键提示 #

• 确保report列的内容是合法JSON格式（比如示例中的001会被解析为数字1，若需保留字符串，原文件应写成"001"）
• 方法1是效率最高的方案，无需额外文件预处理，直接完成解析与列展开

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