I've recently completed a project to compile publicly-available NFL play-by-play data. It took a while, but now it's ready.
The resulting database comprises nearly all non-preseason games from the 2002 through [edit: 2012] seasons. I have not performed any analysis on the data, so what you'll get are the only basics--time, down, distance, yard line, play description, and score. It's almost exactly what I started with. I'll leave any analysis up to you.
I can see where this might become a community effort, where one person might add a column for yardage gained or lost. Another contributor might add a column for whether or not the current series leads to a first down. Someone else might add a column for drive result, and so on.
I'll share a little trick right now. Say you want to identify the plays that were interceptions. The play descriptions are in column J in the data I provided, and they will contain the word "INTERCEPTED" if there was an interception on the play. In a new column you would type "ISNUMBER(SEARCH("INTERCEPTED",J2)). The cell will say TRUE or FALSE according to whether the text "INTERCEPTED" was found. Excel can only take you so far, however. To do more advanced analysis, you may need a scripting language.
I hope this data is put to good use. With many more minds testing theories, there's no telling what interesting things we'll learn. Happy crunching.
Play-by-Play Data
By
Brian Burke
Subscribe to:
Post Comments (Atom)
Thank you so much. I've been looking for something like this for a year now.
This a fantastic idea. Thank you!
Great stuff Brian. Thanks for making this data available.
This is great. Not to sound ungrateful, but is there any reason why the 2009 data isn't available as well?
Great stuff Brian. I did try something like this a while ago but put it on the back burner when I realised a) how hard it was and b) the NFL don't like play-by-play data to be too freely available. Amazing commitment.
Very interesting. I was surprised to see that the play-by-play actually says where each run went (up the middle, off-tackle, etc.) Sadly, it gives no such narrative for the pass plays. While this data is extremely helpful, and Brian has probably done more with it than anyone could reasonably expect, it strikes me that there is so much MORE meaningful analysis that could be done if we had more meaningful narratives available to us.
I know that for baseball, if you subscribe to some very expensive stats service, you can get ridiculously detailed descriptions of every single pitch. You can find exactly where every ball was hit in the in/outfield, what kind of pitch was thrown, where the pitch was positioned over the plate, etc.
Brian, do you know if any such service exists for football? I'm sure that it must be terribly expensive if it does, but I'm curious about whether it is even possible to get more detailed information about games from throughout the decade. Specifically, I would love to see information on:
-Which players were on the field for offense/defense on each play (could lead to development of some fascinating +/- stats for football)
-How far incomplete passes were thrown (of course, it currently gives the length of completed passes, but it says nothing about how far an incomplete pass attempt went)
-Where passes (complete or incomplete) were thrown (left/right? over the middle? along the sideline?)
-The offensive/defensive package on the field (this could kinda be inferred if we knew the personnel on the field, but it would be much clearer to just know that the defense was in their nickel package, or the offense was in the shotgun spread)
-Was the QB hit on the play? (Of course, sacks are listed, but this only tells a small piece of the QB-pressure story.)
I could think of more examples if I spent some time on it, but I think you get my point. Do stats like these even exist anywhere? Does someone like Stats, Inc. even track that kind of stuff on a play-by-play basis?
Excellent, Brian!
Will there ever be a MySynergySports for football also? That's what Alchemist wants to know...
Awesome!
I love you.
This is awesome. Thanks for making it freely available.
I tried to to this a while back (basically once the offseason started). For anyone looking to do this on their own, CBSSports.com gives detailed play-by-play, and I was able to import that fairly directly into Excel for single games. But writing a script to do that for a whole season's worth of games was beyond my abilities, and far too tedious to do by hand.
1) A sample line from the 2002 file:
20020905_SF@NYG,1,59,20,NYG,SF,3,11,81,(14:20) (Shotgun) K.Collins pass intended for T.Barber INTERCEPTED by T.Parrish (M.Rumph) at NYG 29. T.Parrish to NYG 23 for 6 yards (T.Barber).,0,0,2002
Who is M.Rumph? What did he do on the play? Did T.Barber tackle T.Parrish? This is undoubtedly me not being familiar with play descriptions, but I figured I'd ask here.
2) If someone were to obtain a set of coaches' videos of all the games, or even of some games, the analysis would be a lot more interesting than even if someone found a set of all the broadcast videos, because the coach video is from behind the quarterback (like in Madden) so you can see the safeties and what coverage the defense was in and what routes the receivers ran. There are a lot of games, but not really that many compared to baseball, and I wouldn't be surprised if people could collaborate to compile data from such video if they split games up. A link to a torrent would be a safe and easy way to share such a discovery.
3) Brian, you're awesome.
Football Outsiders and Pro Football Focus both have more detailed play-by-play information based on people watching the videos of every game and tracking what happens. They're just based on the tv coverage, though, not the coaches' film, and they only cover the past few seasons. They don't keep track of everything that you might want included, but they do include a lot of good stuff. FO sells their data (listed as "game charting data" in their store). PFF has a lot of their data available for free - most of it is only available aggregated by game in charts like this one, but player participation data is available in play-by-play format (which includes who was on the field for each play, where they lined up, and on passing plays whether they rushed the passer or played coverage / blocked or ran a route).
Elliot, players in parentheses are defenders who are responsible for a tackle or a pass defensed. On that play, M.Rumph of SF defensed the pass to T.Barber which T.Parrish intercepted, and T.Barber of NYG and was responsible for tackling T.Parrish after the change of possession.
Alchemist - I emailed Stats Inc once to ask if they had player info (who was on the field for each play) for football in the same way they do for basketball, and they did not. So someone might, but you can at least cross them off your list.
This is fantastic, Brian. Awesome of you to post all of this for the public.
A quick scan tells me that every play can be categorized as a pass, sack, non-scrimmage play (KO or PAT), field goal attempt, punt, penalty, kneel, or rushing play. The latter is the most difficult to sort based on the descriptions, but I think it wouldn't take more than a half an hour to code these files to make it even cooler. You might need to manually go through and figure out the replay ones and stuff, but I think coding it for type of play would be one nice improvement, and easy to do in Excel.
@Chase...how about this for catgorizing each play:
=IF(ISNUMBER(SEARCH("right tackle",J2)),"rush",IF(ISNUMBER(SEARCH("right guard",J2)),"rush",IF(ISNUMBER(SEARCH("left guard",J2)),"rush",IF(ISNUMBER(SEARCH("up the middle",J2)),"rush",IF(ISNUMBER(SEARCH("left tackle",J2)),"rush",IF(ISNUMBER(SEARCH("left end",J2)),"rush",IF(ISNUMBER(SEARCH("right end",J2)),"rush",IF(ISNUMBER(SEARCH("pass",J2)),"pass",IF(ISNUMBER(SEARCH("kneel",J2)),"kneel",IF(ISNUMBER(SEARCH("punt",J2)),"punt",IF(ISNUMBER(SEARCH("kicks",J2)),"kickoff",IF(ISNUMBER(SEARCH("extra point",J2)),"extrapoint",IF(ISNUMBER(SEARCH("sacked",J2)),"sack",IF(ISNUMBER(SEARCH("PENALTY",J2)),"penalty",IF(ISNUMBER(SEARCH("field goal",J2)),"fieldgoal",IF(ISNUMBER(SEARCH("FUMBLES",J2)),"fumble",IF(ISNUMBER(SEARCH("spiked",J2)),"spike",IF(ISNUMBER(SEARCH("scrambles",J2)),"rush","rush"))))))))))))))))))
I defaulted to "rush" because some rushes that gain no yards or lose yards are listed as "(5:37) D.Williams to SD 10 for -1 yards (S.Merriman)." which has no distinguishing features. I didn't find any other plays like that.
All these ideas sound great! Thanks for the info. Does anyone have an idea where we might be able to find a complete player database. If we could isolate players involved on each play based on the play categorization above there is a lot of cool analysis that could be done. I'm thinking of this more in a fantasy football type of context but depending on the standard deviation is would be interesting to see performance based on a players age, the point in the season, and the relationship to the game. Do veteran players statistically perform better in game changing situations?
I was looking in the FAQ, and it said that this play by play data was received from a source that isn't public, but can't the raw data be taken easily enough from a site like NFL.com, and then after some time being put into it come up with something like that?
Brian very awesome stuff this is some great stuff and some of the stuff that i have been looking for! I'm going to try to come up with a few things and I will add my thoughts.
@weinsteinium-Very great start. Couple of thoughts - Should all aborted plays get pulled out as the first item since they can distort various plays? Also i think "scramble" could be added as a lookup to seperate qb scramble from rush att since those are probably originally pass attempts.
Penalty is also pretty tricky - there are plenty of penalty plays that aren't pulled up as penalty (same with fumble) should the plan be to include all plays with a penalty as penalty - dont think that is a good idea because several are declined - but there are several that show up as pass but are really incomplete passes but the penalty was accepted for instance.
Just some initial thoughts. Look forward to seeing what everyone can come up with!
@buzz - I see what you are saying about penalties. Because I put the check for penalty at the end of the list, if it was a run and a penalty it shows up as a run.
Really something should only be marked as a penalty if the penalty is accepted. I'll have to think about that.
Actually penalties are tricky because sometimes the penalty replaces the play and sometimes the play stands and then the penalty is tacked on.
So for, "(2:38) J.Campbell pass short middle to C.Portis to NYG 29 for 8 yards (D.Clark). PENALTY on WAS-C.Samuels Offensive Holding 10 yards enforced at NYG 37 - No Play." it's pretty obvious that should be marked as a penalty.
However, "(6:12) C.Portis left guard to WAS 7 for -3 yards (F.Robbins). PENALTY on NYG-G.Wilkinson Unnecessary Roughness 15 yards enforced at WAS 7."
Isn't as obvious. They took Portis' run for -3 yards and then tacked on a 15 yard penalty. That is both a run and a penalty. Maybe penalty needs to be a seperate column. So I would label the first one "noplay" and the second one "rush" with a penalty marked in the penalty column for each one.
A little bit of advice. I wouldn't have 1 master column (data field) that would contain more than one "dimension" of information. For example, I'd have a play type field, and a separate penalty field. Play type would be run, pass, kneel, abort, punt, FG att, KO. Penalties would be separate. Also some penalties are declined, some make a play a "no play," and some are accepted on top of play. Some are "dead ball" penalties that occur between plays. It's more complicated than most people think.
Brian,
Any thoughts on how to fix errors - wasn't sure how often you come across them in these files. For instance "20021208_DET@ARI" after this play "(9:53) J. Plummer pass to J. Makovicka for 1 yard TOUCHDOWN." the score goes from 0-0 to 7-7. Then the final score progresses a little goofy and ends is a 20-20 tie where as the actual results are 23-20
Try these four columns:
Play Type|Penalty|Fumble|Yards gained
It paints a pretty decent picture of what is going on. You'd need to take into account which team has possession (especially on fumbles where it may or may not indicate change of possession:
More than 7 levels of if/then/else may not work on older versions of excel
Play type (I fixed a couple of things, using "no play", and labeling scrambles as such and moving interception front. I also took fumble out and made it a separate column since a fumble could happen on a rush, pass, kickoff or punt):
=IF(ISNUMBER(SEARCH("no play",J2)),"noplay",IF(ISNUMBER(SEARCH("intercepted",J2)),"interception",IF(ISNUMBER(SEARCH("right tackle",J2)),"rush",IF(ISNUMBER(SEARCH("right guard",J2)),"rush",IF(ISNUMBER(SEARCH("left guard",J2)),"rush",IF(ISNUMBER(SEARCH("up the middle",J2)),"rush",IF(ISNUMBER(SEARCH("left tackle",J2)),"rush",IF(ISNUMBER(SEARCH("left end",J2)),"rush",IF(ISNUMBER(SEARCH("right end",J2)),"rush",IF(ISNUMBER(SEARCH("pass",J2)),"pass",IF(ISNUMBER(SEARCH("kneel",J2)),"kneel",IF(ISNUMBER(SEARCH("punt",J2)),"punt",IF(ISNUMBER(SEARCH("kicks",J2)),"kickoff",IF(ISNUMBER(SEARCH("extra point",J2)),"extrapoint",IF(ISNUMBER(SEARCH("sacked",J2)),"sack",IF(ISNUMBER(SEARCH("field goal",J2)),"fieldgoal",IF(ISNUMBER(SEARCH("spiked",J2)),"spike",IF(ISNUMBER(SEARCH("scrambles",J2)),"scramble","rush"))))))))))))))))))
penalty:
=IF(ISNUMBER(SEARCH("penalty",J2)),"penalty","")
fumble:
=IF(ISNUMBER(SEARCH("fumble",J2)),"fumble","")
yards gained:
=IF(ISNUMBER(SEARCH("touchdown",J2)),$I2-0,IF(ISNUMBER(SEARCH("fumble",J2)),$I3-$I2,IF(ISNUMBER(SEARCH("intercepted",J2)),$I3-$I2,IF(ISNUMBER(SEARCH("kicks",J2)),"",IF(ISNUMBER(SEARCH("extra point",J2)),"",IF(ISNUMBER(SEARCH("field goal",J2)),"",IF(ISNUMBER(SEARCH("punt",J2)),"",$I2-$I3)))))))
Very nice, Brian. Thank you.
I've tried to do this sort of thing manually during the season for Eagles Stats, but find that the bus ride just doesn't provide enough time to get it done during the week.
Any chance these data will be updated during the season on a regular basis?
Whatever the case, thanks.
Here are a couple of random things I put together. Not sure if they are as interesting to everyone as play type but anyway...apparently they won’t let me post everything in a comment for some reason…
Here is a formula for type of penalty
=IF(ISNUMBER(SEARCH("penalty",J2)),IF(ISNUMBER(SEARCH("illegal kick",J2)),"ill kick",IF(ISNUMBER(SEARCH("illegal receiver",J2)),"ill rec",IF(ISNUMBER(SEARCH("invalid fair catch",J2)),"ill fair catch",IF(ISNUMBER(SEARCH("crackback",J2)),"crack",IF(ISNUMBER(SEARCH("no penalty",J2)),"no penalty",IF(ISNUMBER(SEARCH("excessive celebration",J2)),"excess celeb",IF(ISNUMBER(SEARCH("illegal bat",J2)),"ill bat",IF(ISNUMBER(SEARCH("illegal cut",J2)),"ill cut",IF(ISNUMBER(SEARCH("player out of bounds",J2)),"ill touch",IF(ISNUMBER(SEARCH("illegal motion",J2)),"ill motion",IF(ISNUMBER(SEARCH("offensive offside",J2)),"off offside",IF(ISNUMBER(SEARCH("intentional ground",J2)),"int ground",IF(ISNUMBER(SEARCH("12 on-field",J2)),"too many on field",IF(ISNUMBER(SEARCH("illegal forward pass",J2)),"ILL forw pass",IF(ISNUMBER(SEARCH("offside on free kick",J2)),"Off free kick",IF(ISNUMBER(SEARCH("illegal subst",J2)),"ILL subst",IF(ISNUMBER(SEARCH("neutral zone",J2)),"neut zne",IF(ISNUMBER(SEARCH("Taunting",J2)),"taunt",IF(ISNUMBER(SEARCH("leverage",J2)),"leverage",IF(ISNUMBER(SEARCH("clipping",J2)),"clip",IF(ISNUMBER(SEARCH("roughing the passer",J2)),"Rough Pass",IF(ISNUMBER(SEARCH("Unnecessary Roughness",J2)),"unnec rough",IF(ISNUMBER(SEARCH("Illegal Use of Hands",J2)),"ILL use hands",IF(ISNUMBER(SEARCH("illegal procedure",J2)),"ILL Proc",IF(ISNUMBER(SEARCH("tripping",J2)),"trip",IF(ISNUMBER(SEARCH("running into",J2)),"running k",IF(ISNUMBER(SEARCH("personal foul",J2)),"pers foul",IF(ISNUMBER(SEARCH("Illegal block",J2)),"ILL block",IF(ISNUMBER(SEARCH("Ineligible Downfield",J2)),"Inel downfield",IF(ISNUMBER(SEARCH("illegal contact",J2)),"Ill Contact",IF(ISNUMBER(SEARCH("unsportsmanlike",J2)),"Unsportsman",IF(ISNUMBER(SEARCH("illegal touch",J2)),"ILL touch",IF(ISNUMBER(SEARCH("chop block",J2)),"chop",IF(ISNUMBER(SEARCH("illegal formation",J2)),"ILL form",IF(ISNUMBER(SEARCH("offensive holding",J2)),"off hold",IF(ISNUMBER(SEARCH("defensive holding",J2)),"def hold",IF(ISNUMBER(SEARCH("shift",J2)),"ILL shift",IF(ISNUMBER(SEARCH("low block",J2)),"low block",IF(ISNUMBER(SEARCH("defensive offside",J2)),"def offside",IF(ISNUMBER(SEARCH("face mask",J2)),"face mask",IF(ISNUMBER(SEARCH("encroach",J2)),"encroach",IF(ISNUMBER(SEARCH("delay",J2)),"delay gm",IF(ISNUMBER(SEARCH("roughing the kicker",J2)),"roughing k",IF(ISNUMBER(SEARCH("inter",J2)),"PI",IF(ISNUMBER(SEARCH("false start",J2)),"fls strt"))))))))))))))))))))))))))))))))))))))))))))),"")
PArt2 - Here is if a fumble is recovered or lost. You simply need to add a formula in column Q that says =e3=e2 to see if the team retained posession of the ball
=IF(IF(ISNUMBER(SEARCH("fumble",J2)),"fumble","")="fumble",IF(Q2=FALSE,"fum lost","fum Recov"),"")
Here is the direction of run assuming you use wein's formula above in column N.
=IF(N2="rush",IF(ISNUMBER(SEARCH("right tackle",J2)),"right tackle",IF(ISNUMBER(SEARCH("right guard",J2)),"right guard",IF(ISNUMBER(SEARCH("left guard",J2)),"left guard",IF(ISNUMBER(SEARCH("up the middle",J2)),"middle",IF(ISNUMBER(SEARCH("left tackle",J2)),"left tackle",IF(ISNUMBER(SEARCH("left end",J2)),"left end",IF(ISNUMBER(SEARCH("right end",J2)),"right end"))))))),"")
Couple of other random thoughts.
@weinsteinium
- should abort be at the top of your list as that is sort of a goofy play?
- Should yards gained on play for kickoff be something like the next yard line -30 to give you a net kickoff yardage. Haven't really looked into that formula too much yet but it looks good in general.
@Buzz - very nice stuff. I really like the penalty descriptions and the fumble lost/recovered.
And I think that you are right about the kickoff. And put should be current location - next row's location which should be easy to figure (same as I did with yards gained).
Here my last one for the day....
First a clean description of the play (removing things like (no huddle) and (shotgun) and the time):
=IF(ISNUMBER(SEARCH("shotgun",J2)),RIGHT(J2,LEN(J2)-SEARCH("shotgun",J2)-8),IF(ISNUMBER(SEARCH("field goal formation",J2)),RIGHT(J2,LEN(J2)-SEARCH("field goal formation",J2)-21),IF(ISNUMBER(SEARCH("no huddle",J2)),RIGHT(J2,LEN(J2)-SEARCH("no huddle",J2)-9),IF(ISNUMBER(SEARCH("run formation",J2)),RIGHT(J2,LEN(J2)-SEARCH("run formation",J2)-14),IF(ISNUMBER(SEARCH("punt formation",J2)),RIGHT(J2,LEN(J2)-SEARCH("punt formation",J2)-15),IF(ISNUMBER(SEARCH("(",J2)),IF(SEARCH("(",J2)=1,RIGHT(J2,LEN(J2)-SEARCH(")",J2)-1),J2),J2))))))
That's is a prerequisite for finding the player who did the action (running or throwing). Getting the receiver will be more difficult. Assuming that the clean description is in column U:
=IF(ISNUMBER(SEARCH("no play",U2)),"",IF(ISNUMBER(SEARCH("scrambles",U2)),LEFT(U2,SEARCH("scrambles",U2)-2),IF(ISNUMBER(SEARCH("kicks",U2)),LEFT(U2,SEARCH("kicks",U2)-2),IF(ISNUMBER(SEARCH("right tackle",U2)),LEFT(U2,SEARCH("right tackle",U2)-2),IF(ISNUMBER(SEARCH("right end",U2)),LEFT(U2,SEARCH("right end",U2)-2),IF(ISNUMBER(SEARCH("right guard",U2)),LEFT(U2,SEARCH("right guard",U2)-2),IF(ISNUMBER(SEARCH("up the middle",U2)),LEFT(U2,SEARCH("up the middle",U2)-2),IF(ISNUMBER(SEARCH("left guard",U2)),LEFT(U2,SEARCH("left guard",U2)-2),IF(ISNUMBER(SEARCH("left tackle",U2)),LEFT(U2,SEARCH("left tackle",U2)-2),IF(ISNUMBER(SEARCH("left end",U2)),LEFT(U2,SEARCH("left end",U2)-2),IF(ISNUMBER(SEARCH("pass",U2)),LEFT(U2,SEARCH("pass",U2)-2),IF(ISNUMBER(SEARCH("field goal",U2)),LEFT(U2,SEARCH("field goal",U2)-10),IF(ISNUMBER(SEARCH("punts",U2)),LEFT(U2,SEARCH("punts",U2)-2),IF(ISNUMBER(SEARCH("sacked",U2)),LEFT(U2,SEARCH("sacked",U2)-2),IF(ISNUMBER(SEARCH("extra point",U2)),LEFT(U2,SEARCH("extra point",U2)-2),IF(ISNUMBER(SEARCH("spike",U2)),LEFT(U2,SEARCH("spike",U2)-2),IF(ISNUMBER(SEARCH("kneel",U2)),LEFT(U2,SEARCH("kneel",U2)-2),IF(ISNUMBER(SEARCH("fumbles",U2)),LEFT(U2,SEARCH("fumbles",U2)-2),IF(ISNUMBER(SEARCH("to",U2)),LEFT(U2,SEARCH("to",U2)-2),"")))))))))))))))))))
Here is a play detail field. Basically gives a little more detail to the first play description that weinsteinium put together - for example if a pass was complete, incomplete, intercepted, -if a field goal was good, no good, blocked, etc. - this assumes the play type formula is in "N"
=IF(N2="noplay","noplay",IF(ISNUMBER(SEARCH("abort",J2)),"abort",IF(ISNUMBER(SEARCH("succeeds",J2)),"succeeds",IF(ISNUMBER(SEARCH("fails",J2)),"fails",IF(ISNUMBER(SEARCH("incomplete",J2)),"incomplete",IF(ISNUMBER(SEARCH("intercept",J2)),"int",IF(ISNUMBER(SEARCH("scramble",J2)),"qbscramble",IF(ISNUMBER(SEARCH("onside",J2)),"onside",IF(ISNUMBER(SEARCH("downed",J2)),"downed",IF(ISNUMBER(SEARCH("out of bounds",J2)),"k out of bounds",IF(ISNUMBER(SEARCH("fair",J2)),"fair catch",IF(ISNUMBER(SEARCH("muffed",J2)),"muffed",IF(ISNUMBER(SEARCH("touchback",J2)),"touchback",IF(ISNUMBER(SEARCH("spike",J2)),"spike",IF(ISNUMBER(SEARCH("sack",J2)),"sack",IF(ISNUMBER(SEARCH("kneel",J2)),"kneel",IF(N2="pass","complete",IF(N2="rush","rush",IF(ISNUMBER(SEARCH("no good",J2)),"no good",IF(ISNUMBER(SEARCH("good",J2)),"good",IF(N2="kickoff","K return",IF(N2="punt","P return",IF(ISNUMBER(SEARCH("blocked",J2)),"blocked","")))))))))))))))))))))))
@weinsteinium - I also changed your original formula a little bit to include sacks and int's as pass, added 2pt conversion, and a few other minor changes. Basically the goal is to show what the original play call was supposed to be. If you want to use that then that is fine - if not also fine. one that i was thinking about was should a scramble go under pass or rush - i would say for 90% of qb's it would go under pass - for guys like vick/young it would probably go under rush. Maybe just leave it as it is.
=IF(ISNUMBER(SEARCH("no play",J2)),"noplay",IF(ISNUMBER(SEARCH("abort",J2)),"abort",IF(ISNUMBER(SEARCH("TWO-Point",J2)),"2PT",IF(ISNUMBER(SEARCH("right tackle",J2)),"rush",IF(ISNUMBER(SEARCH("right guard",J2)),"rush",IF(ISNUMBER(SEARCH("left guard",J2)),"rush",IF(ISNUMBER(SEARCH("up the middle",J2)),"rush",IF(ISNUMBER(SEARCH("left tackle",J2)),"rush",IF(ISNUMBER(SEARCH("left end",J2)),"rush",IF(ISNUMBER(SEARCH("right end",J2)),"rush",IF(ISNUMBER(SEARCH("pass",J2)),"pass",IF(ISNUMBER(SEARCH("kneel",J2)),"kneel",IF(ISNUMBER(SEARCH("punt",J2)),"punt",IF(ISNUMBER(SEARCH("sacked",J2)),"pass",IF(ISNUMBER(SEARCH("kicks",J2)),"kickoff",IF(ISNUMBER(SEARCH("extra point",J2)),"extrapoint",IF(ISNUMBER(SEARCH("field goal",J2)),"fieldgoal",IF(ISNUMBER(SEARCH("spiked",J2)),"spike",IF(ISNUMBER(SEARCH("scrambles",J2)),"scramble","rush")))))))))))))))))))
@weinsteinium - very nice on splitting out the players btw.
Here is a retry of play subtype - had a couple of minor errors in the last one - this is only tested on the 2008 season so can't guarantee it for any other season.
=IF(N2="noplay","noplay",IF(ISNUMBER(SEARCH("abort",J2)),"abort",IF(ISNUMBER(SEARCH("succeeds",J2)),"succeeds",IF(ISNUMBER(SEARCH("fails",J2)),"fails",IF(ISNUMBER(SEARCH("incomplete",J2)),"incomplete",IF(ISNUMBER(SEARCH("intercept",J2)),"int",IF(ISNUMBER(SEARCH("scramble",J2)),"qbscramble",IF(ISNUMBER(SEARCH("onside",J2)),"onside",IF(ISNUMBER(SEARCH("downed",J2)),"downed",IF(ISNUMBER(SEARCH("fair",J2)),"fair catch",IF(ISNUMBER(SEARCH("muffs",J2)),"muffed",IF(ISNUMBER(SEARCH("spiked",J2)),"spiked",IF(ISNUMBER(SEARCH("sack",J2)),"sack",IF(ISNUMBER(SEARCH("kneel",J2)),"kneel",IF(N2="pass","complete",IF(N2="rush","rush",IF(ISNUMBER(SEARCH("no good",J2)),"no good",IF(N2="kickoff",IF(ISNUMBER(SEARCH("touchback",J2)),"touchback","K return"),IF(N2="punt",IF(ISNUMBER(SEARCH("out of bounds",J2)),"k out of bounds","P return"),IF(ISNUMBER(SEARCH("blocked",J2)),"blocked",IF(ISNUMBER(SEARCH("good",J2)),"good","")))))))))))))))))))))
Here is a formula for scoring type. You have to have my play subtype from above in col "J" and the play type in column J. Also need to add this in column AG for nullified plays.You could simply add this phrase to the score type formula but i think nullifed TD's is a useful stat anyway.
These work for almost all plays (total pts is within 7 of pro-footballreference total), however the scores aren't 100% right due to challenges that are reversed. Any thoughts on how to handle reversed plays for scores?
I have this for if the play was challenged in column AV
=IF(ISNUMBER(SEARCH("challenge",J2)),"challenge","")
And this if the challenge is won
=IF(AV1302="challenge",IF(ISNUMBER(SEARCH("reversed",J1302)),"Play reversed","Play stands"),"")
However, the problem is sometimes a reversal may result in a TD and sometimes it will take a TD away. The actual play result is listed after the reversal so it seems like if you could split off the name of the play there you could get it that way? But not sure how to do that.
Anyway here are the score type - the best i have now....
=IF(ISNUMBER(SEARCH("nullified",J2)),"nullified","")
=IF(ISNUMBER(SEARCH("nullified",AG2)),"",IF(ISNUMBER(SEARCH("safety",J2)),"safety",IF(ISNUMBER(SEARCH("succeeds",J2)),"2PT",IF(ISNUMBER(SEARCH("touchdown",J2)),IF(S2="fumble","Fum Ret TD",IF(O2="blocked","Blocked K Ret TD",IF(O2="int","INT RET TD",IF(O2="K return","K Ret TD",IF(O2="P return","P Ret TD",IF(N2="pass","Pass TD",IF(N2="rush","Rush TD",""))))))),IF(N2="fieldgoal",IF(O2="good","FG",""),IF(N2="extrapoint",IF(O2="good","extrapoint",""),""))))))
Actually here is a good way to take care of the reversed plays. Then only select from this data instead of the original so it doesn't mix up what play to select as this picks up after the reversed. Now almost perfect on the scoring (as in agreeing to PFR). Had to add a felix jones kick return TD vs phi that was not included in the play by play data for some reason. I'm also missing a bears "other" td somewhere and have 1 too many Dal fgs. Everything else matches up.
=IF(ISERROR(FIND("REVERSED", J2)),J2,MID(J2,FIND("REVERSED", J2)-1,999))
@buzz - Great work. Good idea to add up the game score by taking each individual score into account.
And I like the idea of a better play description. I just threw that together based on what I found in the play-by-play, I didn't think about how I'd use it.
I dont know if this is the right place to discuss findings from this wonderful data but here goes..
One piece of evidence I always wanted to know when trying to recreate the Romer analysis was if you go for it on 4th down and make it how many extra yards do you gain on average. Similarly If you dont make it on 4th down how many yards do you gain/lose on average.
This allows you to work out the risk reward of going for it rather than kicking/punting more accurately than just assuming that you make the first down exactly.
Thanks to this wonderful resource and looking at 3rd downs in 2008 (using 3rd down as a proxy for going for it on 4th down) I found that on average 6 yards more than needed were gained on successful 3rd down conversions.
On 3rd and 1 it was only 4 yards 3rd and 2 was a bit more more but from 3rd and 3 or longer it was 6 yards.
This seemed very high to me but I think it is because occasionally on 3rd down a team will make a long gain and this adds a lot to the excess yardage gained. I think the median (rather than the mean) would look more "normal" but for evaluating the benefits of "going for it" you have to factor in the possibility of a long gain.
Unsuccessful attempts that did not result in a turnover didnt show much of a pattern with unusceesful attmepts at 3 and 1 resulting in a net loss of 0.7 yards but this gradually improved as the distance to go increased.
I excluded "no play" penalties, taking a knee and spiking the ball. I din't take score, time remaining or field position into account.
I also wanted to include the full effect of turnovers i.e. the change in possession and the change in field position.
Looking at all turnovers in 2008 showed that a turnover gains a net 7 yards for the team that turnsover the ball. e.g. if you have the ball on your 20 yard line and commit a turnover the other team will take over at your 27 yard line on average. Interceptions were a bit higher and fumbles a bit lower. I included turnovers returned for a td as negative yards so in the above example -20 yards would be added to the yardage gained by a turnover.
James
James - if you don't mind what was the formula you used to determine first downs? This is what i have right now where Q is the scoring type.
=IF(ISNUMBER(SEARCH("TD",Q2)),"TD",IF(G2="","",IF(E2=E3,IF(G3=1,"1st",""),"")))
It comes up with about 8,913 regular season first downs vs 9,443 on PFR. I haven't had a chance to go back to see what might be missing.
Very interesting research btw. I agree that always assuming you only get the first down and nothing more was a pretty conservative estimate on the going for it analysis as well.
buzz
I dont have my spreasdhseet on this computer but basically I didn't do it in a single line of excel as I didnt explicitly work out first downs. Instead I identified 3rd downs and then listed the yards to go. Then I worked out how many yards were gained by subtracting the yardline of the next play from the yard line of the 3rd down play. So if it is 3rd and 3 on the 77 yard line and the next play is on the 73 yard line then 4 yards are gained so this is a first down. TDs, last play of the half, turnovers and no play penalties all had to be accounted for in different ways as my basic approch wouldn't work.
If you wanted to calculate first downs I would simply count the number of times the down column is 1 and the same team was in possesion on the previous play which should take care of most plays except fumbles on kicks, plays with two turnovers and sucessful onside kicks.
But you could probaly exclude plays where punt or kickoff is in the play description.
James
James,
One thing to keep in mind when comparing 3rd vs 4th down is that i think you have to really temper your expectations for the big gains on 4th down as teams are going to be so much more conservative on 4th down that they won't take the chance for the big play because they are already risking 30-40 yards of field position on just getting the first down. See all the 4th's that the pats converted this year. they were almost always power runs or the short route in the colts game for example. I think if teams eventually started going for it more often they would be a little more risky but i doubt it would ever get to the point of where they are on 3rd down.
I just found this site today and find it fascinating. One suggestion -
1. Buzz - I added a choice for offsetting penalties near the start of your penalty type formula in the text below but I'm not entirely sure if that is the best approach. Without that you will see the first of the two penalties that are called on a play but not the second. It may be better to not identify it as a penalty at all or to find a way to show both penalites. It really depends on how you use that info.
=IF(ISNUMBER(SEARCH("penalty",J3148)),IF(ISNUMBER(SEARCH("offsetting",J3148)),"offsetting",IF(ISNUMBER(SEARCH("illegal kick",J3148)),"ill kick",IF(ISNUMBER(SEARCH("illegal receiver",J3148)),"ill rec",IF(ISNUMBER(SEARCH("invalid fair catch",J3148)),"ill fair catch",IF(ISNUMBER(SEARCH("crackback",J3148)),"crack",IF(ISNUMBER(SEARCH("no penalty",J3148)),"no penalty",IF(ISNUMBER(SEARCH("excessive celebration",J3148)),"excess celeb",IF(ISNUMBER(SEARCH("illegal bat",J3148)),"ill bat",IF(ISNUMBER(SEARCH("illegal cut",J3148)),"ill cut",IF(ISNUMBER(SEARCH("player out of bounds",J3148)),"ill touch",IF(ISNUMBER(SEARCH("illegal motion",J3148)),"ill motion",IF(ISNUMBER(SEARCH("offensive offside",J3148)),"off offside",IF(ISNUMBER(SEARCH("intentional ground",J3148)),"int ground",IF(ISNUMBER(SEARCH("12 on-field",J3148)),"too many on field",IF(ISNUMBER(SEARCH("illegal forward pass",J3148)),"ILL forw pass",IF(ISNUMBER(SEARCH("offside on free kick",J3148)),"Off free kick",IF(ISNUMBER(SEARCH("illegal subst",J3148)),"ILL subst",IF(ISNUMBER(SEARCH("neutral zone",J3148)),"neut zne",IF(ISNUMBER(SEARCH("Taunting",J3148)),"taunt",IF(ISNUMBER(SEARCH("leverage",J3148)),"leverage",IF(ISNUMBER(SEARCH("clipping",J3148)),"clip",IF(ISNUMBER(SEARCH("roughing the passer",J3148)),"Rough Pass",IF(ISNUMBER(SEARCH("Unnecessary Roughness",J3148)),"unnec rough",IF(ISNUMBER(SEARCH("Illegal Use of Hands",J3148)),"ILL use hands",IF(ISNUMBER(SEARCH("illegal procedure",J3148)),"ILL Proc",IF(ISNUMBER(SEARCH("tripping",J3148)),"trip",IF(ISNUMBER(SEARCH("running into",J3148)),"running k",IF(ISNUMBER(SEARCH("personal foul",J3148)),"pers foul",IF(ISNUMBER(SEARCH("Illegal block",J3148)),"ILL block",IF(ISNUMBER(SEARCH("Ineligible Downfield",J3148)),"Inel downfield",IF(ISNUMBER(SEARCH("illegal contact",J3148)),"Ill Contact",IF(ISNUMBER(SEARCH("unsportsmanlike",J3148)),"Unsportsman",IF(ISNUMBER(SEARCH("illegal touch",J3148)),"ILL touch",IF(ISNUMBER(SEARCH("chop block",J3148)),"chop",IF(ISNUMBER(SEARCH("illegal formation",J3148)),"ILL form",IF(ISNUMBER(SEARCH("offensive holding",J3148)),"off hold",IF(ISNUMBER(SEARCH("defensive holding",J3148)),"def hold",IF(ISNUMBER(SEARCH("shift",J3148)),"ILL shift",IF(ISNUMBER(SEARCH("low block",J3148)),"low block",IF(ISNUMBER(SEARCH("defensive offside",J3148)),"def offside",IF(ISNUMBER(SEARCH("face mask",J3148)),"face mask",IF(ISNUMBER(SEARCH("encroach",J3148)),"encroach",IF(ISNUMBER(SEARCH("delay",J3148)),"delay gm",IF(ISNUMBER(SEARCH("roughing the kicker",J3148)),"roughing k",IF(ISNUMBER(SEARCH("inter",J3148)),"PI",IF(ISNUMBER(SEARCH("false start",J3148)),"fls strt")))))))))))))))))))))))))))))))))))))))))))))),"")
John,
Welcome to one of the best football sites around. Thanks for your post on the formula. I think that is a very good approach. Since this post I had actually added a new column to see if the penalty was offsetting, declined, or enforced as follows, i think both together are good:
=IF(R2="penalty",IF(ISNUMBER(SEARCH("offsetting",J2)),"offset",IF(ISNUMBER(SEARCH("declined",J2)),"declined",IF(ISNUMBER(SEARCH("enforce",J2)),"enforce",""))),"")
Also another formula that you might like is pass complete to:
=IF(O2="complete",IF(ISNUMBER(FIND("to ",BE2)),IF(AE2="unknown",LEFT(MID(BE2,FIND("to",BE2)+3,25),FIND(" ",MID(BE2,FIND("to",BE2)+3,25))-1),IF(ISNUMBER(FIND(" ",MID(BE2,FIND(AE2,BE2)+LEN(AE2)+4,25),1)),LEFT(MID(BE2,FIND(AE2,BE2)+LEN(AE2)+4,25),FIND(" ",MID(BE2,FIND(AE2,BE2)+LEN(AE2)+4,25),1)-1),MID(MID(BE2,FIND(AE2,BE2)+LEN(AE2)+4,25),1,LEN(MID(BE2,FIND(AE2,BE2)+LEN(AE2)+4,25))-1))),"No one"),"")
and intended receiver on an incomplete pass...
=IF(O2="incomplete",IF(ISNUMBER(FIND("to ",BE2)),IF(AE2="unknown",IF(ISNUMBER(FIND(" ",MID(BE2,FIND("to",BE2)+3,25))),LEFT(MID(BE2,FIND("to",BE2)+3,25),FIND(" ",MID(BE2,FIND("to",BE2)+3,25))-1),MID(BE2,FIND("to",BE2)+3,25)),IF(ISNUMBER(FIND(" ",MID(BE2,FIND(AE2,BE2)+LEN(AE2)+4,25),1)),LEFT(MID(BE2,FIND(AE2,BE2)+LEN(AE2)+4,25),FIND(" ",MID(BE2,FIND(AE2,BE2)+LEN(AE2)+4,25),1)-1),MID(MID(BE2,FIND(AE2,BE2)+LEN(AE2)+4,25),1,LEN(MID(BE2,FIND(AE2,BE2)+LEN(AE2)+4,25))-1))),"No one"),"")
Where BE is my formula above taking out reversed plays and AE is this formula saying the pass location.
=IF(N2="pass",IF(ISNUMBER(SEARCH("deep middle",BE2)),"deep middle",IF(ISNUMBER(SEARCH("deep right",BE2)),"deep right",IF(ISNUMBER(SEARCH("deep left",BE2)),"deep left",IF(ISNUMBER(SEARCH("short right",BE2)),"short right",IF(ISNUMBER(SEARCH("short middle",BE2)),"short middle",IF(ISNUMBER(SEARCH("short left",BE2)),"short left","unknown")))))),"")
Or finally intended reciever on an interception.
=IF(O2="int",IF(ISNUMBER(SEARCH("intended for",BE2)),LEFT(MID(BE2,FIND("intended for",BE2)+13,35),FIND("INTERCEPTED",MID(BE2,FIND("intended for",BE2)+13,35),1)-2),"unknown"),"")
I think you could probably pretty easily wrap this into the incompletion one above but i didn't think of it until afterwards and havne't gone back and done so.
Thought those might be interesting/helpful. I have some preliminary data findings but want to wait until i have all the years put together.
Thanks for the data sets.
Les
Brian - First a big hardy thank you for sharing the play by play data.
To all the responses thanks for sharing the formulas for the rest of us non excel gurus.
Being that I am one who has very limited knowledge of excel is there any chance that someone with a pre-formated excel sheet can post and share for the rest to look through the data?
I'm working on extracting the penalties from the play-by-play data you posted. I think I have it figured out -- but it doesn't fit into a single formula that I could post here. If there is a way to share an entire spreadsheet I could do that.
In the meantime I was wondering if any of you had any suggestions for format. Currently the output would be
* which team committed the penalty
* if they were offense or defense
* if the penalty was enforced, declined, or offsetting
* how many yards
I am planning to do this for up to 3 penalties per play (it wouldn't be hard to go farther, but I don't recall seeing more than 3 penalties on a single play). I could also extract who committed the penalty (the one trick would be team penalties like 12 men on the field that is not charged to an individual).
I just noticed that the team abbreviations do not always match in the data.
* Houston is "HST" in the descriptions but "HOU" in the GAMEID, OFF, and DEF
* St. Louis is "SL" in the descriptions but "STL"
in the GAMEID, OFF, and DEF
*Jacksonville is "JAX" in the descriptions but "JAC" in the GAMEID, OFF, and DEF
*Cleveland is "CLV" in the descriptions but "CLE" in the GAMEID, OFF, and DEF
* Baltimore is "BLT" in the descriptions but "BAL" in the GAMEID, OFF, and DEF'
* Baltimore is "ARZ" in the descriptions but "ARI" in the GAMEID, OFF, and DEF'
Specifically those are in the 2007 data. I think those are all the unmatching abbreviations.
That might throw off some automated data extraction that people put together. At this point I'm not sure what the best solution would be.
@Tim Folkerts - I don't think that anyone has used the team name in the description.
The easiest solution would be to correct one to match the other.
Once choice is to use the replace function in excel to change the team code in the description. Search for " SL " and replace it with " STL ". That'd be 6 replaces per spreadsheet. Kinda a hassle but not too bad.
The other choice to add new columns that map the GAMEID, OFF and DEF to the teams in the desciption.
Something like:
Corrected OFF:
=IF(E2="STL","SL",IF(E2="BAL","BLT",IF(E2="HOU","HST",IF(E2="JAC","JAX",IF(E2="CLE","CLV",IF(E2="ARI","ARZ",E2))))))
Then apply that formula to all the rows in the spreadsheet. Use the "corrected" team name when aggregating your results.
I too thank you for the information and making it "free", however as posted on this site:
If you enjoy Advanced NFL Stats, please consider a small donation to The Fisher House, a place where families of injured servicemen can stay while visiting their hospitalized heroes.
Unfortunately our freedom comes at a very high cost.
Here is some research I did on goal line running backs using the play by play data. I will post in three seperate posts as they won't all fit in one....
How much is a goal line running back worth?
If the red zone is the most important real estate in football then the goal line might be the Holy Grail. From 2002-2008 there have been approximately 8,000 plays in this sacred space (note all 2002-2008 stats from www.advancednflstats.com play by play data and 2009 stats from my own downloads). Of this amount approximately 4,400, or 600 per year, are given to running backs to attempt a TD. Most of these attempts go to “goal line backs” because as some fans lead you to believe these backs will score nearly every time. But how much better are goal line backs in reality when compared to non-goal line backs?
To answer this question I started by determining how often a running back should be expected score from a specific yard line, similar to an expected points calculation. I limited the study to all carries within the 5 yard line since these are typically considered a “goal line carry”. From 2002-2008 there were approximately 4,400 carries that met this criteria with the following conversion rates.
Yard line Conversion rate
1 56%
2 42%
3 32%
4 26%
5 20%
Using these rates you can convert every play into an Expected TD added (ETDA) by comparing the actual results to the expected results. For example, if a running back scored a TD from the 1 yard line they will be given credit for 1 TD and charged .57 TD’s since a league average running back would have scored 57% of the time thus giving them an ETDA of .43. Positive ETDA is good and negative is bad. Additionally, we can divide the actual TD’s by the expected TD’s to determine an ETDA%, which in this case would be 175% (1/.57).
After determining the ETDA for all plays in this time frame I decided to split the running backs by “goal line backs”, which I designated as the running back on each team with the most goal line carries, and “non goal line backs”. By these designations it turns out that the goal line players scored 1,243 TD’s vs. an expectation 1,209 (103%)where as the non-goal line running backs scored only 464 TD’s vs an expectation of 500 (93%), we can consider this a rough “replacement level” value. Based on these numbers it seems that running backs that are trusted to score the most goal line carries score do actually score at a slightly higher rate than non goal line running backs.
Note, how small the difference is though. The average team is expected to get between 13-25 goal line carries per year with the average right at 20 and if the distribution between 1-5 yards is uniform throughout the league they would be expected to score approximately 7.8 TD’s on those 20 carries (based on a 100% ETDA%). If a team used only running backs that made up the non-goal line running backs group they would only be expected to score 7.3 TD’s. If we compared this to the expected TD’s a “goal line back” would score (which is 8.0) you can see that a “goal line back” only scores 0.7 TD’s than a “replacement level back” for the entire season. Furthermore, given that a good portion of these runs are on a first down and the offense has more chances to score means that even less than 0.7 TD’s are lost from a team without a goal line back.
Now most will argue well “wait I only mean the good goal line backs score TD’s all the time”. So I decided to research this a little further by splitting the running backs into two fairly equal groups. All running backs with over 15 goal line carries in a given year and all running backs with less than 15 goal line carries in a specific year. By splitting the groups like this the high volume running backs scored 736 TD’s when they would have been expected to score 701 (105%). On the flip side the low volume running backs scored 971 vs an expectation of 1,008 (96%). Using the same calculations as above a team of all of the league average “best” goal line backs would score 8.2 TD’s per season where as the non-goal line backs would score 7.5 for again a difference of 0.7 TD’s, or 0.9TD’s over a “replacement level”.
No, what I what I meant by the good goalline backs is really the ELITE goal line backs score the most. So what if we limit our sample to only the running backs who were given the most goal line chances over the entire 7 year period? No small sample sizes and lots of attempts time over a long time. Were they able to score at a much higher than average rate? For this sample I looked at the top 5% of running backs in terms of total goal line carries for the entire period of 2002-2008. In this sample the running backs scored 687 TD’s vs an expected 630 for a 109% rate, which while better than the other averages is still not going to set the world on fire. This would equate to approximately 8.5 TD’s for the season, 1.2 more than our imaginary replacement level.
That said there were 3 running backs in this group that really stood out from the rest as shown below.
Player Team Expected TDs Actual TDs Inside 5 carries ETDA %
L.Tomlinson SD 56 73 147 130%
S.Alexander SEA 49 66 113 135%
P.Holmes KC 28 45 76 161%
Tomlinson, Alexander, and Holmes are clearly 3 of the best running backs in the NFL over the last decade but would you think of them as the best goal line backs as well? Maybe the best goal line back is simply the best player or maybe the player behind a line that can block for a short gain. Or maybe the best goal line back is just the freshest player? Teams are splitting their carries up now as much as or more than ever in order to keep players fresh and to avoid injuries. Maybe some of the “goal line specialists” just take advantage of this freshness opportunity to score ever slightly more often. This would be a good reason to keep a fresh back available for goal line carries since they are significant play as long as you aren’t paying anymore of a premium for this “goal line back” than any other backup running back.
Are there any alternatives that could result in a higher conversion rate without spending premium cap money on a goal line running back? First of all an offense could try a quarterback sneak from the 1 yard line more often. As noted above running backs are successful at scoring approximately 56% of the time from the 1 yard line. QB rushing attempts are successful 66% of the time resulting in an ETDA% of 119% which is better than all but the very elite running backs. Unfortunately, from the play by play data we don’t really know how many of these plays are sneaks or if a large percentage of them are rollout passes where a lane opens up in front of the QB and he can walk into the end zone. Obviously the latter are all successful attempts and are probably enough to skew the data enough to say that this is inconclusive at best.
What about simply the direction a running back runs? We know that coaches don’t always go for the most optimal plays by kicking field goals, punting, and running too often could they be doing the same with a run direction. The chart below shows the expected vs actual TD’s by run direction only.
Direction Actual Expected ETDA
LE 150 119 126%
LT 230 219 105%
LG 240 234 102%
mid 553 573 97%
RG 235 253 93%
RT 186 201 92%
RE 100 90 111%
Not specified 13 17 74%
Total 1707 1,707 100%
As expected most runs are right up the middle but are actually slightly less effective than the average of all runs. This is probably because it is ran so often that the defense is over playing it. However, the further out an offense goes on both sides of the line their chances increase. Unfortunately for the offense the further out you go the number of plays decreases. This is especially the case running wide to the left behind what is assumed to be a big blocking left tackle. Maybe it would make more sense to have a quicker goal line running back who can get outside quicker to a direction that a defense isn’t expecting as much?
Right now it appears that the defenses seem to be doing a better job of play calling at the goal line against a somewhat predictable offense. This is because the directions ran the most often have of the lowest conversion rates where as the directions that are the most successful are ran the least. The defense is essentially “selling out” to stop up the middle runs and the offense is continuing to run at this less successful part of the field. Of course if a specific team starts to run outside more often then the defenses will adjust to take this advantage away, but it will again open up the middle of the field where most teams want to run anyway resulting in a higher overall rate. Best yet this wouldn’t cost anything in terms of cap money.
In conclusion, it appears that the running backs that the coaches feel are most skilled at running at the goal line do outperform the non goal line running backs. Unfortunately, the margin of over performance is very minimal and on average may not produce more than 2-4 points a year per team. However, considering that a team may already be spending money on multiple running backs it might not be a bad idea to get one that can be more successful at the goal line, as long as that running back doesn’t come at a premium. Finally, if offensive coordinators started calling more plays to run behind the ends they may increase their overall conversion rates since these calls are currently more likely to be successful and in the long run might increase their conversion rates on runs up the middle.
Wow, great job Buzz!
I've been working on cleaning up my data a bit, I'll try to post a parsed out csv when I'm done
@Buzz one more thing, How did you get the name of the player running the ball? The way I posted earlier had trouble with things like "Arz defers the kick-off" and "J.Russell back in a QB".
I worked out a better way using a VBA macro but I was wondering how you did it.
weinsteinium - unfortunately, don't have the file with me right now. Basically what I did was added a few more conditions to your original formula that you posted then manually fixed the rest. There were about 200 per season and took about 10 minutes each season. I figured I was going to spend more time figuring out better formulas than to just fix them manually.
I would post my csv file but am not very savy with those types of things. Although I did learn a few things pulling down the 2009 data, if you have a specific stat that you are having issues with i would be more than happy to share a column though.
After looking at all of those crazy situations, I decided that it would be easier to look for a word like "pass" and then find the word before it to see who threw it. That reduced it down to one play that needed to be fixed (Someone in as 3rd QB or something like that).
Here my VBA macro to parse out the QB...it is looking for the description in column 13 ('l') and putting the value in column 17 ('p'):
Sub getQB()
For i = 2 To 43000
buf = Worksheets("2008_nfl_pbp_data").Cells(i, 13)
indx = InStr(buf, " ") - 1
Do While indx > 0
word = Left(buf, indx - 1)
If word = "pass" Then
Worksheets("2008_nfl_pbp_data").Cells(i, 17) = prevWord
indx = -1
ElseIf word = "sacked" Then
Worksheets("2008_nfl_pbp_data").Cells(i, 17) = prevWord
indx = -1
ElseIf word = "spiked" Then
Worksheets("2008_nfl_pbp_data").Cells(i, 17) = prevWord
indx = -1
ElseIf word = "kneel" Then
Worksheets("2008_nfl_pbp_data").Cells(i, 17) = prevWord
indx = -1
Else
prevWord = word
buf2 = Right(buf, Len(buf) - indx)
buf = buf2
indx = InStr(buf, " ")
End If
Loop
Next i
End Sub
Errr, I just realized that this will report some strange data on run plays that are listed as a backward pass. If you run the QB one first and then follow it up with the running play (posted below) one it will clean up the player. It would have been better to use the play type and only look for "pass" on passing plays.
Together they produced only one row that needed to be cleaned up (I only ran it against 2008):
Sub getRush()
For i = 2 To 43000
buf = Worksheets("2008_nfl_pbp_data").Cells(i, 13)
ReDim allWords(100)
j = 1
indx = InStr(buf, " ")
Do While indx > 0
word = Left(buf, indx - 1)
allWords(j) = word
j = j + 1
buf2 = Right(buf, Len(buf) - indx)
buf = buf2
indx = InStr(buf, " ")
Loop
wordCount = j
For k = 1 To wordCount
word = allWords(k)
If word = "up" Then
nextWord = allWords(k + 1)
If nextWord = "the" Then
nextNextWord = allWords(k + 2)
If nextNextWord = "middle" Then
prevWord = allWords(k - 1)
Worksheets("2008_nfl_pbp_data").Cells(i, 17) = prevWord
k = wordCount
End If
End If
ElseIf word = "right" Then
nextWord = allWords(k + 1)
If nextWord = "end" Then
prevWord = allWords(k - 1)
Worksheets("2008_nfl_pbp_data").Cells(i, 17) = prevWord
k = wordCount
ElseIf nextWord = "tackle" Then
prevWord = allWords(k - 1)
Worksheets("2008_nfl_pbp_data").Cells(i, 17) = prevWord
k = wordCount
ElseIf nextWord = "guard" Then
prevWord = allWords(k - 1)
Worksheets("2008_nfl_pbp_data").Cells(i, 17) = prevWord
k = wordCount
End If
ElseIf word = "left" Then
nextWord = allWords(k + 1)
If nextWord = "end" Then
prevWord = allWords(k - 1)
Worksheets("2008_nfl_pbp_data").Cells(i, 17) = prevWord
k = wordCount
ElseIf nextWord = "tackle" Then
prevWord = allWords(k - 1)
Worksheets("2008_nfl_pbp_data").Cells(i, 17) = prevWord
k = wordCount
ElseIf nextWord = "guard" Then
prevWord = allWords(k - 1)
Worksheets("2008_nfl_pbp_data").Cells(i, 17) = prevWord
k = wordCount
End If
End If
Next k
Next i
End Sub
Wow.. these data are amazing! Thank you!
I cannot download anything but 2002 and 2007. IS it possible for you to reupload them or send them to me directly?
Thanks,
I have noticed a few mistakes in the data. I'm posting them here for now. Would it be better to email these to Brian to create an improved set of data?
Obviously incorrect data will lead everyone to incorrect results.
***********************************************
The second line listed here is wrong. The first line shows CIN on offense scoring a touchdown. But then is shows BAL on offense scoring a 2 pt conversion.
20070910_BAL@CIN 4 8 53 CIN BAL 1 7 7 (8:53) C.Palmer pass short left to T.Houshmandzadeh for 7 yards TOUCHDOWN.
20070910_BAL@CIN 4 8 53 BAL CIN 7 TWO-POINT CONVERSION ATTEMPT. R.Johnson rushes left end. ATTEMPT SUCCEEDS.
*****************************************
The following lines from 2007 all have the offensive teams missing from the "off" column.
20070909_DET@OAK 4 4 22
20070910_BAL@CIN 4 8 53
20070923_MIA@NYJ 4 1 23
20070923_ARI@BAL 2 31 13
20071007_TB@IND 4 9 24
20071014_HOU@JAC 2 33 56
20071021_TEN@HOU 4 1 41
20071021_TEN@HOU 4 1 32
20071021_TEN@HOU 4 1 32
20071021_TEN@HOU 4 1 9
20071021_TEN@HOU 4 1 6
20071021_TEN@HOU 4 1 6
20071028_HOU@SD 4 4 44
20071104_WAS@NYJ 2 35 25
20071104_WAS@NYJ 2 34 49
20071104_WAS@NYJ 2 34 17
20071104_WAS@NYJ 2 33 41
20071104_WAS@NYJ 2 33 8
20071104_WAS@NYJ 2 32 28
20071104_WAS@NYJ 2 32 0
20071104_WAS@NYJ 2 31 54
20071104_WAS@NYJ 2 31 12
20071104_WAS@NYJ 2 31 4
20071104_WAS@NYJ 2 30 58
20071104_GB@KC 4 5 23
20071111_BUF@MIA 3 15 4
20071129_GB@DAL 1 45 10
20071209_OAK@GB 3 24 24
20071209_IND@BAL 4 14 15
20071223_TB@SF 3 24 43
20071230_SEA@ATL 4 0 59
20080105_JAC@PIT 4 10 29
****************************************
And as I pointed out earlier, the abbreviations for the teams are not consistent.
For some reason I am having trouble accessing the data on Google documents. I would really be interested in getting a hold of it. Also, thanks so much for providing it. It's a phenomenal resource. In addition, I was wondering if you would ever be willing to provide the play by play data with your breakdown of WPA per play included. I would love to have access to this if possible. Thanks again for your generosity.
@Tim Folkerts - The best I can suggest is posting the corrected csv's on google so people can download them instead of Brian's.
@John - What browser are you using, I am still able to download the play-by-play data.
Weinsteinium, thanks so much for the browser suggestion. I had the same problem that the anonymous user mentioned with Google Chrome. I could only download 2007 and 2002 for some reason. But I tried the rest with Firefox and it seemed to work. Thanks again for the help.
Brian,
Sorry to reiterate the question from before, but is there a way for you to make the 2009 data available as well? Also, I was wondering which site you got this data from that made it easiest to parse the data into Excel the way you have it? Thanks.
@John you can get the play-by-play info from nfl.com like so:
http://www.nfl.com/liveupdate/gamecenter/54467/PIT_Gamebook.pdf
Or from espn.com:
http://scores.espn.go.com/nfl/playbyplay?gameId=290910023&period=0
Neither of them is in the exact format that Brian supplied so he must have massaged the data a bit. If you take the 2009 data and create csv's out of them, post them on google for the rest of us to see.
Weinsteinium,
I came up with the same solution in trying to get the 2009 data. Of course it's not too hard, just import from web in the data tab in Excel and then parse. I was just wondering how much of the data Brian massaged from those obvious sites, or whether he found a site with a better outline for the way his data was arranged. If I do end up arranging the 2009 data I'll definitely post it. Thanks for the suggestion.
Hey guys, would it at all be possible next season to document some stats that no one else is recording? They are really simple and can be seen in the box score. One of them is Total turnovers.
A total turnover is any event in which the ball is turned over without 6 points being first awarded to the offensive team. These include the classically considered turnovers of interceptions thrown and fumbles lost. In addition to this, you would have to add Field Goals Missed/Blocked, Safeties Allowed (If the team gave up two points to their opponent), turnover on downs, punts/punts blocked, onside kicks lost, and turnovers due to time. In addition other events are counted as half-turnovers as well; These are missed extra points, failed 2 point attempts, and successful field goals (Since they don’t score 3 points), and two point conversions allowed (Charged to the team on defense).
A Turnover due to time is where a team in possession of the football who is also trailing fails to score and get the lead before halftime and due to the coin toss result or own choice, has to kickoff to start the 3rd quarter. This counts as a complete turnover. However, if another event causes a turnover as time expires, say a missed field goal, that turnover is counted and the TDT is nullified. If the game is tied or the team kicking off at the start of the third quarter was in possession of the ball and ahead, it does not count as a turnover.
In addition the team that loses the game gets a turnover due to time as well. This can occur so that the team that won will win the turnover battle. Like a touchdown that happened as time expired in the 4th quarter that gave the other team the lead. That would count as a turnover due to time on the defensive team that allowed the score since they no longer will have the ability to come back.
I have proven that when these stats are collected in a game and compared and contrast the team with the least total turnovers always wins the game.
I have also shown that when counted as a whole (from broken data from the NFL and College Football stat websites) that teams with the least total turnovers tend to be better teams than those with greater numbers of turnovers. I have the spreadsheets done in Excel if you would like to see them. My hope is that one day coaches will see these numbers and base their thinking on them. But even if that doesn’t happen, I’ll be happy either way.
Please let me know what you think, and I would be happy to assist you in the data collection.
Rushingfn@yahoo.com
@Rushingfn - I think that you can calculate most of that with the stuff that Buzz and I have posted. The ones where the team that is losing at the half is charged with a turnover if they kick the ball away to start the 3rd quarter is a bit more difficult but I think that you could do that too.
@John Candido - The espn play-by-play doesn't always have the timestamp on each play. Without that you can't tell what minute of the game it is.
The NFL.com play-by-play has the player number in front of their name like 17-J.Delhomme which will make it harder to match to the 2002-2008 data that we already have. On the other hand it might help out with players with common names like S.Smith.
If you want to use the NFL.com data but don't want to deal with PDF's you can find the play-by-play in html format here:
http://www.nfl.com/gamecenter/2009091000/2009/REG1/titans@steelers/analyze/box-score
Hit the Play-By-Play link. For some reason I can't link to it directly (the play-by-play button is highlighted like it's selected but you still get box score info).
Dear Brain:
Doesn't the premium subscription I pay for include your doing all the analyes for me?
How do I get my Tenges back?
@weinsteinium
While the NFL.com play-by-play is probably easier to parse in HTML format, there's some extra useful data in the gamebook. Specifically, the weather info, stadium info and lineups -- particularly the offensive and defensive lines -- are pretty useful for doing this type of analysis.
Great breakdown Weinsteinium, and thanks so much again for all of the hard work Brian. You're the best.
Hey everyone, just wondering...I'm currently extracting various data I'm interested in for analyses I'll be posting on Niners Nation, and, while going through the data set for 2007, I noticed that the regular season only has 254 games. I went through and tried to figure out the 2 games I'm missing & I've come up with the following:
Week 5: ARI @ STL
Week 5: DAL @ BUF
Does anyone else have this same issue in the 2007 data set that they downloaded? If so, then Brian, is there any chance you can reparse the data for 2007 so that it's accurate? I mean, based on my discovery & the data errors detailed in previous comments, it seems like 2007 is non-trivially unreliable.
Thanks in advance.
There are about 20 total missing games in the entire set for all years. That's not due to the parsing, it's due to missing or mis-formatted source data. Sorry.
OK...good to know. Thanks for the quick response, Brian. Since you seem to know this off the top of our head, any chance you could list the games that are missing? If not, no worries. I'll figure it out.
Any suggestions on where to upload user-analyzed versions of the data files? I'd like to have a place where guys can post corrected data and processed versions. Google docs is good, but I'd like to use a more open, collaborative site. The only pre-requisite is that I am not required to be in the loop for every upload.
Danny-I don't have the games identified. I just know that when they totaled up, 20 were missing. I checked a sample of them out, and the structure of the html files were screwy. You can probably fill in any missing games with an easy copy and paste in to Excel, then use text-to-columns to parse out the down, distance, etc.
Check out the Second Week of 2002. I believe there are a few missing games there as well that I came across when I was going over the data.
the download for the 2009 data does not seem to be working
I am not able to download the 2009 data either, and you should also update the contact e-mail on your blog as my attempt to email you bounced back.
There is currently some problem at Google docs. Not sure how to fix. Attempts to re-post 2009 have been unsuccessful.
2009 might be working now. If someone successfully downloads, please let me know.
2009 worked for me. Appreciate the effort.
Thanks, I was able to download 2009 now as well. Thanks again for posting. You might consider a Windows SkyDrive (available with a hotmail account) as a collaboration site. That way you can add "friends" who have access to upload and modify files on the site, and everything can be made available to the public to view.
I have an excel file that takes similar data and parses out a ton of information. I'm sure I could apply it to these tables. Would anyone like to collaborate.
I.e., my parsing file teases out the passer, rusher, yards, where a drive starts and ends etc...
But it is somewhat raew and a bit sloppy as I have about 100 columns,
Luis, I compiled the excel files that Brian posted and added most of the formulas from helpful commenters in this thread and modified some of my own as well. In any case, my spreadsheet now returns play type, play subtype (complete or incomplete), play call (e.g. = pass when play type = INT), fumble/result, penalty/type/decision, challenge/decision, score type, passer/rusher, run direction, pass location, yards gained, intended receiver when complete/incomplete/intercepted, etc. all for the 2002-2009 seasons in one spreadsheet.
If you would like, I'd certainly love to collaborate on this, because some of my formulas don't parse all of the information correctly. Plus, adding an additional column such as play formation and etc. are in the works but needs heavy work on.
My Excel skills definitely still need some work, so if there's anyway we can compile and put together the ultimate PBP sheet, that would be awesome.
Albert, I would love to collaborate. You have some info I haven't added yet - and I may have some for you.
I have some other data as well. I have about 12 years of spread vs result data. Colleges and draft rounds of players - etc.....
Like you, my parsing never takes care of 100% and sometimes requires manual adjustments......
email me at ldeloureiro@gmail.com
Sounds great.
For anyone who is interested, I have analyzed these spreadsheets and written code to extract all of the following stats. If you would like a copy, please e-mail me at statpredictor@gmail.com. If any of you have any spreadsheets or csv files with additional stats for any sport, I'd be very grateful for receiving those in exchange.
Play Type; Formation; Tacklers; Asst Tacklers; Play Aborted; Runner; Passer; Receiver; Kicker; Returner; Direction of Pass or Run; Incomplete/Complete Pass; Pass Defended By; Pass Intercepted By; Fumble Recovered By; Result of Play (FG/XP); Holder; Center; Kick Blocked By; Play Distance; Location of Reception (thrown or kicked); Location of Tackle; Penalty on the play; Penalty Description; Play not counted;
YES! AWESOME
Hi,
I just stumbled upon this page and I think it's a very interesting initiative. Unfortunately when I click on any of the download links all I get is a blank page... (tried multiple browsers/platforms). Any ideas as to what I may be doing wrong?
qsi-it's still working for me. Not sure what the issue might be. Can you access Google Docs otherwise?
Brian,
I can access spread conversions without a problem, but your links keep coming up with a blank page. I've tried Chrome, IE and Safari on XP-64 and Windows 7-32bit, but no luck. Generally I haven't had a problem accessing Google Docs (although I've never created one). Are there geographic restrictions on Google Docs? I'm outside of the US, but I'm really clutching at straws here...
I'll try a Mac next.
Thanks!
The links don't work for me either. Nothing but blank pages.
The links from this page should take you to a google docs page with a Download link. You can click on that and your browser will ask you if you would like to open or save the file.
If that's not working for you, try right-clicking on the Download link, and choosing Save link as... or Save Target as...whatever your browser calls it.
It's working for me, and all the downloads are open to anyone, ie. you don't need a password or invitation.
I only get a completely blank page without anything showing or anything to click on. "View Source" also comes up with a blank document.
Very strange...
Also I tried this on a Mac with Safari and get the same blank page.
When all else fails, try the absurd. In a last desperate attempt to download the data, I right-clicked on a link and opened it in an "Incognito" window in Chrome. For some reason, this works. Bizarre.
In any case, thanks for making this data available!
I have a script that parses all the data and puts it into a database (SQLite, which can be converted back to Excel). It splits the data into side of the field the play was run, QB, RB, tacklers, etc. Is this something people would want?
Hey guys...
You can download 430,000 plays with well over 100 variables per play in a CSV file at my site (for free) http://www.armchairanalysis.com/nfl-play-by-play.html.
It's been triple checked for accuracy and includes a breakdown of penalties and player positions as well.
Regards,
Dennis
Sorry: try this link..
http://www.armchairanalysis.com/nfl-play-by-play-data.html
Does anyone know how to obtain a real time play by play data source?
it's amazing THANK YOU SO MUCH
Hi Folks, Great site and my first post. I have play-by-play data for the years of 2001 and 2000. It is in somewhat of a messy format and frankly, I don't have the time (nor skills) to sort it into the formats used here.
If you'd like to include it into the data set, I'll send it to you. Rather thn post my e-mail address in this public place (that has been a disaster in the past), I'll post it to Brian's address.
thanks for making this data public.
ilovelevere: I would love to check it out. I'm about to have my friend help me import everything into a SAS dataset as well, if that helps anyone. You can reach me at johnnyletter75@gmail.com (old email I still use - don't want to get spammed!)
ilovelevere: I use Stata and am about to start my Masters Thesis on NFL play calling over the last 10 years. I'd love to get those .do files from you if you don't mind sharing them. my email address is: sohampatel1989@hotmail.com. Thanks so much!
I am wondering if anyone has already set this data up so it's in the format of by player per week? I am about to import to SQL, but wanted to know if anyone had already done this?
Thanks!
Looks like there is something wrong with the 2010 link...
I just finally got around to trying to play with this data and was hit by the errors in the scores. Has anyone put together a scrubbed dataset with these errors removed?
Example: 2003/09/28 - shows Trent Green kneeling down for the tie in the 4th quarter against baltimore. The root cause seems to be that a Dante Hall touchdown didn't count in the score.
I just finally got around to trying to play with this data and was hit by the errors in the scores. Has anyone put together a scrubbed dataset with these errors removed?
Example: 2003/09/28 - shows Trent Green kneeling down for the tie in the 4th quarter against baltimore. The root cause seems to be that a Dante Hall touchdown didn't count in the score.
http://www.armchairanalysis.com/nfl-play-by-play-data.php
This has great data and is free except for 2012, but that will be free after the season ends.
where's the expected points data?
Will put in all of the data later (just found this a few minutes ago), but here are the results of all 4th-and-1 plays in 2012.
Pass Run All
Success 20 55 75
Fail 8 21 29
Pass Run All
Success 71% 72% 72%
Fail 29% 28% 28%
Remarkably efficient. I wonder if it's always been this way or if coaches have evolved.
You're missing a few plays, especially those with complicated events. For example this blocked punt in the TB-KC 2012 Week 5 game (Description from PFR):
Michael Koenen punts, blocked by Shaun Draughn, recovered by Michael Koenen and returned 1 yard. Josh Freeman fumbles, recovered by Edgar Jones at TAM-11 and returned for 11 yards, touchdown
There's no cell for this scoring play in your CSV. The subsequent extra point row has the 7-point scorechange with no mention of the actual TD play.
Why are TD values assigned to extra point and 2pt conversion tries anyway? The 6-point TD should be assigned to the TD play and a 0, 1 or 2 for the next play depending on the outcome. Each play should get its own value for the number of points it produced. The way you have it now (at least for 2012 which is the only year I've looked at) doesn't make sense.
Thanks for the effort of putting this together, but don't even bother putting it out if it's not complete and accurate. How's anyone supposed to do any meaningful analysis with faulty data?
@djnumbers
"Michael Koenen punts, blocked by Shaun Draughn, recovered by Michael Koenen and returned 1 yard. Josh Freeman fumbles, recovered by Edgar Jones at TAM-11 and returned for 11 yards, touchdown"
If you're going to nitpick the data accuracy, at least pick a play that another source gets right. Do you really think Josh Freeman was in on a punting play?
Looking through the 2002 season, there's an oddity around touchdowns and extra points. It seems that the 6 points for the touchdown are bundled with the extra point, and the score is not updated until the extra point is complete.
It seems this might result in bugs, as in the Oct 20, 2002 game between Dallas and Arizona. In the third quarter, with a score of Arizona 6 - Dallas 0, Dallas scored a touchdown (row 13900) but "aborted" the extra point (row 13901). The 6 points for the Cowboys are not recorded in the data.
The game eventually went to overtime, with the Cardinals kicking a winning field goal in OT for a final score of Arizona 9 - Dallas 6, but the data here records it as Arizona 6 - Dallas 0.
Hi --- the 2010 link doesn't seem to be working. All the others worked just fine. thanks for this!
james.
Thanks for the data. +1 on getting the 2010 data.
Here is the breakdown of most incompletions by a QB to a receiver over the dataset. http://t.co/NjRkBQxW
Not to be skeptical, but I took a look at the first file (2002 data) and scrolled straight to the bottom. The very first game has the wrong score (Oakland TB 34-21 final, whereas actual is 48-21, as seen here: http://scores.espn.go.com/nfl/recap?gameId=230126027). It is difficult for me to justify spending time analyzing this data set if I can't be sure it is correct...
By game, I mean Super Bowl...
I'm working on getting this data into a SQL 2012 Database and I ran into a loading issue within the "sec" column where the entry is ".B" or ".P" This occurs around like 775. Can you update your post with any data nuances like that?
The 2010 link is still accessible, use the file menu and choose download.
Post with code and graphs dealing with incomplete passes to receivers averaged over seasons together. http://www.jesse-anderson.com/2013/01/nfl-play-by-play-analysis/
What is the copyright license for this data? I could put some work to make it accessible via database, but I'd only want to invest the effort if this was under some sort of license that permitted unrestricted distribution.
The data is provided for research purposes only. I've chosen to present it in .csv format precisely so it does not compete with easy-to-reference commercial providers with licenses.
first off, amazing, i just threw up in my mouth at the thought of how little work i'm going to get done over the next few days. thank you.
second, commenters, and i say this as an excel magician who loves it to death, stop it stop it stop it. perl, man. python. regex. nut up. nested nested nested ifs are bad bad bad practice. array sums are great if you don't mind all four of your processors doing nothing else for ten minutes.
hashes of hashes, people. taste the freshness.
Thank you for publishing this! I have been looking for a good source of play by play data to do a few posts for my blog and I finally have it! Great work!
post showing % of plays ran no huddle by team by season:
link: http://sportsdataviz.com/percentage-no-huddle-plays-by-nfl-team-by-season-2002-2012/
This data is awesome.
Has anyone done any cleaning on top of what's been released?
I was just looking at the games that had < 100 plays, and it looks like the 2003 NE/Colts game was missing: http://www.nfl.com/gamecenter/2004011800/2003/POST20/colts@patriots#tab=analyze&analyze=playbyplay
Did anyone else notice the 2002 Week 2 missing data? There is only game (MIA@IND) from what I saw, then it went directly into IND@HOU. Am I missing something or did other people catch that as well?
Thanks for providing this data, its great. Do you have plans to post data for the remainder of the 2012 season?
Yea Greg it looks like 2002 is missing an entire week of games. I'm also seeing some missing games in other seasons as well. Brian, any plans to update the data?
Thanks for this, this is awesome. Any chance you can include WPA and EPA on the databases? Thanks.
hey do u play any fantasy football contests eh?
could you email me back plz , thanks
itsoneshiningmoment@yahoo.com
Thank you so much, Brian! You are a truly awesome and benevolent guy!
I was wondering if anyone has reduced these to game totals,
such as yards passing, yards rushing, etc.
If anyone has done this, or would be interested in doing it
for a reasonable fee, please drop me a note and I'll send
you a list of exactly what I'm looking for. Looking for
the 2010, 2011, 2012 seasons. Thanks
navarre_jj@yahoo.com
Brian:
Thanks for your wonderful work compiling these datasets.
In the same spirit in which you've provided this data, here's some work-in-progress that I'm doing on cleaning, parsing and aggregating the 2012 data. My code uses the free, open-source R statistical software, so anyone could use it.
Note that I'm only doing data cleaning in the sense of internal consistency. (No, Roethlisberger didn't run a play for Seattle last year; it just felt like it on that interception.) I'm not doing the legwork that would be involved in validating the data.
I'm working only with the 2012 data for now. I'm probably a bit over half way to a comprehensive unpacking of the play descriptions. I might not have too much that hasn't been covered in the Excel code that others have posted in the comments above, but I hope to get the comprehensive job done soon (a few weeks), as I'm over the learning curve for performing the extraction systematically and working around the data's idiosyncrasies. However, I thought that I'd make my work-in-progress available so others can run with the ball in case I fumble.
My code includes the data inspection and data cleaning steps that I'm taking, and aggregates the data on both a per-game and per-quarter basis. As I've left my data inspection routines in place, there's a fair bit of mess hanging around. On the other hand, it provides something of a tutorial for newcomers on how to do this stuff.
You can find my code here:
https://www.dropbox.com/s/cvxtwy03zeow0av/2012%20NFL%20play-by-play%20data%20cleaning%20parsing%20aggregation.R
Thanks again for your awesome work, Brian!
2010 data is not available, is there something wrong with the link?
great work :D
how do you accumulate this/ is there any chance of an update for 2013 season soon?
This is great stuff...wish I had come across it years ago!
I do have a question about a specific play that happened this past weekend and why it was treated as it was.
Minnesota @ Dallas Nov 2, 2013, 4th Qtr, 22 seconds left:
DE Everette Brown knocked the ball out of Christian Ponder's hand as he was attempting to pass(fumble), Ponder recovers fumble while on his feet and then goes down to his knees once he recovered the ball with Nick Hayden the first to down him. E.Brown never touches Ponder, but apparently got credit for the sack while Hayden got credit for an unassisted tackle. Ponder could have run with the ball and until Hayden or anyone else tackled him.
Here's the play by play info:
http://www.nfl.com/gamecenter/2013110302/2013/REG9/vikings@cowboys#menu=highlights%7CcontentId%3A0ap2000000276450&tab=analyze&analyze=playbyplay
2-10-MIN 42 (:22) (Shotgun) 7-C.Ponder sacked at MIN 35 for -7 yards (71-E.Brown). FUMBLES (71-E.Brown), and recovers at MIN 35. 7-C.Ponder to MIN 35 for no gain (96-N.Hayden).
Highlight is play #3 at http://www.dallascowboys.com/multimedia/videos/Upon_Further_Review_Film_Breakdown_Of_Week_9/b60e77e8-ce6f-4fdf-9b83-adc1c31aacc6
Why did Brown get the sack and not Hayden? Nothing he did caused Ponder to go to the ground except his desire to not get creamed by Hayden.
Causing the quarterback to fumble counts as a sack. This is why they use the term strip-sack a lot.
Although I think it would be interesting if Ponder picked up the ball and then threw it, would they then be awarded a sack even though the ball was perhaps completed?
That I'm not sure about.
How do you compile this data? I am looking for a similar data set for college football and having a hard time finding it -- thinking of compiling my own. Any suggestions / knowledge of some one else who has already done this?
I should specify: the play direction (run left, pass middle, etc) is something I haven't found elsewhere.
I am definitely going to begin some work on this over the winter break. I am going to school for computer engineering and see that the description could "easily" be parsed and categorized to provide data readily usable for statistical analysis. There is already a person who has posted a more comprehensive csv on GitHub (github.com/veltman/nflplays). I want to expand on his compilation and associate the data with specific players as well. I'd also like to provide a basic script (python, perl, ...) that could do this automatically for new sets of data and that will also create a log spreadsheet with anomalies to be sorted out by the user (something I think is not in the compiled sheet previously mentioned).
Any chance you have the data through week 16?
Will there be an update to 2013 to include entire regular season?
I am curious about how and where do you get this data from ?
Hi Brian,
Any response to the question about the inaccuracies of this data from SB37? The score and play-by-play data from the third and fourth quarters is not correct, and I was just wondering what might be the case there. I'd love to use this data for an independent study, but would like to know how reliable it is, if possible! Thank you.
Thomas
Thanks for this - I'm about to get lost in this for 6 months.
Hi there,
I'm currently working on a project for my Operations Research class with this data. It seems that the data for the 2010 season is not opening (I've tried on a couple different computers). Is there anything you can do about that? Otherwise this is an awesome resource.
-Liam
Do You Need A Loan At 3% Rate To Pay Off Bills Or Start Up Business.? our customers comfort and happiness is our goal.Contact us Now, This is to inform you that we offer all types of Loan (Business, Personal, Consolidation, etc) For further details kindly respond to our e-mail:
Sadek.loanfirm008@yahoo.com
Mobile: +919958413792
Fix 2010, and please update Season for 2013
Any idea when we can access the rest of the 2013 data?