how to create several columns from list of dictionaries

Question

So I am trying to parse Facebook ads data using the Facebook Graph API, I was able to create and get all the data I needed, however two of the returned items are lists of dictionaries, specifically the actions field and action_value field, each of which contains the name of the action and the value of that action.

I have added the 2 list of dicts as is in my dataframe, however, I need to actually have each name/value in a column, the column name would be the action name from the dict, and the value would be the action value from the same dict. so instead of having for example 30 columns per row, I would like to have 28 + however many dicts are within the actions or action_value lists.

Please notice how the action_values and action columns consist of several values 'which are dicts' I would like to have each of the items within the dict to be a column of its own.

Answer 1

I got your point. You need to extract every k v pairs from the list of dictionaries to generate a flat one. Here's some code:

import pandas as pd


if __name__ == '__main__':
    data = [{"action_values": [{"action_type": "post", "value": 1},{"action_type": "purchase", "value": 1}],
             "actions": [{"action_type": "post_1", "value": 1}, {"action_type": "purchase", "value": 2}],
             "click": 123}]
    cleaned = []
    for i in data:
        for action_values in i["action_values"]:
            tmp = {**i}
            tmp.pop("action_values")
            tmp.pop("actions")
            for k, v in action_values.items():
                tmp[f"action_values/{k}"] = v
            for action in i["actions"]:
                for k2, v2 in action.items():
                    tmp[f"actions/{k2}"] = v2
                cleaned.append(tmp.copy())
    df = pd.DataFrame(cleaned)
    print(df)

Thus, In the before, you have one row in df, with action_values field assigns with list of two dictionaries, actions field assign with list of two dictionaries. After processing, you get 2 * 2 = 4 rows in df.