CODEWITHSUNDEEP
pythonjsonpandasmongodb
Souvik
Souvik

Reputation: 905

KeyError Pandas

I am trying to read a nested JSON using json_normalize method of Pandas. I am trying to use one of the fields as the record_path. I have also included the errors = 'ignore' to ignore any errors due to missing key. Can you please help me with what am I doing wrong here?

Here is the JSON -

{
    "_id" : "31aa9894-6a43-40f9-8911-116c14c42636",
    "message" : {
        "serviceOperationName" : "/logUserEvents/event",
        "accountNumber" : "1234",
        "userId" : null,
        "market" : null,
        "extract" : {
            "request" : {
                "USER_EVENT_LOGGING" : {
                    "payload" : [ 
                        {
                            "eventType" : "audibleSummaryUsage",
                            "ntid" : "abc",
                            "accountNumber" : "Not Found",
                            "workOrderNumber" : "",
                            "data" : [ 
                                {
                                    "name" : "userAction",
                                    "value" : "DISMISSED"
                                }, 
                                {
                                    "name" : "employeeTenure",
                                    "value" : "3.9"
                                }, 
                                {
                                    "name" : "ffc",
                                    "value" : "1234"
                                }, 
                                {
                                    "name" : "ntid",
                                    "value" : "abcd"
                                }, 
                                {
                                    "name" : "isAccountView",
                                    "value" : "true"
                                }, 
                                {
                                    "name" : "userAction",
                                    "value" : "DISMISSED"
                                }, 
                                {
                                    "name" : "title",
                                    "value" : "abcd"
                                }, 
                                {
                                    "name" : "jobType",
                                    "value" : ""
                                }, 
                                {
                                    "name" : "jobClassCd",
                                    "value" : ""
                                }
                            ]
                        }
                    ]
                }
            },
            "response" : {}
        },
        "@timestamp" : "2021-02-18T05:38:48.00269Z",
        "eventKeys" : [ 
            "USER_EVENT_LOGGING"
        ],
        "requestStartTimestampText" : "2021-02-18T05:38:48.268Z"
    },
    "createdOn" : ISODate("2021-02-18T05:38:48.269Z")
}

/* 2 */
{
    "_id" : "4189da82-299d-4a9e-8f10-ddb5da9b97b5",
    "message" : {
        "serviceOperationName" : "/logUserEvents/event",
        "accountNumber" : "7890",
        "userId" : null,
        "market" : null,
        "extract" : {
            "request" : {
                "USER_EVENT_LOGGING" : {
                    "payload" : [ 
                        {
                            "eventType" : "audibleSummaryUsage",
                            "ntid" : "defg",
                            "accountNumber" : "Not Found",
                            "workOrderNumber" : "",
                            "data" : [ 
                                {
                                    "name" : "userAction",
                                    "value" : "DISMISSED"
                                }, 
                                {
                                    "name" : "userAction",
                                    "value" : "DISMISSED"
                                }, 
                                {
                                    "name" : "employeeTenure",
                                    "value" : "3.9"
                                }, 
                                {
                                    "name" : "jobType",
                                    "value" : ""
                                }, 
                                {
                                    "name" : "jobClassCd",
                                    "value" : ""
                                }, 
                                {
                                    "name" : "ntid",
                                    "value" : "dfer"
                                }, 
                                {
                                    "name" : "ffc",
                                    "value" : "3456"
                                }, 
                                {
                                    "name" : "title",
                                    "value" : "erty"
                                }, 
                                {
                                    "name" : "isAccountView",
                                    "value" : "true"
                                }
                            ]
                        }
                    ]
                }
            },
            "response" : {}
        },
        "@timestamp" : "2021-02-18T05:39:11.00659Z",
        "eventKeys" : [ 
            "USER_EVENT_LOGGING"
        ],
        "requestStartTimestampText" : "2021-02-18T05:39:11.658Z"
    },
    "createdOn" : ISODate("2021-02-18T05:39:11.659Z")
}

Here is the code -

db = mongo_client.conciselogs
col = db.logs
cursor = col.find({"message.extract.request.USER_EVENT_LOGGING.payload.eventType":"audibleSummaryUsage"})
mongo_docs = list(cursor)
df = pd.json_normalize(mongo_docs, ['message.extract.request.USER_EVENT_LOGGING.payload.data'], errors = 'ignore')
df.to_csv('sample_data0220_3.csv', index=False)```

Upvotes: 1

Views: 271

Answers (1)

bherbruck
bherbruck

Reputation: 2226

Your record_path argument is incorrect, it should be a list:

df = pd.json_normalize(
    mongo_docs,
    ['message', 'extract', 'request', 'USER_EVENT_LOGGING', 'payload', 'data'], # list, not 'key.key.key'
    errors='ignore',
)

df.to_csv('sample_data0220_3.csv', index=False)

Output:

name,value
userAction,DISMISSED
employeeTenure,3.9
ffc,1234
ntid,abcd
isAccountView,true
userAction,DISMISSED
title,abcd
jobType,
jobClassCd,
userAction,DISMISSED
userAction,DISMISSED
employeeTenure,3.9
jobType,
jobClassCd,
ntid,dfer
ffc,3456
title,erty
isAccountView,true

Upvotes: 1

Related Questions