Defense doesn't get credit for certain safeties about nfldb

[BurntSushi]

Nice find. For reference, here's all of them:

gsis_id: 2009092006, drive: 24, play_id: 4217
(NO, OWN 1, Q4, 4 and 14) (3:41) D.Brees right end to NO -9 for -10 yards. FUMBLES, ball out of bounds in End Zone, SAFETY.
     Drew Brees (NO, QB) {'fumbles_oob': 1, 'fumbles_tot': 2, 'fumbles_notforced': 1, 'rushing_yds': -1, 'rushing_att': 1}
-------------------------------------------------------------------------------
gsis_id: 2010082753, drive: 10, play_id: 1624
(WAS, OWN 6, Q2, 2 and 12) (2:00) (Shotgun) R.Grossman FUMBLES (Aborted) at WAS 0, ball out of bounds in End Zone, SAFETY. Penalty on WAS-R.Grossman, Illegal Kick, declined.
     Rex Grossman (UNK, UNK) {'fumbles_oob': 1, 'fumbles_tot': 2, 'fumbles_notforced': 1, 'fumbles_rec_yds': -6, 'rushing_att': 1}
-------------------------------------------------------------------------------
gsis_id: 2010091201, drive: 21, play_id: 4199
(BUF, OWN 1, Q4, 4 and 10) (1:33) G.Sanborn FUMBLES (Aborted) at BUF 0, ball out of bounds in End Zone, SAFETY. Intentionally snapped out of end zone.
     Garrison Sanborn (BUF, LS) {'fumbles_oob': 1, 'fumbles_tot': 2, 'fumbles_notforced': 1, 'fumbles_rec_yds': -1, 'rushing_att': 1}
-------------------------------------------------------------------------------
gsis_id: 2010092000, drive: 1, play_id: 111
(SF, OWN 15, Q1, 3 and 5) (13:32) (Shotgun) A.Smith Aborted. D.Baas FUMBLES at SF 15, ball out of bounds in End Zone, SAFETY. Penalty on SF-A.Smith, Illegal Bat, declined. Snap sailed over QB's head into end zone; A.Smith batted ball at SF -6 through the back of end zone.
     David Baas (UNK, UNK) {'fumbles_oob': 1, 'fumbles_tot': 2, 'fumbles_notforced': 1, 'fumbles_rec_yds': -15}
     Alex Smith (KC, QB) {'rushing_att': 1}
-------------------------------------------------------------------------------
gsis_id: 2011103008, drive: 18, play_id: 4149
(NE, OWN 22, Q4, 1 and 10) (:19) (Shotgun) T.Brady sacked at NE 19 for -3 yards (B.Keisel). FUMBLES (B.Keisel) [B.Keisel], touched at NE 15, ball out of bounds in End Zone, SAFETY. The Replay Assistant challenged the loose ball recovery ruling, and the play was Upheld.
     Brett Keisel (UNK, UNK) {'defense_qbhit': 1, 'defense_sk_yds': -7, 'defense_tkl': 1, 'defense_ffum': 1, 'defense_sk': 1.0}
     Troy Polamalu (PIT, SS) {'defense_safe': 1}
     Tom Brady (NE, QB) {'fumbles_oob': 1, 'fumbles_tot': 2, 'passing_sk': 1, 'fumbles_forced': 1, 'passing_sk_yds': -7}
-------------------------------------------------------------------------------
gsis_id: 2012102108, drive: 8, play_id: 1338
(NYJ, OWN 12, Q2, 1 and 10) (13:14) M.Sanchez FUMBLES (Aborted) at NYJ 6, ball out of bounds in End Zone, SAFETY. Penalty on NYJ-M.Sanchez, Illegal Kick, declined. Sanchez kicked the ball through the end zone and out of bounds
     Mark Sanchez (PHI, QB) {'fumbles_oob': 1, 'fumbles_tot': 2, 'fumbles_notforced': 1, 'fumbles_rec_yds': -12, 'rushing_att': 1}
