How can we parse a JSON string and reshape it into a dataframe?

Question

I am playing around with this code.

import requests
import json
import pandas as pd
from pandas.io.json import json_normalize

url = 'https://stats.nba.com/'
headers= {'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.110 Safari/537.36',
         'Referer': 'https://www.nba.com/'}

payload = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:109.0) Gecko/20100101 Firefox/109.0',
    'Accept': '*/*',
    'Accept-Language': 'en-US,en;q=0.5',
    'Referer': 'https://www.nba.com/',
    'Origin': 'https://www.nba.com',
    'Connection': 'keep-alive',
    'Sec-Fetch-Dest': 'empty',
    'Sec-Fetch-Mode': 'cors',
    'Sec-Fetch-Site': 'same-site',
    'If-Modified-Since': 'Fri, 24 Feb 2023 22:32:49 GMT',
    'If-None-Match': '1e760c69cdbf7ece59e5733255b8eb25'
}

response = requests.get('https://cdn.nba.com/static/json/liveData/odds/odds_todaysGames.json', headers=headers)

jsonData = response.json()

df_nested_list = pd.json_normalize(jsonData)


results = []
for item in df_nested_list['games']:
    print(item)
    results.append([item['homeTeamId']])
df = pd.DataFrame(results)
print(df)

I am stuck on the loop part. When I do print(item) I get this.

