I have the following pandas dataframe:
df1 = pd.DataFrame({'date': [200101,200101,200101,200101,200102,200102,200102,200102],'blockcount': [1,1,2,2,1,1,2,2],'reactiontime': [350,400,200,250,100,300,450,400]})
I am trying to create a hierarchical dictionary, with the values of the embedded dictionary as lists, that looks like this:
{200101: {1:[350, 400], 2:[200, 250]}, 200102: {1:[100, 300], 2:[450, 400]}}
How would I do this? The closest I get is using this code:
df1.set_index('date').groupby(level='date').apply(lambda x: x.set_index('blockcount').squeeze().to_dict()).to_dict()
Which returns:
{200101: {1: 400, 2: 250}, 200102: {1: 300, 2: 400}}
To convert pandas DataFrame to Dictionary object, use to_dict() method, this takes orient as dict by default which returns the DataFrame in format {column -> {index -> value}} . When no orient is specified, to_dict() returns in this format.
We first take the list of nested dictionary and extract the rows of data from it. Then we create another for loop to append the rows into the new list which was originally created empty. Finally we apply the DataFrames function in the pandas library to create the Data Frame.
To make the column an index, we use the Set_index() function of pandas. If we want to make one column an index, we can simply pass the name of the column as a string in set_index(). If we want to do multi-indexing or Hierarchical Indexing, we pass the list of column names in the set_index().
Here is another way using pivot_table
:
d = df1.pivot_table(index='blockcount',columns='date',
values='reactiontime',aggfunc=list).to_dict()
print(d)
{200101: {1: [350, 400], 2: [200, 250]},
200102: {1: [100, 300], 2: [450, 400]}}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With