-------------------------------------------------------------------------------
gsis_id: 2013090807, drive: 3, play_id: 522
(TB, OWN 8, Q1, 3 and 5) (8:38) (Shotgun) J.Freeman FUMBLES (Aborted) at TB 0, ball out of bounds in End Zone, SAFETY. Penalty on TB-J.Freeman, Illegal Bat, declined.
     Josh Freeman (UNK, UNK) {'fumbles_oob': 1, 'fumbles_tot': 2, 'fumbles_notforced': 1, 'fumbles_rec_yds': -8, 'rushing_att': 1}
-------------------------------------------------------------------------------
gsis_id: 2013102012, drive: 11, play_id: 1576
(DEN, OWN 9, Q2, 3 and 7) (9:01) (Shotgun) P.Manning sacked at DEN 0 for -9 yards (R.Mathis). FUMBLES (R.Mathis) [R.Mathis], ball out of bounds in End Zone, SAFETY. The Replay Assistant challenged the safety ruling, and the play was Upheld.
     Peyton Manning (DEN, QB) {'fumbles_oob': 1, 'fumbles_tot': 2, 'passing_sk': 1, 'fumbles_forced': 1, 'passing_sk_yds': -9}
     Robert Mathis (IND, OLB) {'defense_qbhit': 1, 'defense_sk_yds': -9, 'defense_tkl': 1, 'defense_ffum': 1, 'defense_sk': 1.0}
-------------------------------------------------------------------------------
gsis_id: 2013120101, drive: 24, play_id: 3839
(CLE, OWN 14, Q4, 1 and 10) (7:47) (Shotgun) B.Weeden Aborted. A.Mack FUMBLES at CLE 0, touched at CLE 0, ball out of bounds in End Zone, SAFETY NULLIFIED by Penalty. PENALTY on CLE-B.Weeden, Illegal Kick, 0 yards, enforced in End Zone, SAFETY.
     Brandon Weeden (DAL, QB) {'rushing_att': 1}
     Alex Mack (CLE, C) {'fumbles_oob': 1, 'fumbles_tot': 2, 'fumbles_notforced': 1, 'fumbles_rec_yds': -14}
-------------------------------------------------------------------------------
gsis_id: 2014011101, drive: 10, play_id: 1704
(NE, OWN 44, Q2, 4 and 7) (2:28) R.Allen Aborted. D.Aiken FUMBLES at NE 44, recovered by NE-R.Allen at NE 3. R.Allen to NE 3 for no gain. FUMBLES, touched at NE 1, declared dead in End Zone, SAFETY.
     Ryan Allen (NE, P) {'rushing_att': 1, 'fumbles_tot': 1, 'fumbles_notforced': 1, 'fumbles_rec': 1, 'fumbles_rec_yds': -2}
     Danny Aiken (NE, LS) {'fumbles_tot': 1, 'fumbles_notforced': 1, 'fumbles_rec_yds': -41}

Interestingly, some of them are actually caused by a forced fumble by the defense, and yet, they still aren't credited with the safety. Weird.

I believe that the defence should be credited with a safety
even though no individual defensive player got credit.

