Search code examples
pythonjsonpandasdata-processing

Best way to parse nested json from an api response

So I have some code to generate a json response from an api:

r4 = requests.get(url, params=mlp)
mlpr = r4.json()

1 row of the response looks like this.

'command': 'SELECT', 'rowCount': 134, 'oid': None, 'rows': [{'match_id': 5334428840, 'start_time': 1586029157, 'leagueid': 11823, 'patch': '7.25', 'name': 'ESL One Los Angeles 2020 Online powered by Intel', 'radiant_team': 'Cyber Legacy', 'dire_team': 'B8', 'picks_bans': [{'is_pick': False, 'hero_id': 98, 'team': 0, 'order': 0}, {'is_pick': False, 'hero_id': 95, 'team': 1, 'order': 1}, {'is_pick': False, 'hero_id': 66, 'team': 0, 'order': 2}, {'is_pick': False, 'hero_id': 43, 'team': 1, 'order': 3}, {'is_pick': False, 'hero_id': 49, 'team': 0, 'order': 4}, {'is_pick': False, 'hero_id': 110, 'team': 1, 'order': 5}, {'is_pick': False, 'hero_id': 79, 'team': 0, 'order': 6}, {'is_pick': False, 'hero_id': 106, 'team': 1, 'order': 7}, {'is_pick': True, 'hero_id': 96, 'team': 0, 'order': 8}, {'is_pick': True, 'hero_id': 86, 'team': 1, 'order': 9}, {'is_pick': True, 'hero_id': 129, 'team': 1, 'order': 10}, {'is_pick': True, 'hero_id': 50, 'team': 0, 'order': 11}, {'is_pick': False, 'hero_id': 12, 'team': 0, 'order': 12}, {'is_pick': False, 'hero_id': 77, 'team': 1, 'order': 13}, {'is_pick': True, 'hero_id': 128, 'team': 1, 'order': 14}, {'is_pick': True, 'hero_id': 121, 'team': 0, 'order': 15}, {'is_pick': True, 'hero_id': 41, 'team': 1, 'order': 16}, {'is_pick': True, 'hero_id': 42, 'team': 0, 'order': 17}, {'is_pick': False, 'hero_id': 126, 'team': 1, 'order': 18}, {'is_pick': False, 'hero_id': 65, 'team': 0, 'order': 19}, {'is_pick': True, 'hero_id': 31, 'team': 0, 'order': 20}, {'is_pick': True, 'hero_id': 45, 'team': 1, 'order': 21}]}

As you can see the picks_bans "Column has a nested dictionary of 3 additional columns I need to pull out and spread across each match ID.

Here is the code I am using to put my response into an initial Dataframe, but it is only getting me to the initial level.

mlpr_df = pd.DataFrame(mlpr.get('rows'))
mlpr_df

[example dataframe][1]

How do I go about unnesting the picks_bans column appropriately?

Edit: I attempted to change the code to:

r4 = requests.get(url, params=mlp)
mlpr = r4.json()
data = mlpr.get('rows')

df = pd.concat([pd.DataFrame(data), 
                json_normalize(data['picks_bans'])], 
                axis=1).drop('picks_bans', 1)

I receive and error that says "list indices must be integers or slices, not str"

Solution

  • json_normalize is what you are looking for.

    As a trick, I'm using the list of keys of the first row of data minus the field to expand to get the list of fields to use as metadata -- it's easier to write and more resilient. I've put the name of the arguments in the call to be more explicit.

    import pandas as pd
from pandas import json_normalize

df = json_normalize(data, record_path="picks_bans", 
                    meta=[col for col in data[0].keys() if col != "picks_bans"])
df.head()

#     is_pick      hero_id    team    order    match_id    start_time    leagueid    patch  name                                              radiant_team    dire_team
# --  ---------  ---------  ------  -------  ----------  ------------  ----------  -------  ------------------------------------------------  --------------  -----------
#  0  False             98       0        0  5334428840    1586029157       11823     7.25  ESL One Los Angeles 2020 Online powered by Intel  Cyber Legacy    B8
#  1  False             95       1        1  5334428840    1586029157       11823     7.25  ESL One Los Angeles 2020 Online powered by Intel  Cyber Legacy    B8
#  2  False             66       0        2  5334428840    1586029157       11823     7.25  ESL One Los Angeles 2020 Online powered by Intel  Cyber Legacy    B8

    Data sample

    data = [{'match_id': 5334428840, 'start_time': 1586029157, 'leagueid': 11823, 'patch': '7.25', 'name': 'ESL One Los Angeles 2020 Online powered by Intel', 'radiant_team': 'Cyber Legacy', 'dire_team': 'B8', 'picks_bans': 
        [{'is_pick': False, 'hero_id': 98, 'team': 0, 'order': 0}, {'is_pick': False, 'hero_id': 95, 'team': 1, 'order': 1}, {'is_pick': False, 'hero_id': 66, 'team': 0, 'order': 2}, 
         {'is_pick': False, 'hero_id': 43, 'team': 1, 'order': 3}, {'is_pick': False, 'hero_id': 49, 'team': 0, 'order': 4}, {'is_pick': False, 'hero_id': 110, 'team': 1, 'order': 5}, 
         {'is_pick': False, 'hero_id': 79, 'team': 0, 'order': 6}, {'is_pick': False, 'hero_id': 106, 'team': 1, 'order': 7}, {'is_pick': True, 'hero_id': 96, 'team': 0, 'order': 8}, 
         {'is_pick': True, 'hero_id': 86, 'team': 1, 'order': 9}, {'is_pick': True, 'hero_id': 129, 'team': 1, 'order': 10}, {'is_pick': True, 'hero_id': 50, 'team': 0, 'order': 11}, 
         {'is_pick': False, 'hero_id': 12, 'team': 0, 'order': 12}, {'is_pick': False, 'hero_id': 77, 'team': 1, 'order': 13}, {'is_pick': True, 'hero_id': 128, 'team': 1, 'order': 14}, 
         {'is_pick': True, 'hero_id': 121, 'team': 0, 'order': 15}, {'is_pick': True, 'hero_id': 41, 'team': 1, 'order': 16}, {'is_pick': True, 'hero_id': 42, 'team': 0, 'order': 17}, 
         {'is_pick': False, 'hero_id': 126, 'team': 1, 'order': 18}, {'is_pick': False, 'hero_id': 65, 'team': 0, 'order': 19}, {'is_pick': True, 'hero_id': 31, 'team': 0, 'order': 20}, 
         {'is_pick': True, 'hero_id': 45, 'team': 1, 'order': 21}]},
        {'match_id': 5334428840, 'start_time': 1586029157, 'leagueid': 11823, 'patch': '7.25', 'name': 'ESL One Los Angeles 2020 Online powered by Intel', 'radiant_team': 'Cyber Legacy', 'dire_team': 'B8', 'picks_bans': 
        [{'is_pick': False, 'hero_id': 98, 'team': 0, 'order': 0}, {'is_pick': False, 'hero_id': 95, 'team': 1, 'order': 1}, {'is_pick': False, 'hero_id': 66, 'team': 0, 'order': 2}, 
         {'is_pick': False, 'hero_id': 43, 'team': 1, 'order': 3}, {'is_pick': False, 'hero_id': 49, 'team': 0, 'order': 4}, {'is_pick': False, 'hero_id': 110, 'team': 1, 'order': 5}, 
         {'is_pick': False, 'hero_id': 79, 'team': 0, 'order': 6}, {'is_pick': False, 'hero_id': 106, 'team': 1, 'order': 7}, {'is_pick': True, 'hero_id': 96, 'team': 0, 'order': 8}, 
         {'is_pick': True, 'hero_id': 86, 'team': 1, 'order': 9}, {'is_pick': True, 'hero_id': 129, 'team': 1, 'order': 10}, {'is_pick': True, 'hero_id': 50, 'team': 0, 'order': 11}, 
         {'is_pick': False, 'hero_id': 12, 'team': 0, 'order': 12}, {'is_pick': False, 'hero_id': 77, 'team': 1, 'order': 13}, {'is_pick': True, 'hero_id': 128, 'team': 1, 'order': 14}, 
         {'is_pick': True, 'hero_id': 121, 'team': 0, 'order': 15}, {'is_pick': True, 'hero_id': 41, 'team': 1, 'order': 16}, {'is_pick': True, 'hero_id': 42, 'team': 0, 'order': 17}, 
         {'is_pick': False, 'hero_id': 126, 'team': 1, 'order': 18}, {'is_pick': False, 'hero_id': 65, 'team': 0, 'order': 19}, {'is_pick': True, 'hero_id': 31, 'team': 0, 'order': 20}, 
         {'is_pick': True, 'hero_id': 45, 'team': 1, 'order': 21}]}
       ]