flatten nested dictionary in list-CodePudding

I have a data from Facebook Insights API and some columns in the data look like this:

"[{'action_type': 'link_click', 'value': '812'}, {'action_type':'page_engagement','value':'823'}, {'action_type': 'post_engagement', 'value': '823'}, {'action_type':'post', 'value': '1'}, {'action_type': 'post_reaction', 'value': '10'}, {'action_type':'landing_page_view', 'value': '143'}]"]

I'm trying to put the data here in a dataframe, but there is a different campaign in each row and I need to add more than one row for a campaign here, so for campaign a, link click: 812 is also post_engag for campaign a. 823 and these need to be in separate columns for that campaign, so the data needs to be separated as follows:

link_click    page_engagement   post_engagement  post   post_reaction   landing_page_view
   812              823              823          1         10               143

I have a method but I get an error:

def function2(df, col_name, filter="action_type",
                        which_fields=''):
    xx = df[xcol].apply(pd.Series).merge(df["index"], right_index=True, left_index=True).melt
    xx2 = xx[col_name].apply(pd.Series).merge(xx["index"], right_index=True, left_index=True)
    xx2 = xx2.loc[xx2[filter] == which_fields, ["value", "index"]]
    xx2.rename(columns={"value": col_name   '_'   which_fields.replace('.','_')},inplace=True)

    return xx2

The error I get is:

    raise KeyError(key) from err
KeyError: 'action_type'

How can I do that with Python ?

CodePudding user response：

You can use ast.literal_eval and the DataFrame constructor:

from ast import literal_eval

out = pd.DataFrame(literal_eval(s)).set_index('action_type').T

output:

action_type link_click page_engagement post_engagement post post_reaction landing_page_view
value              812             823             823    1            10               143

used input:

s = "[{'action_type': 'link_click', 'value': '812'}, {'action_type':'page_engagement','value':'823'}, {'action_type': 'post_engagement', 'value': '823'}, {'action_type':'post', 'value': '1'}, {'action_type': 'post_reaction', 'value': '10'}, {'action_type':'landing_page_view', 'value': '143'}]"

CodePudding user response：

Try:

lst = [
    {"action_type": "link_click", "value": "812"},
    {"action_type": "page_engagement", "value": "823"},
    {"action_type": "post_engagement", "value": "823"},
    {"action_type": "post", "value": "1"},
    {"action_type": "post_reaction", "value": "10"},
    {"action_type": "landing_page_view", "value": "143"},
]

df = pd.DataFrame([{d.get("action_type"): d.get("value") for d in lst}])
print(df)

Prints:

  link_click page_engagement post_engagement post post_reaction landing_page_view
0        812             823             823    1            10               143