解决pandas.read_json报错ValueError: Expected object or value的问题

我尝试用以下代码读取JSON文件并转存为CSV：

import pandas as pd
df = pd.read_json('publicextract.charity.json')
csvData = df.to_csv('new.csv')

执行read_json时触发错误：

Error - loads(json, precise_float=self.precise_float), dtype=None
ValueError: Expected object or value

JSON数据来自慈善机构注册文件，示例格式如下：

[{"date_of_extract":"2022-10-15T00:00:00","organisation_number":1,"registered_charity_number":200027,"linked_charity_number":1,"charity_name":"POTTERNE MISSION ROOM AND TRUST","charity_type":null,"charity_registration_status":"Removed","date_of_registration":"1962-05-17T00:00:00","date_of_removal":"2014-04-16T00:00:00","charity_reporting_status":null,"latest_acc_fin_period_start_date":null,"latest_acc_fin_period_end_date":null,"latest_income":null,"latest_expenditure":null,"charity_contact_address1":null,"charity_contact_address2":null,"charity_contact_address3":null,"charity_contact_address4":null,"charity_contact_address5":null,"charity_contact_postcode":null,"charity_contact_phone":null,"charity_contact_email":null,"charity_contact_web":null,"charity_company_registration_number":null,"charity_insolvent":false,"charity_in_administration":false,"charity_previously_excepted":null,"charity_is_cdf_or_cif":null,"charity_is_cio":null,"cio_is_dissolved":null,"date_cio_dissolution_notice":null,"charity_activities":null,"charity_gift_aid":null,"charity_has_land":null}
,{"date_of_extract":"2022-10-15T00:00:00","organisation_number":2,"registered_charity_number":200027,"linked_charity_number":2,"charity_name":"HITCHAM FREE CHURCH","charity_type":null,"charity_registration_status":"Registered","date_of_registration":"1962-05-17T00:00:00","date_of_removal":null,"charity_reporting_status":null,"latest_acc_fin_period_start_date":null,"latest_acc_fin_period_end_date":null,"latest_income":null,"latest_expenditure":null,"charity_contact_address1":null,"charity_contact_address2":null,"charity_contact_address3":null....}]

这个错误通常是JSON文件格式不符合pandas默认读取规则，或存在格式瑕疵、文件过大导致的，可尝试以下方案：

方案1：指定行分隔JSON格式读取 #

大型数据集常采用每行一个独立JSON对象的格式，pandas默认无法识别，需添加lines=True参数：

import pandas as pd
# 读取行分隔格式的JSON
df = pd.read_json('publicextract.charity.json', lines=True)
# 保存为CSV，index=False避免生成冗余索引列
df.to_csv('new.csv', index=False)

方案2：手动读取并修复JSON格式 #

若文件存在轻微格式问题（如末尾多余逗号、转义异常），用Python原生json模块先解析再转DataFrame：

import pandas as pd
import json

# 读取文件内容
with open('publicextract.charity.json', 'r', encoding='utf-8') as f:
    data = json.load(f)

# 转成DataFrame并保存
df = pd.DataFrame(data)
df.to_csv('new.csv', index=False)

若仍报错，可开启非严格模式忽略轻微格式错误：

import pandas as pd
import json

with open('publicextract.charity.json', 'r', encoding='utf-8') as f:
    content = f.read()
    # 非严格模式解析JSON
    data = json.loads(content, strict=False)

df = pd.DataFrame(data)
df.to_csv('new.csv', index=False)

方案3：分块读取超大文件 #

若文件体积过大导致内存不足，可分块读取并写入CSV：

import pandas as pd

# 分块读取JSON，每块10000条数据
chunk_iter = pd.read_json('publicextract.charity.json', lines=True, chunksize=10000)

# 逐块写入CSV，仅第一块写入表头
for i, chunk in enumerate(chunk_iter):
    chunk.to_csv('new.csv', mode='a', index=False, header=(i==0))

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