Reputation: 2586
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.
Upvotes: 2
Views: 1570
Answers (2)
Reputation: 512
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
Upvotes: 2
Reputation: 1272
I do not understand your intention completely, but i think json_normalize is the way to go. (For me your given data is mssing some commas). With pd.normalize you can easier read json data into an dataframe. If you have a lot of nested dicts (without lists) with this you can directly flatten your data. I took the name and the Error.id as reference.
With record_path=[...] you can select specific elements. With meta=[...] you cann add some other data of the json to your elements.
import pandas as pd
df_main = pd.json_normalize(json_data)[["name", "date"]]
df_error = pd.json_normalize(json_data, record_path=["Error"], meta=[["name"]])[["id", "code", "name"]]
df_students = pd.json_normalize(json_data, record_path=["Students"], meta=["name"])
df_error_messages = pd.json_normalize(json_data, record_path=["Error", "emessage"], meta=[["Error", "id"]])
print(df_main)
print(df_error)
print(df_students)
print(df_error_messages)
The outputs:
run
name date
0 Some name 12:23:2022
Error
id code name
0 ,some id some code Some name
1 ,some id2 some code 2 Some name
2 ,some id3 some code3 Some name
Students
id value name
0 ,some id some val Some name
1 ,some id2 some val2 Some name
2 ,some id3 some val3 Some name
error messages
err_trac Err_code Error.id
0 Missing syntax ; ,some id
1 invalid syntax = ,some id
2 Missing syntax ; ,some id2
3 invalid syntax = ,some id2
4 Missing syntax ; ,some id3
5 invalid syntax = ,some id3
Upvotes: 1
Related Questions
- Python : nested json to dataframe
- Nested .json to pandas dataframe
- Nested JSON to Dataframe using Python Pandas
- Nested json into dataframe with python3
- Pandas Dataframe to Nested JSON
- pandas dataframe as nested json
- Nested json to pandas dataframe
- JSON to multiple DataFrames with Pandas
- parsing nested JSON into multiple dataframe using pandas python
- DataFrame to nested JSON