Home How does one create a dictionary from a data-frame with a multi-index column?

Questions

How does one create a dictionary from a data-frame with a multi-index column?

January 28, 2022

How can I create a dictionary from the following df? Trying to use the to_dict method but not having much luck.

df = pd.DataFrame([[100,90,80,70,36,45], [101,78,65,88,55,78], [92,77,42,79,43,32], [103,98,76,54,45,65]], index = pd.date_range(start='2022-01-01' ,periods=4))
df.columns = pd.MultiIndex.from_tuples((("mkf", "Open"), ("mkf", "Close"), ("tdf", "Open"), ("tdf","Close"), ("ghi","Open"), ("ghi", "Close")))
df
            mkf        tdf        ghi
           Open Close Open Close Open Close
2022-01-01  100    90   80    70   36    45
2022-01-02  101    78   65    88   55    78
2022-01-03   92    77   42    79   43    32
2022-01-04  103    98   76    54   45    65

The desired outcome is a dictionary where the keys are mkf, tdf, ghi and the values would be the contents of the sub data frames:

{'mkf' : 
                Open Close
    2022-01-01  100    90   
    2022-01-02  101    78   
    2022-01-03   92    77   
    2022-01-04  103    98,
'tdf' :         Open Close
    2022-01-01  80    70
    2022-01-02  65    88
    2022-01-03  42    79
    2022-01-04  76    54,
'ghi':
                Open Close
    2022-01-01   36    45
    2022-01-02   55    78
    2022-01-03   43    32
    2022-01-04   45    65 
}

>Solution :

Are you looking for something like this?

dct = {c: df[c] for c in df.columns.get_level_values(0).unique()}

Output:

{'mkf':             Open  Close
 2022-01-01   100     90
 2022-01-02   101     78
 2022-01-03    92     77
 2022-01-04   103     98,
 'tdf':             Open  Close
 2022-01-01    80     70
 2022-01-02    65     88
 2022-01-03    42     79
 2022-01-04    76     54,
 'ghi':             Open  Close
 2022-01-01    36     45
 2022-01-02    55     78
 2022-01-03    43     32
 2022-01-04    45     65}