How to extract insights from facebook action dataset and covert all values into each column

Question

Here is dataset as shown in below and I want to convert it into each data column with their values as

i want to append the values in columns and I tried this code

y = data['actions'].apply(lambda x: str(x).replace("'",'"'))
json.loads(y[0])
json.loads(y[1])

it gives output like as shown in below

[{'action_type': 'post_reaction', 'value': '2'},
 {'action_type': 'link_click', 'value': '42'},
 {'action_type': 'comment', 'value': '1'},
 {'action_type': 'post_engagement', 'value': '45'},
 {'action_type': 'page_engagement', 'value': '45'},
 {'action_type': 'onsite_conversion.lead_grouped', 'value': '6'},
 {'action_type': 'leadgen_grouped', 'value': '6'},
 {'action_type': 'lead', 'value': '6'}]

[{'action_type': 'onsite_conversion.post_save', 'value': '1'},
 {'action_type': 'post_reaction', 'value': '4'},
 {'action_type': 'link_click', 'value': '62'},
 {'action_type': 'post_engagement', 'value': '67'},
 {'action_type': 'page_engagement', 'value': '67'},
 {'action_type': 'onsite_conversion.lead_grouped', 'value': '6'},
 {'action_type': 'leadgen_grouped', 'value': '6'},
 {'action_type': 'lead', 'value': '6'}]

I want to create the dataframe that gives each action type as column and append their values in respective columns and if there is no value it appends zero like

| post_reaction| link click | comment |---------------------
| --------     | -----------|---------|
| 2            | 42         |1        |
|  4           | 62         |67       |

Answer 1

If no lists in data use ast.literal_eval for converting first and then with list comprehension create DataFrame:

import ast

y = data['actions'].apply(ast.literal_eval)

df = pd.DataFrame([{z['action_type']:z['value'] for z in x} for x in y]).fillna(0)

If lists in data use only list comprehension:

df = (pd.DataFrame([{z['action_type']:z['value'] for z in x} for x in data['actions']])
        .fillna(0))