How to unpack complicated nested column (list of dictionaries of dictionaries) in Python? [Twitter Ads API]

Question

Hi I am fairly new to Python/programming and am having trouble with a unpacking a nested column in my dataframe.

The df in question looks like this:

The column I am trying to unpack looks like this (in JSON format):

df['id_data'] = [{u'metrics': {u'app_clicks': [6, 28, 13, 28, 43, 45],
  u'card_engagements': [6, 28, 13, 28, 43, 45],
  u'carousel_swipes': None,
  u'clicks': [18, 33, 32, 48, 70, 95],
  u'engagements': [25, 68, 46, 79, 119, 152],
  u'follows': [0, 4, 1, 1, 1, 5],
  u'impressions': [1697, 5887, 3174, 6383, 10250, 12301],
  u'likes': [3, 4, 6, 9, 12, 15],
  u'poll_card_vote': None,
  u'qualified_impressions': None,
  u'replies': [0, 0, 0, 0, 0, 1],
  u'retweets': [1, 3, 0, 2, 5, 6],
  u'tweets_send': None,
  u'url_clicks': None},
 u'segment': None}]

As you can see, there is a lot going on in this column. There is a list -> dictionary -> dictionary -> potentially another list. I would like each individual metric (app_clicks, card_engagement, carousel_swipes, etc) to be its own column. I've tried the following code with no progress.

df['app_clicks'] = df.apply(lambda row: u['app_clicks'] for y in row['id_data'] if y['metricdata'] = 'list', axis=1)

Any thoughts on how I could tackle this?

Answer 1

You should be able to pass the dictionary directly to the dataframe constructor:

foo = pd.DataFrame(df['id_data'][0]['metrics'])
foo.iloc[:3, :4]

    app_clicks  card_engagements    carousel_swipes clicks
0   6           6                   None            18
1   28          28                  None            33
2   13          13                  None            32

Hopefully I am understanding your question correctly and this gets you what you need