Nested Values from a Json value

Question

From a json file, I know you can use json_normalize to convert said json file into a pandas Dataframe. When there are nested dictionaries, json_normalize does flatten them. However, for my json file, the nested dictionaries are within a list, see Var2 below. How would I access the elements within Var2 and convert them to be there own dataframe?

from pandas.io.json import json_normalize
data = [
        {"Id":1,"Var1":2,"Var2":[{"Var2.1":4,"Var2.2":3}],"Var3":3},
        {"Id":2,"Var1":2,"Var2":[{"Var2.1":4}],"Var3":3},
        {"Id":3,"Var1":2,"Var2":[{"Var2.1":4,"Var2.2":3,"Var2.3":2}],"Var3":3}
        ]
df = json_normalize(data)

I have tried df["Var2"].apply(json_normalize), but that resulted in a really weird output.

How have others solve this problem?

Does this answer your question? subsetting a Python DataFrame — Jon
– Jon, Commented Mar 19, 2020 at 20:26

Pygirl · Accepted Answer · 2020-03-19 20:41:39Z

3

from pandas.io.json import json_normalize
df = json_normalize(data)
df['Var2'] = df['Var2'].apply(pd.Series)

result = df['Var2'].apply(pd.Series)

df:

Id  Var1    Var2                                    Var3
0   1   2   {'Var2.1': 4, 'Var2.2': 3}              3
1   2   2   {'Var2.1': 4}                           3 
2   3   2   {'Var2.1': 4, 'Var2.2': 3, 'Var2.3': 2} 3

result:

    Var2.1  Var2.2  Var2.3
0   4.0     3.0     NaN
1   4.0     NaN     NaN
2   4.0     3.0     2.0

answered Mar 19, 2020 at 20:41

Pygirl

13.4k6 gold badges36 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Nested Values from a Json value

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related