[Football Analysis]05.선수별 경기기록 집계(1)

 

작성일자 : 2023-09-30
Ver 0.1.1

경기 데이터 불러오기

import numpy as np
import pandas as pd
from tqdm import tqdm
import os

pd.set_option('display.max_columns', 40) #최대로 보여지는 칼럼수 지정

current_dir = os.getcwd()
current_dir

'/Users/limjongjun/Desktop/JayJay/Growth/Python/soccer-analytics/Excercise'

new_dir = '/Users/limjongjun/Desktop/JayJay/Growth/Python/soccer-analytics'
os.chdir(new_dir)
print(current_dir)

/Users/limjongjun/Desktop/JayJay/Growth/Python/soccer-analytics/Excercise

match_id = 2499895
match_events = pd.read_pickle(f'data/refined_events/England/2499895.pkl') #pkl 파일을 Dataframe으로 불러오기
match_events

	match_id	event_id	period	time	team_id	team_name	player_id	player_name	event_type	sub_event_type	tags	start_x	start_y	end_x	end_y
0	2499895	215108367	1H	1.784	1625	Manchester City	8325	S. Agüero	Pass	Simple pass	[Accurate]	52.00	34.68	40.56	34.68
1	2499895	215108368	1H	3.324	1625	Manchester City	105339	Fernandinho	Pass	Simple pass	[Accurate]	40.56	34.68	29.12	8.16
2	2499895	215108369	1H	6.406	1625	Manchester City	8277	K. Walker	Pass	Simple pass	[Accurate]	29.12	8.16	44.72	10.20
3	2499895	215108370	1H	7.124	1625	Manchester City	38021	K. De Bruyne	Pass	Simple pass	[Accurate]	44.72	10.20	69.68	6.12
4	2499895	215108371	1H	8.676	1625	Manchester City	11066	R. Sterling	Pass	Simple pass	[Accurate]	69.68	6.12	58.24	12.24
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
1607	2499895	215110122	2H	2875.703	1624	Tottenham Hotspur	210044	E. Dier	Pass	Simple pass	[Accurate]	36.40	26.52	48.88	29.92
1608	2499895	215110123	2H	2876.142	1624	Tottenham Hotspur	240070	H. Winks	Duel	Ground attacking duel	[Anticipation, Lost, Not accurate]	48.88	29.92	43.68	24.48
1609	2499895	215109959	2H	2876.768	1625	Manchester City	447205	P. Foden	Duel	Ground defending duel	[Anticipated, Won, Accurate]	55.12	38.08	60.32	43.52
1610	2499895	215109960	2H	2878.046	1625	Manchester City	11066	R. Sterling	Duel	Ground attacking duel	[Free space right, Lost, Not accurate]	60.32	43.52	60.32	43.52
1611	2499895	215110124	2H	2878.216	1624	Tottenham Hotspur	240070	H. Winks	Duel	Ground defending duel	[Free space left, Lost, Not accurate]	43.68	24.48	NaN	NaN

1612 rows × 15 columns

경기 내 선수별 기록 집계

(1) 이벤트 유형을 기준으로 추출되는 기록의 집계

#슈팅 횟수

shot_records = match_events[
    (match_events['event_type'] == 'Shot') |
    (match_events['sub_event_type'].isin(['Free kick shot', 'Penalty']))
]

shots = shot_records.groupby(['team_id', 'team_name', 'player_id', 'player_name'])['event_id'].count()
shots.name = 'total_shots'
shots

team_id  team_name          player_id  player_name   
1624     Tottenham Hotspur  48         J. Vertonghen     1
                            54         C. Eriksen        1
                            8292       D. Rose           1
                            8717       H. Kane           2
                            14911      Son Heung-Min     1
1625     Manchester City    8325       S. Agüero         5
                            11066      R. Sterling       6
                            14808      İ. Gündoğan       3
                            38021      K. De Bruyne      2
                            245364     L. Sané           2
                            265673     Bernardo Silva    1
                            340386     Gabriel Jesus     1
Name: total_shots, dtype: int64

#패스 횟수

pass_records = match_events[
    (match_events['event_type'] == 'Pass') |
    (match_events['sub_event_type'].isin(['Free kick', 'Free kick cross', 'corner']))
]

passes = pass_records.groupby(['team_id', 'team_name', 'player_id', 'player_name'])['event_id'].count()
passes.name = 'total_passes'
passes

