伤病停赛影响数据对比实用脚本
以下提供几个实用脚本,涵盖不同维度的伤病影响分析：
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)
使用建议：
- 数据准备：确保数据包含球员伤病起止日期、每场比赛日期和结果
- 样本量：至少需要受伤前后各10场以上比赛才有统计意义
- 对比维度：可对比同一球员受伤前后、不同球员/位置的差异、主客场差异等
- 进阶方向：可加入对手强度、背靠背比赛、客场等因素进行多元回归分析
- 可视化：推荐使用柱状图+误差条、箱线图展示分布差异
如果你需要针对特定数据格式或特定联赛（NBA、英超、CBA等）的定制化脚本，可以告诉我具体需求，我再给你调整。
