实用脚本统计伤病停赛影响数据对比？

伤病停赛影响数据对比实用脚本

以下提供几个实用脚本,涵盖不同维度的伤病影响分析：

Python脚本：伤病缺阵球员对球队胜率影响对比

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from datetime import datetime
# ========== 创建示例数据 ==========
# 假设数据结构：球员、受伤日期、复出日期、球队、球队在该场比赛的胜/负
data = {
    'player': ['LeBron James', 'KD', 'Curry', '字母哥', 'Jokic', 'Tatum', 'Luka', 'Embiid'],
    'team': ['LAL', 'BKN', 'GSW', 'MIL', 'DEN', 'BOS', 'DAL', 'PHI'],
    'injury_start': ['2024-01-15', '2024-02-01', '2024-01-20', '2024-02-10', '2024-01-05', '2024-02-15', '2024-01-25', '2024-02-20'],
    'injury_end': ['2024-02-10', '2024-02-28', '2024-02-05', '2024-03-01', '2024-01-30', '2024-03-10', '2024-02-20', '2024-03-15'],
}
df_injuries = pd.DataFrame(data)
df_injuries['injury_start'] = pd.to_datetime(df_injuries['injury_start'])
df_injuries['injury_end'] = pd.to_datetime(df_injuries['injury_end'])
# 模拟比赛数据（2024赛季）
match_dates = pd.date_range('2024-01-01', '2024-04-01', freq='2D')
match_data = []
for date in match_dates:
    for team in ['LAL', 'BKN', 'GSW', 'MIL', 'DEN', 'BOS', 'DAL', 'PHI']:
        match_data.append({
            'date': date,
            'team': team,
            'opponent': 'OPP',
            'result': np.random.choice(['W', 'L'], p=[0.55, 0.45])
        })
df_matches = pd.DataFrame(match_data)
# ========== 分析函数 ==========
def analyze_injury_impact(injury_df, match_df):
    """统计每个球员缺阵期间和复出后球队的胜率对比"""
    # 为每场比赛标记是否存在伤病球员缺阵
    match_df['has_key_injury'] = False
    for _, injury in injury_df.iterrows():
        mask = ((match_df['team'] == injury['team']) & 
                (match_df['date'] >= injury['injury_start']) & 
                (match_df['date'] <= injury['injury_end']))
        match_df.loc[mask, 'has_key_injury'] = True
    # 计算两类比赛的胜率
    stats = {
        'with_injury': {
            'games': len(match_df[match_df['has_key_injury']]),
            'wins': len(match_df[(match_df['has_key_injury']) & (match_df['result'] == 'W')])
        },
        'without_injury': {
            'games': len(match_df[~match_df['has_key_injury']]),
            'wins': len(match_df[(~match_df['has_key_injury']) & (match_df['result'] == 'W')])
        }
    }
    return stats
# ========== 执行分析 ==========
stats = analyze_injury_impact(df_injuries, df_matches)
# 计算胜率
win_rate_with = stats['with_injury']['wins'] / stats['with_injury']['games'] if stats['with_injury']['games'] > 0 else 0
win_rate_without = stats['without_injury']['wins'] / stats['without_injury']['games'] if stats['without_injury']['games'] > 0 else 0
# ========== 可视化对比 ==========
fig, axes = plt.subplots(1, 2, figsize=(12, 5))
# 胜率对比柱状图
axes[0].bar(['有核心球员缺阵', '无核心球员缺阵'], 
            [win_rate_with * 100, win_rate_without * 100],
            color=['red', 'green'], alpha=0.7)
axes[0].set_ylabel('胜率 (%)')
axes[0].set_title('核心球员缺阵 vs 全员健康胜率对比')
axes[0].set_ylim(0, 100)
# 胜场数对比
games_label = ['有伤病缺阵', '无伤病缺阵']
axes[1].bar(games_label, [stats['with_injury']['wins'], stats['without_injury']['wins']], 
            color=['orange', 'blue'], alpha=0.7)