team_id  team_name          player_id  player_name   
1624     Tottenham Hotspur  48         J. Vertonghen     54
                            54         C. Eriksen        34
                            8292       D. Rose           45
                            8717       H. Kane           15
                            8945       K. Trippier       46
                            11152      M. Dembélé        32
                            13484      D. Alli           26
                            14911      Son Heung-Min     17
                            20441      E. Lamela          4
                            25381      H. Lloris         32
                            25804      M. Sissoko         2
                            210044     E. Dier           51
                            240070     H. Winks          32
1625     Manchester City    8277       K. Walker         54
                            8325       S. Agüero         13
                            8464       F. Delph          54
                            11066      R. Sterling       35
                            14808      İ. Gündoğan       39
                            38021      K. De Bruyne      49
                            70085      E. Mangala        36
                            70086      N. Otamendi       41
                            71654      Ederson           31
                            105339     Fernandinho       36
                            245364     L. Sané           29
                            265673     Bernardo Silva     5
                            340386     Gabriel Jesus      8
                            447205     P. Foden           8
Name: total_passes, dtype: int64

#파울 횟수

foul_records = match_events[match_events['event_type'] == 'Foul']
fouls = foul_records.groupby(['team_id', 'team_name', 'player_id', 'player_name'])['event_id'].count()
fouls.name = 'fouls'
fouls

team_id  team_name          player_id  player_name  
1624     Tottenham Hotspur  48         J. Vertonghen    2
                            54         C. Eriksen       1
                            8292       D. Rose          3
                            8717       H. Kane          2
                            8945       K. Trippier      1
                            11152      M. Dembélé       5
                            13484      D. Alli          3
                            20441      E. Lamela        2
                            25804      M. Sissoko       1
1625     Manchester City    8464       F. Delph         2
                            38021      K. De Bruyne     3
                            70086      N. Otamendi      1
                            105339     Fernandinho      5
                            245364     L. Sané          1
                            447205     P. Foden         1
Name: fouls, dtype: int64

#오프사이드 횟수

offside_records = match_events[match_events['event_type'] == 'Offside']
offsides = offside_records.groupby(['team_id', 'team_name', 'player_id', 'player_name'])['event_id'].count()
offsides.name = 'offsides'
offsides

team_id  team_name          player_id  player_name  
1624     Tottenham Hotspur  8717       H. Kane          1
                            14911      Son Heung-Min    1
1625     Manchester City    8325       S. Agüero        2
                            11066      R. Sterling      1
                            340386     Gabriel Jesus    1
Name: offsides, dtype: int64

(2) 태그 정보를 기준으로 추출되는 기록의 집계

# 유효슈팅 횟수

acc_shot_records = shot_records[shot_records['tags'].apply(lambda x : 'Accurate' in x)]
acc_shots = acc_shot_records.groupby(['team_id', 'team_name','player_id','player_name'])['event_id'].count()
acc_shots.name = 'shots_on_target'
acc_shots

team_id  team_name          player_id  player_name   
1624     Tottenham Hotspur  54         C. Eriksen        1
                            8717       H. Kane           1
1625     Manchester City    8325       S. Agüero         1
                            11066      R. Sterling       3
                            14808      İ. Gündoğan       3
                            38021      K. De Bruyne      2
                            245364     L. Sané           1
                            265673     Bernardo Silva    1
Name: shots_on_target, dtype: int64

# 신체 부위별 슈팅 횟수

rshot_records = shot_records[shot_records['tags'].apply(lambda x : 'Right foot' in x)]
rshots = rshot_records.groupby(['team_id', 'team_name','player_id','player_name'])['event_id'].count()
rshots.name = 'rfoot_shots'

lshot_records = shot_records[shot_records['tags'].apply(lambda x : 'Left foot' in x)]
lshots = lshot_records.groupby(['team_id', 'team_name','player_id','player_name'])['event_id'].count()
lshots.name = 'lfoot_shots'

hshot_records = shot_records[shot_records['tags'].apply(lambda x : 'Head/body' in x)]
hshots = hshot_records.groupby(['team_id', 'team_name','player_id','player_name'])['event_id'].count()
hshots.name = 'hfoot_shots'

shot_stats_list = [shots, acc_shots, rshots, lshots, hshots]
shot_stats = pd.concat(shot_stats_list, axis =1).fillna(0).astype(int)
shot_stats

				total_shots	shots_on_target	rfoot_shots	lfoot_shots	hfoot_shots
team_id	team_name	player_id	player_name
1624	Tottenham Hotspur	48	J. Vertonghen	1	0	1	0	0
		54	C. Eriksen	1	1	0	1	0
		8292	D. Rose	1	0	0	1	0
		8717	H. Kane	2	1	2	0	0
		14911	Son Heung-Min	1	0	0	1	0
