Normalize nested json column and append it to original dataframe-CodePudding

I am currently stuck trying to append the normalized json columns to the original dataframe. Merging is not possible, because there is no common column to join on. What can I do?

df=pd.DataFrame({
    "json":[{'foo': {'foo_1': {'foo_1_1': 1.0, 'foo_1_2': 'foo'}, 'foo_2': {'foo_2_1': 1.0, 'foo_2_2': 'moo'}}} , {'foo': {'foo_1': {'foo_1_1': 1.0, 'foo_1_2': 'foo'}, 'foo_2': {'foo_2_1': 1, 'foo_2_2': 'moo'}}} 
           ],
    "id": [1,1],
    "label": ['foo', 'moo']
})
df1= pd.json_normalize(df['json'])

expected result is:

id label foo.foo_1.foo_1_1 foo.foo_1.foo_1_2 foo.foo_2.foo_2_1 foo.foo_2.foo_2_2
1  foo   1.0               foo               1.0               moo
1  moo   1.0               foo               1.0               moo

CodePudding user response：

You can join to the original DataFrame:

df1 = df.drop(columns='json').join(pd.json_normalize(df['json']))

Or, if you plan to assign back to the same name, use pop:

df = df.join(pd.json_normalize(df.pop('json')))

output:

   id label  foo.foo_1.foo_1_1 foo.foo_1.foo_1_2  foo.foo_2.foo_2_1 foo.foo_2.foo_2_2
0   1   foo                1.0               foo                1.0               moo
1   1   moo                1.0               foo                1.0               moo