Working with JSON in Python and pandas: json.load, JSON Lines and read_json

JSON shows up in two shapes in data work: a single document (an object or an array) and JSON Lines, one JSON record per line, which is what most exports and log pipelines produce. Python’s json module handles single documents; pandas’ read_json handles both, as long as you tell it which one you have. This guide covers reading and writing with json, cleaning a malformed file, and loading both shapes into a DataFrame.

Read a JSON file with the `json` module

import json

# Open and read JSON file
with open("data.json", "r") as file:
    data = json.load(file)

print(data)

json.load() needs the whole file to be one valid JSON document. A valid JSON array of URLs looks like this:

["https://example.com/page1", "https://example.com/page2", "https://example.com/page3"]

Clean up a file with extra quotes or escapes

Sometimes a file holds JSON that was itself serialized as a string, with extra quotation marks and escaped quotes around it. json.load() then returns a string, or fails. One way to recover a list of URLs from such a file:

# Example of cleaning up JSON content
with open("data.json", "r") as file:
    json_content = file.read()

# Remove extra quotes and escape characters
cleaned_content = json_content.strip('"\n[]').replace('\\"', '"').split(',')

# Create a list from the cleaned content
urls = [url.strip('"') for url in cleaned_content]

print(urls)

This works for a flat list of simple strings. It breaks if a value contains a comma. If the file is valid JSON wrapped in a string, calling json.loads() on the result of json.load() is the cleaner fix.

Write JSON

data = ["https://example.com/page1", "https://example.com/page2", "https://example.com/page3"]

# Write to JSON file
with open("output.json", "w") as file:
    json.dump(data, file, indent=4)

Load a JSON array into a pandas DataFrame

When the file is an array of flat objects, pd.read_json() maps each object to a row and each key to a column:

[
    {
        "name": "John",
        "age": 30,
        "city": "New York"
    },
    {
        "name": "Anna",
        "age": 22,
        "city": "London"
    },
    {
        "name": "Mike",
        "age": 32,
        "city": "San Francisco"
    }
]

import pandas as pd

# Read the JSON file
df = pd.read_json('data.json')

print(df)

   name  age           city
0  John   30       New York
1  Anna   22         London
2  Mike   32  San Francisco

Load JSON Lines (one record per line)

Exports from document stores and event pipelines usually write one JSON object per line. Here is a survey-response export where each line is a separate record:

{"__key__":{"namespace":"","app":"s~app-12345","path":"\"responses_2022\", \"user1@example.com|XYZ123|2022-02-11|1|1\"","kind":"responses_2022","name":"user1@example.com|XYZ123|2022-02-11|1|1"},"__error__":[],"__has_error__":false}
{"startDate":"2021-10-20","stage":"I. Detection Stage","questionNum":"1","communityAdmin":"admin@example.com","title":"First Questionnaire","lastName":"Doe","endDate":"2022-08-15","firstName":"John","email":"user1@example.com","question":"Where do people migrate to in your community?","community":"Green Valley","response":"d) People do not migrate","age":"30","timestamp":"2022-01-28 21:13:59.289 UTC","__key__":{"namespace":"","app":"s~app-12345","path":"\"responses_2022\", \"user1@example.com|XYZ123|2021-10-20|1|1\"","kind":"responses_2022","name":"user1@example.com|XYZ123|2021-10-20|1|1"},"__error__":[],"__has_error__":false}
{"startDate":"2021-10-21","stage":"I. Detection Stage","questionNum":"4","communityAdmin":"admin@example.com","title":"First Questionnaire","lastName":"Smith","endDate":"2022-08-15","firstName":"Jane","email":"user2@example.com","question":"Did you pay for the recruiter's service?","community":"Blue Lake","response":"a) Yes","age":"25","timestamp":"2022-02-14 19:47:00.205 UTC","__key__":{"namespace":"","app":"s~app-12345","path":"\"responses_2022\", \"user2@example.com|XYZ123|2021-10-21|1|4\"","kind":"responses_2022","name":"user2@example.com|XYZ123|2021-10-21|1|4"},"__error__":[],"__has_error__":false}

Pass lines=True:

import pandas as pd

# Load JSON data from file
df = pd.read_json('data.json', lines=True)

print(df)

Then inspect what you loaded:

# Display the first few rows
print(df.head())

# Display information about the DataFrame
print(df.info())

# Display summary statistics
print(df.describe())

Records don’t need the same keys. The first line above has no survey fields, so its row gets NaN in those columns.

Gotchas

ValueError: Trailing data. This is what pd.read_json() raises on a JSON Lines file read without lines=True. The json module fails on the same file with json.decoder.JSONDecodeError: Extra data.
Nested objects stay nested. In the export above, __key__ becomes a column of Python dicts. Flatten nested fields with pd.json_normalize() on the parsed records if you need them as columns.
read_json converts types. With the export above (pandas 2.3), "age": "30" loads as float64 (float because of the missing value in the first row), and timestamp is parsed into datetime64[ns, UTC]. Pass dtype=False and convert_dates=False to keep the raw values.
String surgery is a last resort. The strip/replace/split cleanup above is only safe for flat lists of simple strings.

Selecting, dropping and inserting rows in a pandas DataFrame is the next step after loading.
Querying JSONB in PostgreSQL efficiently expands a JSON object into rows in SQL instead of pandas.
Understanding the differences between Parquet, Avro, JSON and CSV helps you decide when JSON is the wrong storage format.