Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Every NFL play for the past 10 years in CSV format

advancednflstats.com

Post reply on HN
Print view View original

41–50 of 105 posts

Re: Every NFL play for the past 10 years in CSV format

#41

Post by danvoell »

ImJasonH wrote: post #34

I've started uploading these CSVs to a public Google BigQuery dataset called [nfl], so you can run queries over them like this: SELECT off, COUNT(off) AS count FROM [nfl.2012reg] WHERE description CONTAINS "INTERCEPTED" GROUP BY off ORDER BY count DESC (This counts the number of plays that resulted in an interception by the team that threw the interception, sorted from most to fewest INTs)

I'm new to BigQuery, how do I access a public dataset? I ran the query and got the error Not Found: Dataset 578707073226:nfl

Re: Every NFL play for the past 10 years in CSV format

#42

Post by fennecfoxen »

fleaflicker wrote: post #25
edw519 wrote: post #9

From Line 42536 of the 2008 CSV file: 20090201_PIT@ARI,2,30,18,ARI,PIT,1,1,1,(:18) (Shotgun) K.Warner pass short middle intended for A.Boldin INTERCEPTED by J.Harrison at PIT 0. J.Harrison for 100 yards TOUCHDOWN. Super Bowl Record longest interception return yards. Penalty on ARZ-E.Brown Face Mask (15 Yards) declined. The Replay Assistant challenged the runner broke the plane ruling and the play was Upheld.,7,10,200…

You'll find that the text descriptions aren't consistently formatted. It's tough to extract structured data from all play descriptions. For example, first initial plus last name does does not uniquely identify a player. You'll need accurate roster data first, and even then there are clashes. We store play data by its structured components (players involved, play type, player roles, etc) and then derive the text descr…

"It's tough to extract structured data from all play descriptions."

Which means you can treat it a bit like a text mining program. NASA had a text mining contest in 2007 as part of the SIAM conference on data mining which was really similar - instead of football plays it was textual descriptions of aeronautics incident reports and their classification. There were several papers that came out of that (I was with a group that did one of them, using an approximate nonnegative matrix classification approach - got beat out by some ensemble approaches).

Anyway - if you'd like to do something with unstructured football play descriptions, text mining might be able to empower you to some extent without going through a full manual analysis, and those papers could be a good starting point. I think some of them ended up in a volume titled _Survey of Text Mining II_.

Re: Every NFL play for the past 10 years in CSV format

#43

Post by ImJasonH »

danvoell wrote: post #41
ImJasonH wrote: post #34

I've started uploading these CSVs to a public Google BigQuery dataset called [nfl], so you can run queries over them like this: SELECT off, COUNT(off) AS count FROM [nfl.2012reg] WHERE description CONTAINS "INTERCEPTED" GROUP BY off ORDER BY count DESC (This counts the number of plays that resulted in an interception by the team that threw the interception, sorted from most to fewest INTs)

I'm new to BigQuery, how do I access a public dataset? I ran the query and got the error Not Found: Dataset 578707073226:nfl

Sorry, I didn't actually make it public it seems. Should work now.

Re: Every NFL play for the past 10 years in CSV format

#44

Post by brianbreslin »

stuff4ben wrote: post #36
kevinburke wrote: post #22

I wrote a small wrapper around the 4th down calculator on that site, which should help you figure out if your team should go for it on 4th down: http://downanddistance.herokuapp.com/

Need a bounds check or two in there. Tried setting the number of yards you need to 1 and the yards away from the endzone to 99 and it threw up a nice exception. Cool calculator though!

As a Madden (game) aficionado my first thought was "ALWAYS go for it on 4th!" but then again I play super recklessly...

Re: Every NFL play for the past 10 years in CSV format

#45

Post by danvoell »

ImJasonH wrote: post #43
danvoell wrote: post #41

Earlier quoted context omitted.

I'm new to BigQuery, how do I access a public dataset? I ran the query and got the error Not Found: Dataset 578707073226:nfl

Sorry, I didn't actually make it public it seems. Should work now.

thanks!

Re: Every NFL play for the past 10 years in CSV format

#46

Post by JL2010 »

fleaflicker wrote: post #25
edw519 wrote: post #9

From Line 42536 of the 2008 CSV file: 20090201_PIT@ARI,2,30,18,ARI,PIT,1,1,1,(:18) (Shotgun) K.Warner pass short middle intended for A.Boldin INTERCEPTED by J.Harrison at PIT 0. J.Harrison for 100 yards TOUCHDOWN. Super Bowl Record longest interception return yards. Penalty on ARZ-E.Brown Face Mask (15 Yards) declined. The Replay Assistant challenged the runner broke the plane ruling and the play was Upheld.,7,10,200…

You'll find that the text descriptions aren't consistently formatted. It's tough to extract structured data from all play descriptions. For example, first initial plus last name does does not uniquely identify a player. You'll need accurate roster data first, and even then there are clashes. We store play data by its structured components (players involved, play type, player roles, etc) and then derive the text descr…

I had asked a question on stack-overflow a while ago asking for some guidance on parsing this exact kind of stuff. http://stackoverflow.com/questions/8198923/natural-language-...

Re: Every NFL play for the past 10 years in CSV format

#47

Post by danso »

This looks like great fun...Judging by some of the sample entries, it will also be an instructive example of the limitations of CSV and why serious analysts who want to work with unstructured data need to know a scripting language, or at least regexes.

Sample description field: > 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

In the comments section of the OP, someone posted this sample Excel function:

    	=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"))))))))))))))))))
Dear god, at what point do people finally realize that it's worth learning some simple scripting to work with text files?

Re: Every NFL play for the past 10 years in CSV format

#49

Post by snake_plissken »

ImJasonH wrote: post #34

I've started uploading these CSVs to a public Google BigQuery dataset called [nfl], so you can run queries over them like this: SELECT off, COUNT(off) AS count FROM [nfl.2012reg] WHERE description CONTAINS "INTERCEPTED" GROUP BY off ORDER BY count DESC (This counts the number of plays that resulted in an interception by the team that threw the interception, sorted from most to fewest INTs)

mean reversion between ints for NFL live betting on next int? i smell greenbacks!

Re: Every NFL play for the past 10 years in CSV format

#50

Post by jredwards »

I've used data from Brian Burke's site before. I think it's the exact PBP data the NFL has, but you'll find that the structure and common phrasings change over the years. I had to write a lot of regular expressions and I was still catching edge cases for weeks.

btw, pro-football-reference has pbp data now too, and it probably goes back a lot further, but I think they discourage mass scraping of their site.

Post reply on HN