Create a dataframe from a deeply nested dictionary-CodePudding

I currently using API to pull a some data example below

{'Data':[{'id':'123','subdata':[{'Addnl':'bar','details':[],
'country':'BRA'},{'Addnl':'foobar'
,'details':[{'resttype':'prod','restsubtype':'foobar'},{'resttype':'dev','restsubtype':'foobar'}],
'country':'USA'}]}]}

Expected dataframe output

id  addnl resttype restsubtype country
123 bar                         BRA
123 foobar prod      foobar     USA
123 foobar dev       foobar     USA

Using json.normalize I am getting the below

id  addnl details                  country
123 bar     {}                        BRA
123 foobar {'resttype':'prod','restsubtype'foobar}    USA
123 foobar   {'resttype':'dev','restsubtype'foobar}    USA

CodePudding user response：

If dct is your dictionary from the question, then:

df = pd.DataFrame(dct["Data"]).explode("subdata")
df = pd.concat([df, df.pop("subdata").apply(pd.Series)], axis=1).explode(
    "details"
)
df = pd.concat([df, df.pop("details").apply(pd.Series)], axis=1)
df.pop(0)
print(df.fillna("").reset_index(drop=True))

Prints:

    id   Addnl country resttype restsubtype
0  123     bar     BRA                     
1  123  foobar     USA     prod      foobar
2  123  foobar     USA      dev      foobar