Generating Team Defensive Stats

[IHaveAGenericUserName]

Hello,

I'm learning python and how to utilize it in relation to nfldb, which will take some time. I was hoping to just get some basic insight into pulling team defensive stats.

If I could just see an example of pulling how many passing yards a team allowed in one game I could build off of that, but I can't seem to find any example and am lost on my own.

Any help would be appreciated. Thanks for making such an awesome resource!

[BurntSushi]

There is no "passing yards allowed" statistic. To compute that, you'd have to fetch all passing yards recorded by the opposing team and sum them. You'll have to do that with most other stats too, other than the defense_* ones listed here: https://github.com/BurntSushi/nfldb/wiki/Statistical-categories

[sansbacon]

Here is a SQL query that will give you passing yards allowed Def_team is the team allowing the yards, off_team is the team that accrued the passing yards So, for example, in the first row returned by the query, GB is the defense who allowed PHI to pass for 254 yards.

WITH aggwt AS (
    SELECT
        pp.gsis_id, pp.team, sum(pp.passing_yds) as pyds
    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.team
)

SELECT
    aggwt.gsis_id, g.season_year, g.week,
    (CASE WHEN aggwt.team = g.home_team THEN g.away_team ELSE g.home_team END) as def_team,
    aggwt.team as off_team,
    aggwt.pyds as pass_yds_allowed

FROM aggwt
INNER JOIN game as g ON aggwt.gsis_id = g.gsis_id
ORDER BY gsis_id DESC