0

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)
Gerard
  • 117
  • 7

1 Answers1

1

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
Dani Mesejo
  • 61,499
  • 6
  • 49
  • 76