CODEWITHSUNDEEP
pythonjsonpython-3.xpandas
Alberto Alvarez
Alberto Alvarez

Reputation: 855

How to read a JSON file as a pandas DataFrame?

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)

Upvotes: 33

Views: 119297

Answers (6)

Yashraj Nigam
Yashraj Nigam

Reputation: 348

The easiest way to read json file using pandas is:

pd.read_json("sample.json",lines=True,orient='columns')

To deal with nested json like this

[[{Value1:1},{value2:2}],[{value3:3},{value4:4}],.....]

Use Python basics

value1 = df['column_name'][0][0].get(Value1)

Upvotes: 1

Stephen Rauch
Stephen Rauch

Reputation: 49774

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)

Upvotes: 10

Amir Md Amiruzzaman
Amir Md Amiruzzaman

Reputation: 2069

Please the code below

#call the pandas library
import pandas as pd
#set the file location as URL or filepath of the json file
url = 'https://www.something.com/data.json'
#load the json data from the file to a pandas dataframe
df = pd.read_json(url, orient='columns')
#display the top 10 rows from the dataframe (this is to test only)
df.head(10)

Please review the code and modify based on your need. I have added comments to explain each line of code. Hope this helps!

Upvotes: 0

cs95
cs95

Reputation: 402263

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
Remove lines=True if you have a single JSON object instead of individual JSON objects on each line.

Upvotes: 56

A.Emad
A.Emad

Reputation: 11

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])

Upvotes: 1

James Doepp - pihentagyu
James Doepp - pihentagyu

Reputation: 1308

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)

Upvotes: 14

Related Questions