Pandas Read JSON
Pandas read_json() — Beginner to Advanced Guide
read_json() is used to load JSON data into a Pandas DataFrame.
JSON is super common in:
APIs
Web apps
Config files
Logs
1. Basic Syntax (Beginner)
- Reads a JSON file
- Converts it into a DataFrame automatically
2. Read JSON from a String
- Useful when JSON comes from API response or variable
3. Read JSON from URL (API Data)
- Direct API → DataFrame
- Very common in data science projects
4. JSON Orient (VERY IMPORTANT)
JSON can have different structures.orient tells Pandas how data is arranged.
Common orient values:
| orient | Structure |
|---|---|
records | list of dicts |
index | dict of dicts |
columns | column-wise |
values | values only |
table | schema-based |
orient='records' (Most Common)
JSON:
orient='index'
5. Nested JSON (Intermediate Level)
Example JSON:
read_json() alone is NOT enough
1 2 3 4 5 6 7 8 9 | import pandas as pd from pandas import json_normalize import json with open("data.json") as f: data = json.load(f) df = json_normalize(data) print(df) |
- Flattens nested JSON
- Essential for API responses
6. Read JSON Lines (.jsonl) (Advanced)
Used in logs & big data
Correct way:
- Without
lines=True→ ERROR
7. Select Specific Keys from JSON
- Same as column selection in CSV
8. Handle Missing Values
or
9. Convert JSON Column to Separate Columns
Useful when:
10. Read Large JSON Files (Performance Tip)
- Saves memory
- Used in production systems
11. Common Errors & Fixes
ValueError: Trailing data
Use:
JSONDecodeError
Check:
Invalid commas
Missing brackets
Wrong quotes
12. read_json() vs json.load()
| Feature | read_json | json.load |
|---|---|---|
| Direct DataFrame | Yes | No |
| Nested control | No | Yes |
| API ready | Yes | No |
Best Practice
Simple JSON →
read_json()Complex nested JSON →
json.load()+json_normalize()
13. Interview Questions (Favorite)
Q1: Can Pandas read API JSON directly?
Yes, using URL
Q2: How to read JSON logs?lines=True
Q3: How to flatten nested JSON?json_normalize()
