How to transform a list of nested dictionaries into a data frame, pd.json

I want to transform a list of nested dictionaries into a data frame, here is the sample data:

data = [{'apple': {'units': 3, 'price': 4}}, 
       {'banana': {'units': 20, 'price': 2}},
       {'orange': {'units': 15, 'price': 5}}]

The expected data frame should have three rows - representing three fruits - apple, banana, and orange, then it should have two columns - units and price.

I have tried to use df=pd.json_normalize(data) to turn it into a data frame, however, the result gives a lot of NaNs and didn't work out as expected. I searched a lot of similar Q&As, but can't find a solution. The hard part is those fruits don't have a uniform key such as 'fruit', but each fruit's name is its own key.

Thank you in advance for your help!

CodePudding user response：

Merge your dictionaries:

>>> from functools import reduce
>>> from operator import ior
>>> mapping = reduce(ior, data, {})
>>> mapping
{'apple': {'units': 3, 'price': 4},
 'banana': {'units': 20, 'price': 2},
 'orange': {'units': 15, 'price': 5}}
>>> pd.DataFrame(mapping).T
        units  price
apple       3      4
banana     20      2
orange     15      5

CodePudding user response：

You can unpack your dictionaries:

pd.DataFrame.from_dict({k:v for d in data for k,v in d.items()}, 
                       orient='index')

OUtput:

        units  price
apple       3      4
banana     20      2
orange     15      5