1625	Manchester City	8325	S. Agüero	5	1	1	3	1
		11066	R. Sterling	6	3	2	4	0
		14808	İ. Gündoğan	3	3	2	0	1
		38021	K. De Bruyne	2	2	0	2	0
		245364	L. Sané	2	1	1	1	0
		265673	Bernardo Silva	1	1	0	1	0
		340386	Gabriel Jesus	1	0	1	0	0

# 득점,도움, 자책골 횟수

goal_records = match_events[match_events['tags'].apply(lambda x : 'Goal' in x)]
goals = rshot_records.groupby(['team_id', 'team_name','player_id','player_name'])['event_id'].count()
goals.name = 'goals'

assist_records = match_events[match_events['tags'].apply(lambda x : 'Assist' in x)]
assists = assist_records.groupby(['team_id', 'team_name','player_id','player_name'])['event_id'].count()
assists.name = 'assists'

own_goal_records = match_events[match_events['tags'].apply(lambda x : 'Own goal' in x)]
own_goals = own_goal_records.groupby(['team_id', 'team_name','player_id','player_name'])['event_id'].count()
own_goals.name = 'own_goals'

goal_stats_list = [goals, assists, own_goals]
goal_stats = pd.concat(goal_stats_list, axis =1).fillna(0).astype(int)
goal_stats

				goals	assists	own_goals
team_id	team_name	player_id	player_name
1624	Tottenham Hotspur	48	J. Vertonghen	1	0	0
		8717	H. Kane	2	0	0
1625	Manchester City	8325	S. Agüero	1	0	0
		11066	R. Sterling	2	0	0
		14808	İ. Gündoğan	2	0	0
		245364	L. Sané	1	2	0
		340386	Gabriel Jesus	1	0	0
		265673	Bernardo Silva	0	1	0

# 성공한 패스 횟수 및 패스 성공률

acc_pass_records = pass_records[pass_records['tags'].apply(lambda x : 'Accurate' in x)]
acc_passes = acc_pass_records.groupby(['team_id', 'team_name','player_id','player_name'])['event_id'].count()
acc_passes.name = 'acc_passes'

pass_stats = pd.concat([passes, acc_passes], axis =1).fillna(0).astype(int)
pass_stats['pass_accuracy'] = (pass_stats['acc_passes'] / pass_stats['total_passes']).round(2)
pass_stats

				total_passes	acc_passes	pass_accuracy
team_id	team_name	player_id	player_name
1624	Tottenham Hotspur	48	J. Vertonghen	54	47	0.87
		54	C. Eriksen	34	24	0.71
		8292	D. Rose	45	33	0.73
		8717	H. Kane	15	9	0.60
		8945	K. Trippier	46	35	0.76
		11152	M. Dembélé	32	28	0.88
		13484	D. Alli	26	17	0.65
		14911	Son Heung-Min	17	14	0.82
		20441	E. Lamela	4	2	0.50
		25381	H. Lloris	32	28	0.88
		25804	M. Sissoko	2	1	0.50
		210044	E. Dier	51	49	0.96
		240070	H. Winks	32	31	0.97
1625	Manchester City	8277	K. Walker	54	46	0.85
		8325	S. Agüero	13	11	0.85
		8464	F. Delph	54	46	0.85
		11066	R. Sterling	35	32	0.91
		14808	İ. Gündoğan	39	35	0.90
		38021	K. De Bruyne	49	38	0.78
		70085	E. Mangala	36	30	0.83
		70086	N. Otamendi	41	37	0.90
		71654	Ederson	31	25	0.81
		105339	Fernandinho	36	31	0.86
		245364	L. Sané	29	22	0.76
		265673	Bernardo Silva	5	3	0.60
		340386	Gabriel Jesus	8	6	0.75
		447205	P. Foden	8	7	0.88

# 경고, 퇴장 횟수

yellow_records = foul_records[foul_records['tags'].apply(lambda x : 'Yellow card' in x)]
yellows = yellow_records.groupby(['team_id', 'team_name','player_id','player_name'])['event_id'].count()
yellows.name = 'yellow_cards'

red_records = foul_records[foul_records['tags'].apply(lambda x : 'Red card' in x)]
reds = red_records.groupby(['team_id', 'team_name','player_id','player_name'])['event_id'].count()
reds.name = 'red_cards'

foul_stats = pd.concat([fouls, offsides, yellows, reds], axis = 1).fillna(0).astype(int)
foul_stats

				fouls	offsides	yellow_cards	red_cards
