ZetCode

Pandas JSON

last modified November 17, 2026 Python 3.15

JSON (JavaScript Object Notation) is a lightweight text format for exchanging data, built from objects, arrays, strings and numbers. Pandas can turn JSON into a DataFrame with read_json and write a DataFrame back with to_json.

We use small JSON strings directly in the code so that each example runs on its own, without any data files. The examples were verified with pandas 3.1.1.

Reading JSON

The read_json function parses JSON text into a DataFrame. Here the text is a JSON array of objects, so we pass orient='records': every object becomes one row, and its keys become column names. We do not need a file on disk — io.StringIO wraps the string in a file-like object, which is any object that can be read like an open file.

json_read.py
import io
import pandas as pd

json_string = '[{"name": "Alice", "age": 25}, {"name": "Bob", "age": 32}]'
df = pd.read_json(io.StringIO(json_string), orient='records')
print(df)

The two objects in the array became two rows. Pandas inferred the column names from the keys name and age, and gave the result its usual integer index.

$ python json_read.py
    name  age
0  Alice   25
1    Bob   32

Writing JSON with to_json

The to_json method converts a DataFrame to a JSON string. Its orient parameter controls the layout: records makes a list of row objects, columns makes an object keyed by column name, index makes an object keyed by row label, and values makes a bare list of lists with no labels at all.

json_orient.py
import pandas as pd

df = pd.DataFrame({'name': ['Alice', 'Bob'], 'age': [25, 32]})

print(df.to_json(orient='records'))
print(df.to_json(orient='columns'))
print(df.to_json(orient='index'))
print(df.to_json(orient='values'))

Compare the four lines: the first keeps both column names and row structure, the second groups values by column, the third groups them by index label, and the last throws the labels away and keeps only the data. Use orient='records' when another program expects a list of records, which is the most common shape for APIs.

$ python json_orient.py
[{"name":"Alice","age":25},{"name":"Bob","age":32}]
{"name":{"0":"Alice","1":"Bob"},"age":{"0":25,"1":32}}
{"0":{"name":"Alice","age":25},"1":{"name":"Bob","age":32}}
[["Alice",25],["Bob",32]]

Flattening nested records

Real JSON is often nested: an object may contain another object. The function json_normalize flattens such nested records, meaning records that hold other records inside them, into a flat DataFrame. The sep parameter chooses the separator between the outer and inner key.

json_normalize.py
import pandas as pd

data = [
    {'name': 'Alice', 'address': {'city': 'Bratislava', 'zip': '81101'}},
    {'name': 'Bob', 'address': {'city': 'Prague', 'zip': '11000'}}
]
df = pd.json_normalize(data, sep='_')
print(df)

The inner keys city and zip became the new columns address_city and address_zip. Without json_normalize those values would remain trapped inside an address column as Python dictionaries.

$ python json_normalize.py
    name address_city address_zip
0  Alice   Bratislava       81101
1    Bob       Prague       11000

Pretty printing with indent

JSON produced by to_json is compact by default: it has no superfluous whitespace. Pass indent to spread the output over several lines and indent nested levels, which is handy when you want to read the JSON yourself or include it in a configuration file.

json_indent.py
import pandas as pd

df = pd.DataFrame({'name': ['Alice', 'Bob'], 'age': [25, 32]})
print(df.to_json(orient='records', indent=2))

Each record now occupies several lines, indented by two spaces per level. The data is unchanged; only the surrounding whitespace differs.

$ python json_indent.py
[
  {
    "name":"Alice",
    "age":25
  },
  {
    "name":"Bob",
    "age":32
  }
]

Dates in JSON

JSON has no dedicated date type, so pandas writes dates as ISO 8601 strings — an international format that starts with the year, such as 2026-01-15. Set date_format='iso' when writing. When reading, read_json may leave the column as text, so we convert it explicitly with pd.to_datetime afterwards.

json_dates.py
import io
import pandas as pd

df = pd.DataFrame({
    'date': pd.to_datetime(['2026-01-15', '2026-02-20']),
    'value': [10, 20]
})
text = df.to_json(orient='records', date_format='iso')
print(text)
restored = pd.read_json(io.StringIO(text), orient='records')
restored['date'] = pd.to_datetime(restored['date'])
print(restored)
print(restored['date'].dtype)

The JSON holds timestamps such as 2026-01-15T00:00:00.000. After reading and converting, the column has a datetime64 data type again, so date-based operations work on it. On pandas 3.1.1 the unit is microseconds, shown as datetime64[us].

$ python json_dates.py
[{"date":"2026-01-15T00:00:00.000","value":10},{"date":"2026-02-20T00:00:00.000","value":20}]
        date  value
0 2026-01-15     10
1 2026-02-20     20
datetime64[us]

Source

Pandas read_json Documentation, Pandas DataFrame.to_json Documentation, and Pandas json_normalize Documentation

In this article we have read and written JSON, compared the four orientations, used io.StringIO for in-memory text, flattened nested records with json_normalize, formatted JSON with indent and handled dates. For another tabular format see the Pandas read CSV tutorial, and for SQL databases see the Pandas SQL tutorial.

Author

I am Jan Bodnar, a programmer and technical writer. I have been writing programming tutorials at ZetCode since 2007 and have published eight e-books. See about the author.

List all Pandas tutorials.