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.
Advertisement
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