Get team player was on during the game, not current team

[theSanchize3]

I am trying to create a master table of every player (besides kickers) and each of their stats for each week. I want the 'Team' column in the table to show the team they were on for that game, not the team that they are currently on; but, I am not sure how to do this.

import nfldb
import pandas as pd
from pandas import DataFrame as df

db = nfldb.connect()

playerList = []
bigList = []

def Get_Yds_Per(yds,att):
    if att != 0:
        return yds / att
    elif att == 0:
        return 0

q = nfldb.Query(db)
q.game(season_year=2015,season_type='Regular')
q.player(position__ne='K')
for g in q.as_players():
    playerList.append([str(g.full_name),str(g.team),str(g.position)])

for i in range(1,18):
    for p in playerList:
        q = nfldb.Query(db)
        q.game(season_year=2015,season_type='Regular',week=i)
        q.player(full_name=p[0])
        for a in q.as_aggregate():
            bigList.append([p[0],p[1],p[2],i,a.passing_cmp,a.passing_att,a.passing_yds,
                            Get_Yds_Per(a.passing_cmp_air_yds,a.passing_att),a.passing_int,
                            a.passing_tds,a.passing_sk,a.rushing_att,a.rushing_yds,
                            Get_Yds_Per(a.rushing_yds,a.rushing_att),a.rushing_tds,
                            a.receiving_rec,a.receiving_tar,a.receiving_yds,
                            Get_Yds_Per(a.receiving_yds,a.receiving_rec),a.receiving_tds,
                            a.fumbles_rec+a.fumbles_lost,a.fumbles_lost])

bigDF = df(bigList, columns=["Name","Team","Position","Week","Pass Comp","Pass Atts",
                             "Pass Yds","Yds/Pass","Ints","Pass TDs","Sacks","Rush Atts",
                             "Rush Yds","Yds/Rush","Rush TDs","Receptions","Targets",
                             "Rec Yds","Yds/Rec","Rec TDs","Fumbles","Fumbles Lost"])

print bigDF

Any help is appreciated.

Thanks

[BurntSushi]

You'll need to use the team field from the play_player table, which records the team of the player for which that statistical event occurred. Instead of using p[1] for the team, try a.team? (Although I'm not sure if the team field will survive an aggegation. If not, you'll need to do it all manually.)

[theSanchize3]

Thanks for responding so quickly. I tried using a.team in place of p[1], but it did not work. The 'Team' value for every player is 'None'.

What do you mean by do it all manually?

[BurntSushi]

Doing it manually means one of two things:

1. Not using as_aggregate and summing the stats yourself.
2. Write the SQL by hand.

[theSanchize3]

Ok, thanks for the help!

[theSanchize3]

So I tried getting the team that the players were on at the time the game occurred using team from as_play_players, but it still returned the team the player is currently on. Can you give an example that shows how to get the team the player was on when the game occurred?

[sansbacon]

here is an example of weekly targets and receptions per player - team is taken from play_player, which should be the team the player was on in that game (rather than his current team):

WITH aggwt AS (
SELECT
pp.gsis_id, pp.player_id, pp.team, sum(pp.receiving_tar) as tar, sum(pp.receiving_rec) as rec
FROM
play_player AS pp
INNER JOIN game as g ON pp.gsis_id = g.gsis_id
WHERE g.season_type = 'Regular'
GROUP BY
pp.gsis_id, pp.player_id, pp.team
)

SELECT
aggwt.gsis_id, g.season_year, g.week, aggwt.player_id, p.full_name, p.position, aggwt.team,
(CASE WHEN aggwt.team = g.home_team THEN g.away_team ELSE g.home_team END) as opp,
aggwt.tar, aggwt.rec

FROM aggwt
INNER JOIN player as p ON aggwt.player_id = p.player_id
INNER JOIN game as g ON aggwt.gsis_id = g.gsis_id

WHERE
p.position in ('RB', 'WR', 'TE')