How can I create columns out of a nested dictionary in python pandas

Question

I'm trying to filter my data frame by making separate columns to increase readability and usability.

Problem statement: Column "Editables" has a nested dictionary as the value I'm trying to create separate columns as per the key. For instance 'photo_repace' 'text_remove', 'text_add' has different columns.

The nested dictionary:

{
'photo': {
    'photo_replace': None,
    'photo_remove': None,
    'photo_add': None,
    'photo_effect': None,
    'photo_brightness': None,
    'background_color': None,
    'photo_resize': None,
    'photo_rotate': None,
    'photo_mirror': None,
    'photo_layer_rearrange': None,
    'photo_move': None
},
'text': {
    'text_remove': None,
    'text_add': None,
    'text_edit': None,
    'font_select': None,
    'text_color': None,
    'text_style': None,
    'background_color': None,
    'text_align': None,
    'text_resize': None,
    'text_rotate': None,
    'text_move': None,
    'text_layer_rearrange': None
}
}

Output

Code I've been using:

df["editables"] = df["editables"].apply(lambda x : dict(eval(x)))
df_edit = df["editables"].apply(pd.Series )

Output:

Sayandip Dutta · Accepted Answer · 2020-02-19 09:47:51Z

Try this:

>>> dct = {
'photo': {
    'photo_replace': None,
    'photo_remove': None,
    'photo_add': None,
    'photo_effect': None,
    'photo_brightness': None,
    'background_color': None,
    'photo_resize': None,
    'photo_rotate': None,
    'photo_mirror': None,
    'photo_layer_rearrange': None,
    'photo_move': None
},
'text': {
    'text_remove': None,
    'text_add': None,
    'text_edit': None,
    'font_select': None,
    'text_color': None,
    'text_style': None,
    'background_color': None,
    'text_align': None,
    'text_resize': None,
    'text_rotate': None,
    'text_move': None,
    'text_layer_rearrange': None
}
}
>>> pd.DataFrame(dct).T
       photo_replace  photo_remove  ...  text_move  text_layer_rearrange
photo            NaN           NaN  ...        NaN                   NaN
text             NaN           NaN  ...        NaN                   NaN

[2 rows x 22 columns]
>>> pd.DataFrame(dct).T[['photo_replace', 'photo_remove', 'text_remove']]
       photo_replace  photo_remove  text_remove
photo            NaN           NaN          NaN
text             NaN           NaN          NaN

NOTE: .T transposes the dataframe.

jezrael · Accepted Answer · 2020-02-19 10:15:07Z

1

Use json.json_normalize per rows and concat, last remove values before first . in columns names:

from pandas.io.json import json_normalize

c = ['photo.photo_replace', 'photo.photo_remove', 'text.text_remove']
df = pd.concat([json_normalize(x)[c] for x in df['editables']], ignore_index=True)
df.columns = df.columns.str.split('.').str[1]
print (df)
  photo_replace photo_remove text_remove
0          None         None        None
1          None         None        None

edited Feb 19, 2020 at 10:15

answered Feb 19, 2020 at 9:53

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

How can I create columns out of a nested dictionary in python pandas

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related