Reputation: 4070
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.
Upvotes: 1
Views: 455
Reputation: 863801
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
Upvotes: 3
Reputation: 425
you can also try:
df = pd.DataFrame(d)
df[['Value','Key']]=df.dp.apply(pd.Series)
df = df.drop('dp', axis=1)
Upvotes: 1