axes[1].set_ylabel('胜场数')
axes[1].set_title('胜场数对比')
plt.tight_layout()
plt.show()
# 输出详细数据
print("="*50)
print("伤病影响分析结果")
print("="*50)
print(f"有核心球员缺阵: {stats['with_injury']['games']}场比赛, 胜率 {win_rate_with:.1%}")
print(f"无核心球员缺阵: {stats['without_injury']['games']}场比赛, 胜率 {win_rate_without:.1%}")
print(f"胜率差异: {(win_rate_with - win_rate_without)*100:.1f} 个百分点")

SQL脚本：球队赛季胜率与伤病球员数据关联分析

-- 需求表结构：teams, players, injuries, games, game_stats
-- 1. 计算每支球队有/无核心球员伤病时的胜率对比
WITH injury_periods AS (
    SELECT 
        team_id,
        player_id,
        injury_start,
        injury_end,
        DATEDIFF(injury_end, injury_start) AS days_out
    FROM injuries
    WHERE injury_end IS NOT NULL
),
games_with_injury AS (
    SELECT 
        g.team_id,
        g.game_id,
        g.result,
        MAX(CASE WHEN ip.player_id IS NOT NULL THEN 1 ELSE 0 END) AS has_injury
    FROM games g
    LEFT JOIN injury_periods ip ON g.team_id = ip.team_id 
        AND g.game_date BETWEEN ip.injury_start AND ip.injury_end
    GROUP BY g.team_id, g.game_id, g.result
)
SELECT 
    team_id,
    SUM(CASE WHEN has_injury = 1 THEN 1 ELSE 0 END) AS games_with_injury,
    SUM(CASE WHEN has_injury = 1 AND result = 'W' THEN 1 ELSE 0 END) AS wins_with_injury,
    ROUND(
        SUM(CASE WHEN has_injury = 1 AND result = 'W' THEN 1 ELSE 0 END) / 
        NULLIF(SUM(CASE WHEN has_injury = 1 THEN 1 ELSE 0 END), 0) * 100, 2
    ) AS win_rate_with_injury_pct,
    SUM(CASE WHEN has_injury = 0 THEN 1 ELSE 0 END) AS games_without_injury,
    SUM(CASE WHEN has_injury = 0 AND result = 'W' THEN 1 ELSE 0 END) AS wins_without_injury,
    ROUND(
        SUM(CASE WHEN has_injury = 0 AND result = 'W' THEN 1 ELSE 0 END) / 
        NULLIF(SUM(CASE WHEN has_injury = 0 THEN 1 ELSE 0 END), 0) * 100, 2
    ) AS win_rate_without_injury_pct
FROM games_with_injury
GROUP BY team_id
HAVING games_with_injury > 3  -- 至少3场有伤病的比赛才统计
ORDER BY win_rate_with_injury_pct - win_rate_without_injury_pct ASC;

R脚本：伤病对不同位置的球员影响

# 加载必要的包
library(dplyr)
library(ggplot2)
library(tidyr)
# 创建示例数据
set.seed(123)
n_players <- 50
players <- data.frame(
  player_id = 1:n_players,
  position = sample(c("PG", "SG", "SF", "PF", "C"), n_players, replace = TRUE),
  team = sample(LETTERS[1:10], n_players, replace = TRUE)
)
# 模拟伤病数据
injuries <- data.frame(
  injury_id = 1:80,
  player_id = sample(1:n_players, 80, replace = TRUE),
  days_out = sample(3:45, 80, replace = TRUE),
  games_missed = sample(2:20, 80, replace = TRUE),
  injury_type = sample(c("腿筋拉伤", "脚踝扭伤", "膝关节损伤", "背部痉挛", "脑震荡"), 80, replace = TRUE)
)
# 球队胜率数据
teams <- data.frame(
  team = LETTERS[1:10],
  win_rate = runif(10, 0.3, 0.7)
)
# 合并数据
injury_impact <- injuries %>%
  left_join(players, by = "player_id") %>%
  left_join(teams, by = "team")
