Creating Multiindex Panda Dataframe from nested dict

Question

I got a directory in this form:

dict = {'Filter1':{'Method1':{'Fuction1':{'Value1': 1, 'Value2': 2},
                              'Fuction2':{'Value1': 1, 'Value2': 2}},
                   'Method2':{'Fuction1':{'Value1': 1, 'Value2': 2},
                              'Fuction2':{'Value1': 1, 'Value2': 2}}},
        'Filter2':{'Method1':{'Fuction1':{'Value1': 1, 'Value2': 2},
                              'Fuction2':{'Value1': 1, 'Value2': 2}},
                   'Method2':{'Fuction1':{'Value1': 1, 'Value2': 2},
                              'Fuction2':{'Value1': 1, 'Value2': 2}}}}

and i want to create a Dataframe in this shape:

                      Filter1         Filter2
                Method1   Method2   Method1  Method2
Function1 Value1   1        1          1         1
          Value2   2        2          2         2
Function2 Value1   1        1          1         1
          Value2   2        2          2         2

how do i do this? All the posts i could find were just refering to two subrows or subcolumns but never both. Thanks ahead guys!!

So far i tried this aproach:

df = pd.DataFrame.from_dict({(i, j): dict[i][j]
                         for i in dict.keys()
                         for j in dict[i].keys()
                         },
                        orient='index')

witch gave me this result:

                                       Fuction1                    Fuction2
Filter1 Method1  {'Value1': 1, 'Value2': 2}  {'Value1': 1, 'Value2': 2}
        Method2  {'Value1': 1, 'Value2': 2}  {'Value1': 1, 'Value2': 2}
Filter2 Method1  {'Value1': 1, 'Value2': 2}  {'Value1': 1, 'Value2': 2}
        Method2  {'Value1': 1, 'Value2': 2}  {'Value1': 1, 'Value2': 2}

but i want the row and the columns swaped and Value1 and Value2 as the rownames

`{'Value1': 1, 'Value2': 2, 'Value2'}` this is not valid python. — Ch3steR, Oct 21 '20 at 13:04
Does this work: https://stackoverflow.com/questions/24988131/nested-dictionary-to-multiindex-dataframe-where-dictionary-keys-are-column-label — qmeeus, Oct 21 '20 at 13:08

score 1 · Accepted Answer · answered Oct 21 '20 at 13:29

You want to 1) flatten your dictionary, 2) load it into a dataframe and 3) reformat the dataframe to your liking.

# 1. Flatten, eg using https://stackoverflow.com/questions/6027558/flatten-nested-dictionaries-compressing-keys
import collections
def flatten(dictionary, parent_key=False, separator='.'):
    """
    Turn a nested dictionary into a flattened dictionary
    :param dictionary: The dictionary to flatten
    :param parent_key: The string to prepend to dictionary's keys
    :param separator: The string used to separate flattened keys
    :return: A flattened dictionary
    """

    items = []
    for key, value in dictionary.items():
        new_key = str(parent_key) + separator + key if parent_key else key
        if isinstance(value, collections.MutableMapping):
            items.extend(flatten(value, new_key, separator).items())
        elif isinstance(value, list):
            for k, v in enumerate(value):
                items.extend(flatten({str(k): v}, new_key).items())
        else:
            items.append((new_key, value))
    return dict(items)

data_flat = flatten(data_dict)

# 2. Load it in pandas
df = pd.DataFrame.from_dict(data_flat, orient="index")

# 3. Reshape to your liking
df.index = pd.MultiIndex.from_tuples(df.index.str.split(".").map(tuple))
df = df.unstack(level=[0,1]).droplevel(axis=1, level=0)

Creating Multiindex Panda Dataframe from nested dict

1 Answers1