Why does pd.read_json() give me a DataFrame with the wrong shape?

The orient argument doesn't match your JSON's actual structure. Most REST APIs return a list of flat objects, which needs orient='records' — check the raw JSON shape and set orient explicitly instead of relying on inference.

How do I handle JSON with deeply nested objects, like an address inside a user record?

Load the JSON with the standard json module, then pass the resulting list/dict to pd.json_normalize(), which flattens nested keys into dot-separated columns like address.city instead of leaving them as dicts inside a cell.

How do I send a DataFrame back to an API as JSON?

Use df.to_json(orient='records') to serialize the DataFrame into the same list-of-objects shape most APIs expect, or df.to_json('output.json') to write it directly to a file.

HTML MASTER CLASS /// LEARN TAGS /// BUILD STRUCTURE /// SEMANTIC WEB /// HTML MASTER CLASS /// LEARN TAGS ///

Reading JSON Files in Python

Learn about Reading JSON Files in this comprehensive Python tutorial. Learn how to parse JSON APIs, handle different orientations, and flatten deeply nested web data.

⚡ Total XP: 0|💻 pandas XP: 0

Skill Matrix

UNLOCK NODES BY LEARNING NEW TAGS.

System Hub

Core logic.

Quick Quiz //

What does pd.json_normalize() help with, that plain pd.read_json() doesn't handle well?

🚀 LEVEL UP TO SENIOR:Unlock 500+ Advanced Practical Challenges & Exercises.

🎓 COURSERA PARTNER:Earn professional Google, Meta, and IBM certificates to supercharge your resume.

Listen up. If you're going to process data in Python, you need to understand Reading JSON Files in Python. This is where data engineers separate themselves from script kiddies. It's about writing code that scales.

1Pandas read json Part 1

REST APIs overwhelmingly speak JSON, so pd.read_json() is often the first function standing between a live API response and a usable DataFrame. Called with just a path or URL, pd.read_json('data.json') parses the file and tries to infer how it maps to rows and columns automatically — but that inference only works reliably for a handful of common shapes.

That's where the orient argument comes in: it tells Pandas explicitly how the JSON is structured. orient='records' is the shape most APIs actually return — a list of flat objects like [{"id": 1, "name": "Pop"}, {"id": 2, "name": "Lolly"}] — while other orientations ('columns', 'index', 'split') describe JSON that's already organized column-by-column or keyed by row index. Picking the wrong orient doesn't always raise an error; it can just as easily produce a DataFrame with the wrong shape or columns full of dictionaries.

Real API payloads are rarely flat, though — a user record might nest an address object inside it, or a list of tags inside that. read_json() alone leaves those nested structures as Python dicts/lists sitting inside single cells, which is unusable for filtering or aggregation. pd.json_normalize() solves that by flattening nested dictionaries into their own dot-separated columns (e.g. address.city, address.zip), turning deeply nested JSON into a proper tabular DataFrame. And the round trip back out uses .to_json(), which serializes a DataFrame back into a JSON string or file for sending to another API or service.

—

# Example
import pandas as pd
print("Running Pandas...")

localhost:3000

Jupyter Notebook / Console Output

Code Executed Successfully
Data processed and aggregated.

2Step-by-Step Breakdown

JSON (JavaScript Object Notation) is the language of the web. Most REST APIs return data as JSON. Pandas can read it natively.

Which function is used to load a JSON file into a Pandas DataFrame?

→pd.load_json()
→pd.read_json()
→pd.parse_web()

JSON data can be structured in many different ways. In Pandas, we use the "orient" argument to specify how the JSON maps to rows and columns.

If your JSON file contains a list of dictionaries (like [{"id": 1, "name": "Pop"}]), which orient argument should you use?

→orient='columns'
→orient='records'
→orient='index'

Sometimes JSON data is deeply nested (dictionaries inside dictionaries). Standard read_json fails here. We use json_normalize to flatten it.

