{"cells":[{"metadata":{"_uuid":"615b20c1f3c2cde43ef78188c3f512dbe54dd5b5"},"cell_type":"markdown","source":"NFL Punt Analytics"},{"metadata":{"_uuid":"a06d10ae6913fbd4ec8c4273b10f5a19694d7914"},"cell_type":"markdown","source":"Programming note: We performed our analysis in SQL Server and Visual FoxPro. We are just using the Kernel to list the queries and programs we ran, knowing they will not run in the Kernel. When reviewing the Kernel, we noticed that asterisks were being deleted from our statements when the Kernel was saved (they showed up OK in the editing window). Therefore, in select statements, if you see a table being selected without specified columns (table name followed by a period and no column name after), there should be an asterisk at that spot.\n\nOur key thinking point was how to make a rule change for punts to increase player safety, and the rule had to be actionable. The thing that helped us most for this Kaggle competition was something unexpected.\n\nThe main Kevin (McKee) has been watching the \"Last Call\" show for the past 2 years. After each weekend of NFL games, Mike Pereira and Dean Blandino (former Vice Presidents of Officiating at the NFL) explain calls and interesting things from the prior week’s games. They not only go into the details of the rules, they also explain the theory behind the rules. Getting in the mindset of former NFL officials, and learning about the rule making process, was extremely valuable when it got to the point of proposing a new rule. Kevin felt like he had insider knowledge from watching the show.\n\nWe decided to ignore things that couldn't be changed. For example (fact not true), assume 100% of punt concussion occurred in domed stadiums. Would we propose a rule to outlaw domed stadiums? Extreme example, but we also considered a plethora of other examples we felt could not be implemented.\n\nThere have been numerous rules already put in place (ie, \"12-2-8   Makes lowering the head to initiate contact with the helmet a foul.\") (source: NFL 2018 Rule Book) to increase player safety. We wanted to make sure not to duplicate any of those rules. \n\nOur initial thoughts included line formation changes, gunner changes and punt receiver positioning. Our proposal was not something we expected before we started looking at the data.\n\nWe first ran the SQL statements in the \"SQL tables\" kernel. This brought all the competition csv files into a SQL database. The Next Gen Stats files were all put in one table. Microsoft SQL Server 2017 was used.\n\nLooking at the tables, we noticed the role data for the players did not have if the player was on offense or defense. We created a new table with the following query:\n\nSELECT Distinct [Role]\n\n  INTO [KaggleNFLPunt].[dbo].[roles]\n\n  FROM [KaggleNFLPunt].[dbo].[play_player_role_data]\n\n  ORDER BY [Role]\n\nWe then added a column to this table called offdef, and manually entered \"O\" or \"D\" depending on if this player role was on offense or defense. Offense or defense was determined using the appendix from the Punt Data and Attributes Manual included in the contest data.\n\nWe ran the following to get the total number of plays in the database:\n\nSELECT [play_information].*, [video_review].*\n\n  FROM [KaggleNFLPunt].[dbo].[play_information]\n\n  LEFT JOIN [KaggleNFLPunt].[dbo].[video_review]\n\n  ON [play_information].[GameKey] = [video_review].[GameKey]\n\n  AND [play_information].[PlayID] = [video_review].[PlayID] \n\nThere were 6,681 rows returned, so there were that many plays in the database. (Going forward, when we give a play count after a query, it will be the number of rows returned by the query.)\n\nWe then ran the following: (Going forward, we will not be putting this in front of each query. Assume each query listed is run and the results discussed below the query.)\n\nSELECT [play_information].*, [video_review].*\n\n  FROM [KaggleNFLPunt].[dbo].[play_information]\n\n  LEFT JOIN [KaggleNFLPunt].[dbo].[video_review]\n\n  ON [play_information].[GameKey] = [video_review].[GameKey]\n\n  AND [play_information].[PlayID] = [video_review].[PlayID]\n\n  WHERE [video_review].[PlayID] > 0\n\nThis told us there were 37 plays resulting in concussions in the database.\n\nNext, we had to determine how many of the punt formation plays were punts. We noticed that if the word \"punts\" was not in the play_information database column PlayDescription, the play was not a punt.\n\nSELECT [play_information].*, [video_review].*\n\n  FROM [KaggleNFLPunt].[dbo].[play_information]\n\n  LEFT JOIN [KaggleNFLPunt].[dbo].[video_review]\n\n  ON [play_information].[GameKey] = [video_review].[GameKey]\n\n  AND [play_information].[PlayID] = [video_review].[PlayID]\n\n  WHERE CHARINDEX('punts',[play_information].[PlayDescription]) = 0\n\nThere 219 non-punt plays in the database.\n\nSELECT [play_information].*, [video_review].*\n\n  FROM [KaggleNFLPunt].[dbo].[play_information]\n\n  LEFT JOIN [KaggleNFLPunt].[dbo].[video_review]\n\n  ON [play_information].[GameKey] = [video_review].[GameKey]\n\n  AND [play_information].[PlayID] = [video_review].[PlayID]\n\n  WHERE CHARINDEX('punts',[play_information].[PlayDescription]) = 0\n\n  AND [video_review].[PlayID] > 0\n  \nOne of the non-punt plays resulted in a concussion.\nNotice the previous 2 queries are the same except for the last line:\n\nAND [video_review].[PlayID] > 0\n\nGoing forward, when we give a query that returns a number of plays, we will also give the number of concussions, but not include that query, because it will be the same as the previous query except for adding \"AND [video_review].[PlayID] > 0\" at the end.\n  \nWe wanted a slide (#2) to break out the various non-punt plays in the data. \n\nSELECT [play_information].*, [video_review].*\n\n  FROM [KaggleNFLPunt].[dbo].[play_information]\n\n  LEFT JOIN [KaggleNFLPunt].[dbo].[video_review]\n\n  ON [play_information].[GameKey] = [video_review].[GameKey]\n\n  AND [play_information].[PlayID] = [video_review].[PlayID]\n\n  WHERE CHARINDEX('punts',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('blocked',[play_information].[PlayDescription]) > 0\n  \n29 blocked punts, 0 concussions\n\nSELECT [play_information].*, [video_review].*\n\n  FROM [KaggleNFLPunt].[dbo].[play_information]\n\n  LEFT JOIN [KaggleNFLPunt].[dbo].[video_review]\n\n  ON [play_information].[GameKey] = [video_review].[GameKey]\n\n  AND [play_information].[PlayID] = [video_review].[PlayID]\n\n  WHERE CHARINDEX('punts',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('blocked',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('aborted',[play_information].[PlayDescription]) > 0\n  \n5 aborted punts, 0 concussions. The aborted punts include the punter fumbling based on the play description.\n\nSELECT [play_information].*, [video_review].*\n\n  FROM [KaggleNFLPunt].[dbo].[play_information]\n\n  LEFT JOIN [KaggleNFLPunt].[dbo].[video_review]\n\n  ON [play_information].[GameKey] = [video_review].[GameKey]\n\n  AND [play_information].[PlayID] = [video_review].[PlayID]\n\n  WHERE CHARINDEX('punts',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('blocked',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('aborted',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('pass',[play_information].[PlayDescription]) > 0\n\n19 passes, 0 concussions. \nNote: The order we are running the SQL statements determines the results. There was one blocked punt that turned into a pass play. As we are counting that in the blocked punts, we are not counting that in the pass plays. This is not relevant to our analysis, just wanted to point it out for completeness. \n\nSELECT [play_information].*, [video_review].*\n\n  FROM [KaggleNFLPunt].[dbo].[play_information]\n\n  LEFT JOIN [KaggleNFLPunt].[dbo].[video_review]\n\n  ON [play_information].[GameKey] = [video_review].[GameKey]\n\n  AND [play_information].[PlayID] = [video_review].[PlayID]\n\n  WHERE CHARINDEX('punts',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('blocked',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('aborted',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('pass',[play_information].[PlayDescription]) = 0  \n\n  AND CHARINDEX('penalty',[play_information].[PlayDescription]) > 0 \n\n142 penalties, 0 concussions. (Does not include 6 penalties in the blocked category, and 4 in the pass category.)\n\nSELECT [play_information].*, [video_review].*\n\n  FROM [KaggleNFLPunt].[dbo].[play_information]\n\n  LEFT JOIN [KaggleNFLPunt].[dbo].[video_review]\n\n  ON [play_information].[GameKey] = [video_review].[GameKey]\n\n  AND [play_information].[PlayID] = [video_review].[PlayID]\n\n  WHERE CHARINDEX('punts',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('blocked',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('aborted',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('pass',[play_information].[PlayDescription]) = 0  \n\n  AND CHARINDEX('penalty',[play_information].[PlayDescription]) = 0 \n\n24 runs, 1 concussion. (Since the play was none of the above types, it was a run.) \nAll this information is summarized on Slide #2.\n\nNow it is time to analyze the punt plays.\n\nSELECT [play_information].*, [video_review].*\n\n  FROM [KaggleNFLPunt].[dbo].[play_information]\n\n  LEFT JOIN [KaggleNFLPunt].[dbo].[video_review]\n\n  ON [play_information].[GameKey] = [video_review].[GameKey]\n\n  AND [play_information].[PlayID] = [video_review].[PlayID]\n\n  WHERE CHARINDEX('punts',[play_information].[PlayDescription]) > 0\n\n  AND CHARINDEX('fair catch',[play_information].[PlayDescription]) > 0\n\n1,663 fair catches, 2 concussions.\n\nSELECT [play_information].*, [video_review].*\n\n  FROM [KaggleNFLPunt].[dbo].[play_information]\n\n  LEFT JOIN [KaggleNFLPunt].[dbo].[video_review]\n\n  ON [play_information].[GameKey] = [video_review].[GameKey]\n\n  AND [play_information].[PlayID] = [video_review].[PlayID]\n\n  WHERE CHARINDEX('punts',[play_information].[PlayDescription]) > 0\n\n  AND CHARINDEX('fair catch',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('downed',[play_information].[PlayDescription]) > 0\n\n810 downed, 3 concussion.\n\nSELECT [play_information].*, [video_review].*\n\n  FROM [KaggleNFLPunt].[dbo].[play_information]\n\n  LEFT JOIN [KaggleNFLPunt].[dbo].[video_review]\n\n  ON [play_information].[GameKey] = [video_review].[GameKey]\n\n  AND [play_information].[PlayID] = [video_review].[PlayID]\n\n  WHERE CHARINDEX('punts',[play_information].[PlayDescription]) > 0\n\n  AND CHARINDEX('fair catch',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('downed',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('touchback',[play_information].[PlayDescription]) > 0\n\n402 touchbacks, 0 concussions.\n\nSELECT [play_information].*, [video_review].*\n\n  FROM [KaggleNFLPunt].[dbo].[play_information]\n\n  LEFT JOIN [KaggleNFLPunt].[dbo].[video_review]\n\n  ON [play_information].[GameKey] = [video_review].[GameKey]\n\n  AND [play_information].[PlayID] = [video_review].[PlayID]\n\n  WHERE CHARINDEX('punts',[play_information].[PlayDescription]) > 0\n\n  AND CHARINDEX('fair catch',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('downed',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('touchback',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('muff',[play_information].[PlayDescription]) > 0\n\n201 muffed punts, 2 concussions.\n\nSELECT [play_information].*, [video_review].*\n\n  FROM [KaggleNFLPunt].[dbo].[play_information]\n\n  LEFT JOIN [KaggleNFLPunt].[dbo].[video_review]\n\n  ON [play_information].[GameKey] = [video_review].[GameKey]\n\n  AND [play_information].[PlayID] = [video_review].[PlayID]\n\n  WHERE CHARINDEX('punts',[play_information].[PlayDescription]) > 0\n\n  AND CHARINDEX('fair catch',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('downed',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('touchback',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('muff',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('for ',[play_information].[PlayDescription]) > 0\n\n2,746 regular punts (i.e. none of the types already listed), 29 concussions.\n\nSELECT [play_information].*, [video_review].*\n\n  FROM [KaggleNFLPunt].[dbo].[play_information]\n\n  LEFT JOIN [KaggleNFLPunt].[dbo].[video_review]\n\n  ON [play_information].[GameKey] = [video_review].[GameKey]\n\n  AND [play_information].[PlayID] = [video_review].[PlayID]\n\n  WHERE CHARINDEX('punts',[play_information].[PlayDescription]) > 0\n\n  AND CHARINDEX('fair catch',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('downed',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('touchback',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('muff',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('for ',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('out of bounds',[play_information].[PlayDescription]) > 0\n\n639 out of bounds, 0 concussions.\n\nSELECT [play_information].*, [video_review].*\n\n  FROM [KaggleNFLPunt].[dbo].[play_information]\n\n  LEFT JOIN [KaggleNFLPunt].[dbo].[video_review]\n\n  ON [play_information].[GameKey] = [video_review].[GameKey]\n\n  AND [play_information].[PlayID] = [video_review].[PlayID]\n\n  WHERE CHARINDEX('punts',[play_information].[PlayDescription]) > 0\n\n  AND CHARINDEX('fair catch',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('downed',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('touchback',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('muff',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('for ',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('out of bounds',[play_information].[PlayDescription]) = 0\n\n1 uncategorized play, 0 concussions. Reading the description, this was a touchback, so this will be added to the touchback total on the slide.\n\nWe prepared slide #3 with this information, along with the percentage of punts each play accounted for, and the concussion percentage for each play type. We noticed that fair catches had a concussion percentage of only 0.12%. If we could increase the percentage of fair catches, this should reduce the number of concussions. We figured the way to do this would be to award the team some yardage after a fair catch. To determine how many yards, we needed more information.\n\nWe first wanted to see average return distance. \n\nSELECT SUBSTRING([play_information].[PlayDescription],CHARINDEX('for ',[play_information].[PlayDescription])+4,3) As RetYds, \n\n  [play_information].*, [video_review].* \n\n  FROM [KaggleNFLPunt].[dbo].[play_information]\n\n  LEFT JOIN [KaggleNFLPunt].[dbo].[video_review]\n\n  ON [play_information].[GameKey] = [video_review].[GameKey]\n\n  AND [play_information].[PlayID] = [video_review].[PlayID]\n\n  WHERE CHARINDEX('punts',[play_information].[PlayDescription]) > 0\n\n  AND CHARINDEX('fair catch',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('downed',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('touchback',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('muff',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('for ',[play_information].[PlayDescription]) > 0\n\n  order by retyds\n\nFrom the SQL results window, we selected all and copied with headers, and pasted to an Excel workbook. We inserted column B, and used the following formula to put the return yards in this column, as the yards from the SQL query needed some processing.  \n\n“=IF(ISNUMBER(A2),A2,IF(LEFT(A2,2)=\"no\",0,VALUE(LEFT(A2,1))))”\n\nWe found that 10.1 yards was the average return distance. We then created a pivot table and used that to create the graph seen in Slide #4. \n\nSELECT [ngs].* , \n\n  [play_player_role_data].[Role],\n \n  [roles].[offdef]\n\n  FROM [KaggleNFLPunt].[dbo].[ngs]\n\n  LEFT JOIN [KaggleNFLPunt].[dbo].[play_player_role_data]\n\n  ON  [ngs].[GameKey] =  [play_player_role_data].[GameKey]\n\n  AND  [ngs].[PlayID] =  [play_player_role_data].[PlayID]\n\n  AND  [ngs].[GSISID] =  [play_player_role_data].[GSISID]\n\n  LEFT JOIN [KaggleNFLPunt].[dbo].[roles]\n\n  ON [play_player_role_data].[Role] = [roles].[Role]\n\n  WHERE [ngs].[Event] = 'punt_received' AND [roles].[Role] IS NOT NULL\n\n  ORDER BY [ngs].[PlayID], [ngs].[GameKey], [roles].[offdef]\n\nThis gave us the Next Gen Stats info at the time the punt was received. We copied the results to a csv file named puntngs.csv. (We ran the same query later, changing the event from ‘punt_received’ to ‘fair_catch’ to analyze fair catches.)\n\nSELECT  [play_information].*, [video_review].* \n\n  FROM [KaggleNFLPunt].[dbo].[play_information]\n\n  LEFT JOIN [KaggleNFLPunt].[dbo].[video_review]\n\n  ON [play_information].[GameKey] = [video_review].[GameKey]\n\n  AND [play_information].[PlayID] = [video_review].[PlayID]\n\n  WHERE CHARINDEX('punts',[play_information].[PlayDescription]) > 0\n\n AND CHARINDEX('fair catch',[play_information].[PlayDescription]) = 0\n\n AND CHARINDEX('downed',[play_information].[PlayDescription]) = 0\n\n AND CHARINDEX('touchback',[play_information].[PlayDescription]) = 0\n\n AND CHARINDEX('muff',[play_information].[PlayDescription]) = 0\n\n  AND CHARINDEX('for ',[play_information].[PlayDescription]) > 0\n\nThis gave us the play information for regular punt plays. We copied the results to a csv file named puntdefd.csv.\n\nFollowing is the Visual Foxpro code we ran to get the distance of the nearest 3 opposing players at the time of the catch. We assumed the punt returner was the man fielding the ball. (There was no ball data in the Next Gen Stats provided to us, which would have allowed us to identify the player receiving the punt.) We feel any error because of this assumption will be minimal. When adding the Next Gen Stats to our play information database, if there was no Next Gen info, or if there were duplicate punt_received events for the specific Game/Play, we would delete that record from our play information database. We ended up excluding 54 plays (and 46 fair catch plays below) due to this. As this was less than 2% (3% for fair catches) of the plays, we do not think this missing information would have changed our analysis.\n\n----- Begin FoxPro Code -----\nSET EXCLUSIVE OFF\n\nCLOSE ALL\n\nCLEAR ALL\n\nRELEASE ALL\n\nSET TALK ON\n\nSET DEFAULT TO c:\\nfl\n\nCLOSE DATABASES ALL\n\nIF FILE('puntngs.dbf')\n\n  DELETE FILE 'puntngs.dbf'\n\nENDIF\n\nCREATE TABLE puntngs( ;\n\n  nSeason    N(4,0), ;\n\n  nGameKey   N(3,0), ;\n\n  nPlayId    N(4), ;\n\n  nGSISID    N(5), ;\n\n  cTime      c(23), ;\n\n  nX         N(12,8), ;\n\n  nY         N(11,8), ;\n\n  nDis       N(4,2), ;\n\n  nO         N(12,8), ;\n\n  nDir       N(12,8), ;\n\n  cEvent     c(24), ;\n\n  cRole      c(4), ;\n\n  cOffDef    c(1))\n\nSELECT puntngs\n\nAPPEND FROM puntngs.CSV TYPE DELIMITED\n\nIF FILE('puntdefd.dbf')\n\n  DELETE FILE 'puntdefd.dbf'\n\nENDIF\n\nCREATE TABLE puntdefd( ;\n\n  nSYear     N(4,0), ;\n\n  sSType     c(4), ;\n\n  nGameKey   N(3,0), ;\n\n  cGameDate  c(10), ;\n\n  nWeek      N(2,0), ;\n\n  nPlayId    N(4), ;\n\n  cGameClock c(5), ;\n\n  cYardLine  c(6), ;\n\n  cQuarter   N(1,0), ;\n\n  cPlayType  c(4), ;\n\n  cPossTeam  c(3), ;\n\n  cHTVT      c(7), ;\n\n  cScore     c(7), ;\n\n  cPlayDesc  c(254))\n\nSELECT puntdefd\n\nAPPEND FROM puntdefd.CSV TYPE DELIMITED\n\nALTER TABLE puntdefd ADD COLUMN nDef1 N(12,8)\n\nALTER TABLE puntdefd ADD COLUMN nDef2 N(12,8)\n\nALTER TABLE puntdefd ADD COLUMN nDef3 N(12,8)\n\nnPRX = 0\n\nnPRY = 0\n\nSELECT puntdefd\n\nGO TOP\n\nDO WHILE !EOF()\n\n  SELECT puntngs.* ;\n\n    FROM puntngs ;\n\n    WHERE puntdefd.nGameKey = puntngs.nGameKey ;\n\n    AND puntdefd.nPlayId = puntngs.nPlayId ;\n\n    AND puntngs.cRole = \"PR \" ;\n\n    INTO CURSOR curNGS\n\n  nRecs = RECCOUNT()\n\n  IF nRecs = 1\n\n    nPRX = curNGS.nX\n\n    nPRY = curNGS.nY\n\n    SELECT puntngs.*, ;\n\n      SQRT((nPRX - puntngs.nX)^2 + (nPRY - puntngs.nY)^2) AS nDefDist ;\n\n      FROM puntngs ;\n\n      WHERE puntdefd.nGameKey = puntngs.nGameKey ;\n\n      AND puntdefd.nPlayId = puntngs.nPlayId ;\n\n      AND puntngs.cOffDef = \"O\" ;\n\n      ORDER BY nDefDist ;\n\n      INTO CURSOR curNGS\n      \n    SELECT curNGS\n\n    GO TOP\n\n    REPLACE puntdefd.nDef1 WITH curNGS.nDefDist\n\n    SKIP\n\n    REPLACE puntdefd.nDef2 WITH curNGS.nDefDist\n\n    SKIP\n    REPLACE puntdefd.nDef3 WITH curNGS.nDefDist\n\n  ELSE\n\n    SELECT puntdefd\n\n    DELETE\n\n  ENDIF\n\n  SELECT puntdefd\n\n  SKIP\n\nENDDO\n\nSELECT puntdefd\n\nPACK\n\nCOPY TO puntdefd TYPE xl5\n\n----- End FoxPro Code -----\n\nThis gave us an Excel workbook, which when then worked on. We did the same analysis for fair catches, running the following 2 SQL commands before running the Foxpro program above.\n\nSELECT [ngs].* , \n\n  [play_player_role_data].[Role],\n \n  [roles].[offdef]\n\n  FROM [KaggleNFLPunt].[dbo].[ngs]\n\n  LEFT JOIN [KaggleNFLPunt].[dbo].[play_player_role_data]\n\n  ON  [ngs].[GameKey] =  [play_player_role_data].[GameKey]\n\n  AND  [ngs].[PlayID] =  [play_player_role_data].[PlayID]\n\n  AND  [ngs].[GSISID] =  [play_player_role_data].[GSISID]\n\n  LEFT JOIN [KaggleNFLPunt].[dbo].[roles]\n\n  ON [play_player_role_data].[Role] = [roles].[Role]\n\n  WHERE [ngs].[Event] = 'fair_catch' AND [roles].[Role] IS NOT NULL\n\n  ORDER BY [ngs].[PlayID], [ngs].[GameKey], [roles].[offdef]\n\n\nSELECT  [play_information].*, [video_review].*\n\n  FROM [KaggleNFLPunt].[dbo].[play_information]\n\n  LEFT JOIN [KaggleNFLPunt].[dbo].[video_review]\n\n  ON [play_information].[GameKey] = [video_review].[GameKey]\n\n  AND [play_information].[PlayID] = [video_review].[PlayID]\n\n  WHERE CHARINDEX('punts',[play_information].[PlayDescription]) > 0\n\n AND CHARINDEX('fair catch',[play_information].[PlayDescription]) > 0\n\nTo calculate the return distance based on the nearest defender (Slide 5), we put the following formulas in the Excel spreadsheet.\n\nCol S: “=INT(O2)” – Integer value for closest defender\n\nCol V: “=IFERROR(SEARCH(\"for \",N2),0)”  -- use for next calculation\n\nCol W: “=IFERROR(IF(V2>0,VALUE(MID(N2, V2+3,3)),\"N/A\"),0)”  -- return distance\n\nFor punt returns, we created a pivot table, and then created a chart from the pivot table data (Slide #5). This confirmed that the further away the defenders are at the time of the catch, the more yardage one gains. We noted that if a defender is within 6 yards, average return distance is less than 8 yards. If the nearest defender is more than 10 yards away, the average return is greater than 10 yards.\n\nWe also created pivot tables for both punt return and fair catches, showing the number of each play based on distance from the nearest defender. We combined the data to get the fair catch % of total punt plays broken out by the distance to the nearest defender (Slide #6).\n\nFair catches are called over 50% of the time when the nearest defender is within 4 years. Eyeballing slides 4 and 5, punt returner seems to be giving up about 5 yards by doing this. Based on this analysis, we are proposing the rule changes in our slides. See slides for details.\n\n"},{"metadata":{"_uuid":"dd0da519eda1cdf8012dfcb29e31351019095076"},"cell_type":"markdown","source":""}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.6","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}