Convert dict to pandas dataframe?

user_1030099 0
Viewed 90

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.

pandas dataframe python-3.x dictionary
2 Answers

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

For correct ordering use:

#create list of columns dynamic - all columns names without dp
cols = [c for c in d.keys() if c != 'dp']
print(cols)
['id']

df = pd.json_normalize(d, 'dp', 'id')
#change ordering by joined lists
df = df[cols + df.columns.difference(cols, sort=False).tolist()]
print(df)
    id Value   Key
0  666    11   abc
1  666    88  kuku
2  666    99  lulu
3  666  John  name
user_29010020

you can also try:

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