Python converting a list in a nested dictionary into a Pandas Dataframe

Question

I have the following dictionary

test = {'data': [
  {'actions': [
    {'action_type': 'link_click', 'value': '16'},
    {'action_type': 'post_engagement', 'value': '16'},
    {'action_type': 'page_engagement', 'value': '16'}],
   'spend': '13.59',
   'date_start': '2023-02-07',
   'date_stop': '2023-02-07'},
  {'actions': [
    {'action_type': 'comment', 'value': '5'},
    {'action_type': 'onsite_conversion.post_save', 'value': '1'},
    {'action_type': 'link_click', 'value': '465'},
    {'action_type': 'post', 'value': '1'},
    {'action_type': 'post_reaction', 'value': '20'},
    {'action_type': 'video_view', 'value': '4462'},
    {'action_type': 'post_engagement', 'value': '4954'},
    {'action_type': 'page_engagement', 'value': '4954'}],
   'spend': '214.71',
   'date_start': '2023-02-07',
   'date_stop': '2023-02-07'}]}

And I am trying to convert it where each element after action type is a pandas DataFrame column, and the value as row. Something like

link_click post_engagement page_engagement   spend comment onsite_conversion ...
        16              16              16   13.59     N/A               N/A
       465            4954            4954  214.71       5                 1

I understand that the first list does not have comment, post, etc, and the rows would be N/A. How do I manage this complicated data structure?

I'm not quite sure how to work this, but you might try using json_normalize, then you might need to do a pivot. Or I suppose you could do it manually :| i.e. use Python structures like list comprehensions and such. — wjandrea
– wjandrea, Commented Feb 9, 2023 at 20:54

wjandrea · Accepted Answer · 2023-02-10 19:07:46Z

1

You could use something like this function:

# import pandas as pd

def tabulate_actions(actionsList:list, returnDf=False):
    aTbl = [{
        a['action_type']: a['value'] for a in al['actions']
        # if isinstance(a, dict) and all([k in a for k in ['action_type', 'value']])
    } for al in actionsList
    # if isinstance(al, dict) and isinstance(al.get('actions'), list)
    ]
    return pd.DataFrame(aTbl) if returnDf else aTbl
## uncomment the conditions if you're unsure of your data structure

tabulate_actions(test['data']) should return this list of dictionaries:

[{'link_click': '16',
  'post_engagement': '16',
  'page_engagement': '16'},
 {'comment': '5',
  'onsite_conversion.post_save': '1',
  'link_click': '465',
  'post': '1',
  'post_reaction': '20',
  'video_view': '4462',
  'post_engagement': '4954',
  'page_engagement': '4954'}]

and passing returnDf=True should make it return a DataFrame:

edited Feb 10, 2023 at 19:07

wjandrea

34k10 gold badges69 silver badges105 bronze badges

answered Feb 9, 2023 at 21:19

Driftr95

4,7792 gold badges13 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python converting a list in a nested dictionary into a Pandas Dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related