带前缀b的非标准JSON文件转Pandas DataFrame求助

问题核心 #

开头的b'...'是Python的字节串字面量，不属于JSON格式本身；同时你的数据是JSON Lines结构（每行一个独立JSON对象），这是导致pd.read_json()和json.loads()直接失效的原因。

具体解决步骤 #

1. 处理字节串转字符串 #

如果是从文件读取到的字节内容，直接用.decode('utf-8')转换为普通字符串；如果是复制的带b'前缀的内容，先手动去掉首尾的b'和'，再处理。

2. 两种解析方式 #

方式一：拆分后逐个解析JSON

import json
import pandas as pd

# 假设原始字节数据存在变量data中
data = b'{"Cost Center":"7F6600","ID Subtask":"A1","ID Task":"W8","Task":"OtC CS","Subtask":"Order to Bill"}\n{"Cost Center":"AU0408","ID Subtask":"D0","ID Task":"P0","Task":"Supply Chain","Subtask":"SC-ExtWH) CS&AH"}\n{"Cost Center":"BC0C6180","ID Subtask":"D0","ID Task":"P4","Task":"Supply Chain","Subtask":"SC(FG-ExtWH) CS&AH"}\n{"Cost Center":"CJ3101","ID Subtask":"A0","ID Task":"W8","Task":"OtC CS","Subtask":"Or to Bill"}\n{"Cost Center":"CJ61053B","ID Subtask":"A1","ID Task":"O5","Task":"Production","Subtask":"P(Corp.)"}\n{"Cost Center":"H1093","ID Subtask":"D0","ID Task":"P0","Task":"Supply Chain","Subtask":"SC(FG-H) CS&AH"}\n{"Cost Center":"IQSES61","ID Subtask":"A9","ID Task":"W8","Task":"OtC CS","Subtask":"Order to Bill"}'

# 解码字节串为字符串
str_data = data.decode('utf-8')
# 按换行拆分每行，过滤空行
json_lines = [line for line in str_data.split('\n') if line.strip()]
# 逐个解析JSON对象
json_list = [json.loads(line) for line in json_lines]
# 转为DataFrame
df = pd.DataFrame(json_list)

方式二：用Pandas直接读取JSON Lines

Pandas支持直接识别JSON Lines格式，只需指定lines=True参数：

import pandas as pd
from io import StringIO

str_data = data.decode('utf-8')
# 用StringIO模拟文件对象，直接读取
df = pd.read_json(StringIO(str_data), lines=True)

# 如果是从本地文件读取（文件需去掉b'前缀，保留每行JSON）
# df = pd.read_json('your_file.json', lines=True)

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