如何根据条件访问嵌套JSON文件中的值

Question

我最近在做一个项目，需要从一些接口（api）获取数据。这些接口里有个叫“decimal”的值，我需要用到这个值。我可以顺利地获取和显示这个decimal值，但当我想根据json中的另一个值来只显示decimal值时，就遇到困难了。现在我的代码是这样的：

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

我在YouTube和StackOverflow上到处找，想弄清楚我哪里出错了，但就是找不到原因。还有一点很重要，就是在变量'match_result'中，我通过使用record_path来获取类型，但对于'decimal变量'，我需要在for循环中进一步深入record path，使用'prices'。我觉得我在这里可能出错了，但还是不知道具体哪里不对。

我想获取数据的这个Json文件大概长这样：

            "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

Answer 1

这里有一种方法：

• 使用 pd.json_normalize，把'type'当作元数据（包括除了'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                                --  

• 接下来，使用 df.loc 和 Series.eq，来获取'column'列中所有'decimal'的值，前提是我们的元数据列等于'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

这里的索引值（0, 1, 2, 20, 21）指的是'type'等于'MR'的行。

Answer 2

简单来说，你可以通过索引来使用数据框对象 match_result。想要获取小数部分，可以用：match_result['prices'][i][0]['decimal']。如果想知道类型，可以用：match_result['type'][i]

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()

match_result = pd.json_normalize(red, record_path=['data', 'events', 'markets', 'outcomes'])

for i, v in match_result.iterrows():
    if  match_result['type'][i] == 'MR':
        print(match_result['prices'][i][0]['decimal']) 
        # To print decimal use: match_result['prices'][i][0]['decimal']
        # To print type use:  match_result['type'][i]