Nested JSON to Multiple Dataframe in Pandas

I am trying to build a tool which can take any JSON data and convert that into multiple data frame based on data types. I am trying to add each data frame with a relation so that we can identify which data belong to which parent element(key).

For Example :

    {

       "name":"Some name"
       "date": "12:23:2022"
       "Students":[

               {
                "id":",some id"
                 "value": "some val"
         },
         {
                "id":",some id2"
                 "value": "some val2"
         },    {
                "id":",some id3"
                 "value": "some val3"
         },

        ],
       "Error":[

               {
                "id":",some id",
                 "code": "some code",
                "emessage":[
                   {
 
                     "err_trac":"Missing syntax",
                     "Err_code":";"
                   },
                   {
 
                     "err_trac":"invalid syntax",
                     "Err_code":"="
                   }
                 ]
         },
         {
                "id":",some id2",
                "code": "some code 2",
                "emessage":[
                   {
 
                     "err_trac":"Missing syntax",
                     "Err_code":";"
                   },
                   {
 
                     "err_trac":"invalid syntax",
                     "Err_code":"="
                   }
                 ]

         },    {
                "id":",some id3",
                "code": "some code3",
                "emessage":[
                   {
 
                     "err_trac":"Missing syntax",
                     "Err_code":";"
                   },
                   {
 
                     "err_trac":"invalid syntax",
                     "Err_code":"="
                   }
                 ]

         },

        ]


    }

I wanted to have data frame such as

Run 
name, date , id (uuid)
Error 
 id, code parent_id(id of run), id (uuid)

Students 
 id, value, parent_id(id of run) , id (uuid)

emessage 
  err_trac, Err_code , parent_id(id of Error )

And have a relations with UUID to identify which key belongs to which parents id. I am trying the flattening approach to solve this problem using python and pandas . But my solution does not works for nested JSON.

Here is what I am trying.

import json
import pandas as pd

op = {}
import uuid


def format_string(string):

    return string.replace(" ", "_")


def get_prefix(prefix, key):

    if not key:
        return format_string(prefix)
    if prefix:
        return format_string(prefix + "_" + key)
    else:
        return key


def flatten(prefix, key, value, uid, result=[]):

    if isinstance(value, str):
        result.append({get_prefix(prefix, key): value})
    if isinstance(value, dict):
        for item in value.keys():
            flatten(get_prefix(prefix, key), item, value.get(item), uid, result)
    if isinstance(value, list):
        if prefix:
           for i in range(len(value)):
                flatten(
                    get_prefix(prefix, key + "[{}]".format(i)),
                    "",
                    value[i],
                    uid,
                    op[key],
                )
        else:
            for i in range(len(value)):
                flatten(
                    get_prefix(prefix, key + "[{}]".format(i)),
                    "",
                    value[i],
                    uid,
                    result,
                )
        res = {key: val for d in result for key, val in d.items()}
        df = pd.DataFrame.from_dict(res, orient="index")
        df["uuid"] = uid
        op["result"] = df
        return result


def solution() -> str:

    f = open("example-input/sample.json", "r")

    if f:
        str_val = json.load(f)
        print("j")
        for key, value in str_val.items():
            #  pd_op = pd.json_normalize(str_val)
            #  print(pd_op.columns)
            #  for x in pd_op["run.tip usage"]:
            #      print(x[0])
            #  break
            flatten("", key, str_val.get(key), uuid.uuid4())
    return op


print(solution())

Update

The reason I wanted to create multiple dataframe is to put this data into Datalake and later access it via Athena in AWS. Once I get the dataframe I can move them into SQL tables.

Answer

The structure you are describing – a JSON of an indefinitely defined number of nested JSONs – fits exactly with a tree data structure. Since we are looking to store the ID of the parent JSON in each dataframe, we will approach this with BFS (breadth first search) a.k.a. level order traversal. This is a common graph traversal algorithm well suited to this kind of problem.

If a element has an id of None, it indicates it is the root or top level element.

import json
import pandas as pd
import uuid


def nested_json_list_df(file : str):
    dict_list = []
    def bfs(json_dict : dict, node_name : str, parent_uuid : uuid.UUID):
        """Breadth First Search a.k.a. Level order Traversal
        """
        # Create parent node
        uuid_val = uuid.uuid1()
        out_dict = {'id': node_name, 'uuid': uuid_val, 'parent id': parent_uuid}

        # Search child nodes
        for key, val in json_dict.items():
            # If a child node is a dict itself, it is a sub-nested JSON
            if isinstance(val, dict):
                bfs(val, key, uuid_val)
            # A list of single-nested dicts is simply a new entry
            # A list containing dicts within dicts is interpreted
            # as another nested JSON
            elif isinstance(val, list):
                new_val = []
                for v in val:
                    if isinstance(v, dict):
                        new_dict = dict()
                        for key2, val2 in v.items():
                            # Indicates nested JSONs
                            if isinstance(val2, dict):
                                bfs(val2, key, uuid_val)
                            else:
                                new_dict[key2] = val2
                        new_val.append(new_dict)
                    else:
                        new_val.append(v)
                uuid2 = uuid.uuid1()
                out_dict2 = {'id': key, 'uuid': uuid2, 'parent id': parent_uuid, key : new_val}
                dict_list.append({key : out_dict2})
            else:
                out_dict[key] = val
        
        dict_list.append({node_name : out_dict})
        return dict_list
    
    ## Run BFS ##
    with open(file) as f:
        json_dict = json.load(f)
    df_list = []
    for d in bfs(json_dict, file, None):
        df_list.append(pd.DataFrame(d))
    return df_list


df_list = nested_json_list_df('temp.json')
for df in df_list:
     print(df)

Output:

                                                    Students
Students   [{'id': ',some id', 'value': 'some val'}, {'id...
id                                                  Students
parent id                                               None
uuid                    2d68cce3-c7f7-11ec-81a3-b0227ae68aa0
                                                       Error
Error      [{'id': ',some id', 'code': 'some code', 'emes...
id                                                     Error
parent id                                               None
uuid                    2d68cce4-c7f7-11ec-b0da-b0227ae68aa0
                                      temp.json
date                                 12:23:2022
id                                    temp.json
name                                  Some name
parent id                                  None
uuid       2d68cce2-c7f7-11ec-bcd8-b0227ae68aa0

Level Order Traversal

Breadth First Search