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.
Advertisement
Answer
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}}