How to Read JSON in Pandas: Simple Guide with Examples

Use pandas.read_json() to load JSON data into a DataFrame. You can pass a JSON string, file path, or URL to this function to convert JSON data into a tabular format easily.

Syntax

The basic syntax to read JSON data into a pandas DataFrame is:

• pandas.read_json(path_or_buf, orient=None, typ='frame', ...)
• path_or_buf: JSON string, file path, or URL.
• orient: Format of JSON string (e.g., 'records', 'split').
• typ: Type of object to return, usually 'frame' for DataFrame.

python

import pandas as pd

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

Example

This example shows how to read JSON data from a string and convert it into a pandas DataFrame.

python

import pandas as pd

json_data = '''[
  {"name": "Alice", "age": 25, "city": "New York"},
  {"name": "Bob", "age": 30, "city": "Paris"},
  {"name": "Charlie", "age": 35, "city": "London"}
]'''

df = pd.read_json(json_data, orient='records')
print(df)

Output

      name  age      city
0    Alice   25  New York
1      Bob   30     Paris
2  Charlie   35    London

Common Pitfalls

Common mistakes when reading JSON in pandas include:

• Passing a JSON string without proper format or missing quotes.
• Not specifying the correct orient when JSON structure is not the default.
• Trying to read nested JSON without flattening it first.

Always check your JSON format and use orient parameter if needed.

python

import pandas as pd

# Wrong: JSON string with single quotes (invalid JSON)
json_wrong = "[{ 'name': 'Alice', 'age': 25 }]"

# Correct: Use double quotes for JSON strings
json_correct = '[{"name": "Alice", "age": 25}]'

df = pd.read_json(json_correct, orient='records')
print(df)

Output

    name  age
0  Alice   25

Quick Reference

Here is a quick summary of key parameters for pandas.read_json():

Parameter	Description	Default
path_or_buf	JSON string, file path, or URL to read from	None
orient	Format of JSON string (e.g., 'records', 'split', 'index')	None (auto-detect)
typ	Type of object to return ('frame' or 'series')	'frame'
convert_dates	Convert date-like strings to datetime	True
lines	Read JSON as line-delimited (one JSON object per line)	False

Key Takeaways

Use pandas.read_json() to load JSON data into a DataFrame easily.

Pass JSON string, file path, or URL as input to read_json().

Specify the orient parameter if JSON format is not default.

Ensure JSON strings use double quotes and valid JSON format.

For nested JSON, consider flattening before reading into pandas.