team_id	team_name	player_id	player_name
1624	Tottenham Hotspur	48	J. Vertonghen	2	0	0	0
		54	C. Eriksen	1	0	0	0
		8292	D. Rose	3	0	0	0
		8717	H. Kane	2	1	1	0
		8945	K. Trippier	1	0	0	0
		11152	M. Dembélé	5	0	1	0
		13484	D. Alli	3	0	1	0
		20441	E. Lamela	2	0	0	0
		25804	M. Sissoko	1	0	1	0
1625	Manchester City	8464	F. Delph	2	0	1	0
		38021	K. De Bruyne	3	0	0	0
		70086	N. Otamendi	1	0	1	0
		105339	Fernandinho	5	0	0	0
		245364	L. Sané	1	0	0	0
		447205	P. Foden	1	0	0	0
1624	Tottenham Hotspur	14911	Son Heung-Min	0	1	0	0
1625	Manchester City	8325	S. Agüero	0	2	0	0
		11066	R. Sterling	0	1	0	0
		340386	Gabriel Jesus	0	1	0	0

(3) 선수별 통계 정리

player_stats = pd.concat([goal_stats, shot_stats, foul_stats, pass_stats], axis =1, sort = True)
player_stats

				goals	assists	own_goals	total_shots	shots_on_target	rfoot_shots	lfoot_shots	hfoot_shots	fouls	offsides	yellow_cards	red_cards	total_passes	acc_passes	pass_accuracy
team_id	team_name	player_id	player_name
1624	Tottenham Hotspur	48	J. Vertonghen	1.0	0.0	0.0	1.0	0.0	1.0	0.0	0.0	2.0	0.0	0.0	0.0	54	47	0.87
		54	C. Eriksen	NaN	NaN	NaN	1.0	1.0	0.0	1.0	0.0	1.0	0.0	0.0	0.0	34	24	0.71
		8292	D. Rose	NaN	NaN	NaN	1.0	0.0	0.0	1.0	0.0	3.0	0.0	0.0	0.0	45	33	0.73
		8717	H. Kane	2.0	0.0	0.0	2.0	1.0	2.0	0.0	0.0	2.0	1.0	1.0	0.0	15	9	0.60
		8945	K. Trippier	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	1.0	0.0	0.0	0.0	46	35	0.76
		11152	M. Dembélé	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	5.0	0.0	1.0	0.0	32	28	0.88
		13484	D. Alli	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	3.0	0.0	1.0	0.0	26	17	0.65
		14911	Son Heung-Min	NaN	NaN	NaN	1.0	0.0	0.0	1.0	0.0	0.0	1.0	0.0	0.0	17	14	0.82
		20441	E. Lamela	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	2.0	0.0	0.0	0.0	4	2	0.50
		25381	H. Lloris	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	32	28	0.88
		25804	M. Sissoko	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	1.0	0.0	1.0	0.0	2	1	0.50
		210044	E. Dier	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	51	49	0.96
		240070	H. Winks	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	32	31	0.97
1625	Manchester City	8277	K. Walker	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	54	46	0.85
		8325	S. Agüero	1.0	0.0	0.0	5.0	1.0	1.0	3.0	1.0	0.0	2.0	0.0	0.0	13	11	0.85
		8464	F. Delph	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	2.0	0.0	1.0	0.0	54	46	0.85
		11066	R. Sterling	2.0	0.0	0.0	6.0	3.0	2.0	4.0	0.0	0.0	1.0	0.0	0.0	35	32	0.91
		14808	İ. Gündoğan	2.0	0.0	0.0	3.0	3.0	2.0	0.0	1.0	NaN	NaN	NaN	NaN	39	35	0.90
		38021	K. De Bruyne	NaN	NaN	NaN	2.0	2.0	0.0	2.0	0.0	3.0	0.0	0.0	0.0	49	38	0.78
		70085	E. Mangala	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	36	30	0.83
		70086	N. Otamendi	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	1.0	0.0	1.0	0.0	41	37	0.90
		71654	Ederson	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	31	25	0.81
		105339	Fernandinho	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	5.0	0.0	0.0	0.0	36	31	0.86
		245364	L. Sané	1.0	2.0	0.0	2.0	1.0	1.0	1.0	0.0	1.0	0.0	0.0	0.0	29	22	0.76
		265673	Bernardo Silva	0.0	1.0	0.0	1.0	1.0	0.0	1.0	0.0	NaN	NaN	NaN	NaN	5	3	0.60
		340386	Gabriel Jesus	1.0	0.0	0.0	1.0	0.0	1.0	0.0	0.0	0.0	1.0	0.0	0.0	8	6	0.75
		447205	P. Foden	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	1.0	0.0	0.0	0.0	8	7	0.88

