JSON数据解析失败，转换为DataFrame报错ValueError: Trailing data

我参考相关问题编写了发送AJAX请求的代码，已移除cookie和CSRF相关代码，请求能正常返回状态码，但提取JSON响应中的数据并转换为DataFrame或字典时失败。

请求代码：

import requests
import json

headers = {
    'authority': 'services-dynarep.ddwa.finra.org',
    'accept': 'application/json, text/plain, */*',
    'accept-language': 'en-US,en;q=0.6',
    'content-type': 'application/json',
}

data = '{"fields":["issueSymbolIdentifier","issuerName","isCallable","productSubTypeCode","couponRate","maturityDate","industryGroup","moodysRating","standardAndPoorsRating","lastSalePrice","lastSaleYield"],"dateRangeFilters":[],"domainFilters":[],"compareFilters":[],"multiFieldMatchFilters":[{"fuzzy":false,"searchValue":"gme","synonym":true,"fields":[{"name":"issuerName","boost":1}]}],"orFilters":[],"aggregationFilter":null,"sortFields":["+issuerName"],"limit":50,"offset":0,"delimiter":null,"quoteValues":false}'

response = requests.post('https://services-dynarep.ddwa.finra.org/public/reporting/v2/data/group/FixedIncomeMarket/name/CorporateAndAgencySecurities',
                         headers=headers, data=data)
print(response.status_code)

我尝试的处理代码：

data = json.dumps(response.json()['returnBody']['data'], indent=4)

print(data.replace('\n', '').replace('\\', ''))
# print(pd.read_json(data.replace('\n', '').replace('\\', '')))

执行后输出的内容（包裹在双引号中的JSON数组）：

"[{"isCallable":"Y","couponRate":null,"issueSymbolIdentifier":"NTEB5563784","issuerName":"NTE MOBILITY PARTNERS SEGMENTS 3 LLC","maturityDate":"2028-06-30","productSubTypeCode":"CORP","moodysRating":"Baa2","standardAndPoorsRating":null,"lastSaleYield":null,"industryGroup":null,"lastSalePrice":null},{"isCallable":"Y","couponRate":null,"issueSymbolIdentifier":"NTEB5563785","issuerName":"NTE MOBILITY PARTNERS SEGMENTS 3 LLC","maturityDate":"2028-06-30","productSubTypeCode":"CORP","moodysRating":"Baa2","standardAndPoorsRating":null,"lastSaleYield":null,"industryGroup":null,"lastSalePrice":100},{"isCallable":"Y","couponRate":null,"issueSymbolIdentifier":"NTEB5563786","issuerName":"NTE MOBILITY PARTNERS SEGMENTS 3 LLC","maturityDate":"2028-06-30","productSubTypeCode":"CORP","moodysRating":"Baa2","standardAndPoorsRating":null,"lastSaleYield":null,"industryGroup":null,"lastSalePrice":100}]"

尝试转换时触发报错ValueError: Trailing data，请问问题出在哪？

核心问题是你误解了response.json()['returnBody']['data']的类型：它本身就是字符串格式的JSON数组，而非Python原生的列表/字典对象。你用json.dumps()处理它，相当于把这个字符串再次序列化为JSON字符串，最终得到的是被双引号包裹的"字符串的字符串"，这会导致pandas解析时出错。

正确的处理步骤：

1. 用json.loads()把response.json()['returnBody']['data']解析为Python列表对象
2. 直接将这个列表传入pd.DataFrame()即可生成DataFrame，不需要任何字符串替换操作

修正后的代码：

import requests
import json
import pandas as pd

headers = {
    'authority': 'services-dynarep.ddwa.finra.org',
    'accept': 'application/json, text/plain, */*',
    'accept-language': 'en-US,en;q=0.6',
    'content-type': 'application/json',
}

data = '{"fields":["issueSymbolIdentifier","issuerName","isCallable","productSubTypeCode","couponRate","maturityDate","industryGroup","moodysRating","standardAndPoorsRating","lastSalePrice","lastSaleYield"],"dateRangeFilters":[],"domainFilters":[],"compareFilters":[],"multiFieldMatchFilters":[{"fuzzy":false,"searchValue":"gme","synonym":true,"fields":[{"name":"issuerName","boost":1}]}],"orFilters":[],"aggregationFilter":null,"sortFields":["+issuerName"],"limit":50,"offset":0,"delimiter":null,"quoteValues":false}'

response = requests.post('https://services-dynarep.ddwa.finra.org/public/reporting/v2/data/group/FixedIncomeMarket/name/CorporateAndAgencySecurities',
                         headers=headers, data=data)

# 解析嵌套的JSON字符串
raw_data_str = response.json()['returnBody']['data']
parsed_data = json.loads(raw_data_str)

# 转换为DataFrame
df = pd.DataFrame(parsed_data)
print(df)

这样就能正常生成包含所需数据的DataFrame，不会再触发解析错误。

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