How to expand list which contains multiple dictionaries into a dataframe?

user_5037278 0
Viewed 533

I need to expand list with multiple dictionaries but really cant find the way...

This is how df looks:

ColumnA       actions
A             [{'link_click': '1'}, {'post_engagement': '1'}]
B             [{'link_click': '13'}, {'post_engagement': '5'}, {'page_engagement': '7'}]

This is how my desired output should be:

 ColumnA          actions_Link_click       actions_post_engagement      actions_page_engagement
    A                 1                          1                              na
    B                 13                         5                              7
pandas list dictionary python
3 Answers

Start by merging the list of dicts in each row to create a single record corresponding to each row, then create a new dataframe from these generated records now add prefix and join the new dataframe with the original frame

records = [{k:v for d in l for k, v in d.items()} for l in df['actions']]
df.join(pd.DataFrame(records, index=df.index).add_prefix('actions_'))

  Column A                                                                     actions actions_link_click actions_post_engagement actions_page_engagement
0        A                             [{'link_click': '1'}, {'post_engagement': '1'}]                  1                       1                     NaN
1        B  [{'link_click': '13'}, {'post_engagement': '5'}, {'page_engagement': '7'}]                 13                       5                       7
user_128331660

This should work:

df = pd.concat([pd.DataFrame(x) for x in df['actions']], keys=df['ColumnA']).reset_index(level = 1, drop = True)
df = df.groupby('ColumnA').first(dropna = True)

If you want the actions prefix then:

df.columns = ['actions_' + s for s in df.columns]
user_156044840

Another possibility would be:

d = pd.DataFrame.from_records(df["actions"].apply(lambda x: {k: v for d in x for k, v in d.items()}).values)
d["ColumnA"] = df["ColumnA"]

First I convert for each row the list of dicts into one dict. Afterwards, I take this values and create a new DataFrame via from_records and finally I add the missing ColumnA to the DataFrame again.

user_66538420
Related