How to create a pandas dataframe from a nested dictionary with lists of dictionaries?

Question

I have a dictionary like that:

dictionary = {'user1':[{'product1':10}, {'product2':15}, {'product3': 20}],
 'user2':[{'product1':13}, {'product2':8}, {'product3': 50}]}

I want to construct a dataframe where I can see user1 and user2 as indices, product1, product2 and product3 as columns and values of these products should be values of columns. I tried looking here and found this post Construct pandas DataFrame from items in nested dictionary, but my format of data is different and I can't find out how to make products to be my columns. So far I get "'product1':10" as my value in the first column of the first row. Using orient='index' made my main keys as indices, but that's it. Please, help me.

No, I didn't. I am not familiar with that. If you could provide the code example, that would be very helpful. Thank you. — Anna Balanyk, Jun 14 '21 at 14:49

Henry Ecker · Accepted Answer · 2021-06-14T15:16:47.920

One option would be to merge the lists of dicts into a single dict then build a DataFrame.from_dict:

import pandas as pd
from collections import ChainMap

dictionary = {'user1': [{'product1': 10}, {'product2': 15}, {'product3': 20}],
              'user2': [{'product1': 13}, {'product2': 8}, {'product3': 50}]}

df = pd.DataFrame.from_dict(
    {k: dict(ChainMap(*v)) for k, v in dictionary.items()},
    orient='index'
)

df:

       product3  product2  product1
user1        20        15        10
user2        50         8        13

Optional alphanumeric sort with natsort:

from natsort import natsorted

df = df.reindex(columns=natsorted(df.columns))

       product1  product2  product3
user1        10        15        20
user2        13         8        50

{k: dict(ChainMap(*v)) for k, v in dictionary.items()}

{'user1': {'product3': 20, 'product2': 15, 'product1': 10},
 'user2': {'product3': 50, 'product2': 8, 'product1': 13}}

How to create a pandas dataframe from a nested dictionary with lists of dictionaries?

1 Answers1