CODEWITHSUNDEEP
jsonpandasparsingmachine-learningserialization
Alberto Tono
Alberto Tono

Reputation: 324

Expected object or value: Importing JSON into Python (Pandas)

I am using Python 3 and I tried with 

data = pd.read_json('file.json',encoding="utf-8",orient='records',lines=True)

But It gives me:

ValueError: Expected object or value

This is the structure of the Json file, just a quick sample

{ 
"_id" : ObjectId("5af1b1fd4f4733eacf11dba9"), 
"centralPath" : "XXX2", 
"viewStats" : [
    {
        "totalViews" : NumberInt(3642), 
        "totalSheets" : NumberInt(393), 
        "totalSchedules" : NumberInt(427), 
        "viewsOnSheet" : NumberInt(1949), 
        "viewsOnSheetWithTemplate" : NumberInt(625), 
        "schedulesOnSheet" : NumberInt(371), 
        "unclippedViews" : NumberInt(876), 
        "createdOn" : ISODate("2017-10-13T18:06:45.291+0000"), 
        "_id" : ObjectId("59e100b535eeefcc27ee0802")
    }, 
    {
        "totalViews" : NumberInt(3642), 
        "totalSheets" : NumberInt(393), 
        "totalSchedules" : NumberInt(427), 
        "viewsOnSheet" : NumberInt(1949), 
        "viewsOnSheetWithTemplate" : NumberInt(625), 
        "schedulesOnSheet" : NumberInt(371), 
        "unclippedViews" : NumberInt(876), 
        "createdOn" : ISODate("2017-10-13T19:11:47.530+0000"), 
        "_id" : ObjectId("59e10ff3eb0de5740c248df2")
    }
]

}

With this method, I am able to see the data but I would like to have 

with open('file.json', 'r') as viewsmc:
    data = viewsmc.readlines()

With this the output

['{ \n',
 '    "_id" : ObjectId("5af1b1fd4f4733eacf11dba9"), \n',
 '    "centralPath" : "XXX2", \n',
 '    "viewStats" : [\n',
 '        {\n',
 '            "totalViews" : NumberInt(3642), \n',
 '            "totalSheets" : NumberInt(393), \n',
 '            "totalSchedules" : NumberInt(427), \n',
 '            "viewsOnSheet" : NumberInt(1949), \n',
 '            "viewsOnSheetWithTemplate" : NumberInt(625), \n',
 '            "schedulesOnSheet" : NumberInt(371), \n',
 '            "unclippedViews" : NumberInt(876), \n',
 '            "createdOn" : ISODate("2017-10-13T18:06:45.291+0000"), \n',
 '            "_id" : ObjectId("59e100b535eeefcc27ee0802")\n',
 '        }, \n',

I tried all different method and solution reported on the read_json / https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_json.html and load/ loads(str) etc. but nothing.

Upvotes: 0

Views: 642

Answers (2)

Alberto Tono
Alberto Tono

Reputation: 324

The Issue was with the format of the JSON file, we tested with https://jsonformatter.curiousconcept.com/ and modify with a regular expression If you have better suggestions let me know.

import re

with open("views3.json", "r+") as read_file:
   data = read_file.read()
   x = re.sub("\w+\((.+)\)", r'\1', data)
   print(x)

read_file.closed

Upvotes: 1

Frenchy
Frenchy

Reputation: 17027

do you want that?: use modul json for reading file.json

import pandas as pd
import json

with open('file.json') as viewsmc:
   data = json.load(viewsmc)
print data #you have a dict

df = pd.DataFrame(data)
print(df)

Upvotes: 0

Related Questions