[{'gameId': '0022200898', 'sr_id': 'sr:match:35432417', 'srMatchId': '35432417', 'homeTeamId': '1610612750', 'awayTeamId': '1610612766', 'markets': [{'name': '2way', 'odds_type_id': 1, 'group_name': 'regular', 'books': [{'id': 'sr:book:8072', 'name': 'FrancePari3', 'outcomes': [{'odds_field_id': 1, 'type': 'home', 'odds': '1.330', 'opening_odds': '1.300', 'odds_trend': 'down'}, {'odds_field_id': 2, 'type': 'away', 'odds': '3.200', 'opening_odds': '3.400', 'odds_trend': 'up'}], 'url': 'https://ad.doubleclick.net/ddm/trackclk/N30402.2403113NBA/B25332343.296702000;dc_trk_aid=489395076;dc_trk_cid=146209763;dc_lat=;dc_rdid=;tag_for_child_directed_treatment=;tfua=;ltd=?https://www.enligne.parionssport.fdj.fr/paris-basketball/usa/nba?filtre=58557170', 'countryCode': 'FR'}, {'id': 'sr:book:27661', 'name': 'Betplay', 'outcomes': [{'odds_field_id': 1, 'type': 'home', 'odds': '1.380', 'opening_odds': '1.320', 'odds_trend': 'neutral'}, {'odds_field_id': 2, 'type': 'away', 'odds': '3.150', 'opening_odds': '3.450', 'odds_trend': 'up'}], 'url': 'https://betplay.com.co/apuestas#filter/basketball/nba', 'countryCode': 'CO'}, {'id': 'sr:book:35226', 'name': 'TabAustralia', 'outcomes': [{'odds_field_id': 1, 'type': 'home', 'odds': '1.370', 'opening_odds': '1.340', 'odds_trend': 'down'}, {'odds_field_id': 2, 'type': 'away', 'odds': '3.100', 'opening_odds': '3.400', 'odds_trend': 'up'}], 'url': 'https://www.tab.com.au/sports/betting/Basketball/competitions/NBA', 'countryCode': 'AU'}]}, {'name': 'spread', 'odds_type_id': 4, 'group_name': 'regular', 'books': [{'id': 'sr:book:27661', 'name': 'Betplay', 'outcomes': [{'odds_field_id': 10, 'type': 'home', 'spread': '-7.5', 'odds': '1.970', 'opening_odds': '1.900', 'opening_spread': -7.5, 'odds_trend': 'down'}, {'odds_field_id': 12, 'type': 'away', 'spread': '7.5', 'odds': '1.850', 'opening_odds': '1.920', 'opening_spread': 7.5, 'odds_trend': 'up'}], 'url': 'https://betplay.com.co/apuestas#filter/basketball/nba', 'countryCode': 'CO'}, {'id': 'sr:book:35226', 'name': 'TabAustralia', 'outcomes': [{'odds_field_id': 10, 'type': 'home', 'spread': '-7.5', 'odds': '2.050', 'opening_odds': '1.900', 'opening_spread': -7.5, 'odds_trend': 'up'}, {'odds_field_id': 12, 'type': 'away', 'spread': '7.5', 'odds': '1.720', 'opening_odds': '1.900', 'opening_spread': 7.5, 'odds_trend': 'down'}], 'url': 'https://www.tab.com.au/sports/betting/Basketball/competitions/NBA', 'countryCode': 'AU'}]}]}, {'gameId': '0022200901', 'sr_id': 'sr:match:35431757', 'srMatchId': '35431757', 'homeTeamId': '1610612744', 'awayTeamId': '1610612745', 'markets': [{'name': '2way', 'odds_type_id': 1, 'group_name': 'regular', 'books': [{'id': 'sr:book:8072', 'name': 'FrancePari3', 'outcomes': [{'odds_field_id': 1, 'type': 'home', 'odds': '1.230', 'opening_odds': '1.210', 'odds_trend': 'up'}, {'odds_field_id': 2, 'type': 'away', 'odds': '3.950', 'opening_odds': '4.200', 'odds_trend': 'down'}], 'url': 'https://ad.doubleclick.net/ddm/trackclk/N30402.2403113NBA/B25332343.296702000;dc_trk_aid=489395076;dc_trk_cid=146209763;dc_lat=;dc_rdid=;tag_for_child_directed_treatment=;tfua=;ltd=?https://www.enligne.parionssport.fdj.fr/paris-basketball/usa/nba?filtre=58557170', 'countryCode': 'FR'}, {'id': 'sr:book:27661', 'name': 'Betplay', 'outcomes': [{'odds_field_id': 1, 'type': 'home', 'odds': '1.240', 'opening_odds': '1.190', 'odds_trend': 'down'}, {'odds_field_id': 2, 'type': 'away', 'odds': '4.200', 'opening_odds': '4.750', 'odds_trend': 'up'}], 'url': 'https://betplay.com.co/apuestas#filter/basketball/nba', 'countryCode': 'CO'}, {'id': 'sr:book:35226', 'name': 'TabAustralia', 'outcomes': [{'odds_field_id': 1, 'type': 'home', 'odds': '1.260', 'opening_odds': '1.240', 'odds_trend': 'up'}, {'odds_field_id': 2, 'type': 'away', 'odds': '3.900', 'opening_odds': '4.200', 'odds_trend': 'down'}], 'url': 'https://www.tab.com.au/sports/betting/Basketball/competitions/NBA', 'countryCode': 'AU'}]}, {'name': 'spread', 'odds_type_id': 4, 'group_name': 'regular', 'books': [{'id': 'sr:book:27661', 'name': 'Betplay', 'outcomes': [{'odds_field_id': 10, 'type': 'home', 'spread': '-9.5', 'odds': '1.950', 'opening_odds': '1.900', 'opening_spread': -10, 'odds_trend': 'down'}, {'odds_field_id': 12, 'type': 'away', 'spread': '9.5', 'odds': '1.870', 'opening_odds': '1.920', 'opening_spread': 10, 'odds_trend': 'up'}], 'url': 'https://betplay.com.co/apuestas#filter/basketball/nba', 'countryCode': 'CO'}, {'id': 'sr:book:35226', 'name': 'TabAustralia', 'outcomes': [{'odds_field_id': 10, 'type': 'home', 'spread': '-9.5', 'odds': '2.000', 'opening_odds': '1.900', 'opening_spread': -9, 'odds_trend': 'down'}, {'odds_field_id': 12, 'type': 'away', 'spread': '9.5', 'odds': '1.750', 'opening_odds': '1.900', 'opening_spread': 9, 'odds_trend': 'up'}], 'url': 'https://www.tab.com.au/sports/betting/Basketball/competitions/NBA', 'countryCode': 'AU'}]}]}, {'gameId': '0022200899', 'sr_id': 'sr:match:35433317', 'srMatchId': '35433317', 'homeTeamId': '1610612756', 'awayTeamId': '1610612760', 'markets': [{'name': '2way', 'odds_type_id': 1, 'group_name': 'regular', 'books': [{'id': 'sr:book:8072', 'name': 'FrancePari3', 'outcomes': [{'odds_field_id': 1, 'type': 'home', 'odds': '1.220', 'opening_odds': '1.270', 'odds_trend': 'up'}, {'odds_field_id': 2, 'type': 'away', 'odds': '4.100', 'opening_odds': '3.650', 'odds_trend': 'down'}], 'url': 'https://ad.doubleclick.net/ddm/trackclk/N30402.2403113NBA/B25332343.296702000;dc_trk_aid=489395076;dc_trk_cid=146209763;dc_lat=;dc_rdid=;tag_for_child_directed_treatment=;tfua=;ltd=?https://www.enligne.parionssport.fdj.fr/paris-basketball/usa/nba?filtre=58557170', 'countryCode': 'FR'}, {'id': 'sr:book:27661', 'name': 'Betplay', 'outcomes': [{'odds_field_id': 1, 'type': 'home', 'odds': '1.240', 'opening_odds': '1.220', 'odds_trend': 'up'}, {'odds_field_id': 2, 'type': 'away', 'odds': '4.200', 'opening_odds': '4.350', 'odds_trend': 'down'}], 'url': 'https://betplay.com.co/apuestas#filter/basketball/nba', 'countryCode': 'CO'}, {'id': 'sr:book:35226', 'name': 'TabAustralia', 'outcomes': [{'odds_field_id': 1, 'type': 'home', 'odds': '1.230', 'opening_odds': '1.370', 'odds_trend': 'up'}, {'odds_field_id': 2, 'type': 'away', 'odds': '4.200', 'opening_odds': '3.200', 'odds_trend': 'down'}], 'url': 'https://www.tab.com.au/sports/betting/Basketball/competitions/NBA', 'countryCode': 'AU'}]}, {'name': 'spread', 'odds_type_id': 4, 'group_name': 'regular', 'books': [{'id': 'sr:book:27661', 'name': 'Betplay', 'outcomes': [{'odds_field_id': 10, 'type': 'home', 'spread': '-7.5', 'odds': '1.750', 'opening_odds': '1.860', 'opening_spread': -8.5, 'odds_trend': 'down'}, {'odds_field_id': 12, 'type': 'away', 'spread': '7.5', 'odds': '2.100', 'opening_odds': '1.960', 'opening_spread': 8.5, 'odds_trend': 'up'}], 'url': 'https://betplay.com.co/apuestas#filter/basketball/nba', 'countryCode': 'CO'}, {'id': 'sr:book:35226', 'name': 'TabAustralia', 'outcomes': [{'odds_field_id': 10, 'type': 'home', 'spread': '-7.5', 'odds': '1.650', 'opening_odds': '1.950', 'opening_spread': -7.5, 'odds_trend': 'up'}, {'odds_field_id': 12, 'type': 'away', 'spread': '7.5', 'odds': '2.150', 'opening_odds': '1.800', 'opening_spread': 7.5, 'odds_trend': 'down'}], 'url': 'https://www.tab.com.au/sports/betting/Basketball/competitions/NBA', 'countryCode': 'AU'}]}]}, {'gameId': '0022200902', 'sr_id': 'sr:match:35432747', 'srMatchId': '35432747', 'homeTeamId': '1610612746', 'awayTeamId': '1610612758', 'markets': [{'name': '2way', 'odds_type_id': 1, 'group_name': 'regular', 'books': [{'id': 'sr:book:8072', 'name': 'FrancePari3', 'outcomes': [{'odds_field_id': 1, 'type': 'home', 'odds': '1.360', 'opening_odds': '1.360', 'odds_trend': 'down'}, {'odds_field_id': 2, 'type': 'away', 'odds': '3.050', 'opening_odds': '3.050', 'odds_trend': 'up'}], 'url': 'https://ad.doubleclick.net/ddm/trackclk/N30402.2403113NBA/B25332343.296702000;dc_trk_aid=489395076;dc_trk_cid=146209763;dc_lat=;dc_rdid=;tag_for_child_directed_treatment=;tfua=;ltd=?https://www.enligne.parionssport.fdj.fr/paris-basketball/usa/nba?filtre=58557170', 'countryCode': 'FR'}, {'id': 'sr:book:27661', 'name': 'Betplay', 'outcomes': [{'odds_field_id': 1, 'type': 'home', 'odds': '1.410', 'opening_odds': '1.370', 'odds_trend': 'up'}, {'odds_field_id': 2, 'type': 'away', 'odds': '3.000', 'opening_odds': '3.100', 'odds_trend': 'down'}], 'url': 'https://betplay.com.co/apuestas#filter/basketball/nba', 'countryCode': 'CO'}, {'id': 'sr:book:35226', 'name': 'TabAustralia', 'outcomes': [{'odds_field_id': 1, 'type': 'home', 'odds': '1.420', 'opening_odds': '1.390', 'odds_trend': 'up'}, {'odds_field_id': 2, 'type': 'away', 'odds': '2.900', 'opening_odds': '3.100', 'odds_trend': 'down'}], 'url': 'https://www.tab.com.au/sports/betting/Basketball/competitions/NBA', 'countryCode': 'AU'}]}, {'name': 'spread', 'odds_type_id': 4, 'group_name': 'regular', 'books': [{'id': 'sr:book:27661', 'name': 'Betplay', 'outcomes': [{'odds_field_id': 10, 'type': 'home', 'spread': '-6.5', 'odds': '1.950', 'opening_odds': '1.910', 'opening_spread': -6.5, 'odds_trend': 'up'}, {'odds_field_id': 12, 'type': 'away', 'spread': '6.5', 'odds': '1.870', 'opening_odds': '1.910', 'opening_spread': 6.5, 'odds_trend': 'down'}], 'url': 'https://betplay.com.co/apuestas#filter/basketball/nba', 'countryCode': 'CO'}, {'id': 'sr:book:35226', 'name': 'TabAustralia', 'outcomes': [{'odds_field_id': 10, 'type': 'home', 'spread': '-6.5', 'odds': '1.900', 'opening_odds': '1.900', 'opening_spread': -6.5, 'odds_trend': 'down'}, {'odds_field_id': 12, 'type': 'away', 'spread': '6.5', 'odds': '1.850', 'opening_odds': '1.900', 'opening_spread': 6.5, 'odds_trend': 'up'}], 'url': 'https://www.tab.com.au/sports/betting/Basketball/competitions/NBA', 'countryCode': 'AU'}]}]}, {'gameId': '0022200903', 'sr_id': 'sr:match:35433377', 'srMatchId': '35433377', 'homeTeamId': '1610612765', 'awayTeamId': '1610612761', 'markets': [{'name': '2way', 'odds_type_id': 1, 'group_name': 'regular', 'books': [{'id': 'sr:book:35226', 'name': 'TabAustralia', 'outcomes': [{'odds_field_id': 1, 'type': 'home', 'odds': '3.100', 'opening_odds': '3.300', 'odds_trend': 'down'}, {'odds_field_id': 2, 'type': 'away', 'odds': '1.370', 'opening_odds': '1.330', 'odds_trend': 'up'}], 'url': 'https://www.tab.com.au/sports/betting/Basketball/competitions/NBA', 'countryCode': 'AU'}]}, {'name': 'spread', 'odds_type_id': 4, 'group_name': 'regular', 'books': [{'id': 'sr:book:35226', 'name': 'TabAustralia', 'outcomes': [{'odds_field_id': 10, 'type': 'home', 'spread': '6.5', 'odds': '1.900', 'opening_odds': '1.900', 'opening_spread': 7, 'odds_trend': 'down'}, {'odds_field_id': 12, 'type': 'away', 'spread': '-6.5', 'odds': '1.900', 'opening_odds': '1.900', 'opening_spread': -7, 'odds_trend': 'up'}], 'url': 'https://www.tab.com.au/sports/betting/Basketball/competitions/NBA', 'countryCode': 'AU'}]}]}, {'gameId': '0022200904', 'sr_id': 'sr:match:35432299', 'srMatchId': '35432299', 'homeTeamId': '1610612766', 'awayTeamId': '1610612748', 'markets': [{'name': '2way', 'odds_type_id': 1, 'group_name': 'regular', 'books': [{'id': 'sr:book:35226', 'name': 'TabAustralia', 'outcomes': [{'odds_field_id': 1, 'type': 'home', 'odds': '2.600', 'opening_odds': '2.800', 'odds_trend': 'down'}, {'odds_field_id': 2, 'type': 'away', 'odds': '1.500', 'opening_odds': '1.440', 'odds_trend': 'up'}], 'url': 'https://www.tab.com.au/sports/betting/Basketball/competitions/NBA', 'countryCode': 'AU'}]}, {'name': 'spread', 'odds_type_id': 4, 'group_name': 'regular', 'books': [{'id': 'sr:book:35226', 'name': 'TabAustralia', 'outcomes': [{'odds_field_id': 10, 'type': 'home', 'spread': '5.5', 'odds': '1.750', 'opening_odds': '1.900', 'opening_spread': 5.5, 'odds_trend': 'down'}, {'odds_field_id': 12, 'type': 'away', 'spread': '-5.5', 'odds': '2.000', 'opening_odds': '1.900', 'opening_spread': -5.5, 'odds_trend': 'up'}], 'url': 'https://www.tab.com.au/sports/betting/Basketball/competitions/NBA', 'countryCode': 'AU'}]}]}, {'gameId': '0022200905', 'sr_id': 'sr:match:35432307', 'srMatchId': '35432307', 'homeTeamId': '1610612753', 'awayTeamId': '1610612754', 'markets': [{'name': '2way', 'odds_type_id': 1, 'group_name': 'regular', 'books': [{'id': 'sr:book:35226', 'name': 'TabAustralia', 'outcomes': [{'odds_field_id': 1, 'type': 'home', 'odds': '1.620', 'opening_odds': '1.700', 'odds_trend': 'down'}, {'odds_field_id': 2, 'type': 'away', 'odds': '2.350', 'opening_odds': '2.200', 'odds_trend': 'up'}], 'url': 'https://www.tab.com.au/sports/betting/Basketball/competitions/NBA', 'countryCode': 'AU'}]}, {'name': 'spread', 'odds_type_id': 4, 'group_name': 'regular', 'books': [{'id': 'sr:book:35226', 'name': 'TabAustralia', 'outcomes': [{'odds_field_id': 10, 'type': 'home', 'spread': '-2.5', 'odds': '1.750', 'opening_odds': '1.900', 'opening_spread': -2.5, 'odds_trend': 'down'}, {'odds_field_id': 12, 'type': 'away', 'spread': '2.5', 'odds': '2.000', 'opening_odds': '1.900', 'opening_spread': 2.5, 'odds_trend': 'up'}], 'url': 'https://www.tab.com.au/sports/betting/Basketball/competitions/NBA', 'countryCode': 'AU'}]}]}, {'gameId': '0022200906', 'sr_id': 'sr:match:35431923', 'srMatchId': '35431923', 'homeTeamId': '1610612752', 'awayTeamId': '1610612740', 'markets': [{'name': '2way', 'odds_type_id': 1, 'group_name': 'regular', 'books': [{'id': 'sr:book:35226', 'name': 'TabAustralia', 'outcomes': [{'odds_field_id': 1, 'type': 'home', 'odds': '1.650', 'opening_odds': '1.650', 'odds_trend': 'down'}, {'odds_field_id': 2, 'type': 'away', 'odds': '2.300', 'opening_odds': '2.300', 'odds_trend': 'up'}], 'url': 'https://www.tab.com.au/sports/betting/Basketball/competitions/NBA', 'countryCode': 'AU'}]}, {'name': 'spread', 'odds_type_id': 4, 'group_name': 'regular', 'books': [{'id': 'sr:book:35226', 'name': 'TabAustralia', 'outcomes': [{'odds_field_id': 10, 'type': 'home', 'spread': '-3', 'odds': '1.900', 'opening_odds': '1.900', 'opening_spread': -3, 'odds_trend': 'down'}, {'odds_field_id': 12, 'type': 'away', 'spread': '3', 'odds': '1.900', 'opening_odds': '1.900', 'opening_spread': 3, 'odds_trend': 'up'}], 'url': 'https://www.tab.com.au/sports/betting/Basketball/competitions/NBA', 'countryCode': 'AU'}]}]}, {'gameId': '0022200907', 'sr_id': 'sr:match:35432501', 'srMatchId': '35432501', 'homeTeamId': '1610612763', 'awayTeamId': '1610612743', 'markets': [{'name': '2way', 'odds_type_id': 1, 'group_name': 'regular', 'books': [{'id': 'sr:book:35226', 'name': 'TabAustralia', 'outcomes': [{'odds_field_id': 1, 'type': 'home', 'odds': '1.770', 'opening_odds': '2.000', 'odds_trend': 'down'}, {'odds_field_id': 2, 'type': 'away', 'odds': '2.100', 'opening_odds': '1.850', 'odds_trend': 'up'}], 'url': 'https://www.tab.com.au/sports/betting/Basketball/competitions/NBA', 'countryCode': 'AU'}]}, {'name': 'spread', 'odds_type_id': 4, 'group_name': 'regular', 'books': [{'id': 'sr:book:35226', 'name': 'TabAustralia', 'outcomes': [{'odds_field_id': 10, 'type': 'home', 'spread': '-2.5', 'odds': '1.900', 'opening_odds': '2.250', 'opening_spread': -2.5, 'odds_trend': 'neutral'}, {'odds_field_id': 12, 'type': 'away', 'spread': '2.5', 'odds': '1.900', 'opening_odds': '1.600', 'opening_spread': 2.5, 'odds_trend': 'neutral'}], 'url': 'https://www.tab.com.au/sports/betting/Basketball/competitions/NBA', 'countryCode': 'AU'}]}]}, {'gameId': '0022200908', 'sr_id': 'sr:match:35432857', 'srMatchId': '35432857', 'homeTeamId': '1610612755', 'awayTeamId': '1610612738', 'markets': [{'name': '2way', 'odds_type_id': 1, 'group_name': 'regular', 'books': [{'id': 'sr:book:35226', 'name': 'TabAustralia', 'outcomes': [{'odds_field_id': 1, 'type': 'home', 'odds': '2.000', 'opening_odds': '2.000', 'odds_trend': 'neutral'}, {'odds_field_id': 2, 'type': 'away', 'odds': '1.850', 'opening_odds': '1.850', 'odds_trend': 'neutral'}], 'url': 'https://www.tab.com.au/sports/betting/Basketball/competitions/NBA', 'countryCode': 'AU'}]}, {'name': 'spread', 'odds_type_id': 4, 'group_name': 'regular', 'books': [{'id': 'sr:book:35226', 'name': 'TabAustralia', 'outcomes': [{'odds_field_id': 10, 'type': 'home', 'spread': '3.5', 'odds': '1.620', 'opening_odds': '1.650', 'opening_spread': 3.5, 'odds_trend': 'down'}, {'odds_field_id': 12, 'type': 'away', 'spread': '-3.5', 'odds': '2.200', 'opening_odds': '2.150', 'opening_spread': -3.5, 'odds_trend': 'up'}], 'url': 'https://www.tab.com.au/sports/betting/Basketball/competitions/NBA', 'countryCode': 'AU'}]}]}, {'gameId': '0022200909', 'sr_id': 'sr:match:35432317', 'srMatchId': '35432317', 'homeTeamId': '1610612762', 'awayTeamId': '1610612759', 'markets': [{'name': '2way', 'odds_type_id': 1, 'group_name': 'regular', 'books': [{'id': 'sr:book:35226', 'name': 'TabAustralia', 'outcomes': [{'odds_field_id': 1, 'type': 'home', 'odds': '1.180', 'opening_odds': '1.160', 'odds_trend': 'up'}, {'odds_field_id': 2, 'type': 'away', 'odds': '4.800', 'opening_odds': '5.200', 'odds_trend': 'down'}], 'url': 'https://www.tab.com.au/sports/betting/Basketball/competitions/NBA', 'countryCode': 'AU'}]}, {'name': 'spread', 'odds_type_id': 4, 'group_name': 'regular', 'books': [{'id': 'sr:book:35226', 'name': 'TabAustralia', 'outcomes': [{'odds_field_id': 10, 'type': 'home', 'spread': '-12.5', 'odds': '2.200', 'opening_odds': '2.100', 'opening_spread': -12.5, 'odds_trend': 'up'}, {'odds_field_id': 12, 'type': 'away', 'spread': '12.5', 'odds': '1.620', 'opening_odds': '1.680', 'opening_spread': 12.5, 'odds_trend': 'down'}], 'url': 'https://www.tab.com.au/sports/betting/Basketball/competitions/NBA', 'countryCode': 'AU'}]}]}]

