Read json file as pandas dataframe?
Solution 1
If you open the file as binary ('rb'
), you will get bytes. How about:
with open('C:/Users/Alberto/nutrients.json', 'rU') as f:
Also as noted in this answer you can also use pandas directly like:
df = pd.read_json('C:/Users/Alberto/nutrients.json', lines=True)
Solution 2
From your code, it looks like you're loading a JSON file which has JSON data on each separate line. read_json
supports a lines
argument for data like this:
data_df = pd.read_json('C:/Users/Alberto/nutrients.json', lines=True)
Note
Removelines=True
if you have a single JSON object instead of individual JSON objects on each line.
Solution 3
Using the json module you can parse the json into a python object, then create a dataframe from that:
import json
import pandas as pd
with open('C:/Users/Alberto/nutrients.json', 'r') as f:
data = json.load(f)
df = pd.DataFrame(data)
Solution 4
if you want to convert it into an array of JSON objects, I think this one will do what you want
import json
data = []
with open('nutrients.json', errors='ignore') as f:
for line in f:
data.append(json.loads(line))
print(data[0])
Related videos on Youtube
Alberto Alvarez
Updated on July 09, 2022Comments
-
Alberto Alvarez almost 2 years
I am using python 3.6 and trying to download json file (350 MB) as pandas dataframe using the code below. However, I get the following error:
data_json_str = "[" + ",".join(data) + "] "TypeError: sequence item 0: expected str instance, bytes found
How can I fix the error?
import pandas as pd # read the entire file into a python array with open('C:/Users/Alberto/nutrients.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)
-
Biranchi over 4 yearsI used the above code and got "JSONDecodeError: Extra data: line 2 column 1 (char 110)"
-
Kubra Altun almost 3 yearswhat is Value1 here? name of the column?
-
Yashraj Nigam almost 3 years@Kubra Value1 is key and the corresponding value will be returned.
-
questionto42standswithUkraine about 2 yearsNo need for
'r'
in my case, though not harming either (untested, just from experience),