我希望提高我的数据科学技能。 我正在练习从运动网站进行url数据拉取，json文件具有多个嵌套字典。 我想能够拉取这些数据以在matplotlib中映射自己的领先者板，等等，但是很难使json成为可操作的df。
主要网站是：https://www.usopen.com/scoring.html
通过查看背景，我认为实时信息是从下面列出的链接中获取的。 我正在使用Jupyter笔记本工作。 我可以成功地获取数据。
但是，正如您所看到的，它正在提取多个嵌套字典，这使得获取简单的数据框架变得非常困难。
只是想获得球员，杆数得分，总杆数和回合成绩。 任何帮助都将不胜感激，谢谢！
import pandas as pd
import urllib as ul
import json
url = "https://gripapi-static-pd.usopen.com/gripapi/leaderboard.json"
response = ul.request.urlopen(url)
data = json.loads(response.read())
print(data)
requests.get(url).json() 获取数据pandas.json_normalize 将 standings 键解压成数据框roundScores 是字典列表
.explode 扩展列表df 进行合并import requests
import pandas as pd
# load the data
df = pd.json_normalize(requests.get(url).json(), 'standings')
# explode the roundScores column
df = df.explode('roundScores', ignore_index=True)
# normalize the dicts in roundScores and join back to df
df = df.join(pd.json_normalize(df.roundScores), rsuffix='_rs').drop(columns=['roundScores']).reset_index(drop=True)
# display(df.head())
isRecapAvailable player.identifier player.firstName player.lastName player.image.gravity player.image.type player.image.identifier player.image.cropMode player.country.name player.country.code player.country.flag.type player.country.flag.identifier player.isAmateur toPar.value toPar.format toPar.displayValue toParToday.value toParToday.format toParToday.displayValue totalScore.value totalScore.format totalScore.displayValue position.value position.format position.displayValue holesThrough.value holesThrough.format holesThrough.displayValue liveVideo.identifier liveVideo.isLive score.value score.format score.displayValue toPar.value_rs toPar.format_rs toPar.displayValue_rs
0 True 56278 Matthew Wolff center imageCloudinary us-open/players/2020-players/Matthew_Wolff fill United States usa imageCloudinary us-open/flags/usa False -5 absolute -5 -5 absolute -5 140.0 absolute 140 1 absolute 1 10 absolute 10 NaN NaN 66 absolute 66 -4 absolute -4
1 True 56278 Matthew Wolff center imageCloudinary us-open/players/2020-players/Matthew_Wolff fill United States usa imageCloudinary us-open/flags/usa False -5 absolute -5 -5 absolute -5 140.0 absolute 140 1 absolute 1 10 absolute 10 NaN NaN 74 absolute 74 4 absolute +4
2 True 56278 Matthew Wolff center imageCloudinary us-open/players/2020-players/Matthew_Wolff fill United States usa imageCloudinary us-open/flags/usa False -5 absolute -5 -5 absolute -5 140.0 absolute 140 1 absolute 1 10 absolute 10 NaN NaN 0 absolute -5 absolute -5
3 True 34360 Patrick Reed center imageCloudinary us-open/players/2019-players/Patrick-Reed fill United States usa imageCloudinary us-open/flags/usa False -4 absolute -4 0 absolute E 136.0 absolute 136 2 absolute 2 7 absolute 7 NaN NaN 66 absolute 66 -4 absolute -4
4 True 34360 Patrick Reed center imageCloudinary us-open/players/2019-players/Patrick-Reed fill United States usa imageCloudinary us-open/flags/usa False -4 absolute -4 0 absolute E 136.0 absolute 136 2 absolute 2 7 absolute 7 NaN NaN 70 absolute 70 0 absolute E
standings 只是从下载的JSON中的一个键r = requests.get(url).json()
print(r)
[out]:
dict_keys(['currentRound', 'standings', 'fullLegend', 'shortLegend', 'inlineLegend', 'cutLine', 'meta'])
你可能想尝试这个：
import requests
import pandas as pd
url = "https://gripapi-static-pd.usopen.com/gripapi/leaderboard.json"
data = pd.DataFrame.from_dict(requests.get(url).json()['standings'])
print(data['totalScore'])
输出：
0 {'value': 140, 'format': 'absolute', 'displayV...
1 {'value': 136, 'format': 'absolute', 'displayV...
2 {'value': 140, 'format': 'absolute', 'displayV...
3 {'value': 138, 'format': 'absolute', 'displayV...
4 {'value': 138, 'format': 'absolute', 'displayV...
...
您需要编写一些自定义代码才能从json中获取所需内容。 如果您想将某些播放器详细信息放入df中，则可以参考以下示例。
df = pd.DataFrame([x['player'] for x in data['standings']])
df['image'] = df['image'].apply(lambda x: x['identifier'])
df['country'] = df['country'].apply(lambda x: x['name'])
简单快捷的解决方案。使用pandas中的JSON normalize可能会有更好的解决方案，但这对您的用例来说已经足够好了。
def func(x):
if not any(x.isnull()):
return (x['round'], x['player']['firstName'], x['player']['identifier'], x['toParToday']['value'], x['totalScore']['value'])
df = pd.DataFrame(data['standings'])
df['round'] = data['currentRound']['name']
df = df[['player', 'toPar', 'toParToday', 'totalScore', 'round']]
info = df.apply(func, axis=1)
info_df = pd.DataFrame(list(info.values), columns=['Round', 'player_name', 'pid', 'to_par_today', 'totalScore'])
info_df.head()