I want to flatten the following JSON at each level and create a pandas dataframe per level, Im using flatten_json to do that but for that I need to loop through each level which creates multiple nested for loops:
{
"metadata": {
"name": "abc",
"time": "2020-04-01"
},
"data": [
{
"identifiers": [
{
"type": "abc",
"scheme": "def",
"value": "123"
},
{
"type": "abc",
"scheme": "def",
"value": "123"
}
],
"name": "qwer",
"type": "abd",
"level1": [
{
"identifiers": [
{
"type": "abc",
"scheme": "def",
"value": "123"
},
{
"type": "abc",
"scheme": "def",
"value": "123"
}
],
"name": "asd",
"type": "abd",
"level2": [
{
"identifiers": [
{
"type": "abc",
"scheme": "def",
"value": "123"
},
{
"type": "abc",
"scheme": "def",
"value": "123"
}
],
"name": "abs",
"type": "abd"
},
{
"identifiers": [
{
"type": "abc",
"scheme": "def",
"value": "123"
},
{
"type": "abc",
"scheme": "def",
"value": "123"
}
],
"name": "abs",
"type": "abd"
}
]
}
]
}
]
}
I'm trying to flatten this json using flatten_json (Flatten JSON in Python) using the code below:
import pandas as pd
import flatten_json as fj
import json
level2 = []
keys = {'data', 'level1', 'level2'}
with open('test_lh.json') as f:
data = json.load(f)
for x in data['data']:
for y in x['level1']:
for z in y['level2']:
dic = fj.flatten(z)
level2.append(dic)
df = pd.DataFrame(level2)
print(df)
Output given below:
identifiers_0_type identifiers_0_scheme identifiers_0_value identifiers_1_type identifiers_1_scheme identifiers_1_value name type
0 abc def 123 abc def 123 abs abd
1 abc def 123 abc def 123 abs abd
How would I write a recursive function to get this same output without calling n number of for loops? The levels could go down multiple levels. I've tried using json_normalize for this but I also need the parent level identifiers in the final output and json_normalize doesn't work with multiple record paths.
I solved it using recursion, here's my code:
import json
import pandas as pd
import flatten_json as fj
keys = {'data', 'level1', 'level2', 'level3'}
with open('test_lh.json') as f:
data = json.load(f)
levels = ['data.level1.level2.level3', 'data.level1.level2', 'data.level1', 'data']
recs_dict = {}
def do_step(data_dict, level, depth, path):
recs = []
for x in data_dict[level]:
if depth < len(path.split('.'))-1:
do_step(x, path.split('.')[depth+1], depth+1, path)
else:
dic = fj.flatten(x, root_keys_to_ignore=keys)
recs.append(dic)
recs_dict[level] = recs
for path in levels:
do_step(data, path.split('.')[0], 0, path)
for key, value in recs_dict.items():
print(key)
df = pd.DataFrame(recs_dict[key])
print(df)
And here's the output:
level3
identifiers_0_type identifiers_0_scheme identifiers_0_value identifiers_1_type identifiers_1_scheme identifiers_1_value name type
0 abc def 123 abc def 123 abs level3
1 abc def 123 abc def 123 abs level3
level2
identifiers_0_type identifiers_0_scheme identifiers_0_value identifiers_1_type identifiers_1_scheme identifiers_1_value name type
0 abc def 123 abc def 123 abs level2
1 abc def 123 abc def 123 abs abd
level1
identifiers_0_type identifiers_0_scheme identifiers_0_value identifiers_1_type identifiers_1_scheme identifiers_1_value name type
0 abc def 123 abc def 123 asd level1
data
identifiers_0_type identifiers_0_scheme identifiers_0_value identifiers_1_type identifiers_1_scheme identifiers_1_value name type
0 abc def 123 abc def 123 qwer abd