player_stats = player_stats.fillna(0).reset_index()

for col in player_stats.columns[4:]:
    if col != 'pass_accuracy' :
        player_stats[col] = player_stats[col].astype(int)

player_stats

	team_id	team_name	player_id	player_name	goals	assists	own_goals	total_shots	shots_on_target	rfoot_shots	lfoot_shots	hfoot_shots	fouls	offsides	yellow_cards	red_cards	total_passes	acc_passes	pass_accuracy
0	1624	Tottenham Hotspur	48	J. Vertonghen	1	0	0	1	0	1	0	0	2	0	0	0	54	47	0.87
1	1624	Tottenham Hotspur	54	C. Eriksen	0	0	0	1	1	0	1	0	1	0	0	0	34	24	0.71
2	1624	Tottenham Hotspur	8292	D. Rose	0	0	0	1	0	0	1	0	3	0	0	0	45	33	0.73
3	1624	Tottenham Hotspur	8717	H. Kane	2	0	0	2	1	2	0	0	2	1	1	0	15	9	0.60
4	1624	Tottenham Hotspur	8945	K. Trippier	0	0	0	0	0	0	0	0	1	0	0	0	46	35	0.76
5	1624	Tottenham Hotspur	11152	M. Dembélé	0	0	0	0	0	0	0	0	5	0	1	0	32	28	0.88
6	1624	Tottenham Hotspur	13484	D. Alli	0	0	0	0	0	0	0	0	3	0	1	0	26	17	0.65
7	1624	Tottenham Hotspur	14911	Son Heung-Min	0	0	0	1	0	0	1	0	0	1	0	0	17	14	0.82
8	1624	Tottenham Hotspur	20441	E. Lamela	0	0	0	0	0	0	0	0	2	0	0	0	4	2	0.50
9	1624	Tottenham Hotspur	25381	H. Lloris	0	0	0	0	0	0	0	0	0	0	0	0	32	28	0.88
10	1624	Tottenham Hotspur	25804	M. Sissoko	0	0	0	0	0	0	0	0	1	0	1	0	2	1	0.50
11	1624	Tottenham Hotspur	210044	E. Dier	0	0	0	0	0	0	0	0	0	0	0	0	51	49	0.96
12	1624	Tottenham Hotspur	240070	H. Winks	0	0	0	0	0	0	0	0	0	0	0	0	32	31	0.97
13	1625	Manchester City	8277	K. Walker	0	0	0	0	0	0	0	0	0	0	0	0	54	46	0.85
14	1625	Manchester City	8325	S. Agüero	1	0	0	5	1	1	3	1	0	2	0	0	13	11	0.85
15	1625	Manchester City	8464	F. Delph	0	0	0	0	0	0	0	0	2	0	1	0	54	46	0.85
16	1625	Manchester City	11066	R. Sterling	2	0	0	6	3	2	4	0	0	1	0	0	35	32	0.91
17	1625	Manchester City	14808	İ. Gündoğan	2	0	0	3	3	2	0	1	0	0	0	0	39	35	0.90
18	1625	Manchester City	38021	K. De Bruyne	0	0	0	2	2	0	2	0	3	0	0	0	49	38	0.78
19	1625	Manchester City	70085	E. Mangala	0	0	0	0	0	0	0	0	0	0	0	0	36	30	0.83
20	1625	Manchester City	70086	N. Otamendi	0	0	0	0	0	0	0	0	1	0	1	0	41	37	0.90
21	1625	Manchester City	71654	Ederson	0	0	0	0	0	0	0	0	0	0	0	0	31	25	0.81
22	1625	Manchester City	105339	Fernandinho	0	0	0	0	0	0	0	0	5	0	0	0	36	31	0.86
23	1625	Manchester City	245364	L. Sané	1	2	0	2	1	1	1	0	1	0	0	0	29	22	0.76
24	1625	Manchester City	265673	Bernardo Silva	0	1	0	1	1	0	1	0	0	0	0	0	5	3	0.60
25	1625	Manchester City	340386	Gabriel Jesus	1	0	0	1	0	1	0	0	0	1	0	0	8	6	0.75
26	1625	Manchester City	447205	P. Foden	0	0	0	0	0	0	0	0	1	0	0	0	8	7	0.88