parsing nested JSON into multiple dataframe using pandas python

Question

I have a nested JSON as shown below and want to parse into multiple dataframe in python .. please help

{
"tableName": "cases",
"url": "EndpointVoid",
"tableDataList": [{
    "_id": "100017252700",
    "title": "Test",
    "type": "TECH",
    "created": "2016-09-06T19:00:17.071Z",
    "createdBy": "193164275",
    "lastModified": "2016-10-04T21:50:49.539Z",
    "lastModifiedBy": "1074113719",
    "notes": [{
        "id": "30",
        "title": "Multiple devices",
        "type": "INCCL",
        "origin": "D",
        "componentCode": "PD17A",
        "issueCode": "IP321",
        "affectedProduct": "134322",
        "summary": "testing the json",

        "caller": {
            "email": "[email protected]",
            "phone": "651-744-4522"
        }
    }, {
        "id": "50",
        "title": "EDU: Multiple Devices - Lightning-to-USB Cable",
        "type": "INCCL",
        "origin": "D",
        "componentCode": "PD17A",
        "issueCode": "IP321",
        "affectedProduct": "134322",
        "summary": "parsing json 2",
        "caller": {
            "email": "[email protected]",
            "phone": "123-345-1111"
        }
    }],
    "syncCount": 2316,
    "repair": [{
            "id": "D208491610",
            "created": "2016-09-06T19:02:48.000Z",
            "createdBy": "193164275",
            "lastModified": "2016-09-21T12:49:47.000Z"
        }, {
            "id": "D208491610"
        }, {
            "id": "D208491628",
            "created": "2016-09-06T19:03:37.000Z",
            "createdBy": "193164275",
            "lastModified": "2016-09-21T12:49:47.000Z"
        }

    ],
    "enterpriseStatus": "8"
}],
"dateTime": 1475617849,
"primaryKeys": ["$._id"],
"primaryKeyVals": ["100017252700"],
"operation": "UPDATE"

}

I want to parse this and create 3 tables/dataframe/csv as shown below.. please help..

Output table in this format

I think your json is not valid - please check http://jsonlint.com/ — jezrael
– jezrael, Commented Oct 20, 2016 at 5:27
Thanks jezrael for letting me know.. It's copy paste error.. I just fixed the JSON file.. — Raj
– Raj, Commented Oct 20, 2016 at 5:59

su79eu7k · Accepted Answer · 2016-10-20 15:00:54Z

1

I don't think this is best way, but I wanted to show you possibility.

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

with open('your_sample.json') as f:    
    dt = json.load(f)

Table1

df1 = json_normalize(dt, 'tableDataList', 'dateTime')[['_id', 'title', 'type', 'created', 'createdBy', 'lastModified', 'lastModifiedBy', 'dateTime']]
print df1


            _id title  type                   created  createdBy  \
0  100017252700  Test  TECH  2016-09-06T19:00:17.071Z  193164275   

               lastModified lastModifiedBy    dateTime  
0  2016-10-04T21:50:49.539Z     1074113719  1475617849

Table 2

df2 = json_normalize(dt['tableDataList'], 'notes', '_id')
df2['phone'] = df2['caller'].map(lambda x: x['phone'])
df2['email'] = df2['caller'].map(lambda x: x['email'])
df2 = df2[['_id', 'id', 'title', 'email', 'phone']]
print df2


            _id  id                                           title  \
0  100017252700  30                                Multiple devices   
1  100017252700  50  EDU: Multiple Devices - Lightning-to-USB Cable   

                    email         phone  
0  [email protected]  651-744-4522  
1       [email protected]  123-345-1111

Table 3

df3 = json_normalize(dt['tableDataList'], 'repair', '_id').dropna()
print df3


                    created  createdBy          id              lastModified  \
0  2016-09-06T19:02:48.000Z  193164275  D208491610  2016-09-21T12:49:47.000Z   
2  2016-09-06T19:03:37.000Z  193164275  D208491628  2016-09-21T12:49:47.000Z   

            _id  
0  100017252700  
2  100017252700

answered Oct 20, 2016 at 15:00

su79eu7k

7,3664 gold badges36 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Raj Over a year ago

this code works.. basically i am exporting data in JSON from mongodb and if i get multiple case records the code is not working and sometime couple of columns will not be populated in JSON and again facing json index not available issue...

Collectives™ on Stack Overflow

parsing nested JSON into multiple dataframe using pandas python

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related