How can I get that into a dataframe?

What should the df look like? Please [edit] to add your desired output. See [mre] as well as [How to make good reproducible pandas examples](/q/20109391/4518341). — wjandrea, Feb 25 '23 at 03:55
It would have headers like 'gameId', 'sr_id', 'srMatchId', 'homeTeamId', 'awayTeamId', etc. Is this even a looping process, or is there some simpler way of converting a JSON string to a dataframe? — ASH, Feb 25 '23 at 04:08

score 1 · Accepted Answer · answered Feb 25 '23 at 10:06

To get a dataframe from the jsonData you can try:

df = pd.DataFrame(jsonData['games']).explode('markets')

df = pd.concat([df, df.pop('markets').apply(pd.Series).add_prefix('markets_')], axis=1)
df = df.explode('markets_books')

df = pd.concat([df, df.pop('markets_books').apply(pd.Series).add_prefix('markets_books_')], axis=1)
df = df.explode('markets_books_outcomes')

df = pd.concat([df, df.pop('markets_books_outcomes').apply(pd.Series).add_prefix('markets_books_outcomes_')], axis=1)
print(df.head().to_markdown(index=False))

Prints:

gameId	sr_id	srMatchId	homeTeamId	awayTeamId	markets_name	markets_odds_type_id	markets_group_name	markets_books_id	markets_books_name	markets_books_url	markets_books_countryCode	markets_books_outcomes_odds_field_id	markets_books_outcomes_type	markets_books_outcomes_odds	markets_books_outcomes_opening_odds	markets_books_outcomes_odds_trend	markets_books_outcomes_spread	markets_books_outcomes_opening_spread
0022200903	sr:match:35433377	35433377	1610612765	1610612761	2way	1	regular	sr:book:8072	FrancePari3	https://ad.doubleclick.net/ddm/trackclk/N30402.2403113NBA/B25332343.296702000;dc_trk_aid=489395076;dc_trk_cid=146209763;dc_lat=;dc_rdid=;tag_for_child_directed_treatment=;tfua=;ltd=?https://www.enligne.parionssport.fdj.fr/paris-basketball/usa/nba?filtre=58557170	FR	1	home	3.15	3.15	neutral	nan	nan
0022200903	sr:match:35433377	35433377	1610612765	1610612761	2way	1	regular	sr:book:8072	FrancePari3	https://ad.doubleclick.net/ddm/trackclk/N30402.2403113NBA/B25332343.296702000;dc_trk_aid=489395076;dc_trk_cid=146209763;dc_lat=;dc_rdid=;tag_for_child_directed_treatment=;tfua=;ltd=?https://www.enligne.parionssport.fdj.fr/paris-basketball/usa/nba?filtre=58557170	FR	2	away	1.34	1.34	neutral	nan	nan
0022200903	sr:match:35433377	35433377	1610612765	1610612761	2way	1	regular	sr:book:27661	Betplay	https://betplay.com.co/apuestas#filter/basketball/nba	CO	1	home	3.2	3.2	neutral	nan	nan
0022200903	sr:match:35433377	35433377	1610612765	1610612761	2way	1	regular	sr:book:27661	Betplay	https://betplay.com.co/apuestas#filter/basketball/nba	CO	2	away	1.36	1.36	neutral	nan	nan
0022200903	sr:match:35433377	35433377	1610612765	1610612761	2way	1	regular	sr:book:35226	TabAustralia	https://www.tab.com.au/sports/betting/Basketball/competitions/NBA	AU	1	home	3.1	3.3	down	nan	nan

Works great! Thanks a lot!! So, just to be clear, is this pretty much the easiest way to do this kind of thing? I tried this one-liner: pd.json_normalize(jsonData) That gave me a dataframe, but it certainly wasn't normalized like a dataframe is normalized. — ASH, Feb 25 '23 at 13:39
@ASH It could be probably done with some clever one-liner, but to normalize json like yours I'm used to do these `.apply(pd.Series)`. — Andrej Kesely, Feb 25 '23 at 14:32
Ok. Got it. I'll certainly add this to my bag of tricks. Thanks again!! — ASH, Feb 26 '23 at 02:09

How can we parse a JSON string and reshape it into a dataframe?

1 Answers1