向Excel工作簿/工作表发送按请求刮取的表

Question

这里的初学者

我使用nhl.com的请求创建了一个表，我想将它发送到Excel

import requests

url = 'https://api.nhle.com/stats/rest/en/team/powerplay?isAggregate=false&isGame=false&sort=%5B%7B%22property%22:%22powerPlayPct%22,%22direction%22:%22DESC%22%7D,%7B%22property%22:%22teamId%22,%22direction%22:%22ASC%22%7D%5D&start=0&limit=50&factCayenneExp=gamesPlayed%3E=1&cayenneExp=gameTypeId=2%20and%20homeRoad=%22H%22%20and%20seasonId%3C=20212022%20and%20seasonId%3E=20212022'

data = requests.get(url).json()

for i in data['data']:
    print('{:<30} {:.1f}'.format(i['teamFullName'], i['powerPlayPct']*100))

我使用了请求而不是pandas，因为nhl.com上的动态格式用于抓取表格，我不觉得它会创建一个数据帧（就像pandas中的数据帧）来使用df.to_excel发送

我怎么能这么做

Answer 1

尝试使用pd.json_normalize并将记录路径参数作为“数据”传递

import requests
import pandas as pd

url = 'https://api.nhle.com/stats/rest/en/team/powerplay?isAggregate=false&isGame=false&sort=%5B%7B%22property%22:%22powerPlayPct%22,%22direction%22:%22DESC%22%7D,%7B%22property%22:%22teamId%22,%22direction%22:%22ASC%22%7D%5D&start=0&limit=50&factCayenneExp=gamesPlayed%3E=1&cayenneExp=gameTypeId=2%20and%20homeRoad=%22H%22%20and%20seasonId%3C=20212022%20and%20seasonId%3E=20212022'
data = requests.get(url).json()

df = pd.json_normalize(data, record_path='data')

# Do whatever math you want here

df.to_excel('nhl_data.xlsx', index=False)