如何从NBA API获取球队高阶数据并同步至Google Sheets？

我正在搭建基于Google Sheets的NBA数据库，需要获取进攻效率（Offensive Rating）、防守效率（Defensive Rating）、节奏（Pace）、**净效率（Net Rating）**等高阶数据，但现有方法都没成功。目前已经实现了基础赛事数据和球队战绩的抓取（代码如下），试过Basketball Reference但没拿到目标数据，求解决方案。

from flask import Flask, jsonify
from nba_api.stats.endpoints import leaguegamefinder
from google.oauth2 import service_account
from googleapiclient.discovery import build
import pandas as pd
import requests

app = Flask(__name__)

# 加载Google服务账号凭证（替换为你的JSON密钥路径）
creds = service_account.Credentials.from_service_account_file(
    'your-service-account-key.json',
    scopes=['https://www.googleapis.com/auth/spreadsheets']
)

# 初始化Sheets API
service = build('sheets', 'v4', credentials=creds)

# 定义你的表格ID和范围
SPREADSHEET_ID = 'your-spreadsheet-id'
RANGE_NAME = 'planilhanba!A2:ZZZ'

@app.route('/api/jogos/all_teams', methods=['GET'])
def obter_jogos_todos_times():
    # 获取2024-25赛季所有球队的赛事数据
    gamefinder = leaguegamefinder.LeagueGameFinder(
        season_nullable='2024-25'
    )
    games = gamefinder.get_data_frames()[0]

    # 选择需要的基础数据列
    columns_to_include = [
        "TEAM_ID", "TEAM_NAME", "GAME_ID", "GAME_DATE", "MATCHUP", "WL",
        "PTS", "REB", "OREB", "DREB", "AST", "STL", "BLK", "TOV", "PF",
        "PLUS_MINUS", "FGM", "FGA", "FG_PCT", "FG3M", "FG3A", "FG3_PCT",
        "FTM", "FTA", "FT_PCT", "MIN", "TEAM_ABBREVIATION", "SEASON_ID"
    ]
    games = games[columns_to_include]

    # 去重
    games = games.drop_duplicates()

    # 转换数值列类型
    numeric_columns = [
        "PTS", "REB", "OREB", "DREB", "AST", "STL", "BLK", "TOV", "PF",
        "PLUS_MINUS", "FGM", "FGA", "FG3M", "FG3A", "FTM", "FTA", "MIN"
    ]
    for col in numeric_columns:
        games[col] = pd.to_numeric(games[col], errors='coerce')

    # 区分主客场得分
    games['HOME_PTS'] = games.apply(lambda x: x['PTS'] if 'vs' in x['MATCHUP'] else 0, axis=1)
    games['AWAY_PTS'] = games.apply(lambda x: x['PTS'] if 'vs' not in x['MATCHUP'] else 0, axis=1)

    # 按比赛ID分组合并主客场数据
    games_grouped = games.groupby('GAME_ID').agg({
        'GAME_DATE': 'first',
        'MATCHUP': 'first',
        'WL': 'first',
        'HOME_PTS': 'sum',
        'AWAY_PTS': 'sum',
        'PTS': 'sum',
        'PLUS_MINUS': 'sum',
        'FGM': 'sum',
        'FGA': 'sum',
        'FG_PCT': 'mean',
        'FG3M': 'sum',
        'FG3A': 'sum',
        'FG3_PCT': 'mean',
        'FTM': 'sum',
        'FTA': 'sum',
        'FT_PCT': 'mean',
        'SEASON_ID': 'first'
    }).reset_index()

    # 替换空值以适配Sheets
    games_grouped = games_grouped.fillna('')

    # 转换为Sheets需要的格式
    values = games_grouped.to_dict(orient="records")
    body = {'values': [list(item.values()) for item in values]}

    try:
        service.spreadsheets().values().update(
            spreadsheetId=SPREADSHEET_ID,
            range=RANGE_NAME,
            valueInputOption='RAW',
            body=body
        ).execute()
    except Exception as e:
        return jsonify({"error": str(e)}), 400

    return jsonify(values)

