create a dataframe from a dictionary using the keys as a new column?-CodePudding

I have a dictionary I'm trying to turn into a dataframe. The dictionary is essentially nested, where the keys need to be the column. For instance:

{
"apple":[
{
"price":19,
"store":"xyz"},
{"price":13,
"store":"abc"
}
}],
"pear":[{
"price":25,
"store":"xyz"
}]
}

I'd like the final dataframe to be in the format of

FRUIT   PRICE   STORE
apple   19      xyz
apple   13      abc
pear    25      xyz

I'm trying to sort through the list by doing some type of a iteration through the 'fruit' keys like such

for fruit in fruit_dict.keys():
    df['FRUIT']=fruit

and then using pd.normalize to get the price/store, but this feels incredibly convoluted to me. Is there an easier or better way to get this dictionary 'flattened' down?

CodePudding user response：

Use list comprehension for list of dictionaries with concat helper dict for FRUIT column by keys of outer input dicts:

fruit_dict = {
"apple": [{
        "price": 19,
        "store": "xyz"
    },
    {
        "price": 13,
        "store": "abc"
    }
],
"pear": [{
    "price": 25,
    "store": "xyz"
}]}

df = pd.DataFrame([{**{'FRUIT':k}, **x} for k, v in fruit_dict.items() for x in v])
print (df)
   FRUIT  price store
0  apple     19   xyz
1  apple     13   abc
2   pear     25   xyz

CodePudding user response：

If the desired shape of the final df is (6,1) instead of (3,3) then you could use this

fruits = []
details = []
for fruit,detail in fruits_dict.items():
    for x in detail:
        details.append(pd.DataFrame.from_dict({'Details':x}))
        fruits.append(fruit)
final = pd.concat(details,keys=fruits)

Also refer to this for a similar question : Construct pandas DataFrame from items in nested dictionary