How to Read JSON into a Pandas DataFrame

Loading JSON into pandas sounds simple — until you hit nested objects, the cryptic orient parameter, or the Expected object or value error. This guide covers the right way to read JSON into a DataFrame, including messy real-world cases.

The basic case

import pandas as pd
df = pd.read_json('data.json')

This works when your JSON is an array of flat objects:

[
  {"id": 1, "name": "Alice"},
  {"id": 2, "name": "Bob"}
]

The "Expected object or value" error

This comes from Python's json module, which pandas uses internally. Common causes:

• Malformed JSON (missing brackets, commas, quotes)
• The file is NDJSON (one object per line), not a single array
• The server returned an HTML error page instead of JSON
• Encoding issues or a partial download

First step: validate the file before blaming pandas.

Line-delimited JSON (NDJSON)

Many exports use NDJSON — one object per line, no enclosing array. Use lines=True:

df = pd.read_json('data.ndjson', lines=True)

The orient parameter

• records — list of dicts (most common)
• columns — pandas default
• index — keyed by row index
• split — separate keys for columns, index, data
• values — a 2D array

df = pd.read_json('data.json', orient='records')

Flattening nested JSON

Real API responses are nested. Use json_normalize to flatten them into columns:

import json, pandas as pd
with open('data.json') as f:
    raw = json.load(f)
df = pd.json_normalize(raw)

Nested keys like user.name become flat columns. Control the record path and metadata:

df = pd.json_normalize(raw, record_path='items', meta=['id'])

Reading from an API

import requests, pandas as pd
res = requests.get('https://api.example.com/data')
df = pd.json_normalize(res.json())

Check res.status_code and the content type first — an HTML error page triggers "Expected object or value".

Writing back to JSON

df.to_json('output.json', orient='records', indent=2)