CODEWITHSUNDEEP
pythonpandasdataframedictionary
Gerard
Gerard

Reputation: 117

How to make multiindex dataframe from a nested dictionary keys and lists of values?

I have checked the advicse here: Nested dictionary to multiindex dataframe where dictionary keys are column labels However, I couldn't get it to work in my problem. I would like to change a dictionary into multiindexed dataframe, where 'a','b','c' are names of multiindexes, their values 12,0.8,1.8,bla1,bla2,bla3,bla4 are multiindexes and values from lists are assign to the multiindexes as in the picture of table below.

My dictionary:

dictionary ={
            "{'a': 12.0, 'b': 0.8, 'c': ' bla1'}": [200, 0.0, '0.0'],
            "{'a': 12.0, 'b': 0.8, 'c': ' bla2'}": [37, 44, '0.6'],
            "{'a': 12.0, 'b': 1.8, 'c': ' bla3'}": [100, 2.0, '1.0'],
            "{'a': 12.0, 'b': 1.8, 'c': ' bla4'}": [400, 3.0, '1.0']
            }

The result DataFrame I would like to get:

enter image description here

The code which don't make multiindexes and set every values under each other in next row:

df_a = pd.DataFrame.from_dict(dictionary, orient="index").stack().to_frame()
df_b = pd.DataFrame(df_a[0].values.tolist(), index=df_a.index)

Upvotes: 0

Views: 169

Answers (1)

Dani Mesejo
Dani Mesejo

Reputation: 61930

Use ast.literal_eval to convert each string into a dictionary and build the index from there:

import pandas as pd
from ast import literal_eval

dictionary ={
            "{'a': 12.0, 'b': 0.8, 'c': ' bla1'}": [200, 0.0, '0.0'],
            "{'a': 12.0, 'b': 0.8, 'c': ' bla2'}": [37, 44, '0.6'],
            "{'a': 12.0, 'b': 1.8, 'c': ' bla3'}": [100, 2.0, '1.0'],
            "{'a': 12.0, 'b': 1.8, 'c': ' bla4'}": [400, 3.0, '1.0']
            }


keys, data = zip(*dictionary.items())
index = pd.MultiIndex.from_frame(pd.DataFrame([literal_eval(i) for i in keys]))
res = pd.DataFrame(data=list(data), index=index)
print(res)

Output

                  0     1    2
a    b   c                    
12.0 0.8  bla1  200   0.0  0.0
          bla2   37  44.0  0.6
     1.8  bla3  100   2.0  1.0
          bla4  400   3.0  1.0

Upvotes: 1

Related Questions