flatten_json recursive flattening function for lists

Question

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.

score 0 · Accepted Answer · answered Apr 15 '20 at 17:16

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

flatten_json recursive flattening function for lists

1 Answers1

Linked