def get_team_ratings():
    # 原Basketball Reference抓取逻辑，仅获取基础战绩
    url = 'https://www.basketball-reference.com/leagues/NBA_2025.html'
    response = requests.get(url)
    
    tables = pd.read_html(response.text)

    if len(tables) < 2:
        return pd.DataFrame()

    eastern_conference = tables[0]
    western_conference = tables[1]

    eastern_conference.columns = ['TEAM_NAME', 'W', 'L', 'W/L%', 'GB', 'PS/G', 'PA/G', 'SRS']
    eastern_conference['CONFERENCE'] = 'Eastern'

    western_conference.columns = ['TEAM_NAME', 'W', 'L', 'W/L%', 'GB', 'PS/G', 'PA/G', 'SRS']
    western_conference['CONFERENCE'] = 'Western'

    combined_stats = pd.concat([eastern_conference, western_conference], ignore_index=True)
    combined_stats = combined_stats[['TEAM_NAME', 'W', 'L', 'W/L%', 'PS/G', 'PA/G', 'CONFERENCE']]
    combined_stats = combined_stats.fillna('')

    return combined_stats

@app.route('/api/team_ratings', methods=['GET'])
def obter_team_ratings():
    team_ratings = get_team_ratings()

    if team_ratings.empty:
        return jsonify({"error": "No team ratings found."}), 404

    values = team_ratings.to_dict(orient="records")
    body = {'values': [list(item.values()) for item in values]}

    try:
        service.spreadsheets().values().update(
            spreadsheetId=SPREADSHEET_ID,
            range='planilhanba!S2:DDD',
            valueInputOption='RAW',
            body=body
        ).execute()
    except Exception as e:
        return jsonify({"error": str(e)}), 400

    return jsonify(values)

if __name__ == '__main__':
    app.run(debug=True)

解决方案 #

方案1：用nba_api直接获取高阶数据（推荐） #

nba_api提供了TeamStatsDashboard端点，可以直接获取官方计算的高阶数据，不用手动统计。在现有代码中添加以下内容：

首先导入需要的端点：

from nba_api.stats.endpoints import teamstatsdashboard

然后新增获取高阶数据的函数和路由：

def get_advanced_team_stats():
    # 获取2024-25赛季所有球队的高阶数据
    dashboard = teamstatsdashboard.TeamStatsDashboard(
        season='2024-25',
        season_type_all_star='Regular Season'
    )
    advanced_stats = dashboard.get_data_frames()[2]  # 索引2对应高阶数据表格

    # 筛选需要的高阶数据列
    required_columns = [
        'TEAM_NAME', 'OFF_RATING', 'DEF_RATING', 'NET_RATING', 'PACE'
    ]
    advanced_stats = advanced_stats[required_columns]

    # 重命名列名适配需求
    advanced_stats.rename(columns={
        'OFF_RATING': '进攻效率',
        'DEF_RATING': '防守效率',
        'NET_RATING': '净效率',
        'PACE': '节奏'
    }, inplace=True)

    # 替换空值
    advanced_stats = advanced_stats.fillna('')
    return advanced_stats

@app.route('/api/advanced_team_stats', methods=['GET'])
def obter_advanced_team_stats():
    advanced_stats = get_advanced_team_stats()

    if advanced_stats.empty:
        return jsonify({"error": "No advanced stats found."}), 404

    values = advanced_stats.to_dict(orient="records")
    body = {'values': [list(item.values()) for item in values]}

    try:
        service.spreadsheets().values().update(
            spreadsheetId=SPREADSHEET_ID,
            range='planilhanba!AA2:ZZ',  # 替换成你要写入的Sheets范围
            valueInputOption='RAW',
            body=body
        ).execute()
    except Exception as e:
        return jsonify({"error": str(e)}), 400

    return jsonify(values)

方案2：修正Basketball Reference的抓取逻辑 #

之前的代码只抓取了基础战绩表，高阶数据在页面的Advanced表格里，修改get_team_ratings函数：

def get_team_ratings():
    url = 'https://www.basketball-reference.com/leagues/NBA_2025.html'
    response = requests.get(url)
    
    tables = pd.read_html(response.text)

    # 索引4对应Advanced Stats表格（页面上的第5个表格）
    if len(tables) < 5:
        return pd.DataFrame()

    advanced_table = tables[4]
    # 清理复合表头
    advanced_table.columns = ['_'.join(col).strip() for col in advanced_table.columns.values]
    # 筛选需要的列
    required_columns = [
        'Team', 'OffRtg', 'DefRtg', 'NetRtg', 'Pace'
    ]
    advanced_stats = advanced_table[required_columns]

    # 重命名列并添加分区信息（可选）
    advanced_stats.rename(columns={
        'Team': 'TEAM_NAME',
        'OffRtg': '进攻效率',
        'DefRtg': '防守效率',
        'NetRtg': '净效率',
        'Pace': '节奏'
    }, inplace=True)

    # 替换空值
    advanced_stats = advanced_stats.fillna('')
    return advanced_stats

内容的提问来源于stack exchange，提问作者thiago gabriel