NFL Drive Analysis – Giants vs Lions

I figured I’d take the passive approach for a new blog idea and let my attention flow naturally to what’s most interesting to me at the moment. With my time (and cash) vested in an office Fantasy Football league, my mind has been fixed on NFL data as of late, despite all the upstanding citizens the NFL employs.

Assault, DUIs, performance enhancing substances, shootings and murder aside, the NFL kicked off its new season last weekend. That means my fandom experience is back in full gear. Sometimes, it’s great, but most of the time it’s hard being a New York Giants fan. I’m referring to the NY Giants football team from the East Division of the National Football Conference (NFC) division in the National Football League (NFL). I say it can be great because we’ve managed to pull off (basically steal) 2 Super Bowls in the last 10 years, but most of the time it’s really hard due to games like the one I begrudgingly sat through last Monday.

I digress, last Monday, while watching my beloved NY Giants [compete?] against the Detroit Lions, I saw something that I just had to try and recreate in Tableau. I set my intention to recreate a drive chart by using the play-by-play data from the game.

The play-by-play is a linear view of the game, ordered by the quarter (4 total) and time (15 minutes per quarter), and detailed with the down, 1st down yards remaining, starting field position, and play detail (players involved, play activity (pass/rush/etc), and other important details (touchdowns/penalties/etc). Here’s an example from ESPN:
playbyplay

A very helpful view to visualize the play-by-play detail of a game is the drive chart. The drive chart shows where, on the field, the team with possession starts out and how far they make it down the field either before scoring or turning the ball over. Below is a drive chart, also from ESPN, for the 1st quarter of the game. As you can see, the Detroit Lions (in blue) dominated with 2 scoring drives while the NY Giants (in grey) were forced to turn the ball over through punting on both drives. Now, please, use your vivid imagination to picture me losing my patience and my interest in the game at this point.
espndrive

I grabbed the ESPN play-by-play data and dumped it in Excel. I created two columns, one for the down detail and another for the play detail. The data looked like this after I cleaned it up a bit:
exceloriginal

Now for the fun part! I took the down detail and play detail, extrapolated certain parts of it to new columns for a more database-friendly view of the data, and created some additional columns not provided by the play-by-play data to help me achieve what I needed to create a drive chart in Tableau. The most important new field I had to create is the “Path” field (column D). This field allows Tableau to assign the data with a start and end point for the same field. For example, rows 2 and 3 provide the same start (columns G and H) and end (columns I and J) field positions but are broken out into two separate rows that Tableau can use to reference. The starting field position (NYG 35 yard line) is assigned the path of “0” and the end field position (DET 0 yard line) is assigned the path of “1” in Tableau.
exceldata

Here’s where the magic happens. I have to somehow translate the available data into a start and end position on an X axis for Tableau. Tableau needs to know which side of the field belongs to NY vs Detroit, as well as what happens when the starting field position is on the NY or Detroit side. This is where my fancy calculated field comes into play. It helps Tableau identify which columns to use and what to do with the values when the path is 0 for the starting point on the field, and the same for a path of 1 for the end point on the field. I sat down with a pencil and paper to figure this one out.
Yard

Needless to say, I was pleased with the result because it factored in the full 120 yard size of the field, with each end zone having 10 yards, and it allowed me to recreate a football field in Tableau using a bunch of reference lines and bands with colors, line sizes, and shapes to indicate different activities of the play-by-play!
NFL Drive Analysis_Thumbnail

Now that I had that item tackled, no pun intended, I created many more calculations based on the activity/play detail/etc. Adding these proved to be difficult, especially when trying to tie my numbers with those reported by ESPN. You may see some slight differences here and there, but the general idea remains the same – the Giants got their asses handed to them by Matt Stafford and Megatron. I also added a filter across the entire dashboard for quarter (mostly to filter on the second quarter and give myself some relief that the Giants at least dominated in score, though not even offensive yards…) and used dashboard actions to make every data point interactive across all applicable charts. Here’s the final product… enjoy!

Sharing is caring:

7 thoughts on “NFL Drive Analysis – Giants vs Lions”

  1. In fact, I’m going to write another comment, I’m so excited by this. You should do some work on the tooltips to make the plays really stand out as I explore the viz. I think you need a clearer indication of which direction is which (it takes a while to work it out). Also, some kind of instructional tooltip to hover over on the dashboard itself would help.

    Finally – can you extend this quickly for any/all NFL games? It’d be amazing to see this take off.

    1. Andy, thank you for your comments! I came up with the chart design myself, using reference lines and color bands to create a football field instead of using a picture. I welcomed the challenge of creating everything using Tableau! I really enjoy the chart you’ve linked in your previous comment, it’s amazing to know I’ve unknowingly recreated Brinton’s very interesting way to view this type of drive data. Especially since it is written that “the average person would never think of charting a football game” haha

      I appreciate your feedback on how to make the viz better – it means that you’re really absorbing it and finding ways to help improve my work – thank you!

      The data was a bit of a drag to put together, but maybe someone a bit more savvy can think of a good way to scrape data from ESPN and output it into the format I used for this viz. There was a bit of manual work involved to put the data together, but I’d love to do this for all the Giants games and create one large drive chart for all their games.

  2. Dave,

    This is one of the coolest posts I’ve seen in a while. You have hit on a topic that I’m going to be writing about on my blog (3danim8.wordpress.com) in the near future. This topic is: if you want people to look at your Tableau Public workbooks, don’t write about Tableau! Write about Sports!

    You have made a great accomplishment here. One day, Tableau will realize that they need two new versions: Tableau Scientific and Tableau Sports. People are way more interested in sports stats than just about anything else in analytics. Great job, I’m impressed!

    Ken (a long-time suffering Chi-Bears fan)

    1. Exactly! I think Tableau is interesting enough as it is, but the applications it can be used for are far more interesting… there’s a ton of data out there to be visualized and sports data is some of the best. Thanks for your kind words and what an idea to start developing customized versions based on the type of data being analyzed!

      Thanks again and I look forward to reading your stuff!

  3. Great Viz! Why didn’t you embed the viz directly in this page? You can get the embed code directly in the viz itself by clicking “Share” and then copying & pasting the embed code in your blog html.

    1. I would love to, but unfortunately WordPress does not yet support JavaScript which will render the Tableau embed code as a broken image box after publishing.

Leave a Reply

Your email address will not be published.