Import JSON Lines into Pandas

Question

I want to import a JSON lines file into pandas. I tried to import it like a regular JSON file, but it did not work:

js = pd.read_json (r'C:\Users\Name\Downloads\profilenotes.jsonl')

Answer 1

This medium article provides a fairly simple answer, which can be adapted to be even shorter. All you need to do is read each line then parse each line with json.loads(). Like this:

import json
import pandas as pd


lines = []
with open(r'test.jsonl') as f:
    lines = f.read().splitlines()

line_dicts = [json.loads(line) for line in lines]
df_final = pd.DataFrame(line_dicts)

print(df_final)

As cgobat pointed out in a comment, the medium article adds a few extra unnecessary steps, which have been optimized in this answer.

Answer 2

If your lines have nested expressions like {"A": {"B":3235,"C":2142}} and you want the dataframe to have columns like A.B, A.C instead of A:{"B":3235, "C":2142}, you should use:

import json
import pandas as pd

lines = []
with open(r'test.jsonl') as f:
    lines = f.read().splitlines()
data = [json.loads(line) for line in x.strip().split('\n')]
df = pd.normalize_json(data)