How to access a value within a nested json file based on conditions

March 30, 2024

I’ve been working on a project where I need to access data from api’s. In these api’s are values named ‘decimal’ which I need to access. I have no problem accessing and displaying the decimal value itself, but when I try to only display decimal value based on another value within the json I seem to struggle. My code currently looks like this:

import pandas as pd
import requests as r

api = 'https://content.toto.nl/content-service/api/v1/q/event-list?startTimeFrom=2024-03-31T22%3A00%3A00Z&startTimeTo=2024-04-01T21%3A59%3A59Z&started=false&maxMarkets=10&orderMarketsBy=displayOrder&marketSortsIncluded=--%2CCS%2CDC%2CDN%2CHH%2CHL%2CMH%2CMR%2CWH&marketGroupTypesIncluded=CUSTOM_GROUP%2CDOUBLE_CHANCE%2CDRAW_NO_BET%2CMATCH_RESULT%2CMATCH_WINNER%2CMONEYLINE%2CROLLING_SPREAD%2CROLLING_TOTAL%2CSTATIC_SPREAD%2CSTATIC_TOTAL&eventSortsIncluded=MTCH&includeChildMarkets=true&prioritisePrimaryMarkets=true&includeCommentary=true&includeMedia=true&drilldownTagIds=691&excludeDrilldownTagIds=7291%2C7294%2C7300%2C7303%2C7306'
re = r.get(api)
red = re.json()

#Finding the 'type' variable within the Json file
match_result = pd.json_normalize(red, record_path=['data', 'events', 'markets', 'outcomes'])
match_result = match_result['type']

#For every type variable within the file we check if type == 'MR'
for type in match_result:
    if type == 'MR':  
        #If the type == 'MR' I want to print the decimal belonging to that type value but this is where i'm doing something wrong
        decimal = pd.json_normalize(match_result, record_path=['prices'])
        decimal = decimal['decimal']
        print(decimal)
    else:
        pass

I’ve been looking all over youtube and stackoverflow to find what im doing wrong but can’t seem to figure it out. Also important to note is that in the variable ‘match_result’ I access the type by using the record_path, but for the ‘decimal variable’ I need to go further into the record path with using ‘prices’ in the for loop. This is where I think i’m doing it wrong, but still no idea what it is i’m doing wrong.

The Json file from which I want to get the data looks something like this:

            "type": "MR",
            "subType": "D",

            #Some more data I wont need....

            "lateVoid": false,
            "outcomeScore": null,
            "prices": [
              {
                "numerator": 13,
                "denominator": 4,
                "decimal": 4.25,
                "displayOrder": 1,
                "priceType": "LP",
                "handicapLow": null,
                "handicapHigh": null

>Solution :

Here’s one approach:

Use pd.json_normalize with ‘type’ as metadata (include the same levels except ‘prices’).

df = pd.json_normalize(red, 
                       record_path=['data', 'events', 'markets', 'outcomes', 'prices'], 
                       meta=[['data', 'events', 'markets', 'outcomes', 'type']]
                       )

df.head()

   numerator  denominator  decimal  displayOrder priceType handicapLow  \
0         13            4     4.25             1        LP        None   
1         63          100     1.63             1        LP        None   
2          4            1     5.00             1        LP        None   
3         11           10     2.10             1        LP        None   
4         13           20     1.65             1        LP        None   

  handicapHigh data.events.markets.outcomes.type  
0         None                                MR  
1         None                                MR  
2         None                                MR  
3         None                                --  
4         None                                --

Now, use df.loc with Series.eq to get back all the values from column ‘decimal’ where our meta column equals ‘MR’.

meta_col = 'data.events.markets.outcomes.type'

decimals = df.loc[df[meta_col].eq('MR'), 'decimal']

decimals.head(5)

0     4.25
1     1.63
2     5.00
20    3.40
21    2.40
Name: decimal, dtype: float64

Here the index values (0, 1, 2, 20, 21) refer to the rows where ‘type’ equals ‘MR’.