Merging dictionaries by key-CodePudding

I have a list of dictionaries. Each four with the same keys but different values.

[[{'Vanilla Shake': {'Calories': '505.3532687092'}}],
 [{'Vanilla Shake': {'Protein': '10.7838768505'}}],
 [{'Vanilla Shake': {'Carbohydrates': '83.7223214166'}}],
 [{'Vanilla Shake': {'Total Fat': '13.4791543506'}}],
 [{'Strawberry Banana Smoothie': {'Calories': '190.850660517368'}}],
 [{'Strawberry Banana Smoothie': {'Protein': '2.36839224103716'}}],
 [{'Strawberry Banana Smoothie': {'Carbohydrates': '43.97617701140'}}],
 [{'Strawberry Banana Smoothie': {'Total Fat': '0.61041563715716'}}],
 [{'Mango Pineapple Smoothie': {'Calories': '195.973576950716'}}],
 [{'Mango Pineapple Smoothie': {'Protein': '2.20945589054832'}}],
 [{'Mango Pineapple Smoothie': {'Carbohydrates': '45.2678057742512'}}],
 [{'Mango Pineapple Smoothie': {'Total Fat': '0.64903335741716'}}],
 [{'Fruit & Maple Oatmeal': {'Calories': '319.5439'}}],
 [{'Fruit & Maple Oatmeal': {'Protein': '5.86934'}}],
 [{'Fruit & Maple Oatmeal': {'Carbohydrates': '63.87831'}}],
 [{'Fruit & Maple Oatmeal': {'Total Fat': '4.48686'}}]]

I eventually need to get a database entry of the following type:

can you tell me the best conversions to make?

I've tried converting to pandas.Dataframe, but I'm not sure if I'll get the right result when importing into the database. or am I wrong?

Thank you!

CodePudding user response：

Assuming l the input list, you could do:

from itertools import chain

df = (pd.concat(map(pd.DataFrame, chain.from_iterable(l)))
        .groupby(level=0).first()
        .T
      )

or using a big dictionary comprehension, which should be faster:

df = (pd.Series({(k,k2): v2 for l1 in l for d in l1
                 for k,v in d.items() for k2,v2 in v.items()})
        .unstack(level=1)
      )

output:

                                    Calories     Carbohydrates           Protein         Total Fat
Fruit & Maple Oatmeal               319.5439          63.87831           5.86934           4.48686
Mango Pineapple Smoothie    195.973576950716  45.2678057742512  2.20945589054832  0.64903335741716
Strawberry Banana Smoothie  190.850660517368    43.97617701140  2.36839224103716  0.61041563715716
Vanilla Shake                 505.3532687092     83.7223214166     10.7838768505     13.4791543506