-
Notifications
You must be signed in to change notification settings - Fork 0
/
fetch_players.py
138 lines (116 loc) · 4.89 KB
/
fetch_players.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
import sqlite3
import pandas as pd
import sys
from pprint import pprint
import soccerAnalysis1
import sqlite3
import pandas as pd
from sklearn.manifold import TSNE
from scipy.spatial import distance
from sklearn.preprocessing import StandardScaler
from sklearn.cluster import KMeans
from sklearn.cluster import DBSCAN
from sklearn import metrics
from sklearn.datasets.samples_generator import make_blobs
from sklearn.preprocessing import StandardScaler
from bokeh.plotting import figure, ColumnDataSource, show
from bokeh.models import HoverTool
import numpy as np
from pylab import plot,show
from numpy import vstack,array
from numpy.random import rand
from scipy.cluster.vq import kmeans,vq
from mpl_toolkits.mplot3d import Axes3D
from bokeh.io import output_notebook
import matplotlib.pyplot as plt
import seaborn as sns
database = './data/database.sqlite'
conn = sqlite3.connect(database)
cur = conn.cursor()
teams = []
players = {}
# 1 : attack
# 2 : defense
# 3 : midfield
player_skills = {
0: 'finishing, dribbling, volleys, heading_accuracy, curve, acceleration, sprint_speed, free_kick_accuracy, ball_control, short_passing, penalties, shot_power, balance, jumping',
2: 'vision, ball_control, short_passing, long_passing, long_shots, marking, penalties, potential, aggression, sliding_tackle, standing_tackle',
3: 'marking, standing_tackle, sliding_tackle, short_passing, long_passing, crossing, jumping, stamina, strength, long_shots, aggression, interceptions, positioning'
}
skill_count = {
0: 14,
2: 11,
3: 13
}
def get_field_position(player_id):
if player_id % 3 == 0:
return 0
elif player_id % 3 == 1:
return 2
else:
return 3
def getYoungerPlayer(team_id, season):
cur.execute("SELECT DISTINCT PT.player_api_id, PT.team_api_id from PlayerTeamMod PT, Player P where PT.team_api_id = " + str(team_id) + " AND PT.season = " + str("'" + season + "'") +" AND " + "(SELECT (strftime('%Y', 'now') - strftime('%Y', birthday)) - (strftime('%m-%d', 'now') < strftime('%m-%d', birthday)) from Player where player_api_id = PT.player_api_id) < 21" + " order by PT.team_api_id;")
allrows = cur.fetchall()
print(allrows)
def getOldestPlayer(team_id, season):
cur.execute("SELECT DISTINCT PT.player_api_id, PT.team_api_id from PlayerTeamMod PT, Player P where PT.team_api_id = " + str(team_id) + " AND PT.season = " + str("'" + season + "'") +" AND " + "(SELECT (strftime('%Y', 'now') - strftime('%Y', birthday)) - (strftime('%m-%d', 'now') < strftime('%m-%d', birthday)) from Player where player_api_id = PT.player_api_id) > 34" + " order by PT.team_api_id;")
allrows = cur.fetchall()
print(allrows)
def get_player_stats(player_id, field_pos):
result = cur.execute( "Select " + player_skills[field_pos] + " from Player_Attributes where player_api_id =" + str(
player_id) + " and date between ('2013-07-01') and ('2014-06-30') order by date desc LIMIT 1;")
player_stat = 0
for row in result:
for skill in row:
player_stat += int(skill)
return player_stat/skill_count[field_pos]
def get_weak_player(team):
result = cur.execute("SELECT player_api_id from PlayerTeamMod where team_api_id = " + str(team) + " AND season = '2013/2014' order by team_api_id;")
player_list = {}
for row in result:
player_list[row[0]] = []
team_attack = 0
team_defense = 0
team_midfield = 0
team_attack_count = 0
team_defense_count = 0
team_midfield_count = 0
for player_id in player_list:
field_pos = get_field_position(player_id)
player_stat = get_player_stats(player_id, field_pos)
player_list[player_id] = [field_pos, player_stat]
if field_pos == 1: # attack
team_attack += player_stat
team_attack_count += 1
elif field_pos == 2: # defense
team_defense += player_stat
team_defense_count += 1
else: # midfield
team_midfield += player_stat
team_midfield_count += 1
team_attack /= team_attack_count
team_defense /= team_defense_count
team_midfield /= team_midfield_count
if team_attack > team_midfield:
min_field = 3
min = team_midfield
else:
min_field = 1
min = team_attack
if team_defense < min:
min_field = 2
min_stat = 100
for player_id in player_list:
if player_list[player_id][0] == min_field and min_stat > player_list[player_id][1]:
min_stat = player_list[player_id][1]
min_player = player_id
print (str(team) + " : " +str(min_player))
return (min_player, player_list[min_player][0])
def main():
result = cur.execute("SELECT DISTINCT team_api_id from PlayerTeamMod;")
for team in result:
teams.append(team[0])
for team in teams:
get_weak_player(team)
main()