nfldb is predicated on the assumption that statistics are always assigned at the individual level. I am very hesitant to start breaking this assumption for extreme corner cases. I would consider a safety not caused by a defender to be a pretty rare corner case. As such, we may have to work harder to get those stats. (Are these situations really credited to the defense? I don't know.)

I think for now, if you want to detect this situation, then you can use something like this:

from __future__ import print_function
import pprint

import nfldb

db = nfldb.connect()

q = nfldb.Query(db)
q.drive(result='Fumble, Safety')
q.play(fumbles_tot__ge=1)

defensive_safeties = {}
for p in q.as_plays():
    game = nfldb.Game.from_id(db, p.gsis_id)
    if game.home_team == p.pos_team:
        defense_team = game.away_team
    else:
        defense_team = game.home_team

    key = (p.gsis_id, p.drive_id, p.play_id)
    defensive_safeties[key] = defense_team

pprint.pprint(defensive_safeties)

Which outputs:

[andrew@Liger nfldb] python2 38.py 
{('2009092006', 24, 4217): 'PHI',
 ('2010082753', 10, 1624): 'NYJ',
 ('2010091201', 21, 4199): 'MIA',
 ('2010092000', 1, 111): 'NO',
 ('2011103008', 18, 4149): 'PIT',
 ('2012102108', 8, 1338): 'NE',
 ('2013090807', 3, 522): 'NYJ',
 ('2013102012', 11, 1576): 'IND',
 ('2013120101', 24, 3839): 'JAC',
 ('2014011101', 10, 1704): 'IND'}

It's not ideal, but the number of times it happens is small enough that you can just brute force it.

For my own use, I would like to see more team aggregate columns.
Locally I have created another table with columns I need. This isn't
too much of a problem for me since I don't use Python to access the data
and if I can derive the data I need from existing columns.

Aggregated by what? Team? Defense? Offense? Quarter? Half? Game? Overtime? Week? 4 weeks? Full season? Postseason?

This is why the nfldb.Query module exists. You can do all the aggregation you want in Python and it doesn't need to be stored in the database. This is ideal because storing every possible aggregate combination isn't feasible.

You can see some examples for aggregate queries in the wiki: https://github.com/BurntSushi/nfldb/wiki/Aggregate-searching

[ajjgda]

Thanks for your response. The fantasy team I play in has a weird scoring system.
I am trying to duplicate the system and use your db for projections and what if drafts..
We give points on Team stats, fumbles recovered by def,safeties,ints,tds etc.
We don't draft individual def players so I am in the habit of thinking of the team units as players.
I don't mind creating these fields if I can find the data. Your work has been a huge help.
These little problems pop up when my calculated score doesn't match league history. scores
Over all I get excellent agreement with our league history.

I am trying to shoe horn your db into a bunch of existing programs and therefore can't make
full use of Python. I had thought about running Python inside my programs. but felt I would
never get done before draft night. So I settled for sending sql scripts to your db from my
existing programs.

I probably shouldn't have included the aggregate comment because it wasn't germane to the problem.
I have used your sample scripts as guides for my sql scripts.

[BurntSushi]

I had thought about running Python inside my programs. but felt I would
never get done before draft night.

I hear ya. I am myself trying to get things done before I start drafting. I'm running out of time!

I probably shouldn't have included the aggregate comment because it wasn't germane to the problem.
I have used your sample scripts as guides for my sql scripts.

No worries. I'm a bit of a performance nut, so it'd be nice to store aggregate data. (I did compromise with the new agg_play table.) But there are just so many dimensions that I don't think it's a problem that should be solved upstream (here in nfldb). Instead, aggregate data should be stored on a case-by-case basis. And now with the agg_play as an example, anyone should be able to adopt that strategy to make their own custom materialized views. (A materialized view is a table that is automatically updated with other tables are updated. It's like a view, but actually stores the data, which is useful when the view is built on a hard-to-optimize query.)

A possible feature far in the future would be the ability to create your own materialized views through a convenient nfldb API.

[BurntSushi]

We give points on Team stats, fumbles recovered by def,safeties,ints,tds etc.
We don't draft individual def players so I am in the habit of thinking of the team units as players.

Indeed, most standard fantasy leagues score defenses as a team unit. IDP isn't quite mainstream yet.

It is kind of a pain to score defenses, especially the "points allowed" category. I've already done it in nflfan. It isn't quite as simple to use as nfldb so it doesn't get much love, but it has a completely generalized fantasy scoring system built in. You specify a score schema and let it do the rest.

[BurntSushi]

I am going to close this. Even though you've pointed out an awkward statistic within nfldb, I don't think there are any relatively immediate action items to be taken here.