{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-01-06T22:08:45.606692Z","iopub.execute_input":"2022-01-06T22:08:45.607233Z","iopub.status.idle":"2022-01-06T22:08:45.625465Z","shell.execute_reply.started":"2022-01-06T22:08:45.607079Z","shell.execute_reply":"2022-01-06T22:08:45.624379Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Table of Contents\n1. [Introduction to Ball Proximity Average](#Introduction to Ball Proximity Average)\n2. [BPA Metric Use Case](#BPA Metric Use Case)\n3. [Data Preparation and Processing](#Data Preparation and Processing)\n4. [BPA Analysis by Age](#BPA Analysis by Age)\n5. [BPA Analysis by Position](#BPA Analysis by Position)\n6. [BPA Analysis by College](#BPA Analysis by College)\n7. [Analytical Conclusions](#Analytical Conclusions)\n8. [Appendix](#Appendix)\n9. [Visualizations](#Visualizations)\n\n\n\n## Introduction to Ball Proximity Average (BPA)\n\nThis data analytics project aims to uncover insights with respect to a novel statistic derived in our analysis which we will call Ball Proximity Average or BPA. To summarize the use case of this metric, I will paraphrase a concept that we have all heard from coaches when giving praise to defensive players. “He has a nose for the football”, “She is always around the football disrupting the play”, or “They were ‘Johnny on the spot’” are often notable words of praise on players whose job it is to play defense. Their job is to make tackles, get fumble recoveries, assist teammates in the process or otherwise be disruptive by hunting down the football during the play. Ball Proximity Average (BPA) is defined as the average distance a player is away from the football during the relevant events of all “Live Kickoff” plays that only include touchdown, safety, tackle, fumble, and defensive fumble recovery. This statistic can be viewed objectively as a way to judge how close a kickoff coverage special teams player was to the ball on average when there was a “play to be made”. For each relevant event instance, players were awarded a distance value as measured by the differences in their coordinates and the football coordinates using the Euclidean Distance formula aka the Pythagorean Theorem. Over the course of each of the three sample seasons, BPA, tackles, assists, misses, and fumble recoveries were aggregated and compared using simple linear regression and comparative analysis. The resulting StoryBoard presentation was published in Tableau and made public for this competition. See link to the Tableau Dashboard here. [Our Tableau Dashboard](http://public.tableau.com/app/profile/taidje.tang/viz/NFLBigDataBowl2022BallProximityAnalysis/2018_Avg_BPAvs_Avg_TackleRatebyPosition)\n    \n## BPA Metric Use Case\n\nKickoff Coverage is an area of the game we believe we can improve and make more objectively measurable by using BPA when evaluating players. In this analysis, I will propose the idea of using BPA as a simple, yet articulate way to measure the performance of all non-kicking or quarterbacking players while defending against “Live” Kickoff Returns. This idea may also be useful for other phases of the game, however this analysis focuses on analyzing performance, in-game potential, and overall “hustle” while handling kickoff coverage duties.\n\nFirst imagine you are watching a “live” kickoff play where there was no touchback or out-of-bounds kick with the ball being returned or fielded by the receiving team. No matter the result of the play, the distance of the player from the ball is relevant to his performance because it represents one’s ability to make a play on the ball. Just as one can’t make a tackle from 15 yards away from the ball, he is also limited on his ability to recover a fumble or help a teammate who may have missed a tackle. From this perspective, the BPA metric can also be objectively used to judge a player’s “hustle” and “nose for the football”. For example, in the event that a player is beaten on an open field block that led to a touchdown for the receiving team, that player would have their BPA increase accordingly. By the same token, the player that narrowly missed catching the returner trailing the eventual touchdown would have their BPA much less affected than the weak link player who ended the play many yards away. In American football, proximity to the football matters tremendously, especially when there is a play to be made. \n\nThe potential use case and applications of the BPA metric are extensive. At the end of every game, season, or even career, BPA can be used to summarize players’ play making potential and hustle. While the metric doesn’t always translate into success on a play in the form of a tackle, assist, touch, or fumble recovery, it can certainly quantify the potential of a player to make the play. Much like Batting Average in baseball, this rolling average can be viewed against any number of factors such as field conditions, recency, or vs. certain opponents. I can imagine using this stat to analyze the impact of a player who may not show up in the traditional box score because it may be useful by giving credit to players who are “around the football”. In conclusion, the lower a player’s BPA, the closer they are to the football when a play needs to be made. \n\n## Data Preparation and Processing\n\nSince BPA was strictly defined to be applied to only non-kicker and non-quarterback Kickoff Coverage players, the metric needed to be created only using relevant tracking data for relevant play event instances. The process of the cleaning and refining necessary was meticulous and tedious, but essential to the objectivity of the stat. The following conditions were used to determine “relevant” plays:\n\n-specialTeamsPlayType was limited to ‘Kickoff’ only.\n\n-Relevant events on kickoff play events only included ‘tackle’, ‘fumble’, ‘fumble_defense_recovered’, ‘touchdown’, and ‘safety’. These were determined to be the “plays to be made” on kickoff coverage. \n\n-The set only included players on kickoff coverage, excluding players on the receiving team.\n\nOnce relevant plays and qualifying players were selected, many columns in the data set needed to be cleaned and transformed in order to accurately tally play success measurables such as tackle_rate, touch_rate, and fumble_recovery_rate. The details of the data processing are in the source code of this note book in the [Appendix](#Appendix). \n\n## BPA Analysis by Age\n\nWhen examining age as a factor, BPA tends to increase for players over 30 when compared to players in their 20s. Some outliers exist in the over 30 years old segment. This may be a result of the players who are still playing that postion have specialized and excelled which kept them there, as opposed to being cut for a younger player. \n\n## BPA Analysis by Position\n\nWhen examining position as a factor, avg. BPA tends to increase for players who are on offense as opposed to defense. Tackle rates reflect poorer performance as a result. \n\n## BPA Analysis by College\n\nWhen examining College as a factor, we found it difficult to draw insights from the college attended as a factor in BPA, other than perhaps players coming from smaller programs may be \"huslting\" more on plays because they were always underdogs coming into the league. When sorted in ascending order, most players from schools with the lowest average BPA often come from colleges with very few products in the NFL. \n\n## Analytical Conclusions\n\nUnfortunately, due to our limited Kaggle experience and current difficulties presenting our findings in python, we will have to make my analytical conclusions based on my findings in Tableau. After examining BPA vs. performance stats such as tackle rate, touch rate, and fumble recovery rate against such factors as Age, Postion, College, and BPA segment, we have evidence that BPA can be used as a measure for evaluting players in 2 ways: \n\n*We can use BPA to judge when a player has \"lost a step\" or is \"getting older\" as BPA clearly increases as a player enters their 30s. \n\n*We can use BPA to judge how close a player was to the ball during relevant events and measure their proximity as a metric for \"play making potential\" since there exists a broad inverse relationship between kickoff coverage performance and BPA. \n\nWith our findings, we think there is a good use case argument for Ball Proximity Average as a statistic to follow throughout a player's game, season, and even career. \n","metadata":{}},{"cell_type":"markdown","source":"## Appendix","metadata":{}},{"cell_type":"code","source":"import matplotlib.pyplot as plt\nfrom datetime import datetime, date","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:08:45.627628Z","iopub.execute_input":"2022-01-06T22:08:45.628416Z","iopub.status.idle":"2022-01-06T22:08:45.636113Z","shell.execute_reply.started":"2022-01-06T22:08:45.628376Z","shell.execute_reply":"2022-01-06T22:08:45.635169Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#read tracking data from each season\ntracking_2018 = pd.read_csv(r'/kaggle/input/nfl-big-data-bowl-2022/tracking2018.csv')\ntracking_2019 = pd.read_csv(r'/kaggle/input/nfl-big-data-bowl-2022/tracking2019.csv')\ntracking_2020 = pd.read_csv(r'/kaggle/input/nfl-big-data-bowl-2022/tracking2020.csv')","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:08:45.637880Z","iopub.execute_input":"2022-01-06T22:08:45.638171Z","iopub.status.idle":"2022-01-06T22:10:34.262265Z","shell.execute_reply.started":"2022-01-06T22:08:45.638138Z","shell.execute_reply":"2022-01-06T22:10:34.261242Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#read plays data and assign\nplays = pd.read_csv(r'/kaggle/input/nfl-big-data-bowl-2022/plays.csv')\n#read player data and assign\nplayers = pd.read_csv(r'/kaggle/input/nfl-big-data-bowl-2022/players.csv')\n#read games data and assign\ngames = pd.read_csv(r'/kaggle/input/nfl-big-data-bowl-2022/games.csv')\n#read PFFScouting data and assign\npff = pd.read_csv(r'/kaggle/input/nfl-big-data-bowl-2022/PFFScoutingData.csv')","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:34.265505Z","iopub.execute_input":"2022-01-06T22:10:34.266535Z","iopub.status.idle":"2022-01-06T22:10:34.452646Z","shell.execute_reply.started":"2022-01-06T22:10:34.266490Z","shell.execute_reply":"2022-01-06T22:10:34.451624Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#examine the data using examine function\n\ndef examine(table):\n    print('The dataset details:')\n    print(table.shape)\n    print(table.columns)\n    print(\"*\"*20)\n    print('The statistical breakdown of column ranges:')\n    print(table.describe())\n    print(\"*\"*20)\n    print('Checking the first 5 rows:')\n    print(table.head())","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:34.456596Z","iopub.execute_input":"2022-01-06T22:10:34.456865Z","iopub.status.idle":"2022-01-06T22:10:34.462500Z","shell.execute_reply.started":"2022-01-06T22:10:34.456833Z","shell.execute_reply":"2022-01-06T22:10:34.461475Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#examine(tracking_2018)\n#examine(tracking_2019)\n#examine(tracking_2020)\n#examine(plays)\n#examine(players)\n#examine(games)\n#examine(pff)","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:34.464378Z","iopub.execute_input":"2022-01-06T22:10:34.464961Z","iopub.status.idle":"2022-01-06T22:10:34.476780Z","shell.execute_reply.started":"2022-01-06T22:10:34.464909Z","shell.execute_reply":"2022-01-06T22:10:34.476111Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"After inspecting and examining the data, proceed to preparing and processing the data","metadata":{}},{"cell_type":"code","source":"#filter for Kickoffs plays only\nkickoff_plays = plays[plays['specialTeamsPlayType'] == 'Kickoff']","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:34.477730Z","iopub.execute_input":"2022-01-06T22:10:34.478266Z","iopub.status.idle":"2022-01-06T22:10:34.500493Z","shell.execute_reply.started":"2022-01-06T22:10:34.478235Z","shell.execute_reply":"2022-01-06T22:10:34.499431Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#filter out kickoff plays where the words 'RECOVERED' and 'Replay' are in playDescription\nd_fumble_plays = kickoff_plays[kickoff_plays['playDescription'].str.contains('RECOVERED') ]\n\n#manually inspected all 7 plays and they were all overturned by replay, not actually making them fumbles\nnullified_fumble_plays = d_fumble_plays[d_fumble_plays['playDescription'].str.contains('Replay') ]\n\n#get an indicator value to remove the subset of nullified plays on\nrefined_d_fumble_plays = pd.merge(d_fumble_plays, nullified_fumble_plays, on =['gameId', 'playId'], how = 'left', indicator = True)","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:34.501870Z","iopub.execute_input":"2022-01-06T22:10:34.502888Z","iopub.status.idle":"2022-01-06T22:10:34.533674Z","shell.execute_reply.started":"2022-01-06T22:10:34.502850Z","shell.execute_reply":"2022-01-06T22:10:34.532655Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#this will result in a set without the rows that intersected on left merge\nrefined_d_fumble_plays = refined_d_fumble_plays[refined_d_fumble_plays['_merge'] == 'left_only']\n\n#manually checked and confirmed that all 7 of the resulting plays were NULLIFIED due to replay overturning the ruling on the field\nrefined_d_fumble_plays.drop(['playDescription_y', 'quarter_y', 'down_y', 'yardsToGo_y',\n       'possessionTeam_y', 'specialTeamsPlayType_y', 'specialTeamsResult_y',\n       'kickerId_y', 'returnerId_y', 'kickBlockerId_y', 'yardlineSide_y',\n       'yardlineNumber_y', 'gameClock_y', 'penaltyCodes_y',\n       'penaltyJerseyNumbers_y', 'penaltyYards_y', 'preSnapHomeScore_y',\n       'preSnapVisitorScore_y', 'passResult_y', 'kickLength_y',\n       'kickReturnYardage_y', 'playResult_y', 'absoluteYardlineNumber_y'], axis = 1, inplace = True)\n\n#rename columns back to their original name\nrefined_d_fumble_plays = refined_d_fumble_plays.rename(columns={'playDescription_x': 'playDescription', 'quarter_x':'quarter', 'down_x':'down',\n       'yardsToGo_x': 'yardsToGo', 'possessionTeam_x': 'possessionTeam', 'specialTeamsPlayType_x': 'specialTeamsPlayType',\n       'specialTeamsResult_x': 'specialTeamsResult', 'kickerId_x':'kickerId', 'returnerId_x':'returnerId', 'kickBlockerId_x':'kickBlockerId',\n       'yardlineSide_x': 'yardlineSide', 'yardlineNumber_x': 'yardlineNumber', 'gameClock_x':'gameClock', 'penaltyCodes_x':'penaltyCodes',\n       'penaltyJerseyNumbers_x':'enaltyJerseyNumbers', 'penaltyYards_x':'penaltyYards', 'preSnapHomeScore_x':'preSnapHomeScore',\n       'preSnapVisitorScore_x':'preSnapVisitorScore', 'passResult_x':'passResult', 'kickLength_x':'kickLength',\n       'kickReturnYardage_x':'kickReturnYardage', 'playResult_x':'playResult', 'absoluteYardlineNumber_x':'absoluteYardlineNumber'})","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:34.535213Z","iopub.execute_input":"2022-01-06T22:10:34.535548Z","iopub.status.idle":"2022-01-06T22:10:34.547010Z","shell.execute_reply.started":"2022-01-06T22:10:34.535504Z","shell.execute_reply":"2022-01-06T22:10:34.545936Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#declare a function to search for defensive recovery indication in playDescription column\ndef d_recovery_check(x):\n    word_index = 0\n    word_list = x.split()\n    for word in word_list:\n        if word == 'RECOVERED':\n            return word_list[word_index + 2]\n        else:\n            word_index += 1","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:34.548318Z","iopub.execute_input":"2022-01-06T22:10:34.548699Z","iopub.status.idle":"2022-01-06T22:10:34.562580Z","shell.execute_reply.started":"2022-01-06T22:10:34.548668Z","shell.execute_reply":"2022-01-06T22:10:34.561921Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#now we can count the fumble recoveries based on credits and counts\n#now the d_recovery_credit' column will be used to hold the team abbr and abbr name of the player\n#will be used differently later\nrefined_d_fumble_plays['d_recovery_credit'] = refined_d_fumble_plays['playDescription'].apply(lambda x: d_recovery_check(x))","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:34.563783Z","iopub.execute_input":"2022-01-06T22:10:34.565289Z","iopub.status.idle":"2022-01-06T22:10:34.579652Z","shell.execute_reply.started":"2022-01-06T22:10:34.565238Z","shell.execute_reply":"2022-01-06T22:10:34.578954Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#make a function to parse the string 'd_recovery_credit' and parse out the last name of the player only\n\ndef d_recovery_name(x):\n    credit = x.split('-')\n    player_team = credit[0] \n    player_name = credit[1]\n    player_last_name = player_name.split('.')[1]\n    return player_last_name","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:34.581135Z","iopub.execute_input":"2022-01-06T22:10:34.581457Z","iopub.status.idle":"2022-01-06T22:10:34.591480Z","shell.execute_reply.started":"2022-01-06T22:10:34.581412Z","shell.execute_reply":"2022-01-06T22:10:34.590602Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#split the result into the last name of the player credited with with having recovered the ball\nrefined_d_fumble_plays['recovery_lastName'] = refined_d_fumble_plays['d_recovery_credit'].apply(lambda x: d_recovery_name(x))","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:34.592753Z","iopub.execute_input":"2022-01-06T22:10:34.593156Z","iopub.status.idle":"2022-01-06T22:10:34.603965Z","shell.execute_reply.started":"2022-01-06T22:10:34.593115Z","shell.execute_reply":"2022-01-06T22:10:34.603356Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#merge the 'recovery_lastName' column onto the refined_d_fumble_plays df on gameId and playId\nlastNames_d_fumble_plays = pd.DataFrame(refined_d_fumble_plays, columns = ['recovery_lastName', 'gameId', 'playId'])\n\n#finally left merge the recovery_lastname column back onto the original kickoffs subset\nkickoff_plays = pd.merge(kickoff_plays, lastNames_d_fumble_plays, on =['gameId', 'playId'], how = 'left')","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:34.608416Z","iopub.execute_input":"2022-01-06T22:10:34.608853Z","iopub.status.idle":"2022-01-06T22:10:34.744377Z","shell.execute_reply.started":"2022-01-06T22:10:34.608806Z","shell.execute_reply":"2022-01-06T22:10:34.743438Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#start cleaning player table data to be merged onto the final refined aggregated dataset later\n#create a function for height conversion\n\ndef height_conversion(x):\n    if len(str(x)) >2:\n        ft_in = x.split('-')\n        height_inches = int(ft_in[0])*12 + int(ft_in[1])\n    else:\n        height_inches = x\n    return height_inches","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:34.745840Z","iopub.execute_input":"2022-01-06T22:10:34.746346Z","iopub.status.idle":"2022-01-06T22:10:34.752535Z","shell.execute_reply.started":"2022-01-06T22:10:34.746292Z","shell.execute_reply":"2022-01-06T22:10:34.751540Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#convert height to inches from feet\nplayers['height'] = players['height'].apply(lambda x: height_conversion(x))","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:34.753792Z","iopub.execute_input":"2022-01-06T22:10:34.754606Z","iopub.status.idle":"2022-01-06T22:10:34.768721Z","shell.execute_reply.started":"2022-01-06T22:10:34.754563Z","shell.execute_reply":"2022-01-06T22:10:34.767749Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Start by cleaning the players table for later merge onto aggregate data table","metadata":{}},{"cell_type":"code","source":"#first, manually input the missing birthdates for a number of players\n#used www.espn.com for finding birthdates\nplayers.loc[ players['nflId'] == 52464, 'birthDate'] = '1997-08-21'\nplayers.loc[ players['nflId'] == 52592, 'birthDate'] = '1997-10-18'\nplayers.loc[ players['nflId'] == 53086, 'birthDate'] = '1997-09-24'\nplayers.loc[ players['nflId'] == 52606, 'birthDate'] = '1997-10-28'\nplayers.loc[ players['nflId'] == 52585, 'birthDate'] = '1997-12-01'\nplayers.loc[ players['nflId'] == 52566, 'birthDate'] = '1997-11-05'\nplayers.loc[ players['nflId'] == 52626, 'birthDate'] = '1997-07-21'\nplayers.loc[ players['nflId'] == 50975, 'birthDate'] = '1994-11-26'\nplayers.loc[ players['nflId'] == 52637, 'birthDate'] = '1997-07-30'\nplayers.loc[ players['nflId'] == 53020, 'birthDate'] = '1996-06-30'\nplayers.loc[ players['nflId'] == 52624, 'birthDate'] = '1999-03-31'\nplayers.loc[ players['nflId'] == 52662, 'birthDate'] = '1997-07-11'\nplayers.loc[ players['nflId'] == 52631, 'birthDate'] = '1997-07-17'\nplayers.loc[ players['nflId'] == 52539, 'birthDate'] = '1998-08-27'\nplayers.loc[ players['nflId'] == 41112, 'birthDate'] = '1989-05-23'\nplayers.loc[ players['nflId'] == 52587, 'birthDate'] = '1998-01-17'\nplayers.loc[ players['nflId'] == 52459, 'birthDate'] = '1997-09-20'\n","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:34.770254Z","iopub.execute_input":"2022-01-06T22:10:34.771033Z","iopub.status.idle":"2022-01-06T22:10:34.794025Z","shell.execute_reply.started":"2022-01-06T22:10:34.770990Z","shell.execute_reply":"2022-01-06T22:10:34.793210Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#convert birthDate to a datetime object\nplayers['birthDate'] = pd.to_datetime(players['birthDate']) ","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:34.795571Z","iopub.execute_input":"2022-01-06T22:10:34.795791Z","iopub.status.idle":"2022-01-06T22:10:34.804292Z","shell.execute_reply.started":"2022-01-06T22:10:34.795764Z","shell.execute_reply":"2022-01-06T22:10:34.803119Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#get the datetime delta for each players age using datetime objects for today and birthdate\ntoday = datetime.today()\nplayers['age'] = today - players['birthDate']","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:34.805516Z","iopub.execute_input":"2022-01-06T22:10:34.805747Z","iopub.status.idle":"2022-01-06T22:10:34.819605Z","shell.execute_reply.started":"2022-01-06T22:10:34.805720Z","shell.execute_reply":"2022-01-06T22:10:34.818843Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#with the resulting date time deltas, extract the number of days and divide by 365 for years, leaving NA alone for now\ndef convert_age(x):\n    try:\n        time_delta_str = str(x)\n        days = int(time_delta_str.split()[0])\n        return days/365\n    except:\n        return x","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:34.820663Z","iopub.execute_input":"2022-01-06T22:10:34.821320Z","iopub.status.idle":"2022-01-06T22:10:34.829503Z","shell.execute_reply.started":"2022-01-06T22:10:34.821279Z","shell.execute_reply":"2022-01-06T22:10:34.828587Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#run the convert_age function on the age column\nplayers['age'] = players['age'].apply(lambda x: convert_age(x))\n\n\n#subtract the number of years we are now removed from prior seasons to get players age at the time\nplayers['age_2018'] = (players['age'] - 3).map(int)\nplayers['age_2019'] = (players['age'] - 2).map(int)\nplayers['age_2020'] = (players['age'] - 1).map(int)","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:34.830723Z","iopub.execute_input":"2022-01-06T22:10:34.831244Z","iopub.status.idle":"2022-01-06T22:10:34.889650Z","shell.execute_reply.started":"2022-01-06T22:10:34.831198Z","shell.execute_reply":"2022-01-06T22:10:34.888650Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#filter the tracking events frames to only include the types of play instances of concern","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:34.891307Z","iopub.execute_input":"2022-01-06T22:10:34.891562Z","iopub.status.idle":"2022-01-06T22:10:34.895995Z","shell.execute_reply.started":"2022-01-06T22:10:34.891533Z","shell.execute_reply":"2022-01-06T22:10:34.895112Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#define and assign all relevant event types to track and include on kickoff plays (define the universe of relevant plays)\n\ntackle_event_tracking_2018 = tracking_2018[tracking_2018['event'] == 'tackle' ]\ntackle_event_tracking_2019 = tracking_2019[tracking_2019['event'] == 'tackle' ]\ntackle_event_tracking_2020 = tracking_2020[tracking_2020['event'] == 'tackle' ]\n\nfumble_event_tracking_2018 = tracking_2018[tracking_2018['event'] == 'fumble']\nfumble_event_tracking_2019 = tracking_2019[tracking_2019['event'] == 'fumble']\nfumble_event_tracking_2020 = tracking_2020[tracking_2020['event'] == 'fumble']\n\nd_recovery_event_tracking_2018 = tracking_2018[tracking_2018['event'] == 'fumble_defense_recovered']\nd_recovery_event_tracking_2019 = tracking_2019[tracking_2019['event'] == 'fumble_defense_recovered']\nd_recovery_event_tracking_2020 = tracking_2020[tracking_2020['event'] == 'fumble_defense_recovered']\n\ntouchdown_event_tracking_2018 = tracking_2018[tracking_2018['event'] == 'touchdown']\ntouchdown_event_tracking_2019 = tracking_2019[tracking_2019['event'] == 'touchdown']\ntouchdown_event_tracking_2020 = tracking_2020[tracking_2020['event'] == 'touchdown']\n\nsafety_event_tracking_2018 = tracking_2018[tracking_2018['event'] == 'safety']\nsafety_event_tracking_2019 = tracking_2019[tracking_2019['event'] == 'safety']\nsafety_event_tracking_2020 = tracking_2020[tracking_2020['event'] == 'safety']","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:34.897465Z","iopub.execute_input":"2022-01-06T22:10:34.897992Z","iopub.status.idle":"2022-01-06T22:10:45.816259Z","shell.execute_reply.started":"2022-01-06T22:10:34.897935Z","shell.execute_reply":"2022-01-06T22:10:45.815237Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#concatente the relevant play events for each season \nconcat_2018 = [tackle_event_tracking_2018, fumble_event_tracking_2018, d_recovery_event_tracking_2018, touchdown_event_tracking_2018, safety_event_tracking_2018]\nconcat_2019 = [tackle_event_tracking_2019, fumble_event_tracking_2019, d_recovery_event_tracking_2019, touchdown_event_tracking_2019, safety_event_tracking_2019]\nconcat_2020 = [tackle_event_tracking_2020, fumble_event_tracking_2020, d_recovery_event_tracking_2020, touchdown_event_tracking_2020, safety_event_tracking_2020]\n\n#finish concatenation of play frames\nrelevant_events2018 = pd.concat(concat_2018)\nrelevant_events2019 = pd.concat(concat_2019)\nrelevant_events2020 = pd.concat(concat_2020)","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:45.817528Z","iopub.execute_input":"2022-01-06T22:10:45.817752Z","iopub.status.idle":"2022-01-06T22:10:45.861175Z","shell.execute_reply.started":"2022-01-06T22:10:45.817724Z","shell.execute_reply":"2022-01-06T22:10:45.860497Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#inner join the kickoff plays with the relevant play tracking frames, to get the desired events for kickoffs only\nkickoff_play_frames2018 = pd.merge(relevant_events2018, kickoff_plays, on = ['gameId', 'playId'], how = 'inner' )\nkickoff_play_frames2019 = pd.merge(relevant_events2019, kickoff_plays, on = ['gameId', 'playId'], how = 'inner' )\nkickoff_play_frames2020 = pd.merge(relevant_events2020, kickoff_plays, on = ['gameId', 'playId'], how = 'inner' )","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:45.862557Z","iopub.execute_input":"2022-01-06T22:10:45.862790Z","iopub.status.idle":"2022-01-06T22:10:45.991759Z","shell.execute_reply.started":"2022-01-06T22:10:45.862760Z","shell.execute_reply":"2022-01-06T22:10:45.990877Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#get the specific rows labeled 'football' in the displayName column\nfootball_tracking2018 = kickoff_play_frames2018[kickoff_play_frames2018['displayName'] == 'football']\nfootball_tracking2019 = kickoff_play_frames2019[kickoff_play_frames2019['displayName'] == 'football']\nfootball_tracking2020 = kickoff_play_frames2020[kickoff_play_frames2020['displayName'] == 'football']\n\n#create new shortened dataframes for just the football coordinates, columns that will be used to merge with tracking frames\nfootball_coordinates2018 = pd.DataFrame(football_tracking2018, columns = ['x','y','gameId','playId','frameId'])\nfootball_coordinates2019 = pd.DataFrame(football_tracking2019, columns = ['x','y','gameId','playId','frameId'])\nfootball_coordinates2020 = pd.DataFrame(football_tracking2020, columns = ['x','y','gameId','playId','frameId'])\n\n#rename the columns for ball_x and ball_y to differentiate from other x y coordinates in the row\nfootball_coordinates2018 = football_coordinates2018.rename(columns={\"x\": \"ball_x\", \"y\": \"ball_y\"})\nfootball_coordinates2019 = football_coordinates2019.rename(columns={\"x\": \"ball_x\", \"y\": \"ball_y\"})\nfootball_coordinates2020 = football_coordinates2020.rename(columns={\"x\": \"ball_x\", \"y\": \"ball_y\"})","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:45.993253Z","iopub.execute_input":"2022-01-06T22:10:45.993556Z","iopub.status.idle":"2022-01-06T22:10:46.039975Z","shell.execute_reply.started":"2022-01-06T22:10:45.993513Z","shell.execute_reply":"2022-01-06T22:10:46.039249Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#define two functions that adjust the ball coordinates to keep them in the field of play\ndef adjust_x_inbounds(x):\n    if x < 0.0:\n        return 0.0\n    elif x > 120.0:\n        return 120.0\n    else:\n        return x\n\ndef adjust_y_inbounds(y):\n    if y < 0.0:\n        return 0.0\n    elif y > 53.33:\n        return 53.33\n    else:\n        return y","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:46.041078Z","iopub.execute_input":"2022-01-06T22:10:46.041439Z","iopub.status.idle":"2022-01-06T22:10:46.045902Z","shell.execute_reply.started":"2022-01-06T22:10:46.041411Z","shell.execute_reply":"2022-01-06T22:10:46.045097Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#To reduce error by only including distances that are measured when the ball is in bounds, remember to replace y values \n#that are less than 0.0 and greater than 53.33 and x values that are less than 0 or greater than 120. \n\n#replace them with boundry values 0 or 53.33 for y and 0 or 120 for x\n\n#adjust 'ball_x' for anything out of bounds\nfootball_coordinates2018['ball_x'] = football_coordinates2018['ball_x'].apply(lambda x: adjust_x_inbounds(x))\nfootball_coordinates2019['ball_x'] = football_coordinates2019['ball_x'].apply(lambda x: adjust_x_inbounds(x))\nfootball_coordinates2020['ball_x'] = football_coordinates2020['ball_x'].apply(lambda x: adjust_x_inbounds(x))\n\n#adjust 'ball_y' for anything out of bounds\nfootball_coordinates2018['ball_y'] = football_coordinates2018['ball_y'].apply(lambda x: adjust_y_inbounds(x))\nfootball_coordinates2019['ball_y'] = football_coordinates2019['ball_y'].apply(lambda x: adjust_y_inbounds(x))\nfootball_coordinates2020['ball_y'] = football_coordinates2020['ball_y'].apply(lambda x: adjust_y_inbounds(x))","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:46.047360Z","iopub.execute_input":"2022-01-06T22:10:46.047570Z","iopub.status.idle":"2022-01-06T22:10:46.064510Z","shell.execute_reply.started":"2022-01-06T22:10:46.047545Z","shell.execute_reply":"2022-01-06T22:10:46.063579Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#merge the ball coordinates onto each relevent event's corresponding event frame using inner join\nkickoff_play_frames2018 = pd.merge(kickoff_play_frames2018, football_coordinates2018, on = ['gameId', 'playId', 'frameId'], how = 'inner' )\nkickoff_play_frames2019 = pd.merge(kickoff_play_frames2019, football_coordinates2019, on = ['gameId', 'playId', 'frameId'], how = 'inner' )\nkickoff_play_frames2020 = pd.merge(kickoff_play_frames2020, football_coordinates2020, on = ['gameId', 'playId', 'frameId'], how = 'inner' )","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:46.065967Z","iopub.execute_input":"2022-01-06T22:10:46.066248Z","iopub.status.idle":"2022-01-06T22:10:46.314672Z","shell.execute_reply.started":"2022-01-06T22:10:46.066218Z","shell.execute_reply":"2022-01-06T22:10:46.313739Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#inner join game data that shares the same gameId\nkickoff_play_frames2018 = pd.merge(kickoff_play_frames2018, games, on = ['gameId'], how = 'inner' )\nkickoff_play_frames2019 = pd.merge(kickoff_play_frames2019, games, on = ['gameId'], how = 'inner' )\nkickoff_play_frames2020 = pd.merge(kickoff_play_frames2020, games, on = ['gameId'], how = 'inner' )","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:46.315834Z","iopub.execute_input":"2022-01-06T22:10:46.316087Z","iopub.status.idle":"2022-01-06T22:10:46.448918Z","shell.execute_reply.started":"2022-01-06T22:10:46.316043Z","shell.execute_reply":"2022-01-06T22:10:46.448099Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#create a new column kickTeam that runs a conditional match on possessionTeam and homeTeamAbbr to deduce each players team\n#leave a home or away value in the column for matching in the next step\nkickoff_play_frames2018['kickTeam'] = np.where(kickoff_play_frames2018['possessionTeam'] == kickoff_play_frames2018['homeTeamAbbr'],'home' , 'away')\nkickoff_play_frames2019['kickTeam'] = np.where(kickoff_play_frames2019['possessionTeam'] == kickoff_play_frames2019['homeTeamAbbr'],'home' , 'away')\nkickoff_play_frames2020['kickTeam'] = np.where(kickoff_play_frames2020['possessionTeam'] == kickoff_play_frames2020['homeTeamAbbr'],'home' , 'away')\n\n#only include kickoff coverage team player rows by filtering for a match on team and kickTeam\nkickoff_coverage_2018 = kickoff_play_frames2018[ kickoff_play_frames2018['team'] == kickoff_play_frames2018['kickTeam']]\nkickoff_coverage_2019 = kickoff_play_frames2019[ kickoff_play_frames2019['team'] == kickoff_play_frames2019['kickTeam']]\nkickoff_coverage_2020 = kickoff_play_frames2020[ kickoff_play_frames2020['team'] == kickoff_play_frames2020['kickTeam']]","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:46.450102Z","iopub.execute_input":"2022-01-06T22:10:46.450304Z","iopub.status.idle":"2022-01-06T22:10:46.518408Z","shell.execute_reply.started":"2022-01-06T22:10:46.450279Z","shell.execute_reply":"2022-01-06T22:10:46.517575Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#cast 'jerseyNumber' as integer instead of float\nkickoff_coverage_2018['jerseyNumber'] = kickoff_coverage_2018['jerseyNumber'].map(int)\nkickoff_coverage_2019['jerseyNumber'] = kickoff_coverage_2019['jerseyNumber'].map(int)\nkickoff_coverage_2020['jerseyNumber'] = kickoff_coverage_2020['jerseyNumber'].map(int)\n\n#concatenate team and number for the player\nkickoff_coverage_2018['teamNumber'] = kickoff_coverage_2018['possessionTeam']+ ' ' + kickoff_coverage_2018['jerseyNumber'].map(str)  \nkickoff_coverage_2019['teamNumber'] = kickoff_coverage_2019['possessionTeam']+ ' ' + kickoff_coverage_2019['jerseyNumber'].map(str) \nkickoff_coverage_2020['teamNumber'] = kickoff_coverage_2020['possessionTeam']+ ' ' + kickoff_coverage_2020['jerseyNumber'].map(str) \n","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:46.519485Z","iopub.execute_input":"2022-01-06T22:10:46.519689Z","iopub.status.idle":"2022-01-06T22:10:46.555138Z","shell.execute_reply.started":"2022-01-06T22:10:46.519664Z","shell.execute_reply":"2022-01-06T22:10:46.554156Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#drop irrelevant columns such as 'Out of Bounds', 'Touchback', and ‘Downed’\n\n#filter out \"Out of Bounds\"\nkickoff_coverage_2018 = kickoff_coverage_2018[ kickoff_coverage_2018['specialTeamsResult'] != 'Out of Bounds']\nkickoff_coverage_2019 = kickoff_coverage_2019[ kickoff_coverage_2019['specialTeamsResult'] != 'Out of Bounds']\nkickoff_coverage_2020 = kickoff_coverage_2020[ kickoff_coverage_2020['specialTeamsResult'] != 'Out of Bounds']\n\n#filter out specialTeamsResult == \"Touchback\"\nkickoff_coverage_2018 = kickoff_coverage_2018[ kickoff_coverage_2018['specialTeamsResult'] != 'Touchback']\nkickoff_coverage_2019 = kickoff_coverage_2019[ kickoff_coverage_2019['specialTeamsResult'] != 'Touchback']\nkickoff_coverage_2020 = kickoff_coverage_2020[ kickoff_coverage_2020['specialTeamsResult'] != 'Touchback']\n\n#filter out \"Downed\", since they were all plays negated by penalty\nkickoff_coverage_2018 = kickoff_coverage_2018[ kickoff_coverage_2018['specialTeamsResult'] != 'Downed']\nkickoff_coverage_2019 = kickoff_coverage_2019[ kickoff_coverage_2019['specialTeamsResult'] != 'Downed']\nkickoff_coverage_2020 = kickoff_coverage_2020[ kickoff_coverage_2020['specialTeamsResult'] != 'Downed']","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:46.556791Z","iopub.execute_input":"2022-01-06T22:10:46.557157Z","iopub.status.idle":"2022-01-06T22:10:46.623939Z","shell.execute_reply.started":"2022-01-06T22:10:46.557127Z","shell.execute_reply":"2022-01-06T22:10:46.623153Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#merge selected PffScouting columns to the kickoff_coverage_2018 df for testing for tackle/assisted/missedtackle rates\nPff_Scouting = pd.DataFrame(pff, columns = ['gameId','playId','tackler','assistTackler','missedTackler','hangTime','kickType','kickContactType'])","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:46.624878Z","iopub.execute_input":"2022-01-06T22:10:46.625583Z","iopub.status.idle":"2022-01-06T22:10:46.632026Z","shell.execute_reply.started":"2022-01-06T22:10:46.625542Z","shell.execute_reply":"2022-01-06T22:10:46.631040Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#merge PFFScouting columns onto kickoff_coverage dataframes\nkickoff_coverage_2018 = pd.merge(kickoff_coverage_2018, Pff_Scouting, on = ['gameId', 'playId'], how = 'inner' )\nkickoff_coverage_2019 = pd.merge(kickoff_coverage_2019, Pff_Scouting, on = ['gameId', 'playId'], how = 'inner' )\nkickoff_coverage_2020 = pd.merge(kickoff_coverage_2020, Pff_Scouting, on = ['gameId', 'playId'], how = 'inner' )","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:46.633238Z","iopub.execute_input":"2022-01-06T22:10:46.633536Z","iopub.status.idle":"2022-01-06T22:10:46.833425Z","shell.execute_reply.started":"2022-01-06T22:10:46.633506Z","shell.execute_reply":"2022-01-06T22:10:46.832645Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#start the creation of the stats that we will use to count for the analysis\n#create the column 'ball_distance' by using the formula for Euclidean distance on columns 'x', 'y', 'ball_x' and 'ball_y'\nkickoff_coverage_2018['ball_distance'] = ((kickoff_coverage_2018['x'] - kickoff_coverage_2018['ball_x'])**2 + (kickoff_coverage_2018['y'] - kickoff_coverage_2018['ball_y'])**2)**(0.5) \nkickoff_coverage_2019['ball_distance'] = ((kickoff_coverage_2019['x'] - kickoff_coverage_2019['ball_x'])**2 + (kickoff_coverage_2019['y'] - kickoff_coverage_2019['ball_y'])**2)**(0.5) \nkickoff_coverage_2020['ball_distance'] = ((kickoff_coverage_2020['x'] - kickoff_coverage_2020['ball_x'])**2 + (kickoff_coverage_2020['y'] - kickoff_coverage_2020['ball_y'])**2)**(0.5) ","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:46.834799Z","iopub.execute_input":"2022-01-06T22:10:46.835036Z","iopub.status.idle":"2022-01-06T22:10:46.845787Z","shell.execute_reply.started":"2022-01-06T22:10:46.835009Z","shell.execute_reply":"2022-01-06T22:10:46.844943Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#give tackle credit as 1 for tackle made or 0 for no tackle made when there is a match on teamNumber and tackler\nkickoff_coverage_2018['tackle_credit'] = np.where(kickoff_coverage_2018['teamNumber'] == kickoff_coverage_2018['tackler'],1,0)\nkickoff_coverage_2019['tackle_credit'] = np.where(kickoff_coverage_2019['teamNumber'] == kickoff_coverage_2019['tackler'],1,0)\nkickoff_coverage_2020['tackle_credit'] = np.where(kickoff_coverage_2020['teamNumber'] == kickoff_coverage_2020['tackler'],1,0)","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:46.847110Z","iopub.execute_input":"2022-01-06T22:10:46.847431Z","iopub.status.idle":"2022-01-06T22:10:46.859667Z","shell.execute_reply.started":"2022-01-06T22:10:46.847404Z","shell.execute_reply":"2022-01-06T22:10:46.858925Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#make an 'assist_credit' column for assistTackler on each play frame, credit 1 or 0 if there is a match\nkickoff_coverage_2018['assist_credit'] = np.where(kickoff_coverage_2018['teamNumber'] == kickoff_coverage_2018['assistTackler'], 1, 0)\nkickoff_coverage_2019['assist_credit'] = np.where(kickoff_coverage_2019['teamNumber'] == kickoff_coverage_2019['assistTackler'], 1, 0)\nkickoff_coverage_2020['assist_credit'] = np.where(kickoff_coverage_2020['teamNumber'] == kickoff_coverage_2020['assistTackler'], 1, 0)","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:46.860818Z","iopub.execute_input":"2022-01-06T22:10:46.861261Z","iopub.status.idle":"2022-01-06T22:10:46.878095Z","shell.execute_reply.started":"2022-01-06T22:10:46.861211Z","shell.execute_reply":"2022-01-06T22:10:46.877286Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#with missed tackles, there is much more parsing, so there is a different process\n#start by creating the column 'missed_tackle_credit'\nkickoff_coverage_2018['missed_tackle_credit'] = ''\nkickoff_coverage_2019['missed_tackle_credit'] = ''\nkickoff_coverage_2020['missed_tackle_credit'] = ''","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:46.880970Z","iopub.execute_input":"2022-01-06T22:10:46.881251Z","iopub.status.idle":"2022-01-06T22:10:46.887928Z","shell.execute_reply.started":"2022-01-06T22:10:46.881215Z","shell.execute_reply":"2022-01-06T22:10:46.887383Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#iterate through the equal length series missedTackler one by one by index, and split them or leave them for the next step\n#use counts to check work of the missed tackle crediting algorithm\ncount = 0\nmult_miss_tackle_play_count = 0\nsingular_missed_tackle_play_count = 0\nNA_count = 0\n\nfor row in range(0, len(kickoff_coverage_2018['missedTackler'])):\n    if len(str(kickoff_coverage_2018['missedTackler'][row])) > 6:\n        split_list= str(kickoff_coverage_2018['missedTackler'][row]).split(';')\n        new_list = [split_list[0]]\n        for player in split_list[1:]:\n            new_list.append(player[1:])\n        kickoff_coverage_2018['missedTackler'][row] = new_list\n        count+=1\n        mult_miss_tackle_play_count +=1\n    \n    elif len(str(kickoff_coverage_2018['missedTackler'][row])) <= 6 and len(str(kickoff_coverage_2018['missedTackler'][row])) >=4:\n        singular_missed_tackle_play_count+=1\n        count+=1\n        pass\n    else: \n        count+=1\n        NA_count+=1\n        pass\n#print(count)\n#print(mult_miss_tackle_play_count)\n#print(singular_missed_tackle_play_count)\n#print(NA_count)","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:46.889013Z","iopub.execute_input":"2022-01-06T22:10:46.889260Z","iopub.status.idle":"2022-01-06T22:10:47.175331Z","shell.execute_reply.started":"2022-01-06T22:10:46.889232Z","shell.execute_reply":"2022-01-06T22:10:47.174529Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#2019 missed tackle check\ncount = 0\nmult_miss_tackle_play_count = 0\nsingular_missed_tackle_play_count = 0\nNA_count = 0\nfor row in range(0, len(kickoff_coverage_2019['missedTackler'])):\n    if len(str(kickoff_coverage_2019['missedTackler'][row])) > 6:\n        split_list= str(kickoff_coverage_2019['missedTackler'][row]).split(';')\n        new_list = [split_list[0]]\n        for player in split_list[1:]:\n            new_list.append(player[1:])\n        kickoff_coverage_2019['missedTackler'][row] = new_list\n        count+=1\n        mult_miss_tackle_play_count +=1\n    \n    elif len(str(kickoff_coverage_2019['missedTackler'][row])) <= 6 and len(str(kickoff_coverage_2019['missedTackler'][row])) >=4:\n        singular_missed_tackle_play_count+=1\n        count+=1\n        pass\n    else: \n        count+=1\n        NA_count+=1\n        pass\n#print(count)\n#print(mult_miss_tackle_play_count)\n#print(singular_missed_tackle_play_count)\n#print(NA_count)","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:47.180515Z","iopub.execute_input":"2022-01-06T22:10:47.180922Z","iopub.status.idle":"2022-01-06T22:10:47.525909Z","shell.execute_reply.started":"2022-01-06T22:10:47.180883Z","shell.execute_reply":"2022-01-06T22:10:47.525079Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#2020\ncount = 0\nmult_miss_tackle_play_count = 0\nsingular_missed_tackle_play_count = 0\nNA_count = 0\nfor row in range(0, len(kickoff_coverage_2020['missedTackler'])):\n    if len(str(kickoff_coverage_2020['missedTackler'][row])) > 6:\n        split_list= str(kickoff_coverage_2020['missedTackler'][row]).split(';')\n        new_list = [split_list[0]]\n        for player in split_list[1:]:\n            new_list.append(player[1:])\n        kickoff_coverage_2020['missedTackler'][row] = new_list\n        count+=1\n        mult_miss_tackle_play_count +=1\n    \n    elif len(str(kickoff_coverage_2020['missedTackler'][row])) <= 6 and len(str(kickoff_coverage_2020['missedTackler'][row])) >=4:\n        singular_missed_tackle_play_count+=1\n        count+=1\n        pass\n    else: \n        count+=1\n        NA_count+=1\n        pass\n#print(count)\n#print(mult_miss_tackle_play_count)\n#print(singular_missed_tackle_play_count)\n#print(NA_count)","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:47.527104Z","iopub.execute_input":"2022-01-06T22:10:47.527517Z","iopub.status.idle":"2022-01-06T22:10:47.859360Z","shell.execute_reply.started":"2022-01-06T22:10:47.527486Z","shell.execute_reply":"2022-01-06T22:10:47.858038Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#iterate through each df and check if there is a match between teamNumber and one of the missed tackle strings\nfor row in range(0, len(kickoff_coverage_2018['missedTackler'])):\n    if len(str(kickoff_coverage_2018['missedTackler'][row])) > 6: #takes care of more than 1 misses\n        #print(kickoff_coverage_2018['missedTackler'][row], len(kickoff_coverage_2018['missedTackler'][row]))\n        for player in kickoff_coverage_2018['missedTackler'][row]:\n            if player == kickoff_coverage_2018['teamNumber'][row]:\n                kickoff_coverage_2018['missed_tackle_credit'][row] = 1\n            else:\n                pass\n    else: #taking care of singular misses or NA\n        if kickoff_coverage_2018['missedTackler'][row] == kickoff_coverage_2018['teamNumber'][row]:\n            kickoff_coverage_2018['missed_tackle_credit'][row] = 1\n        else:\n            pass\n#2019 missed tackle award credit      \nfor row in range(0, len(kickoff_coverage_2019['missedTackler'])):\n    if len(str(kickoff_coverage_2019['missedTackler'][row])) > 6: #takes care of more than 1 misses\n        #print(kickoff_coverage_2018['missedTackler'][row], len(kickoff_coverage_2018['missedTackler'][row]))\n        for player in kickoff_coverage_2019['missedTackler'][row]:\n            if player == kickoff_coverage_2019['teamNumber'][row]:\n                kickoff_coverage_2019['missed_tackle_credit'][row] = 1\n            else:\n                pass\n    else: #taking care of singular misses or NA\n        if kickoff_coverage_2019['missedTackler'][row] == kickoff_coverage_2019['teamNumber'][row]:\n            kickoff_coverage_2019['missed_tackle_credit'][row] = 1\n        else:\n            pass\n#2020 missed tackle award credit\nfor row in range(0, len(kickoff_coverage_2020['missedTackler'])):\n    if len(str(kickoff_coverage_2020['missedTackler'][row])) > 6: #takes care of more than 1 misses\n        #print(kickoff_coverage_2018['missedTackler'][row], len(kickoff_coverage_2018['missedTackler'][row]))\n        for player in kickoff_coverage_2020['missedTackler'][row]:\n            if player == kickoff_coverage_2020['teamNumber'][row]:\n                kickoff_coverage_2020['missed_tackle_credit'][row] = 1\n            else:\n                pass\n    else: #taking care of singular misses or NA\n        if kickoff_coverage_2020['missedTackler'][row] == kickoff_coverage_2020['teamNumber'][row]:\n            kickoff_coverage_2020['missed_tackle_credit'][row] = 1\n        else:\n            pass","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:47.860899Z","iopub.execute_input":"2022-01-06T22:10:47.861172Z","iopub.status.idle":"2022-01-06T22:10:48.705245Z","shell.execute_reply.started":"2022-01-06T22:10:47.861139Z","shell.execute_reply":"2022-01-06T22:10:48.704258Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#input 0 for no missed tackle credit\nkickoff_coverage_2018['missed_tackle_credit'] = np.where(kickoff_coverage_2018['missed_tackle_credit'] == 1, 1, 0)\nkickoff_coverage_2019['missed_tackle_credit'] = np.where(kickoff_coverage_2019['missed_tackle_credit'] == 1, 1, 0)\nkickoff_coverage_2020['missed_tackle_credit'] = np.where(kickoff_coverage_2020['missed_tackle_credit'] == 1, 1, 0)","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:48.706490Z","iopub.execute_input":"2022-01-06T22:10:48.707014Z","iopub.status.idle":"2022-01-06T22:10:48.718882Z","shell.execute_reply.started":"2022-01-06T22:10:48.706968Z","shell.execute_reply":"2022-01-06T22:10:48.717823Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#start to create the defensive fumble recovery stat","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:48.720464Z","iopub.execute_input":"2022-01-06T22:10:48.720724Z","iopub.status.idle":"2022-01-06T22:10:48.726599Z","shell.execute_reply.started":"2022-01-06T22:10:48.720696Z","shell.execute_reply":"2022-01-06T22:10:48.725803Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#create a defensive fumble recovery statistic to get d_recovery_rate\ndef split_lastName(x):\n    name = x.split()\n    last_name = name[1]\n    return last_name","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:48.728183Z","iopub.execute_input":"2022-01-06T22:10:48.728542Z","iopub.status.idle":"2022-01-06T22:10:48.738187Z","shell.execute_reply.started":"2022-01-06T22:10:48.728506Z","shell.execute_reply":"2022-01-06T22:10:48.737233Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#get just lastName to match on recovery_lastName\nkickoff_coverage_2018['lastName'] = kickoff_coverage_2018['displayName'].apply(lambda x: split_lastName(x)) \nkickoff_coverage_2019['lastName'] = kickoff_coverage_2019['displayName'].apply(lambda x: split_lastName(x)) \nkickoff_coverage_2020['lastName'] = kickoff_coverage_2020['displayName'].apply(lambda x: split_lastName(x))","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:48.739431Z","iopub.execute_input":"2022-01-06T22:10:48.739748Z","iopub.status.idle":"2022-01-06T22:10:48.768749Z","shell.execute_reply.started":"2022-01-06T22:10:48.739708Z","shell.execute_reply":"2022-01-06T22:10:48.768135Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#give fumble recovery credit when kickoff player gets a defensive recovery, 1 or 0\nkickoff_coverage_2018['d_recovery_credit'] = np.where(kickoff_coverage_2018['recovery_lastName'] == kickoff_coverage_2018['lastName'], 1, 0)\nkickoff_coverage_2019['d_recovery_credit'] = np.where(kickoff_coverage_2019['recovery_lastName'] == kickoff_coverage_2019['lastName'], 1, 0)\nkickoff_coverage_2020['d_recovery_credit'] = np.where(kickoff_coverage_2020['recovery_lastName'] == kickoff_coverage_2020['lastName'], 1, 0)","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:48.769870Z","iopub.execute_input":"2022-01-06T22:10:48.770698Z","iopub.status.idle":"2022-01-06T22:10:48.781576Z","shell.execute_reply.started":"2022-01-06T22:10:48.770644Z","shell.execute_reply":"2022-01-06T22:10:48.780677Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#create a count mechanism for tackles and assisted tackles grouped by gameid, playid using a pivot table\n#switched displayName and nflId\n\npivot_kickoff_2018 = pd.pivot_table(kickoff_coverage_2018, index=['gameId','playId','nflId', 'displayName'], values=['ball_distance', 'tackle_credit', 'assist_credit', 'missed_tackle_credit', 'd_recovery_credit'],aggfunc=np.sum)\npivot_kickoff_2019 = pd.pivot_table(kickoff_coverage_2019, index=['gameId','playId','nflId', 'displayName'], values=['ball_distance', 'tackle_credit', 'assist_credit', 'missed_tackle_credit', 'd_recovery_credit'],aggfunc=np.sum)\npivot_kickoff_2020 = pd.pivot_table(kickoff_coverage_2020, index=['gameId','playId','nflId', 'displayName'], values=['ball_distance', 'tackle_credit', 'assist_credit', 'missed_tackle_credit', 'd_recovery_credit'],aggfunc=np.sum)","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:48.783197Z","iopub.execute_input":"2022-01-06T22:10:48.783893Z","iopub.status.idle":"2022-01-06T22:10:48.925922Z","shell.execute_reply.started":"2022-01-06T22:10:48.783836Z","shell.execute_reply":"2022-01-06T22:10:48.924952Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#inner merge player table onto the pivot table\npivot_kickoff_2018 = pd.merge(pivot_kickoff_2018, players, on = 'nflId', how = 'inner')\npivot_kickoff_2019 = pd.merge(pivot_kickoff_2019, players, on = 'nflId', how = 'inner')\npivot_kickoff_2020 = pd.merge(pivot_kickoff_2020, players, on = 'nflId', how = 'inner')","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:48.927337Z","iopub.execute_input":"2022-01-06T22:10:48.927658Z","iopub.status.idle":"2022-01-06T22:10:48.963356Z","shell.execute_reply.started":"2022-01-06T22:10:48.927615Z","shell.execute_reply":"2022-01-06T22:10:48.962393Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#once the pivot table is made, be sure to adjust to make sure there is only one credit per column per player at most\n\n#adjust to make sure tackles are only being awarded once per play, not for each frame in df\npivot_kickoff_2018.loc[pivot_kickoff_2018['tackle_credit'] > 1, 'tackle_credit'] = 1\npivot_kickoff_2019.loc[pivot_kickoff_2019['tackle_credit'] > 1, 'tackle_credit'] = 1\npivot_kickoff_2020.loc[pivot_kickoff_2020['tackle_credit'] > 1, 'tackle_credit'] = 1\n\n#adjust to make sure assisted tackles are only being awarded once per play, not for each frame in df\npivot_kickoff_2018.loc[pivot_kickoff_2018['assist_credit'] > 1, 'assist_credit'] = 1\npivot_kickoff_2019.loc[pivot_kickoff_2019['assist_credit'] > 1, 'assist_credit'] = 1\npivot_kickoff_2020.loc[pivot_kickoff_2020['assist_credit'] > 1, 'assist_credit'] = 1\n\n#adjust to make sure missed tackles are only being awarded once per play, not for each frame in df\npivot_kickoff_2018.loc[pivot_kickoff_2018['missed_tackle_credit'] > 1, 'missed_tackle_credit'] = 1\npivot_kickoff_2019.loc[pivot_kickoff_2019['missed_tackle_credit'] > 1, 'missed_tackle_credit'] = 1\npivot_kickoff_2020.loc[pivot_kickoff_2020['missed_tackle_credit'] > 1, 'missed_tackle_credit'] = 1\n\n#adjust to make sure missed tackles are only being awarded once per play, not for each frame in df\npivot_kickoff_2018.loc[pivot_kickoff_2018['d_recovery_credit'] > 1, 'd_recovery_credit'] = 1\npivot_kickoff_2019.loc[pivot_kickoff_2019['d_recovery_credit'] > 1, 'd_recovery_credit'] = 1\npivot_kickoff_2020.loc[pivot_kickoff_2020['d_recovery_credit'] > 1, 'd_recovery_credit'] = 1","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:48.964569Z","iopub.execute_input":"2022-01-06T22:10:48.964804Z","iopub.status.idle":"2022-01-06T22:10:48.980951Z","shell.execute_reply.started":"2022-01-06T22:10:48.964775Z","shell.execute_reply":"2022-01-06T22:10:48.980289Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#create touch_credit column for each row in the pivot table to check if a player tackled, assisted or missed on the play\npivot_kickoff_2018['touch_credit'] = pivot_kickoff_2018['tackle_credit'] + pivot_kickoff_2018['assist_credit'] + pivot_kickoff_2018['missed_tackle_credit']\npivot_kickoff_2019['touch_credit'] = pivot_kickoff_2019['tackle_credit'] + pivot_kickoff_2019['assist_credit'] + pivot_kickoff_2019['missed_tackle_credit']\npivot_kickoff_2020['touch_credit'] = pivot_kickoff_2020['tackle_credit'] + pivot_kickoff_2020['assist_credit'] + pivot_kickoff_2020['missed_tackle_credit']","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:48.982400Z","iopub.execute_input":"2022-01-06T22:10:48.982778Z","iopub.status.idle":"2022-01-06T22:10:48.994427Z","shell.execute_reply.started":"2022-01-06T22:10:48.982737Z","shell.execute_reply":"2022-01-06T22:10:48.993467Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#copy the nflId column into a new column count_id because it will be used to count the number of plays \npivot_kickoff_2018['count_id'] = pivot_kickoff_2018['nflId']\npivot_kickoff_2019['count_id'] = pivot_kickoff_2019['nflId']\npivot_kickoff_2020['count_id'] = pivot_kickoff_2020['nflId']","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:48.995888Z","iopub.execute_input":"2022-01-06T22:10:48.996160Z","iopub.status.idle":"2022-01-06T22:10:49.009554Z","shell.execute_reply.started":"2022-01-06T22:10:48.996128Z","shell.execute_reply":"2022-01-06T22:10:49.008617Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#aggregate the pivot table to get tackle and play totals, tackle_rate, touch_rate, d_recovery_rate, grouped by nflid\nkickoff_tackle_touch_rates_2018 = pivot_kickoff_2018.groupby('nflId').agg(num_tackles = ('tackle_credit', 'sum'), num_plays = ('count_id', 'size'), tackle_rate = ('tackle_credit', 'mean'), touch_rate = ('touch_credit', 'mean'), fumble_recovery_rate = ('d_recovery_credit', 'mean'))\nkickoff_tackle_touch_rates_2019 = pivot_kickoff_2019.groupby('nflId').agg(num_tackles = ('tackle_credit', 'sum'), num_plays = ('count_id', 'size'), tackle_rate = ('tackle_credit', 'mean'), touch_rate = ('touch_credit', 'mean'), fumble_recovery_rate = ('d_recovery_credit', 'mean')) \nkickoff_tackle_touch_rates_2020 = pivot_kickoff_2020.groupby('nflId').agg(num_tackles = ('tackle_credit', 'sum'), num_plays = ('count_id', 'size'), tackle_rate = ('tackle_credit', 'mean'), touch_rate = ('touch_credit', 'mean'), fumble_recovery_rate = ('d_recovery_credit', 'mean')) \n","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:49.011245Z","iopub.execute_input":"2022-01-06T22:10:49.011908Z","iopub.status.idle":"2022-01-06T22:10:49.051453Z","shell.execute_reply.started":"2022-01-06T22:10:49.011857Z","shell.execute_reply":"2022-01-06T22:10:49.050470Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#create summary dataframes for 'BPA' metric from kickoff coverage tables for each season grouping by nflId \n#and getting the mean of 'ball_distance' for each player and count the number of instances they had a \n#ball_distance observation\n\nBall_Proximity_Avgs2018 = kickoff_coverage_2018.groupby('nflId') \\\n       .agg(event_count=('nflId', 'size'), ball_proximity_avg=('ball_distance', 'mean')) \n\nBall_Proximity_Avgs2019 = kickoff_coverage_2019.groupby('nflId') \\\n       .agg(event_count =('nflId', 'size'), ball_proximity_avg=('ball_distance', 'mean')) \n\nBall_Proximity_Avgs2020 = kickoff_coverage_2020.groupby('nflId') \\\n       .agg(event_count=('nflId', 'size'), ball_proximity_avg=('ball_distance', 'mean')) ","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:49.055157Z","iopub.execute_input":"2022-01-06T22:10:49.055437Z","iopub.status.idle":"2022-01-06T22:10:49.086506Z","shell.execute_reply.started":"2022-01-06T22:10:49.055401Z","shell.execute_reply":"2022-01-06T22:10:49.085287Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#inner merge ball proximity avg table on nflId\nmerged_BPA_stats_2018 = pd.merge(Ball_Proximity_Avgs2018, kickoff_tackle_touch_rates_2018, on = ['nflId'], how = 'inner' )\nmerged_BPA_stats_2019 = pd.merge(Ball_Proximity_Avgs2019, kickoff_tackle_touch_rates_2019, on = ['nflId'], how = 'inner' )\nmerged_BPA_stats_2020 = pd.merge(Ball_Proximity_Avgs2020, kickoff_tackle_touch_rates_2020, on = ['nflId'], how = 'inner' )","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:49.087837Z","iopub.execute_input":"2022-01-06T22:10:49.088126Z","iopub.status.idle":"2022-01-06T22:10:49.104141Z","shell.execute_reply.started":"2022-01-06T22:10:49.088090Z","shell.execute_reply":"2022-01-06T22:10:49.103343Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#inner merge players table on nflId one last time to include player info on final refined tables\nfinal_merged_BPA_stats_2018 = pd.merge(merged_BPA_stats_2018, players, on = ['nflId'], how = 'inner' )\nfinal_merged_BPA_stats_2019 = pd.merge(merged_BPA_stats_2019, players, on = ['nflId'], how = 'inner' )\nfinal_merged_BPA_stats_2020 = pd.merge(merged_BPA_stats_2020, players, on = ['nflId'], how = 'inner' )","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:49.105701Z","iopub.execute_input":"2022-01-06T22:10:49.106467Z","iopub.status.idle":"2022-01-06T22:10:49.133009Z","shell.execute_reply.started":"2022-01-06T22:10:49.106428Z","shell.execute_reply":"2022-01-06T22:10:49.132022Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#adjust all rates to become percentages, round to 2 decimal places\nfinal_merged_BPA_stats_2018['tackle_rate'] = round(final_merged_BPA_stats_2018['tackle_rate']*100, 2)\nfinal_merged_BPA_stats_2019['tackle_rate'] = round(final_merged_BPA_stats_2019['tackle_rate']*100, 2)\nfinal_merged_BPA_stats_2020['tackle_rate'] = round(final_merged_BPA_stats_2020['tackle_rate']*100, 2)\n\nfinal_merged_BPA_stats_2018['touch_rate'] = round(final_merged_BPA_stats_2018['touch_rate']*100, 2)\nfinal_merged_BPA_stats_2019['touch_rate'] = round(final_merged_BPA_stats_2019['touch_rate']*100, 2)\nfinal_merged_BPA_stats_2020['touch_rate'] = round(final_merged_BPA_stats_2020['touch_rate']*100, 2)\n\nfinal_merged_BPA_stats_2018['fumble_recovery_rate'] = round(final_merged_BPA_stats_2018['fumble_recovery_rate']*100, 2)\nfinal_merged_BPA_stats_2019['fumble_recovery_rate'] = round(final_merged_BPA_stats_2019['fumble_recovery_rate']*100, 2)\nfinal_merged_BPA_stats_2020['fumble_recovery_rate'] = round(final_merged_BPA_stats_2020['fumble_recovery_rate']*100, 2)","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:49.134377Z","iopub.execute_input":"2022-01-06T22:10:49.134629Z","iopub.status.idle":"2022-01-06T22:10:49.148178Z","shell.execute_reply.started":"2022-01-06T22:10:49.134596Z","shell.execute_reply":"2022-01-06T22:10:49.147116Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#export the final aggregated data tables to .csv file for analysis in R and/or Tableau\n\n#final_merged_BPA_stats_2018.to_csv('merged_BPA_stats_2018.csv')\n#final_merged_BPA_stats_2019.to_csv('merged_BPA_stats_2019.csv')\n#final_merged_BPA_stats_2020.to_csv('merged_BPA_stats_2020.csv')","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:49.149453Z","iopub.execute_input":"2022-01-06T22:10:49.150163Z","iopub.status.idle":"2022-01-06T22:10:49.160163Z","shell.execute_reply.started":"2022-01-06T22:10:49.150121Z","shell.execute_reply":"2022-01-06T22:10:49.158960Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#from here, show work that leads to a final conclusion\nimport seaborn as sns","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:49.161941Z","iopub.execute_input":"2022-01-06T22:10:49.162530Z","iopub.status.idle":"2022-01-06T22:10:50.033865Z","shell.execute_reply.started":"2022-01-06T22:10:49.162486Z","shell.execute_reply":"2022-01-06T22:10:50.032694Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#all sets having min. of 16 plays\nMin_16plays = final_merged_BPA_stats_2018[final_merged_BPA_stats_2018['num_plays'] >=16]\n\n#sets excluding kickers, punters, and qbs\nnon_kickers_2018 = Min_16plays[Min_16plays['Position']!= 'K']\nnon_kickers_Punters_2018 = non_kickers_2018[non_kickers_2018['Position']!= 'P']\nnon_kickers_Punters_Qbs_2018 = non_kickers_Punters_2018[non_kickers_Punters_2018['Position'] != 'QB']\n\n#sets filtered by age\n#under 25\nAgeU25_Min_16plays_2018 = non_kickers_Punters_Qbs_2018[non_kickers_Punters_Qbs_2018['age_2018']<25]\n#25-29\nAge2529_Min_16plays_2018 = non_kickers_Punters_Qbs_2018[non_kickers_Punters_Qbs_2018['age_2018']>=25]\nAge2529_Min_16plays_2018 = Age2529_Min_16plays_2018[Age2529_Min_16plays_2018['age_2018']<=29]\n#30 and over\nAge30plus_Min_16plays_2018 = non_kickers_Punters_Qbs_2018[non_kickers_Punters_Qbs_2018['age_2018']>=30]\n","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:50.035439Z","iopub.execute_input":"2022-01-06T22:10:50.038940Z","iopub.status.idle":"2022-01-06T22:10:50.054728Z","shell.execute_reply.started":"2022-01-06T22:10:50.038887Z","shell.execute_reply":"2022-01-06T22:10:50.053713Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Visualizations","metadata":{}},{"cell_type":"code","source":"#BPA vs. Tackle Rates Segmented by age\n\nplt.figure(figsize=(8,5))\nsns.scatterplot(data=AgeU25_Min_16plays_2018,x='tackle_rate',y='ball_proximity_avg').set(title='BPA vs. Tackle Rate for Players under 25')\n\nplt.figure(figsize=(8,5))\nsns.scatterplot(data=AgeU25_Min_16plays_2018,x='touch_rate',y='ball_proximity_avg').set(title='BPA vs. Touch Rate for Players under 25')\n\nplt.figure(figsize=(8,5))\nsns.scatterplot(data=Age2529_Min_16plays_2018,x='tackle_rate',y='ball_proximity_avg').set(title='BPA vs. Tackle Rate for Players 25-29')\n\nplt.figure(figsize=(8,5))\nsns.scatterplot(data=Age2529_Min_16plays_2018,x='touch_rate',y='ball_proximity_avg').set(title='BPA vs. Touch Rate for Players 25-29')\n\nplt.figure(figsize=(8,5))\nsns.scatterplot(data=Age30plus_Min_16plays_2018,x='tackle_rate',y='ball_proximity_avg').set(title='BPA vs. Tackle Rate for Players 30 and over')\n\nplt.figure(figsize=(8,5))\nsns.scatterplot(data=Age30plus_Min_16plays_2018,x='touch_rate',y='ball_proximity_avg').set(title='BPA vs. Touch Rate for Players 30 and over')","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:37:19.776489Z","iopub.execute_input":"2022-01-06T22:37:19.776850Z","iopub.status.idle":"2022-01-06T22:37:21.294965Z","shell.execute_reply.started":"2022-01-06T22:37:19.776807Z","shell.execute_reply":"2022-01-06T22:37:21.294032Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"final_merged_BPA_stats_2018.groupby('age_2018')['ball_proximity_avg'].mean().plot(kind='line')","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:10:50.742998Z","iopub.execute_input":"2022-01-06T22:10:50.743667Z","iopub.status.idle":"2022-01-06T22:10:50.961768Z","shell.execute_reply.started":"2022-01-06T22:10:50.743622Z","shell.execute_reply":"2022-01-06T22:10:50.959799Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"position_BPA = final_merged_BPA_stats_2018.groupby('Position')['ball_proximity_avg'].mean()\nindexed_position_BPA= position_BPA.reset_index()\nindexed_position_BPA.sort_values('ball_proximity_avg')","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:26:26.223742Z","iopub.execute_input":"2022-01-06T22:26:26.224448Z","iopub.status.idle":"2022-01-06T22:26:26.240886Z","shell.execute_reply.started":"2022-01-06T22:26:26.224404Z","shell.execute_reply":"2022-01-06T22:26:26.240283Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Average BPA by college sorted ascending\ncollege_BPA = final_merged_BPA_stats_2018.groupby('collegeName')['ball_proximity_avg'].mean()\ncollege_BPA.reset_index()\ncollege_BPA_sorted = college_BPA.sort_values()\ncollege_BPA_sorted.head(40)","metadata":{"execution":{"iopub.status.busy":"2022-01-06T22:21:59.775209Z","iopub.execute_input":"2022-01-06T22:21:59.775532Z","iopub.status.idle":"2022-01-06T22:21:59.787750Z","shell.execute_reply.started":"2022-01-06T22:21:59.775497Z","shell.execute_reply":"2022-01-06T22:21:59.786730Z"},"trusted":true},"execution_count":null,"outputs":[]}]}