If an API returns deeply nested JSON, which Pandas function should you use to flatten the nested dictionaries into columns?

→pd.flatten_json()
→pd.read_json(nested=True)
→pd.json_normalize()

Now, prepare yourself. We are about to enter the ADA Defense Protocol. Ensure you understand how to export back to JSON.

ADA DEFENSE: You want to send your DataFrame data back to a web API. Which method exports a DataFrame to a JSON string or file?

→.to_json()
→.export_json()
→.write_web()

Threat neutralized. Web compatibility achieved. You can now communicate with APIs.

Threat neutralized. Concept validated. Proceed to the next section.

Parse Real JSON Records. Finish load_records(): use orient="records" for a JSON list of row-objects.

Level Up 🚀

Advanced cheat sheets, SEO tricks, and interview prep for this topic.

Browser Support

ChromeSupported

Fully supported.

FirefoxSupported

Fully supported.

SafariSupported

Fully supported.

EdgeSupported

Fully supported.

Accessibility (A11y)

1Document the Expected orient Up Front

Because the wrong orient can silently produce a misshapen DataFrame instead of an error, leaving a comment noting the JSON's actual structure next to the read_json() call saves the next reader from re-diagnosing the shape from scratch.

# API returns: [{"id": 1, "name": "Pop"}, ...]
df = pd.read_json(response_text, orient='records')

SEO Implications

1
'read_json orient' and 'flatten nested json pandas' Search Intent
Handling nested JSON API responses is one of the most common real-world Pandas pain points, so precise coverage of orient and json_normalize targets high-intent developer searches.

Best Practices

Inspect Raw JSON Structure Before Calling read_json()

Print or eyeball a sample of the raw JSON first — knowing whether it's a list of records, a dict of columns, or deeply nested determines which orient (or whether json_normalize) you actually need.

Use json_normalize() for Nested API Responses

Don't leave nested dictionaries sitting inside single DataFrame cells — flatten them with json_normalize() immediately after loading so downstream filtering and aggregation work on real columns.

Frequent Bugs

THE BUG

Calling pd.read_json() on deeply nested JSON without json_normalize(), leaving whole cells filled with unusable nested Python dicts instead of separate columns.

THE FIX

Load the raw JSON with the json module first, then pass it through pd.json_normalize() to flatten nested keys into their own columns.

Real-World Examples

Flattening a Nested User API Response

An API returns user records where the address is a nested object: [{"id": 1, "name": "Pop", "address": {"city": "NYC", "zip": "10001"}}] — the nested address needs to become its own columns.

import json
with open('users.json') as f:
    data = json.load(f)

df = pd.json_normalize(data)
print(df.columns)
# Index(['id', 'name', 'address.city', 'address.zip'], dtype='object')

Interview Prep

?Frequently Asked Questions

Pascual Vila

Frontend Instructor // Code Syllabus

Common Pitfalls & Errors

The Error //

Wrong orient produces a malformed DataFrame

# Wrong: API returns a list of records, but orient isn't specified correctly
df = pd.read_json("api_response.json", orient="columns")
# Produces a DataFrame with the wrong shape or a single garbled column

# Correct: match orient to the actual JSON structure
df = pd.read_json("api_response.json", orient="records")

The Solution //

read_json() infers structure but doesn't always guess right. Match orient to the actual JSON shape — most REST APIs return a list of flat records, which needs orient='records'.

The Error //

Nested JSON left unflattened

# Wrong: nested "address" object stays as a dict inside one cell
df = pd.read_json("users.json")
print(df["address"][0])  # {'city': 'NYC', 'zip': '10001'} - not usable as a column

# Correct: flatten nested keys into their own columns
import json
with open("users.json") as f:
    data = json.load(f)
df = pd.json_normalize(data)

The Solution //

read_json() alone doesn't unpack nested objects — they stay as raw Python dicts inside a single cell, which breaks any attempt to filter or aggregate on the nested fields. Use json_normalize() instead.