Json Multiple level to Dataframe flat file in Pandas

Santoo 0
Viewed 18

Trying to convert the json with multiple children in this format to dataframe flat file.

{'name': 'A text',
 'children': [{'name': 'A01 text',
   'children': [{'name': 'A01A text',
     'children': [{'name': 'A01AA text',
       'children': [{'name': 'A01AA01 text',
         'children': [{'name': 'D00943  text'}]},
        {'name': 'A01AA02 text',
         'children': [{'name': 'D05864  text'}]},
        {'name': 'A01AA03 text',
         'children': [{'name': 'D05241  text'}]},
        {'name': 'A01AA04 text',
         'children': [{'name': 'D05919  text'}]},
        {'name': 'A01AA30 text'},
        {'name': 'A01AA51 text',
         'children': [{'name': 'D08736  text'}]}]},
...

Tried json_normalize and couldn't achieve it.

Expected dataframe

level_1  level_2   level_3    level_4   level_5       level_6
A text   A01 text  A01A text  A01AA text  A01AA01 text  D00943  text
A text   A01 text  A01A text  A01AA text  A01AA03 text  D05241  text
A text   A01 text  A01A text  A01AA text  A01AA04 text  D05919  text
A text   A01 text  A01A text  A01AA text  A01AA30 text  NA
A text   A01 text  A01A text  A01AA text  A01AA51 text  D08736  text
dataframe pandas json
1 Answers

Given this horrifying thing:

data = {
'children': [{'children': [{'children': [{'children': [{'children': [{'name': 'D00943  text'}], 'name': 'A01AA01 text'},
                                                        {'children': [{'name': 'D05864  text'}], 'name': 'A01AA02 text'},
                                                        {'children': [{'name': 'D05241  text'}], 'name': 'A01AA03 text'},
                                                        {'children': [{'name': 'D05919  text'}], 'name': 'A01AA04 text'},
                                                        {'name': 'A01AA30 text'},
                                                        {'children': [{'name': 'D08736  text'}], 'name': 'A01AA51 text'}],
                                           'name': 'A01AA text'}],
                             'name': 'A01A text'}],
               'name': 'A01 text'}],
 'name': 'A text'
}

Doing:

prefix = ''.join(['children.']*4) 
df = pd.json_normalize(data, 
                       ['children', 'children', 'children', 'children'], 
                       ['name', 
                        ['children', 'name'], 
                        ['children', 'children', 'name'], 
                        ['children', 'children', 'children', 'name']], 
                       record_prefix=prefix)
prefix += 'children'
df = df.explode(prefix)

df = (df.drop(prefix, axis=1)
        .join(df[prefix].apply(pd.Series)
                        .drop(0, axis=1)
                        .add_prefix(prefix + '.')))

df.columns = [f"level{x.count('.')+1}" for x in df.columns]
df = df.sort_index(axis=1)
print(df)

Output:

   level1    level2     level3      level4        level5        level6
0  A text  A01 text  A01A text  A01AA text  A01AA01 text  D00943  text
1  A text  A01 text  A01A text  A01AA text  A01AA02 text  D05864  text
2  A text  A01 text  A01A text  A01AA text  A01AA03 text  D05241  text
3  A text  A01 text  A01A text  A01AA text  A01AA04 text  D05919  text
4  A text  A01 text  A01A text  A01AA text  A01AA30 text           NaN
5  A text  A01 text  A01A text  A01AA text  A01AA51 text  D08736  text
BeRT2me0
Related