CODEWITHSUNDEEP
jsonpython-3.xpandas
Pavan Suvarna
Pavan Suvarna

Reputation: 501

How to read Json file and convert it to dataframe

I was trying to read the JSON file and convert to JSON, but I am finding difficulties here as i dont have much knowledge on this.

from pandas.io.json import json_normalize
import pandas as pd
import json

Path = "NavigatorInstances.json"
with open(Path, 'r') as myfile:
    data= myfile.read()
     
data = json.loads(data)

df = pd.DataFrame.from_dict(json_normalize(data))

I am getting error as: json.decoder.JSONDecodeError: Expecting value: line 2 column 13 (char 15)

My JSON Sample data looks like below

{ 
    "_id" : ObjectId("5ecfe5a0f9fcb510c8ec51e5"), 
    "RNI_Corps" : {
        "MetaData" : {
            "TaxonomyName" : "RN.Corps", 
            "InstanceName" : "Ratings Navigator Instance Corps", 
            "ThisMongoObjectId" : "5ecfe5a0f9fcb510c8ec51e5", 
            "ThisObjectShortId" : "37187", 
            "ReplacedMongoObjectId" : "", 
            "ThreadIDs" : "5ecfe5a0f9fcb510c8ec51e5", 
            "CurrentWF_Activity" : "Drafted", 
            "CurrentWF_ActivityDate" : "2020-05-28 16:23:53", 
            "VersionID" : {
                "Tool" : "RN", 
                "Group" : "Corps", 
                "Sector" : "GenCos", 
                "Version" : "2.8.1.1"
            }, 
            "Cart" : {
                "Id" : null, 
                "Status" : null, 
                "StatusDate" : null, 
                "Locked" : null
            }, 
            "Effective" : {
                "Date" : null, 
                "Reason" : null, 
                "Source" : {
                    "SystemName" : null, 
                    "SystemId" : null, 
                    "EventType" : null
                }
            }, 
            "Criteria" : {
                "Id" : "10123001", 
                "Name" : "Exposure Draft: Sector Navigators", 
                "Date" : "2020-05-20 00:00:00"
            }, 
            "InstanceFileInfo" : {
                "MongoObjectId" : "5ecfe5a0f9fcb510c8ec51df", 
                "MD5CheckSum" : "2d28eabe1a046f76e17a58cca6c386f1", 
                "Name" : "RN_2_8_1_1_96781051_2020_05_28_1.xlsm", 
                "SavedDate" : "2020-05-28 16:24:00"
            }, 
            "EntityInfo" : {
                "AgentID" : NumberInt(1507132), 
                "AgentName" : "AES Mexico Generation Holdings, S. de R.L. de C.V.", 
                "NicknameID" : null, 
                "Nickname" : null, 
                "IssuerID" : NumberInt(96781051), 
                "IssuerName" : "AES Mexico Generation Holdings, S. de R.L. de C.V.", 
                "Region" : "Emerging Markets - Americas", 
                "CountryName" : "Mexico", 
                "Sovereign_Agent_ID" : null, 
                "Sector" : "GenCos"
            },

Please help me to understand how I can convert the JSON data into a readable format such as pandas dataframe

Upvotes: 0

Views: 89

Answers (1)

KaMui
KaMui

Reputation: 168

Your sample is not in the JSON standard(or it is only a sample?). The word "ObjectId" is not a string or number. I think you can try https://www.json.cn to verify your JSON file first. This error is about json not DataFrame.

Upvotes: 1

Related Questions