Convert dict to pandas dataframe?-CodePudding

I have the following dictionary toy example:

d = {"id":666,"dp":[{"Value":"11","Key":"abc"},
{"Value":"88","Key":"kuku"},
{"Value":"99","Key":"lulu"},
{"Value":"John","Key":"name"}]}

I want to convert it to the following dataframe:

id  key  value  
666 abc  11
666 kuku 88
666 lulu 99
666 name John

import pandas as pd

I have tried to use pd.DataFrame.from_dict(d) but I am getting id --> dp dicts.

Please advise, is there any quick method/best practice to attack this kind of format? I know I can do it in few steps (to create the id column and add it to the key-value pairs.

CodePudding user response：

You can use json_normalize, but repeated values are in last column(s):

df = pd.json_normalize(d, 'dp', 'id')
print(df)
  Value   Key   id
0    11   abc  666
1    88  kuku  666
2    99  lulu  666
3  John  name  666

CodePudding user response：

you can also try:

df = pd.DataFrame(d)
df[['Value','Key']]=df.dp.apply(pd.Series)
df = df.drop('dp', axis=1)