# 分析：不同位置的缺阵场次对比
position_summary <- injury_impact %>%
  group_by(position) %>%
  summarise(
    total_days_lost = sum(days_out),
    total_games_missed = sum(games_missed),
    avg_days_per_injury = mean(days_out),
    injury_count = n()
  )
# 可视化
ggplot(position_summary, aes(x = position, y = total_games_missed, fill = position)) +
  geom_bar(stat = "identity") +
  labs(title = "不同位置球员因伤缺阵场次对比",
       x = "位置", y = "总缺阵场次") +
  theme_minimal()
# 伤病类型分布
injury_type_summary <- injury_impact %>%
  group_by(injury_type) %>%
  summarise(
    count = n(),
    avg_days_out = mean(days_out),
    avg_games_missed = mean(games_missed)
  )
print("伤病位置影响分析:")
print(position_summary)
print("\n伤病类型统计:")
print(injury_type_summary)
# 胜率相关性分析
team_impact <- injury_impact %>%
  group_by(team) %>%
  summarise(
    total_games_missed = sum(games_missed),
    avg_games_missed = mean(games_missed)
  ) %>%
  left_join(teams, by = "team")
# 相关性检验
cor_test <- cor.test(team_impact$total_games_missed, team_impact$win_rate)
print(paste0("\n伤病缺阵与球队胜率相关系数: ", round(cor_test$estimate, 3)))
print(paste0("p值: ", round(cor_test$p.value, 4)))

Excel公式版（快速模板）

假设数据排列：
A列：球员名    B列：球队    C列：受伤日期    D列：复出日期
E列：比赛日期  F列：该队在比赛中胜负    G列：该队是否有球员在伤病名单
胜率计算：
=COUNTIFS(F:F,"W",G:G,"是")/COUNTIF(G:G,"是")
=COUNTIFS(F:F,"W",G:G,"否")/COUNTIF(G:G,"否")

进阶版：考虑球员价值的加权分析（Python）

# 进阶：按照球员PER效率值加权
player_per = {'LeBron James': 24.5, 'KD': 26.3, 'Curry': 27.1, 
              '字母哥': 28.0, 'Jokic': 32.5, 'Tatum': 23.0, 
              'Luka': 27.2, 'Embiid': 29.8}
def weighted_injury_impact(injury_df, match_df, player_per):
    """考虑球员价值的伤病影响分析"""
    match_df['impact_score'] = 0.0
    for _, injury in injury_df.iterrows():
        per_value = player_per.get(injury['player'], 15.0)  # 默认低价值
        mask = ((match_df['team'] == injury['team']) & 
                (match_df['date'] >= injury['injury_start']) & 
                (match_df['date'] <= injury['injury_end']))
        match_df.loc[mask, 'impact_score'] += per_value
    # 按影响程度分组
    match_df['impact_level'] = pd.cut(match_df['impact_score'], 
                                       bins=[0, 20, 40, 100], 
                                       labels=['低影响', '中影响', '高影响'])
    # 统计不同影响等级下的胜率
    result = match_df.groupby('impact_level').apply(
        lambda x: pd.Series({
            'games': len(x),
            'wins': len(x[x['result'] == 'W']),
            'win_rate': (x['result'] == 'W').mean()
        })
    ).reset_index()
    return result
# 使用示例
weighted_result = weighted_injury_impact(df_injuries, df_matches.copy(), player_per)
print(weighted_result)

使用建议：

1. 数据准备：确保数据包含球员伤病起止日期、每场比赛日期和结果
2. 样本量：至少需要受伤前后各10场以上比赛才有统计意义
3. 对比维度：可对比同一球员受伤前后、不同球员/位置的差异、主客场差异等
4. 进阶方向：可加入对手强度、背靠背比赛、客场等因素进行多元回归分析
5. 可视化：推荐使用柱状图+误差条、箱线图展示分布差异

如果你需要针对特定数据格式或特定联赛（NBA、英超、CBA等）的定制化脚本，可以告诉我具体需求，我再给你调整。