Loading a file with more than one line of JSON into Pandas

Question

From version 0.19.0 of Pandas you can use the lines parameter, like so:

import pandas as pd

data = pd.read_json('/path/to/file.json', lines=True)

You have to read it line by line. For example, you can use the following code provided by ryptophan on reddit:

import pandas as pd

# read the entire file into a python array
with open('your.json', 'rb') as f:
    data = f.readlines()

# remove the trailing "\n" from each line
data = map(lambda x: x.rstrip(), data)

# each element of 'data' is an individual JSON object.
# i want to convert it into an *array* of JSON objects
# which, in and of itself, is one large JSON object
# basically... add square brackets to the beginning
# and end, and have all the individual business JSON objects
# separated by a comma
data_json_str = "[" + ','.join(data) + "]"

# now, load it into pandas
data_df = pd.read_json(data_json_str)

The following code helped me to load JSON content into a dataframe:

import json
import pandas as pd

with open('Appointment.json', encoding="utf8") as f:
    data = f.readlines()
    data = [json.loads(line) for line in data] #convert string to dict format
df = pd.read_json(data) # Load into dataframe

I've also faced same problem. It happens when your data is written in lines separated by endlines like '\n'; You need to first read them in lines, then convert each line to python built-in types. I solved it in this way:

with open("/path/to/file") as f:
    content = f.readlines()

data = [eval(c) for c in content]
data = pd.DataFrame(data)

Good luck!

I had a similar problem.

It turns out that pd.read_json(myfile.json) will search in the parent folder automatically, but it returns this 'trailing data' error if you're not in the same folder as the file.

I figured it out, because when I tried to do it with open('myfile.json', 'r'), and I got a FileNotFound error, so I checked the paths.

I had failed to move myfile.json into the same folder as my notebook.

Changing it to pd.read_json('../myfile.json') just worked.