{"cells":[{"metadata":{"_uuid":"240fcca8e904078565fb7a979f27a2d6ab4260b6"},"cell_type":"markdown","source":"## Data Output Supporting Submission\n### To view the submission please visit [NFL Punt Safety (McGovern-Steussie](https://www.kaggle.com/mcgovey/nfl-punt-safety-mcgovern-steussie)"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-output":true,"_kg_hide-input":true},"cell_type":"code","source":"import numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\nimport math\n\n# Input data files are available in the \"../input/\" directory.\n# For example, running this (by clicking run or pressing Shift+Enter) will list the files in the input directory\n\nimport os\nprint(os.listdir(\"../input\"))\n\n# Any results you write to the current directory are saved as output.","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# Read video footage data\ninjDF = pd.read_csv('../input/NFL-Punt-Analytics-Competition/video_footage-injury.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"51e74a999cc9144776a88e5bf8bb0a0d90c2e519","_kg_hide-input":true},"cell_type":"code","source":"#Cleanup columns\ninjDF = injDF.drop(columns=['season','Type','Home_team','Visit_Team'])\ninjDF['InjCtrlFlag'] = 'Injury'\ninjDF = injDF.rename(index=str, columns={\"Week\": \"InjuryWeek\", \"Qtr\": \"InjuryQtr\", \"PlayDescription\": \"InjuryPlayDesc\", \"gamekey\": \"GameKey\", \"playid\": \"PlayID\", \"PREVIEW LINK (5000K)\": \"InjuryVideoLink\"})\n#injDF.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"52c7604de9a5cf1da9c7a152e315c913fbaefe54","_kg_hide-input":true},"cell_type":"code","source":"# Add control data\nctrlDF = pd.read_csv('../input/NFL-Punt-Analytics-Competition/video_footage-control.csv')\nctrlDF = ctrlDF.drop(columns=['season','Season_Type','Home_team','Visit_Team'])\nctrlDF['InjCtrlFlag'] = 'Control'\nctrlDF = ctrlDF.rename(index=str, columns={\"Week\": \"InjuryWeek\", \"Qtr\": \"InjuryQtr\", \"PlayDescription\": \"InjuryPlayDesc\", \"gamekey\": \"GameKey\", \"playid\": \"PlayID\", \"Preview Link\": \"InjuryVideoLink\"})\nctrlDF.head()\n# combine control data\npuntDF = pd.concat([injDF, ctrlDF])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a4293dbaf6b11630b4e3cd99cfd698e3c191ce9b","_kg_hide-input":true},"cell_type":"code","source":"#load video review data\ntempDF = pd.read_csv('../input/NFL-Punt-Analytics-Competition/video_review.csv')\npuntDF = pd.merge(puntDF, tempDF, how='outer', on=['GameKey','PlayID'])\n#puntDF.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1ac4342b9d97cbd3eda0ab133f4f859d728362ac","_kg_hide-input":true},"cell_type":"code","source":"# change data types\npuntDF = puntDF.infer_objects()\n# load player punt role data\ntempDF = pd.read_csv('../input/NFL-Punt-Analytics-Competition/play_player_role_data.csv')\ntempDF = tempDF.infer_objects()\ntempDF.rename(columns={'Role':'InjuredRole'}, inplace=True)\n\npuntDF['Primary_Partner_GSISID'] = pd.to_numeric([\"0\" if ele  == \"Unclear\" else ele for ele in puntDF['Primary_Partner_GSISID']])\npuntDF = pd.merge(puntDF, tempDF, how='left', on=['GameKey','PlayID', 'GSISID'])\npuntDF.rename(columns={\n    'Season_Year_x':'Season_Year',\n}, inplace=True)\n\ntempDF.rename(columns={'InjuredRole':'PrimaryActorRole'}, inplace=True)\n\n# merge data sets\npuntDF = pd.merge(puntDF, tempDF, how='left', left_on=['GameKey','PlayID', 'Primary_Partner_GSISID'], right_on=['GameKey','PlayID', 'GSISID'])\n\n#puntDF.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"416892abc6080423b9d90687ea6dcf1044b949fd","_kg_hide-input":true},"cell_type":"code","source":"# clean main data set again\npuntDF.drop(['Season_Year_y', 'GSISID_y'], axis=1, inplace=True)\npuntDF.rename(columns={\n    'Season_Year_x':'Season_Year',\n    'GSISID_x':'GSISID',\n}, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2ed02482c0e3a1f1c1ecd74bc34c37bd573f82cd","_kg_hide-input":true},"cell_type":"code","source":"# load play information\ntempDF = pd.read_csv('../input/NFL-Punt-Analytics-Competition/play_information.csv')\ntempDF = tempDF.drop(columns=['Season_Year','Week','Home_Team_Visit_Team'])\nlist(tempDF)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a6d3434d19a62a8877c1f454a0b6394887afabb5","_kg_hide-input":true},"cell_type":"code","source":"# merge plays and punt data\npuntDF = pd.merge(puntDF, tempDF, how='left', on=['GameKey','PlayID'])\n\ntempDF = None","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"479140f459a28e47bd87e178ce2eeb4e5fcb1414","_kg_hide-input":true},"cell_type":"code","source":"# Load playStatisticsOutcomes to get player and game lookups\ndf = pd.read_csv('../input/playstatisticsoutcomes/players.csv', encoding = \"ISO-8859-1\")\ndf['gsisId'] = pd.to_numeric(df['gsisId'].str[3:])\ndf['nflId'] = pd.to_numeric([\"0\" if ele[0] == \"M\" else ele for ele in df['nflId']])\n\n\ndf2 = pd.read_csv('../input/playstatisticsoutcomes/gameParticipation.csv', encoding = \"ISO-8859-1\")\ndf = pd.merge(df, df2, how='inner', on=['nflId'])\n\ndf2 = pd.read_csv('../input/playstatisticsoutcomes/teams.csv', encoding = \"ISO-8859-1\")\ndf = pd.merge(df, df2, how='inner', on=['teamId'])\n\ndf = df.loc[df['unit'] == 'special teams']\n\ndf = df.loc[:,('gsisId', 'gameId', 'nameLast', 'nameFull', 'position1', 'team')]\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"547aeb4920dba728da207a4d3a7d0604bead967c","_kg_hide-input":true},"cell_type":"code","source":"# Load data to join games based on date and home team\n\ndf3 = pd.read_csv('../input/playstatisticsoutcomes/games.csv', encoding = \"ISO-8859-1\")\ndf3 = pd.merge(df3, df2, how='inner', left_on=['homeTeamId'], right_on=['teamId'])\ndf3 = df3.loc[:,('gameId', 'gameDate', 'teamAbrv')]\ndf3.rename(columns={\n    'teamAbrv':'homeTeamAbrv',\n}, inplace=True)\ndf3['gameDate'] = pd.to_datetime(df3['gameDate'], format='%m/%d/%Y')\n#df3\ndf = pd.merge(df, df3, how='inner', on=['gameId'])\n#df.dtypes","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fc10208e456e1e6257507f6fcaa8de84d9107478","_kg_hide-input":true},"cell_type":"code","source":"# Load game data\ntempDF = pd.read_csv('../input/NFL-Punt-Analytics-Competition/game_data.csv')\ntempDF = tempDF.loc[:,('GameKey', 'Game_Date', 'HomeTeamCode')]\ntempDF['gameDate'] = pd.to_datetime(tempDF['Game_Date'], format='%Y-%m-%d %H:%M:%S.%f')\npuntDF = pd.merge(puntDF, tempDF, how='left', on=['GameKey'])\ntempDF = None","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5ae4c3b5e20b29b177bb861344dc8a3e550538c7","_kg_hide-input":true},"cell_type":"code","source":"#reshape data\ndf = df[(df['gameDate'] > '2016-01-01') & (~np.isnan(df['gsisId']))]\n\n#df.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"53326d7d9c269b884d125d7654ff426f3c761b7e","_kg_hide-input":true},"cell_type":"code","source":"puntDF = pd.merge(puntDF, df, how='left', left_on=['gameDate','GSISID', 'HomeTeamCode'], right_on=['gameDate', 'gsisId', 'homeTeamAbrv'])\ndf = None\ndf2 = None\ndf3 = None","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b5a5ee6ac2ac461a8b87cdbe4ff6967a870cc903","_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"tempDF = pd.read_csv('../input/playstatisticsoutcomes/Injuries_play-level-data.csv')\ntempDF = tempDF.loc[:,('GameKey', 'PlayID', 'GSISID', 'injuryDescription', 'injuryClass', 'blindsideBlock')]\n\npuntDF = pd.merge(puntDF, tempDF, how='left', on=['GameKey', 'PlayID', 'GSISID'])\n#puntDF.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"49ace6d89945709a190ffef28767b4d217e11269","_kg_hide-input":true},"cell_type":"code","source":"# output play level data\npuntDF.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cc8eef2549bc6d16051fcdfe51c6708c003f3457","_kg_hide-input":true},"cell_type":"code","source":"#output play level data\nplayLvlData = puntDF\nplayLvlData.to_csv('play-level-data.csv', index = False)\nplayLvlData = None","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"87cacfd10a374709eb3f8385987d77421cd836dc","_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"# Load NGS Data\ntempDF0 = pd.read_csv('../input/NFL-Punt-Analytics-Competition/NGS-2016-post.csv')\ntempDF0 = pd.merge(puntDF, tempDF0, how='inner', on=['GameKey','PlayID'])\n\ntempDF1 = pd.read_csv('../input/NFL-Punt-Analytics-Competition/NGS-2016-pre.csv')\ntempDF1 = pd.merge(puntDF, tempDF1, how='inner', on=['GameKey','PlayID'])\n\ntempDF2 = pd.read_csv('../input/NFL-Punt-Analytics-Competition/NGS-2016-reg-wk1-6.csv')\ntempDF2 = pd.merge(puntDF, tempDF2, how='inner', on=['GameKey','PlayID'])\n\ntempDF3 = pd.read_csv('../input/NFL-Punt-Analytics-Competition/NGS-2016-reg-wk7-12.csv')\ntempDF3 = pd.merge(puntDF, tempDF3, how='inner', on=['GameKey','PlayID'])\n\ntempDF4 = pd.read_csv('../input/NFL-Punt-Analytics-Competition/NGS-2016-reg-wk13-17.csv')\ntempDF4 = pd.merge(puntDF, tempDF4, how='inner', on=['GameKey','PlayID'])\n\ntempDF5 = pd.read_csv('../input/NFL-Punt-Analytics-Competition/NGS-2017-post.csv')\ntempDF5 = pd.merge(puntDF, tempDF5, how='inner', on=['GameKey','PlayID'])\n\ntempDF6 = pd.read_csv('../input/NFL-Punt-Analytics-Competition/NGS-2017-pre.csv')\ntempDF6 = pd.merge(puntDF, tempDF6, how='inner', on=['GameKey','PlayID'])\n\ntempDF7 = pd.read_csv('../input/NFL-Punt-Analytics-Competition/NGS-2017-reg-wk1-6.csv')\ntempDF7 = pd.merge(puntDF, tempDF7, how='inner', on=['GameKey','PlayID'])\n\ntempDF8 = pd.read_csv('../input/NFL-Punt-Analytics-Competition/NGS-2017-reg-wk7-12.csv')\ntempDF8 = pd.merge(puntDF, tempDF8, how='inner', on=['GameKey','PlayID'])\n\ntempDF9 = pd.read_csv('../input/NFL-Punt-Analytics-Competition/NGS-2017-reg-wk13-17.csv')\ntempDF9 = pd.merge(puntDF, tempDF9, how='inner', on=['GameKey','PlayID'])\n\npuntDF = pd.concat([tempDF0, tempDF1, tempDF2, tempDF3, tempDF4, tempDF5, tempDF6, tempDF7, tempDF8, tempDF9])\npuntDF.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"26ab2dafe89e203404f35df21570f8f03c67d798","_kg_hide-input":true},"cell_type":"code","source":"# calculate time difference for a relative time metric\npuntDF.loc[:,('TimeAlt')] = pd.to_datetime(puntDF['Time'], format='%Y-%m-%d %H:%M:%S.%f')\n\nminTimes = puntDF.groupby('PlayID', as_index=False)['TimeAlt'].min()\n\nminTimes.rename(columns={'TimeAlt':'TimeMin'}, inplace=True)\n# merge min times back into df\npuntDF = pd.merge(puntDF, minTimes, how='left', on=['PlayID'])\npuntDF = puntDF.assign(TimeNum = pd.to_numeric((puntDF['TimeAlt'] - puntDF['TimeMin'])/100000000))\npuntDF = puntDF.sort_values(by=['GameKey', 'PlayID', 'TimeNum', 'GSISID_y'])\n\npuntDF.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1a565b01c7d1bc063c843fa598106a10465542bb","_kg_hide-input":true},"cell_type":"code","source":"# Split into injured and primary actor subsets - keep only key fields and X,Y columns\nmvmtFields = puntDF.loc[:,('GameKey', 'PlayID', 'TimeNum', 'GSISID_x', 'Primary_Partner_GSISID', 'GSISID_y', 'x', 'y')]\ninjuredPlayer = mvmtFields.loc[(mvmtFields['GSISID_x'] == mvmtFields['GSISID_y'])]\nprimaryActor = mvmtFields.loc[(mvmtFields['Primary_Partner_GSISID'] == mvmtFields['GSISID_y'])]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f345ae02929b8378cdb3f1c694a6e0bf596349af","_kg_hide-input":true},"cell_type":"code","source":"# Join subsets together based on GameKey, PlayID, TimeNum\ndistanceBtwn = pd.merge(injuredPlayer, primaryActor, how='inner', on=['GameKey','PlayID', 'TimeNum'])\n# Null out \ninjuredPlayer = None\nprimaryActor = None\ndistanceBtwn = distanceBtwn.loc[:,('GameKey', 'PlayID', 'TimeNum', 'GSISID_x_x', 'Primary_Partner_GSISID_x', 'x_x', 'y_x', 'x_y', 'y_y')]\ndistanceBtwn.rename(columns={\n    'GSISID_x_x':'injuredGSISID',\n    'Primary_Partner_GSISID_x':'Primary_Partner_GSISID',\n    'x_x':'injuredX',\n    'y_x':'injuredY',\n    'x_y':'actorX',\n    'y_y':'actorY',\n}, inplace=True)\n#distanceBtwn.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"043a866edb5cc276adb761650f854dc538f3ba32","_kg_hide-input":true},"cell_type":"code","source":"# Calculate distance from InjuredXY to PrimaryActorXY\ndef distanceCalc(row):\n    a = np.array([row['injuredX'], row['injuredY']])\n    b = np.array([row['actorX'], row['actorY']])\n    return np.linalg.norm(a-b)\n\ndistanceBtwn['Distance'] = distanceBtwn.apply(distanceCalc, axis=1)\n\n#distanceBtwn.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e31f3610aa4f35685fa15d14b8ddd0486bd2610f","_kg_hide-input":true},"cell_type":"code","source":"# merge distances back into main DF\ndistanceBtwn = distanceBtwn.loc[:,('GameKey', 'PlayID', 'TimeNum', 'Distance')]\npuntDF = pd.merge(puntDF, distanceBtwn, how='left', on=['GameKey', 'PlayID', 'TimeNum'])\ndistanceBtwn = None\n#puntDF.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b4cc8cb06af5920736fea42db5399258bfd8b35b","_kg_hide-input":true},"cell_type":"code","source":"# Calculate Player Speed\n# Sort df by game, play, player, time\npuntDF = puntDF.sort_values(by=['GameKey', 'PlayID', 'GSISID_y', 'TimeNum'])\n# shift X and Y\npuntDF['prevX'] = puntDF.loc[(puntDF['GSISID_y'].shift(-1)==puntDF['GSISID_y']), 'x']\npuntDF['prevX'] = puntDF['prevX'].shift()\npuntDF['prevY'] = puntDF.loc[(puntDF['GSISID_y'].shift(-1)==puntDF['GSISID_y']), 'y']\npuntDF['prevY'] = puntDF['prevY'].shift()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b0ff652fea9f3f45ed39e62b4d09343534dba736","_kg_hide-input":true},"cell_type":"code","source":"# Calculate distance traveled (or speed)\ndef distanceTrvledCalc(row):\n    a = np.array([row['x'], row['y']])\n    b = np.array([row['prevX'], row['prevY']])\n    return np.linalg.norm(a-b)\n\npuntDF['Speed'] = puntDF.apply(distanceTrvledCalc, axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e09f531480668d80e3f88b4edb8809bd292e0f41","_kg_hide-input":true},"cell_type":"code","source":"puntDF['prevSpeed'] = puntDF.loc[(puntDF['GSISID_y'].shift(-1)==puntDF['GSISID_y']), 'Speed']\npuntDF['Acceleration'] = puntDF['Speed'] - puntDF['prevSpeed'].shift()\n#puntDF.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"75add6a20b066c7f3da577c8e117b1f4718ea40e","_kg_hide-input":true},"cell_type":"code","source":"puntDF = puntDF.drop(columns=['prevX','prevY','prevSpeed'])\npuntDF.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3c5adb55466663467b011fd5e6b74157f3e6d970"},"cell_type":"code","source":"# output to csv\npuntDF.to_csv('playerMvmt-level-data.csv', index = False)","execution_count":null,"outputs":[]}],"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}