def __init__(self, dataOptions, typeOptions):
# data accepting
(self.replayIDs, self.rTeamIDs, self.rPlayerIDs) = dataOptions
for x in typeOptions[0]:
self.plotBy = x
self.plotByData = typeOptions[0][x]
self.plotOvertime, _, _, _, self.plotBall = typeOptions[1]
session = db.Session()
self.replays = {} # id:frames
if self.replayIDs:
results = session.query(db.Replay).filter(
db.Replay.id.in_(self.replayIDs)).all()
for replay in results:
frames = misc.get_game_frames(self, replay)
self.replays[replay.id] = frames
# print(frames)
output = ''
if self.rPlayerIDs:
for rPlayerID in self.rPlayerIDs:
aPlayer = APlayer(rPlayerID, self.replays, self.plotBy,
self.plotByData, self.plotOvertime)
output += APlayer.output_all(aPlayer)
# print(output)
# self.output = ''
self.output = output
db.Session.remove()
def update_player_names(self, *args):
# startTime = time.time()
gamesSelected = self.gameBox.curselection()
teamsSelected = self.teamBox.curselection()
if teamsSelected:
session = db.Session()
list_of_players = ()
rplayerIDs = []
teamsSelectedNames = [self.curatedRTeamIDs[x]
for x in teamsSelected]
gamesSelectedID = [self.allGameIDs[x] for x in gamesSelected]
rplayers = session.query(db.RPlayer).join(db.Player).join(db.RTeam).join(db.Replay).filter(
db.Replay.id.in_(gamesSelectedID)).filter(db.RTeam.id.in_(teamsSelectedNames)).all()
for rplayer in rplayers:
if rplayer.name not in list_of_players:
list_of_players += (rplayer.name,)
rplayerIDs.append(rplayer.id)
self.curatedPlayerNames.set(list_of_players)
self.curatedRPlayerIDs = rplayerIDs
db.Session.remove()
else:
self.curatedPlayerNames.set(())
self.curatedRPlayerIDs = []
def __init__(self, gameid):
session = db.Session()
replay = session.query(
db.Replay).filter(db.Replay.id == gameid).all()[0]
self.name = replay.name
self.map = replay.map
self.maxFrameNo = replay.maxFrameNo
self.teams = []
self.players = []
for _team in replay.teams:
team = {'players': [], 'colour': _team.colour}
self.teams.append(team)
for _player in _team.players:
player = {
'positions': {
position.frameNo: (position.x, position.y, position.z)
for position in _player.positions
},
'rotations': {
rotation.frameNo:
(rotation.pitch, rotation.yaw, rotation.roll)
for rotation in _player.positions
},
'name': _player.name,
}
team['players'].append(player)
self.players.append(player)
print(player['name'])
# self.ball = {'positions': {position.frameNo:(position.x,position.y,position.z) for position in replay.ball.ballframes}}
self.ball = {
'positions': [(position.x, position.y, position.z)
for position in replay.ball.ballframes]
}
self.ball['positions'] = np.array(self.ball['positions'])
print(type(self.ball))
hits = session.query(db.Hit, db.RPlayer.name).filter(
db.Hit.replay_id == gameid).join(db.Player).join(db.RPlayer).all()
self.hits = []
for _hit in hits:
hit = _hit[0].__dict__
hit['player'] = _hit[1]
self.hits.append(hit)
frames = session.query(
db.Frame).filter(db.Frame.replay_id == gameid).all()
# print(frames)
self.frames = {}
for frame in frames:
self.frames[frame.frameNo] = {
'timeRemaining': frame.timeRemaining,
'isOvertime': frame.isOvertime
}
def analyse_player_hits(self):
session = db.Session()
x = session.query(db.Hit,db.Replay.map,db.Team.colour)\
.filter(db.Hit.player_id.in_(self.playerIDs))\
.join(db.Replay)\
.join(db.Player)\
.filter(db.Player.id.in_(self.playerIDs))\
.join(db.Team)\
# print(len(x.all()))
# print(time.time()-_time)
# _time = time.time()
hits = pd.read_sql_query(
x.selectable,
db.engine
)
print(hits[:5])
positions = hits.loc[:,['hits_x','hits_y','hits_z','replays_map','teams_colour']].rename(columns={'hits_x':'positions_x','hits_y':'positions_y','hits_z':'positions_z'})
_hits = APosition.analyse_position(positions)
keyNames = ['shot','goal','pass_','dribble','save']
for item in keyNames:
_hits[item] = {}
totalHits = hits.hits_id.count()
# print(_hits)
for key in keyNames:
counts = hits['hits_'+key].value_counts()
# print(counts)
for value in counts.index:
_hits[key][value] = counts[value]
# print(_hits)
averageHitDistance = hits[hits['hits_distance']!=0]['hits_distance'].mean()
averageShotDistance = hits[(hits['hits_distance']!=0) & (hits['hits_shot']>0)]['hits_distance'].mean()
averagePassDistance = hits[(hits['hits_distance']!=0) & (hits['hits_pass_']>0)]['hits_distance'].mean()
_hits['aveHitDistance'] = averageHitDistance
_hits['aveShotDistance'] = averageShotDistance
_hits['avePassDistance'] = averagePassDistance
averageDribbleDistance, averageDribbleHits = AHit.get_average_dribble_distance(hits)
_hits['aveDribbleDistance'] = averageDribbleDistance
_hits['aveDribbleHits'] = averageDribbleHits
print(_hits)
return _hits
def analyse_player_position(self):
session = db.Session()
gameFrames = {} # replayid:[(start,end),..]
for playerID in self.playerIDs:
player = session.query(db.Player).filter(db.Player.id==playerID).one()
frames = misc.get_game_frames(self,player.replay)
gameFrames[player.replay.id] = frames
# print(self.playerIDs,playerID)
# stadium
# map = player.replay.map
# half
# a = time.time()
# x = session.query(db.Position).join(db.BallFrame,sq.and_(db.Position.frameNo==db.BallFrame.frameNo,db.Position.replay_id==db.BallFrame.replay_id)).filter(db.Position.player_id==playerID).all()
# print(x)
print(gameFrames)
for replayid in gameFrames:
totalFrames = sum((frame[1]-frame[0]) for frame in gameFrames[replayid])
print(replayid,totalFrames)
a = time.time()
# x = session.query(db.Position, db.BallFrame, db.Replay.map, db.Team.colour)\
x = session.query(db.Position.x,db.Position.y,db.Position.z, db.BallFrame.x,db.BallFrame.y,db.BallFrame.z, db.Replay.map, db.Team.colour)\
.filter(db.Position.player_id.in_(self.playerIDs))\
.filter(sq.or_(sq.and_(sq.or_(db.Position.frameNo.between(frames[0],frames[1]) for frames in gameFrames[replayid]),db.Position.replay_id==replayid) for replayid in gameFrames))\
.join(db.BallFrame,db.BallFrame.frame_id==db.Position.frame_id)\
.join(db.Replay)\
.join(db.Player)\
.filter(db.Player.id.in_(self.playerIDs))\
.join(db.Team)
# print(len(x.all()))
# print(time.time()-_time)
_time = time.time()
positions = pd.read_sql_query(
x.selectable,
db.engine
)
print('Total Frames: ',len(positions))
# print(positions[['positions_id','players_id']][:5])
# try:
# positions = pd.concat([positions,_positions])
# print(2,len(positions),len(positions.columns.values))
# except UnboundLocalError:
# positions = _positions
# print(1,len(positions),len(positions.columns.values))
# print(positions)
print('Total Duration:','{:.3}'.format(time.time()-a))
position = APosition.analyse_position(positions)
def gameMotion(self, event):
x, y = event.x, event.y
try:
gameID = self.allGameIDs[self.gameBox.nearest(y)]
except IndexError:
return
session = db.Session()
game = session.query(db.Replay).filter(db.Replay.id == gameID).one()
text = 'Map: ' + game.map + ', ' + 'Date: ' + \
game.dateTime.strftime('%Y-%m-%d %H:%M') + \
', ID: ' + str(game.replayID) + '\n'
for team in game.teams:
text += team.rteam.name + ': ' + team.colour.title() + ', '
text = text[:-2]
tip = tooltip.ToolTip(self.gameBox, text, x, y)
def update_team_names(self, *args):
# startTime = time.time()
gamesSelected = self.gameBox.curselection()
session = db.Session()
list_of_teams = ()
rteamIDs = []
for x in gamesSelected:
gameID = self.allGameIDs[x]
rteams = session.query(db.RTeam).join(
db.Replay.rteams).filter(db.Replay.id == gameID)
for rteam in rteams:
if rteam.name not in list_of_teams:
list_of_teams += (rteam.name,)
rteamIDs.append(rteam.id)
db.Session.remove()
self.curatedTeamNames.set(list_of_teams)
self.curatedRTeamIDs = rteamIDs
# print('Completed in',time.time()-startTime)
self.update_player_names()
def refresh_all_teamNames(root):
with open(os.path.join(root.rootPath, 'teams.txt'), 'rb') as f:
tmtxt = f.read()
tmtxtmd5 = hashlib.md5(tmtxt).digest()
if tmtxtmd5 != db.tmtxtmd5:
print('Updating team names and player names.')
db.TeamName.load_teams_txt(
os.path.join(root.rootPath, 'teams.txt'))
db.tmtxtmd5 = tmtxtmd5
db.Replay.refresh_all_teamNames()
session = db.Session()
replays = session.query(db.Replay.name, db.Replay.id).order_by(
db.Replay.dateTime).all()
db.Session.remove()
root.dataPicker.allGameNames.set(())
root.dataPicker.allGameIDs = []
for (replayName, replayID) in replays:
root.dataPicker.gameBox.insert('end', replayName)
root.dataPicker.allGameIDs.append(replayID)
def parse_player(self,replays):
session = db.Session()
# get RPlayer
rPlayer = session.query(db.RPlayer).filter(db.RPlayer.id==self.rPlayerID).one()
print('\nAnalysing '+rPlayer.name)
# get players:
playerIDs = session.query(db.Player.id).join(db.RPlayer).join(db.Replay).filter(db.Replay.id.in_(replays.keys())).filter(db.RPlayer.id==self.rPlayerID).all()
for (playerID,) in playerIDs:
self.playerIDs.append(playerID)
# position
# self.position = self.analyse_player_position()
# hits
self.hits = self.analyse_player_hits()
# # add player and ball objects from games
# for game in games:
# # game._framesToParse = misc.get_game_frames(self,game)
# for player in game.players:
# try:
# if player._name == self._name:
# # get position stuff
# player._framesToParse = misc.get_game_frames(self,game)
# self.playerObjects.append(player)
# self.ballObjects.append(game.ball)
# AHit.analyse_game_hits(game)
# APosition.analyse_game_possession(game)
# except AttributeError:
# print(player.name+' has no ._name')
# print(game._name)
# return
# position, speed = self.analyse_player_positions_and_speed()
# hits = self.analyse_player_hits()
possession = self.analyse_player_possession()
self.position = position
self.speed = speed
self.possession = possession
self.hits = hits
pass
def __init__(self, parent, dataOptions, typeOptions):
self.parent = parent
# data accepting
(gameids, rteamids, rplayerids) = dataOptions
for x in typeOptions[0]:
self.plotBy = x
self.plotByData = typeOptions[0][x]
self.plotOvertime, self.plotHeat, self.normaliseHeat, self.plotLine, self.plotBall = typeOptions[
1]
# player/team._name:data
subPlotRows = 1
subPlotColumns = 1
subPlotNumber = 1
session = db.Session()
if rplayerids:
# if there are players selected
noOfPlots = len(rplayerids)
subPlotRows = math.floor(math.sqrt(noOfPlots))
subPlotColumns = math.ceil(noOfPlots / subPlotRows)
print('Plotting a %sx%s grid.' % (subPlotColumns, subPlotRows))
fig = plt.figure(figsize=(subPlotColumns * 4, subPlotRows * 4))
fig.subplots_adjust(bottom=0.05, top=0.95, left=0.02, right=0.98)
# plot players separately
for rplayerid in rplayerids:
(playerName, ) = session.query(
db.RPlayer.name).filter(db.RPlayer.id == rplayerid).one()
print('Plot %s - %s' % (subPlotNumber, playerName))
ax = fig.add_subplot(subPlotRows,
subPlotColumns,
subPlotNumber,
projection='3d',
facecolor='black')
ax.set_title(playerName, color='w')
subPlotNumber += 1
positionsToPlot = []
# find players
playerIDs = session.query(db.Player.id).join(db.RPlayer).join(
db.Replay).filter(db.RPlayer.id == rplayerid).filter(
db.Replay.id.in_(gameids)).all()
playerIDs = list(x for (x, ) in playerIDs)
print('Found %s games for %s.' % (len(playerIDs), playerName))
startTime = time.time()
# print(playerIDs)
gameFrames = {} # replayid:[(start,end),..]
for playerID in playerIDs:
player = session.query(
db.Player).filter(db.Player.id == playerID).one()
frames = misc.get_game_frames(self, player.replay)
gameFrames[player.replay.id] = frames
x = session.query(db.Position, db.Replay.map, db.Team.colour)\
.filter(db.Position.player_id.in_(playerIDs))\
.filter(sq.or_(sq.and_(sq.or_(db.Position.frameNo.between(frames[0], frames[1]) for frames in gameFrames[replayid]), db.Position.replay_id == replayid) for replayid in gameFrames))\
.join(db.Replay)\
.join(db.Player)\
.filter(db.Player.id.in_(playerIDs))\
.join(db.Team)
positions = pd.read_sql_query(x.selectable, db.engine)
positions.ix[positions.teams_colour == 'orange',
'positions_y'] = positions.ix[
positions.teams_colour == 'orange',
'positions_y'] * -1
# print(positions[:5])
positionsToPlot = positions.ix[:, [
'positions_x', 'positions_y', 'positions_z'
]].values.tolist()
duration = '{:.2}'.format(time.time() - startTime)
print('Got', len(positionsToPlot), 'frames in', duration, 's')
self.create_axes(ax)
if self.plotLine:
self.plot_line(ax, positionsToPlot)
if self.plotHeat:
self.plot_heat(ax, positionsToPlot)
plt.show()
elif rteamids:
noOfPlots = len(rteamids)
subPlotRows = math.floor(math.sqrt(noOfPlots))
subPlotColumns = math.ceil(noOfPlots / subPlotRows)
print('Plotting a %sx%s grid.' % (subPlotColumns, subPlotRows))
fig = plt.figure(figsize=(subPlotColumns * 4, subPlotRows * 4))
# plot teams separately
for rteamid in rteamids:
(teamName, ) = session.query(
db.RTeam.name).filter(db.RTeam.id == rteamid).one()
print('Plot %s - %s.' % (subPlotNumber, teamName))
ax = fig.add_subplot(subPlotRows,
subPlotColumns,
subPlotNumber,
projection='3d',
facecolor='black')
ax.set_title(teamName, color='w')
subPlotNumber += 1
positionsToPlot = []
# find teams
teams = session.query(db.Team).join(db.RTeam).join(
db.Replay).filter(db.RTeam.id == rteamid).filter(
db.Replay.id.in_(gameids)).all()
print('Found %s games for %s.' % (len(teams), teamName))
startTime = time.time()
for team in teams:
replay = team.replay
frames = misc.get_game_frames(self, replay)
# print(frames)
for player in team.players:
positions = db.Position.get_positions(player, frames)
if team.colour == 'orange':
for position in positions:
positionsToPlot.append(
[position.x, -position.y, position.z])
elif team.colour == 'blue':
for position in player.positions:
positionsToPlot.append(
[position.x, position.y, position.z])
duration = '{:.2}'.format(time.time() - startTime)
print('Got %s frames in %ss.' %
(len(positionsToPlot), duration))
self.create_axes(ax)
if self.plotLine:
self.plot_line(ax, positionsToPlot)
if self.plotHeat:
self.plot_heat(ax, positionsToPlot)
# GamePlotter.allPlots.append(plt)
plt.show()
def __init__(self, parent):
parent.dataPicker = self
self.parent = parent
# import starting files
filePaths = self.import_starting_files(parent)
self.allGameFileNames = []
parent.status.set('Importing ' + str(len(filePaths)) + ' file(s).')
# parse games in filelist
self.allGameIDs = []
self.curatedRTeamIDs = []
self.curatedRPlayerIDs = []
self.allGameNames = tk.Variable()
# ADD DATA PICKER
self.dataPicker = ttk.Panedwindow(
parent.windowFrame, orient='horizontal')
gamePicker = ttk.Labelframe(
self.dataPicker, text='Game(s):', width=160, height=100)
teamPicker = ttk.Labelframe(
self.dataPicker, text='Team(s):', width=100, height=100)
playerPicker = ttk.Labelframe(
self.dataPicker, text='Player(s):', width=100, height=100)
self.dataPicker.add(gamePicker, weight=16)
self.dataPicker.add(teamPicker, weight=10)
self.dataPicker.add(playerPicker, weight=10)
self.dataPicker.grid(row=0, column=0, sticky='nesw')
parent.windowFrame.grid_rowconfigure(0, weight=1, minsize=200)
parent.windowFrame.grid_columnconfigure(0, weight=5, minsize=400)
listBoxRelief = 'flat'
# game picker
gameButtonText = tk.StringVar()
gameButtonText.set('Select All')
self.gameBox = tk.Listbox(
gamePicker,
width=35,
height=10,
listvariable=self.allGameNames,
selectmode='extended',
activestyle='dotbox',
relief=listBoxRelief,
exportselection=False)
self.gameBox.bind('<<ListboxSelect>>', self.update_team_names)
self.gameBox.bind('<Motion>', self.gameMotion)
self.gameBox.pack(fill='both', expand=True)
gameSelectButton = ttk.Button(gamePicker, textvariable=gameButtonText,
width=13, command=lambda: self.select_x(self.gameBox, gameButtonText))
gameSelectButton.pack(pady=3)
gamePicker.grid_columnconfigure(0, weight=1)
# team picker
teamButtonText = tk.StringVar()
teamButtonText.set('Select All')
self.curatedTeamNames = tk.Variable()
self.teamBox = tk.Listbox(
teamPicker,
width=16,
height=10,
listvariable=self.curatedTeamNames,
selectmode='extended',
activestyle='dotbox',
relief=listBoxRelief,
exportselection=False)
self.teamBox.bind('<<ListboxSelect>>', self.update_player_names)
self.teamBox.pack(fill='both', expand=True)
teamSelectButton = ttk.Button(teamPicker, textvariable=teamButtonText,
width=13, command=lambda: self.select_x(self.teamBox, teamButtonText))
teamSelectButton.pack(pady=3)
teamPicker.grid_columnconfigure(0, weight=1)
# player picker
playerButtonText = tk.StringVar()
playerButtonText.set('Select All')
self.curatedPlayerNames = tk.Variable()
self.playerBox = tk.Listbox(
playerPicker,
width=20,
height=10,
listvariable=self.curatedPlayerNames,
selectmode='extended',
activestyle='dotbox',
relief=listBoxRelief,
exportselection=False)
self.playerBox.pack(fill='both', expand=True)
playerSelectButton = ttk.Button(playerPicker, textvariable=playerButtonText,
width=13, command=lambda: self.select_x(self.playerBox, playerButtonText))
playerSelectButton.pack(pady=3)
playerPicker.grid_columnconfigure(0, weight=1)
# read starting database
session = db.Session()
replays = session.query(db.Replay.name, db.Replay.id).order_by(
db.Replay.dateTime).all()
db.Session.remove()
for (replayName, replayID) in replays:
self.gameBox.insert('end', replayName)
self.allGameIDs.append(replayID)
# finish importing starting files
for gamePath in tqdm.tqdm(filePaths):
match = self.parent.get_file_name(gamePath)
if match:
fileName = match.group(1)
self.add_game(gamePath, fileName)
def analyse_all_player_hits():
session = db.Session()
count = session.query(db.Player.rplayer_id, sq.sql.func.count(
'*').label('replay_count')).group_by(db.Player.rplayer_id).subquery()
count_hits = session.query(db.Hit, db.Player.rplayer_id, sq.sql.func.count(
'*').label('hits')).join(db.Player).group_by(db.Player.rplayer_id).subquery()
count_pass = session.query(db.Hit, db.Player.rplayer_id, sq.sql.func.count(
'*').label('passes')).filter(db.Hit.pass_ > 0).join(db.Player).group_by(db.Player.rplayer_id).subquery()
count_assist = session.query(db.Hit, db.Player.rplayer_id, sq.sql.func.count(
'*').label('assists')).filter(db.Hit.pass_ == 2).join(db.Player).group_by(db.Player.rplayer_id).subquery()
count_shot = session.query(db.Hit, db.Player.rplayer_id, sq.sql.func.count(
'*').label('shots')).filter(db.Hit.shot > 0).join(db.Player).group_by(db.Player.rplayer_id).subquery()
count_shot_ot = session.query(db.Hit, db.Player.rplayer_id, sq.sql.func.count(
'*').label('shots_ot')).filter(db.Hit.shot == 2).join(db.Player).group_by(db.Player.rplayer_id).subquery()
count_goal = session.query(db.Hit, db.Player.rplayer_id, sq.sql.func.count(
'*').label('goals')).filter(db.Hit.goal > 0).join(db.Player).group_by(db.Player.rplayer_id).subquery()
count_assisted_goal = session.query(db.Hit, db.Player.rplayer_id, sq.sql.func.count(
'*').label('assisted_goals')).filter(db.Hit.goal == 2).join(db.Player).group_by(db.Player.rplayer_id).subquery()
count_dribble = session.query(db.Hit, db.Player.rplayer_id, sq.sql.func.count(
'*').label('dribbles')).filter(db.Hit.dribble == 2).join(db.Player).group_by(db.Player.rplayer_id).subquery()
count_save = session.query(db.Hit, db.Player.rplayer_id, sq.sql.func.count(
'*').label('saves')).filter(db.Hit.save > 0).join(db.Player).group_by(db.Player.rplayer_id).subquery()
count_fail_save = session.query(db.Hit, db.Player.rplayer_id, sq.sql.func.count(
'*').label('failed_saves')).filter(db.Hit.save == -1).join(db.Player).group_by(db.Player.rplayer_id).subquery()
ave_hit_dist = session.query(db.Hit, db.Player.rplayer_id, sq.sql.func.avg(db.Hit.distance).label(
'hitdist')).filter(db.Hit.distance > 750).join(db.Player).group_by(db.Player.rplayer_id).subquery()
count_50 = session.query(db.Hit, db.Player.rplayer_id, sq.sql.func.count(
'*').label('_50s')).filter(db.Hit.distance < 500).join(db.Player).group_by(db.Player.rplayer_id).subquery()
ave_shot_distance = session.query(db.Hit, db.Player.rplayer_id, sq.sql.func.avg(db.Hit.distance).label('shotdist')).filter(
db.Hit.shot > 0).filter(db.Hit.distance > 750).join(db.Player).group_by(db.Player.rplayer_id).subquery()
ave_shot_ot_distance = session.query(db.Hit, db.Player.rplayer_id, sq.sql.func.avg(db.Hit.distance).label('shot_otdist')).filter(
db.Hit.shot == 2).filter(db.Hit.distance > 750).join(db.Player).group_by(db.Player.rplayer_id).subquery()
count_block_shot = session.query(db.Hit, db.Player.rplayer_id, sq.sql.func.count('*').label('blocked_shots')).filter(
db.Hit.shot > 0).filter(db.Hit.distance < 300).join(db.Player).group_by(db.Player.rplayer_id).subquery()
print('hi')
x = session.query(db.RPlayer.name, db.RPlayer.id, db.RTeam.name, count.c.replay_count, count_hits.c.hits, count_pass.c.passes, count_assist.c.assists, count_dribble.c.dribbles, count_shot.c.shots, count_shot_ot.c.shots_ot, count_goal.c.goals, count_assisted_goal.c.assisted_goals, count_save.c.saves, count_fail_save.c.failed_saves, ave_hit_dist.c.hitdist, count_50.c._50s, ave_shot_distance.c.shotdist, ave_shot_ot_distance.c.shot_otdist, count_block_shot.c.blocked_shots)\
.join(db.RTeam)\
.outerjoin(count, db.RPlayer.id == count.c.rplayer_id)\
.outerjoin(count_hits, db.RPlayer.id == count_hits.c.rplayer_id)\
.outerjoin(count_pass, db.RPlayer.id == count_pass.c.rplayer_id)\
.outerjoin(count_assist, db.RPlayer.id == count_assist.c.rplayer_id)\
.outerjoin(count_shot, db.RPlayer.id == count_shot.c.rplayer_id)\
.outerjoin(count_shot_ot, db.RPlayer.id == count_shot_ot.c.rplayer_id)\
.outerjoin(count_goal, db.RPlayer.id == count_goal.c.rplayer_id)\
.outerjoin(count_assisted_goal, db.RPlayer.id == count_assisted_goal.c.rplayer_id)\
.outerjoin(count_dribble, db.RPlayer.id == count_dribble.c.rplayer_id)\
.outerjoin(count_save, db.RPlayer.id == count_save.c.rplayer_id)\
.outerjoin(count_fail_save, db.RPlayer.id == count_fail_save.c.rplayer_id)\
.outerjoin(ave_hit_dist, db.RPlayer.id == ave_hit_dist.c.rplayer_id)\
.outerjoin(count_50, db.RPlayer.id == count_50.c.rplayer_id)\
.outerjoin(ave_shot_distance, db.RPlayer.id == ave_shot_distance.c.rplayer_id)\
.outerjoin(ave_shot_ot_distance, db.RPlayer.id == ave_shot_ot_distance.c.rplayer_id)\
.outerjoin(count_block_shot, db.RPlayer.id == count_block_shot.c.rplayer_id)\
.order_by(db.RPlayer.rteam_id)
replay = pd.read_sql_query(
x.selectable,
db.engine,
index_col='rplayers_id',
)
replay.columns = replay.columns.str.replace('^anon_[0-9]+_', '')
velocities = pd.read_sql_query(session.query(db.Hit, db.RPlayer.id).join(db.Player).join(db.RPlayer).selectable,
db.engine
)
# print(velocities[:5])
magic_vel = 0.0043527
velocities['speed'] = ((velocities['hits_vx'] * magic_vel)**2 + (velocities[
'hits_vy'] * magic_vel)**2 + (velocities['hits_vz'] * magic_vel)**2)**0.5
hitvel = velocities.groupby('rplayers_id')['speed'].mean()
shotvel = velocities[velocities.hits_shot > 0].groupby('rplayers_id')[
'speed'].mean()
# print(hitvel)
replay['hitvel'] = hitvel
replay['shotvel'] = shotvel
# print(velocities)
# return
with open("all_player_hit_analysis.txt", 'w') as f:
replay.to_csv(f, index=False)
print('done')
def analyse_all_positions():
session = db.Session()
gameFrames = {} # replayid:[(start,end),..]
replays = session.query(db.Replay).all()
for replay in replays:
frames = get_game_frames(replay)
gameFrames[replay.id] = frames
# print(gameFrames)
# count = session.query(db.Player.rplayer_id, sq.sql.func.count('*').label('replay_count')).group_by(db.Player.rplayer_id).subquery()
# count_positions = session.query(db.Position, db.Player.rplayer_id, sq.sql.func.count('*').label('positions')).join(db.Player).group_by(db.Player.rplayer_id).subquery()
# count_game_positions = session.query(db.Position, db.Player.rplayer_id, sq.sql.func.count('*').label('game_positions')).join(db.Player).filter(sq.or_(sq.and_(sq.or_(db.Position.frameNo.between(frames[0],frames[1]) for frames in gameFrames[replayid]),db.Position.replay_id==replayid) for replayid in gameFrames)).group_by(db.Player.rplayer_id).subquery()
# count_half_attacking = session.query(db.Position, db.Player.rplayer_id, sq.sql.func.count('*').label('attacking_half')).join(db.Player).filter(db.Position.y>0).group_by(db.Player.rplayer_id).subquery()
# count_half_defending = session.query(db.Position, db.Player.rplayer_id, sq.sql.func.count('*').label('defending_half')).join(db.Player).filter(db.Position.y<0).group_by(db.Player.rplayer_id).subquery()
print('hi')
a = time.time()
rplayers = session.query(db.RPlayer).all()
# i=0
for rplayer in tqdm.tqdm(rplayers, leave=True):
# i += 1
# if i>2: break
rpid = rplayer.id
# print('\n',rid)
pids, rids = zip(*session.query(db.Player.id,
db.Player.replay_id).filter(db.Player.rplayer_id == rpid).all())
print(pids, rids)
_gameFrames = {}
for rid in rids:
_gameFrames[rid] = gameFrames[rid]
# limit to 5 games
_temp_gameFrames = {}
i = 0
for rid in rids:
if i > 5:
break
_temp_gameFrames[rid] = gameFrames[rid]
i += 1
print(_temp_gameFrames)
p = session.query(
db.Position,
db.BallFrame.x,
db.BallFrame.y,
db.BallFrame.z,
db.Team.colour,
# db.Velocity.speed
)\
.filter(db.Position.player_id.in_(pids))\
.filter(sq.or_(sq.and_(sq.or_(db.Position.frameNo.between(frames[0], frames[1]) for frames in _temp_gameFrames[replayid]), db.Position.replay_id == replayid) for replayid in _temp_gameFrames))\
.join(db.Player).join(db.Team)\
.join(db.BallFrame, db.BallFrame.frame_id == db.Position.frame_id)\
# .join(db.Velocity, sq.and_(db.Position.frame_id==db.Velocity.frame_id,db.Position.player_id==db.Velocity.player_id))\
# velocity join takes a long time.
# TESTING ABOVE
# p = session.query(
# db.Position,
# db.BallFrame.x,
# db.BallFrame.y,
# db.BallFrame.z,
# db.Team.colour,
# db.Velocity.speed
# )\
# .filter(db.Position.player_id.in_(pids))\
# .filter(sq.or_(sq.and_(sq.or_(db.Position.frameNo.between(frames[0],frames[1]) for frames in _gameFrames[replayid]),db.Position.replay_id==replayid) for replayid in _gameFrames))\
# .join(db.Velocity, sq.and_(db.Position.frame_id==db.Velocity.frame_id,db.Position.player_id==db.Velocity.player_id))\
# .join(db.Player).join(db.Team)\
# .join(db.BallFrame,db.BallFrame.frame_id==db.Position.frame_id)\
# END REAL PART
# p = session.query(db.Position,db.BallFrame.x,db.BallFrame.y,db.BallFrame.z,db.Team.colour)\
# .filter(db.Position.player_id.in_(pids))\
# .filter(sq.or_(sq.and_(sq.or_(db.Position.frameNo.between(frames[0],frames[1]) for frames in _gameFrames[replayid]),db.Position.replay_id==replayid) for replayid in _gameFrames))\
# .join(db.Player).join(db.Team)\
# .join(db.BallFrame,db.BallFrame.frame_id==db.Position.frame_id)\
positions = pd.read_sql_query(
p.selectable,
db.engine
)
# print(positions.columns.values)
# print('\n\n\n')
# print(positions[:5])
# print('\n\n\n')
# print(positions.describe())
print(positions, "!!!!!!!")
_positions1 = analyse_position(positions)
_positions2 = analyse_position_velocity(positions)
# _positions = {**(_positions1), **_positions2}
_positions = OrderedDict()
for key, value in _positions1.items():
_positions[key] = value
for key, value in _positions2.items():
_positions[key] = value
# print(_positions)
try:
for key, value in _positions.items():
positional_analysis[key].append(value)
positional_analysis['name'].append(rplayer.name)
positional_analysis['team'].append(rplayer.rteam.name)
positional_analysis['games'].append(len(pids))
except UnboundLocalError:
positional_analysis = OrderedDict()
positional_analysis['name'] = [rplayer.name]
positional_analysis['team'] = [rplayer.rteam.name]
positional_analysis['games'] = [len(pids)]
for key, value in _positions.items():
positional_analysis[key] = [value]
# print(positional_analysis)
positional_analysis = pd.DataFrame.from_dict(positional_analysis)
# print(positional_analysis)
# replay = pd.read_sql_query(
# x.selectable,
# db.engine
# )
# replay.columns = replay.columns.str.replace('^anon_[0-9]+_','')
print('duration:', int(time.time() - a))
with open("all_player_position_analysis1.txt", 'w') as f:
positional_analysis.to_csv(f, index=False)
print('done')