使用Pandas读取JSON转CSV时遇ValueError: Expected object or value报错

I've run into this exact issue with HubSpot's API JSON responses before—let's break down what's going wrong and fix it without modifying your original single-line JSON file.

Why the Error Happens #

Your HubSpot JSON is a nested top-level object (with results and paging keys), not a flat array of objects. Pandas' read_json() defaults to expecting either:

• A JSON array at the top level, or
• A file with one JSON object per line

Since your data doesn't fit either of these, pandas throws that confusing ValueError.

Solution 1: Load JSON First, Extract Results #

First use Python's built-in json module to load the entire single-line JSON object, then extract the results array (which contains your contact data) to convert into a DataFrame:

import json
import pandas as pd

# Load the single-line JSON file without modifying it
with open('contacts.json', 'r') as json_file:
    raw_data = json.load(json_file)

# Convert the 'results' array to a DataFrame
contacts_df = pd.DataFrame(raw_data['results'])

Solution 2: Expand Nested properties Fields (Better for CSV) #

Notice each entry in results has a nested properties dictionary with the actual contact details (email, name, etc.). To flatten this into separate CSV columns (instead of having a single properties column with dictionaries), use pd.json_normalize():

import json
import pandas as pd

with open('contacts.json', 'r') as json_file:
    raw_data = json.load(json_file)

# Flatten nested properties into individual columns
contacts_df = pd.json_normalize(raw_data['results'])

# Export to CSV (exclude the index column for cleaner output)
contacts_df.to_csv('contacts.csv', index=False)

This will give you a CSV with columns like id, createdAt, properties.email, properties.firstname, etc.—perfect for downstream use.

Why Reinstalling Pandas Didn't Help #

The issue has nothing to do with your Pandas installation—it's purely a mismatch between your JSON structure and Pandas' default parsing logic. The fixes above work with any recent Pandas version.

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