Breaking down nested dictionaries into dataframe columns

Question

I'm working with a dataframe which in one of the columns have nested dictionaries.

df = {"Name": "XYZ", "Age": 42, 
     {"Place":{"KeyA": {"SubKey1": 0.2, "SubKey2": "value2"}}}

So my flat df is:

Name - Age - Place
XYZ - 42 - {"KeyA": {"SubKey-1": 0.2, "SubKey2": "value2"}}

I'm trying to break down the content of that column and transform it on multiple columns to end up with something like this:

Name - Age - KeyA-SubKey1 - KeyA-SubKey2
X - 42 - 0.2 - value2

But everything I was able to come up with so far ended up with KeyError or Length of values does not match length of index.

Any help will be appreciated

score 2 · Answer 1 · answered Aug 28 '20 at 03:34

2

Convert the compound column to a Series, combine with the original DataFrame, and drop the original Place column:

df.join(df['Place'].apply(pd.Series)).drop('Place', axis1)
#     Name  Age  SubKey1 SubKey2
#KeyA  XYZ   42      0.2  value2

answered Aug 28 '20 at 03:34

DYZ

55,249
10
64
93

score 1 · Answer 2 · answered Aug 28 '20 at 03:35

Check the flatten dict method

df.join(pd.DataFrame(df.pop('Place').map(flatten).tolist(),index=df.index))
Out[115]: 
  Name  Age  KeyA_SubKey-1 KeyA_SubKey2
0  XYZ   42            0.2       value2

import collections
    def flatten(d, parent_key='', sep='_'):
         items = []
         for k, v in d.items():
             new_key = parent_key + sep + k if parent_key else k
             if isinstance(v, collections.MutableMapping):
                 items.extend(flatten(v, new_key, sep=sep).items())
             else:
                 items.append((new_key, v))
         return dict(items)

Breaking down nested dictionaries into dataframe columns

2 Answers2