How to convert nested dictionary to Pandas dataframe in python?

Question

I want to convert an array of dictionaries to dataframe. For instance,

data = [{ 'id':1, 
          'points': [{'name':'P01',
                    'coor' : {'x':1,'y':8}
                     },
                    {'name':'P02',
                    'coor' : {'x':2,'y':8}
                     }],
          },
          { 'id':2, 
          'points': [{'name':'P03',
                    'coor' : {'x':33,'y':8}
                     },
                    {'name':'P04',
                    'coor' : {'x':2,'y':18}
                     }],
          }]

I tried Converting this to dataframe using

res = pd.DataFrame.from_dict(data)
print(res.columns)
#Output - ['id','points']

But I wanted the output of res.columns to be ['id','points:name','points:coor:x','points:coor:y']

How do I get this result?

score 3 · Answer 1 · answered Sep 01 '22 at 10:58

You can transform the data dict to desired structure first, or using dataframe manipulations (.explode/.concat/.apply(pd.Series)) afterwards:

df = pd.DataFrame(data).explode("points")
df = pd.concat(
    [df, df.pop("points").apply(pd.Series).add_prefix("points.")], axis=1
)
df = pd.concat(
    [df, df.pop("points.coor").apply(pd.Series).add_prefix("points.coor.")],
    axis=1,
)

print(df)

Prints:

   id points.name  points.coor.x  points.coor.y
0   1         P01              1              8
0   1         P02              2              8
1   2         P03             33              8
1   2         P04              2             18

How to convert nested dictionary to Pandas dataframe in python?

1 Answers1