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')