{"cells":[{"metadata":{"_uuid":"54d30be06668c8fc0cf5015c08f1df5514bd7f0d"},"cell_type":"markdown","source":"# NFL Concussion Prevention Data Preprocessing\n\nThis notebook contains a bunch of preprocessing that I did to make my analysis notebook a little less cluttered. Feel free to give it a read, but it's not all that interesting. The good stuff is in the analysis notebook!"},{"metadata":{"trusted":true,"_uuid":"f6d6136d9e0bb5a6ad4393f03f82f30d4f46e513"},"cell_type":"code","source":"import feather\nimport gc\nimport matplotlib.pyplot as plt\nimport numpy as np\nimport pandas as pd\nimport re\nimport tqdm\n\n%matplotlib inline\npd.set_option('display.max_columns', None)  \npd.set_option('display.expand_frame_repr', False)\npd.set_option('max_colwidth', -1)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"eb94e6dc25f6e33d2c7c762a07cf1e504c9184e2"},"cell_type":"markdown","source":"## Play-level Data"},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"8b1af5b43461bcbc2d216c6ed8f73c35c5066c12"},"cell_type":"code","source":"play_information = pd.read_csv('../input/play_information.csv') # One row per punt\n\n# Play Description Features\nPlayDescription_split = play_information.PlayDescription.str.split(\"punts\")\nplay_information['PlayDescription_last'] = PlayDescription_split.apply(lambda x: x[-1])\nplay_information['Has_Punt'] = PlayDescription_split.apply(lambda x: len(x) > 1)\nplay_information['Has_Fair_Catch'] = play_information.PlayDescription_last.str.contains('fair catch')\nplay_information['Punt_Distance'] = play_information.PlayDescription_last.str.extract('^ ([0-9]+) yard').astype('float')\nplay_information['Has_Muff'] = play_information.PlayDescription_last.str.contains('MUFFS')\nplay_information['Has_Penalty'] = play_information.PlayDescription_last.str.contains('PENALTY')\nplay_information['Has_Return'] = (\n    play_information.Has_Punt & (\n        play_information.PlayDescription_last.str.contains('for -?(?:[0-9]+ yard|no gain)', regex=True)\n        | play_information.Has_Muff\n    )\n)\n\nplay_information['Punt_Type'] = play_information.apply(\n    lambda row:\n        np.NaN if not row.Has_Punt\n        else (\n            'fair catch' if row.Has_Fair_Catch\n            else (\n                'return' if row.Has_Return\n                else 'unreturnable'\n            )\n        )\n    , axis=1\n)\n\ndef extract_punt_return_length(row):\n    if row.Punt_Type == 'unreturnable':\n        return np.nan\n    elif row.Punt_Type == 'fair catch':\n        return 0.0\n    elif 'for no gain' in row.PlayDescription_last:\n        return 0.0\n    else:\n        try:\n            return float(re.search('for (-?[0-9]+) yard', row.PlayDescription_last).group(1))\n        except:\n            return 0.0\n\nplay_information['Punt_Return_Length'] = play_information.apply(\n    lambda row: extract_punt_return_length(row), axis=1\n)\n\n# Time Features\nplay_information['Game_Clock_Min'] = play_information.Game_Clock.str.extract('([0-9]+):[0-9]+').astype('int16')\nplay_information['Game_Clock_Sec'] = play_information.Game_Clock.str.extract('[0-9]+:([0-9]+)').astype('int16')\nplay_information['Time_Passed_Sec'] = play_information.apply(\n    lambda row: \n        (900 * (row['Quarter'] - 1)) +\n        (60 * (15 - (row['Game_Clock_Min'] + 1))) +\n        (60 - row['Game_Clock_Sec'])\n    , axis=1\n)\n\n# Score Features\nplay_information['Home_Team'] = play_information.Home_Team_Visit_Team.str.extract('([A-Z]+)-[A-Z]+')\nplay_information['Away_Team'] = play_information.Home_Team_Visit_Team.str.extract('[A-Z]+-([A-Z]+)')\nplay_information['Home_Score'] = play_information.Score_Home_Visiting.str.extract('([0-9]+) - [0-9]+').astype('int16')\nplay_information['Away_Score'] = play_information.Score_Home_Visiting.str.extract('[0-9]+ - ([0-9]+)').astype('int16')\nplay_information['Score_Differential'] = play_information.apply(\n    lambda row: \n        row.Home_Score - row.Away_Score \n        if row.Poss_Team == row.Home_Team \n        else row.Away_Score - row.Home_Score\n    , axis=1\n)\n\n# Yard Line Features\nplay_information['Yard_Line_Team'] = play_information.YardLine.str.extract('([A-Z]+) [0-9]+')\nplay_information['Yard_Line_Num'] = play_information.YardLine.str.extract('[A-Z]+ ([0-9]+)').astype('int16')\nplay_information['Yard_Line_Absolute'] = play_information.apply(\n    lambda row:\n        row.Yard_Line_Num \n        if row.Yard_Line_Team == row.Poss_Team\n        else 100 - row.Yard_Line_Num\n    , axis=1\n)\n\nplay_information.to_feather('play_information.feather')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"25f87c816ee2dc2cf8b0ed5fe8e72a2b4eb94b47"},"cell_type":"markdown","source":"## Game-level Data"},{"metadata":{"trusted":true,"_uuid":"f0dc7ad299158ec4d42cdf79542a0f21b9e12d86"},"cell_type":"code","source":"game_data = pd.read_csv('../input/game_data.csv') # One row per game\ngame_data.loc[game_data.Stadium == 'Hard Rock Stadium', 'Turf'] = 'Natural Grass'\ngame_data['Is_Grass'] = game_data.Turf.str.strip().str.lower().str.contains('grass|natural')\ngame_data['StadiumType'] = game_data.StadiumType.fillna('Outdoor')\ngame_data['Is_Outdoor'] = game_data.StadiumType.str.lower().str.strip().str.contains('out|open|heinz|oudoor|ourdoor')\ngame_data.loc[~game_data.Is_Outdoor, 'Temperature'] = 70.0\ngame_data.to_feather('game_data.feather')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4f88e54c0d28779803fa84edc84ac3cb55e12b31"},"cell_type":"markdown","source":"## Player Punt Role Data"},{"metadata":{"trusted":true,"_uuid":"9ad2b0f682e3b9ce1bb630fb547bb21ff8fc573f"},"cell_type":"code","source":"play_player_role_data = pd.read_csv('../input/play_player_role_data.csv')\n\n# Mapping punt positions to Kicking and Receiving team\nrole_metadata = {\n    'PR': {'Role_Team': 'R', 'Super_Role': 'Returner'},\n    'PDL1': {'Role_Team': 'R', 'Super_Role': 'Return Lineman'},\n    'PDR1': {'Role_Team': 'R', 'Super_Role': 'Return Lineman'},\n    'PRG': {'Role_Team': 'K', 'Super_Role': 'Coverage Lineman'},\n    'P': {'Role_Team': 'K', 'Super_Role': 'Punter'},\n    'PLG': {'Role_Team': 'K', 'Super_Role': 'Coverage Lineman'},\n    'PRT': {'Role_Team': 'K', 'Super_Role': 'Coverage Lineman'},\n    'PLS': {'Role_Team': 'K', 'Super_Role': 'Coverage Lineman'},\n    'PLT': {'Role_Team': 'K', 'Super_Role': 'Coverage Lineman'},\n    'PLW': {'Role_Team': 'K', 'Super_Role': 'Coverage Wing'},\n    'PDR2': {'Role_Team': 'R', 'Super_Role': 'Return Lineman'},\n    'PRW': {'Role_Team': 'K', 'Super_Role': 'Coverage Lineman'},\n    'PDL2': {'Role_Team': 'R', 'Super_Role': 'Return Lineman'},\n    'GL': {'Role_Team': 'K', 'Super_Role': 'Gunner'},\n    'GR': {'Role_Team': 'K', 'Super_Role': 'Gunner'},\n    'PDL3': {'Role_Team': 'R', 'Super_Role': 'Return Lineman'},\n    'PDR3': {'Role_Team': 'R', 'Super_Role': 'Return Lineman'},\n    'VL': {'Role_Team': 'R', 'Super_Role': 'Return Corner'},\n    'VR': {'Role_Team': 'R', 'Super_Role': 'Return Corner'},\n    'PPR': {'Role_Team': 'K', 'Super_Role': 'Coverage Protector'},\n    'PLL': {'Role_Team': 'R', 'Super_Role': 'Return Linebacker'},\n    'PPL': {'Role_Team': 'K', 'Super_Role': 'Coverage Protector'},\n    'PLR': {'Role_Team': 'R', 'Super_Role': 'Return Linebacker'},\n    'VRo': {'Role_Team': 'R', 'Super_Role': 'Return Corner'},\n    'VRi': {'Role_Team': 'R', 'Super_Role': 'Return Corner'},\n    'VLi': {'Role_Team': 'R', 'Super_Role': 'Return Corner'},\n    'VLo': {'Role_Team': 'R', 'Super_Role': 'Return Corner'},\n    'PDL4': {'Role_Team': 'R', 'Super_Role': 'Return Lineman'},\n    'PDR4': {'Role_Team': 'R', 'Super_Role': 'Return Lineman'},\n    'PLM': {'Role_Team': 'R', 'Super_Role': 'Return Linebacker'},\n    'PLR1': {'Role_Team': 'R', 'Super_Role': 'Return Linebacker'},\n    'PLR2': {'Role_Team': 'R', 'Super_Role': 'Return Linebacker'},\n    'PLL2': {'Role_Team': 'R', 'Super_Role': 'Return Linebacker'},\n    'PLL1': {'Role_Team': 'R', 'Super_Role': 'Return Linebacker'},\n    'PFB': {'Role_Team': 'R', 'Super_Role': 'Return Protector'},\n    'PDL5': {'Role_Team': 'R', 'Super_Role': 'Return Lineman'},\n    'PDR5': {'Role_Team': 'R', 'Super_Role': 'Return Lineman'},\n    'GRo': {'Role_Team': 'K', 'Super_Role': 'Gunner'},\n    'GRi': {'Role_Team': 'K', 'Super_Role': 'Gunner'},\n    'PDM': {'Role_Team': 'R', 'Super_Role': 'Return Lineman'},\n    'GLi': {'Role_Team': 'K', 'Super_Role': 'Gunner'},\n    'GLo': {'Role_Team': 'K', 'Super_Role': 'Gunner'},\n    'PDL6': {'Role_Team': 'R', 'Super_Role': 'Return Lineman'},\n    'PLR3': {'Role_Team': 'R', 'Super_Role': 'Return Linebacker'},\n    'PLL3': {'Role_Team': 'R', 'Super_Role': 'Return Linebacker'},\n    'PPLi': {'Role_Team': 'K', 'Super_Role': 'Coverage Protector'},\n    'PPLo': {'Role_Team': 'K', 'Super_Role': 'Coverage Protector'},\n    'PC': {'Role_Team': 'K', 'Super_Role': 'Coverage Protector'},\n    'PDR6': {'Role_Team': 'R', 'Super_Role': 'Return Lineman'},\n    'PPRi': {'Role_Team': 'K', 'Super_Role': 'Coverage Protector'},\n    'PPRo': {'Role_Team': 'K', 'Super_Role': 'Coverage Protector'},\n    'PLM1': {'Role_Team': 'R', 'Super_Role': 'Return Linebacker'}\n}\n\nplay_player_role_data['Role_Team'] = play_player_role_data.Role.apply(lambda role: role_metadata[role]['Role_Team'])\nplay_player_role_data['Super_Role'] = play_player_role_data.Role.apply(lambda role: role_metadata[role]['Super_Role'])\nplay_player_role_data.to_feather('play_player_role_data.feather')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ddfe7eaa319159c910a2b5ae267a55f81e93bb57"},"cell_type":"markdown","source":"## Concussion Data"},{"metadata":{"trusted":true,"_uuid":"8079b32f99e31d07814d240303ee6ee38f07f2db"},"cell_type":"code","source":"video_review = pd.read_csv('../input/video_review.csv')\nvideo_review = video_review.merge(\n    play_player_role_data,\n    on=['GameKey', 'PlayID', 'GSISID'], how='left',\n    validate='one_to_one'\n)\n\nvideo_review[\n    ['GameKey', 'PlayID', 'GSISID', 'Role', 'Role_Team', 'Super_Role']\n].to_feather('video_review.feather')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1047a4a17c3dced40019eecaf1968163695dad45"},"cell_type":"markdown","source":"## NGS Data\n\nThe code for loading and downcasting NGS data was taken primarily from this kernel (with a few modifications): https://www.kaggle.com/akosciansky/how-to-import-large-csv-files-and-save-efficiently"},{"metadata":{"trusted":true,"_uuid":"3759f8f387c0f2b9792c9a5b28889a166e6fb39a"},"cell_type":"code","source":"dtypes = {'Season_Year': 'int16',\n         'GameKey': 'int16',\n         'PlayID': 'int16',\n         'GSISID': 'float32',\n         'Time': 'str',\n         'x': 'float32',\n         'y': 'float32',\n         'dis': 'float32',\n         'o': 'float32',\n         'dir': 'float32',\n         'Event': 'str'}\n\ncol_names = list(dtypes.keys())\n\npath = '../input/'\nngs_files = ['NGS-2016-pre.csv',\n             'NGS-2016-reg-wk1-6.csv',\n             'NGS-2016-reg-wk7-12.csv',\n             'NGS-2016-reg-wk13-17.csv',\n             'NGS-2016-post.csv',\n             'NGS-2017-pre.csv',\n             'NGS-2017-reg-wk1-6.csv',\n             'NGS-2017-reg-wk7-12.csv',\n             'NGS-2017-reg-wk13-17.csv',\n             'NGS-2017-post.csv']\n\ndf_list = []\n\nfor f in tqdm.tqdm(ngs_files):\n    df = pd.read_csv(path + f, usecols=col_names,dtype=dtypes)\n    \n    df_list.append(df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f60597825c4b525cf6c936b7641a76492886b181"},"cell_type":"code","source":"# Merge all dataframes into one dataframe\nngs = pd.concat(df_list)\n\n# Delete the dataframe list to release memory\ndel df_list\ngc.collect()\n\n# Convert Time to datetime\nngs['Time'] = pd.to_datetime(ngs['Time'], format='%Y-%m-%d %H:%M:%S')\n\n# Convert season year to 0/1\nngs['Season_Year'] = ngs['Season_Year'].astype('category').cat.codes\n\n# Fill NA values then downcast to int32\nngs['GSISID'] = ngs['GSISID'].fillna(-1).astype('int32')\n\n# Convert o and dir to int16 (don't need that level of precision)\nngs['o'] = ngs['o'].astype('int16')\nngs['dir'] = ngs['dir'].astype('int16')\n\n# Write to feather\nngs.reset_index(drop=True).to_feather('ngs.feather')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"171e61de0bb5c0b2cae51d062dc8805caa95bf58"},"cell_type":"markdown","source":"## Coverage Distances"},{"metadata":{"trusted":true,"_uuid":"25c047dbefb410968270eb487e0e810af7cb1623"},"cell_type":"code","source":"catch_events = ngs[ngs.Event.isin(['fair_catch', 'punt_received'])].merge(\n    play_player_role_data, on=['GameKey', 'PlayID', 'GSISID'], how='left', validate='many_to_one'\n)\n\npunt_returner = catch_events[catch_events.Role == 'PR'][\n    ['GameKey', 'PlayID', 'GSISID', 'x', 'y', 'Event', 'Role', 'Super_Role']\n]\nkicking_team = catch_events[catch_events.Role_Team == 'K'][\n    ['GameKey', 'PlayID', 'GSISID', 'x', 'y', 'Event', 'Role', 'Super_Role']\n]\n\npr_cross_kick = punt_returner.merge(\n    kicking_team, on=['GameKey', 'PlayID', 'Event'], how='left', validate='many_to_many',\n    suffixes=['_pr', '_k']\n)\n\npr_cross_kick['Coverage_Distance'] = (\n    (\n        (pr_cross_kick['x_pr'] - pr_cross_kick['x_k']) ** 2\n    ) + (\n        (pr_cross_kick['y_pr'] - pr_cross_kick['y_k']) ** 2\n    )\n) ** .5\n\nmin_distances = pr_cross_kick.loc[pr_cross_kick.groupby(['GameKey', 'PlayID'])['Coverage_Distance'].idxmin()]\nmin_distances.reset_index(drop=True).to_feather('min_distances.feather')\n\npr_cross_kick_2 = pr_cross_kick.drop(pr_cross_kick.groupby(['GameKey', 'PlayID'])['Coverage_Distance'].idxmin())\nsecond_min_distances = pr_cross_kick_2.loc[\n    pr_cross_kick_2.groupby(['GameKey', 'PlayID'])['Coverage_Distance'].idxmin()\n]\n\nsecond_min_distances.reset_index(drop=True).to_feather('second_min_distances.feather')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"48a670c3b553b2ac75f339226f6760d7637c1f84"},"cell_type":"markdown","source":"## Punt Hang Time"},{"metadata":{"trusted":true,"_uuid":"a8b931f0b44c566e1de00efa69f399ca75f42ee6"},"cell_type":"code","source":"punt_time = ngs[ngs.Event.isin(['punt'])].groupby(['GameKey', 'PlayID'])['Time'].min().reset_index()\nreceive_time = ngs[ngs.Event.isin(['fair_catch', 'punt_received'])].groupby(['GameKey', 'PlayID'])['Time'].min().reset_index()\n\npunt_to_reception = punt_time.merge(\n    receive_time, on=['GameKey', 'PlayID'], how='inner', validate='one_to_one', suffixes=['_punt', '_receive']\n)\npunt_to_reception['Hangtime'] = (punt_to_reception['Time_receive'] - punt_to_reception['Time_punt']).dt.total_seconds()\n\npunt_to_reception.to_feather('punt_hangtime.feather')\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d26a47a4866a238291b136fdfdb189f783279418"},"cell_type":"markdown","source":"## Adjusted Coverage Distance"},{"metadata":{"trusted":true,"_uuid":"62229597c18a8e2344868e42c2557bf33a25dbd0"},"cell_type":"code","source":"ngs_punt_to_reception = ngs.merge(\n    punt_to_reception, on=['GameKey', 'PlayID'], how='inner', validate='many_to_one'\n)[['GameKey', 'PlayID', 'GSISID', 'Time', 'x', 'y', 'dis', 'Time_punt', 'Time_receive', 'Hangtime']]\n\nngs_punt_to_reception_filtered = ngs_punt_to_reception[\n    (ngs_punt_to_reception.Time >= ngs_punt_to_reception.Time_punt) &\n    (ngs_punt_to_reception.Time <= ngs_punt_to_reception.Time_receive)\n]\n\npunt_to_reception_speed = ngs_punt_to_reception_filtered.groupby(\n    [\"GameKey\", 'PlayID', 'GSISID']\n).agg({\"dis\": np.sum, \"Hangtime\": np.min}).reset_index()\npunt_to_reception_speed['Yards_Per_Second'] = punt_to_reception_speed.dis / punt_to_reception_speed.Hangtime","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"768664fb85ba858c50eb0ccb1d90dfb555802a23"},"cell_type":"code","source":"snap_time = ngs[ngs.Event == 'ball_snap'].groupby(['GameKey', 'PlayID'])['Time'].min().reset_index()\nsnap_to_punt = snap_time.merge(\n    punt_time, on=['GameKey', 'PlayID'], how='inner', validate='one_to_one', suffixes=['_snap', '_punt']\n)\n\nsnap_to_punt['Snap_To_Punt_time'] = (snap_to_punt['Time_punt'] - snap_to_punt['Time_snap']).dt.total_seconds()\nsnap_to_punt = snap_to_punt[(snap_to_punt.Snap_To_Punt_time > 0) & (snap_to_punt.Snap_To_Punt_time < 5)]\n\npunt_to_reception_speed = punt_to_reception_speed.merge(\n    snap_to_punt[['GameKey', 'PlayID', 'Snap_To_Punt_time']],\n    on=['GameKey', 'PlayID'], how='inner', validate='many_to_one'\n)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c1ed3be876fc304bad9e457076c571f88dc93e91"},"cell_type":"code","source":"adjusted_coverage_distances = pr_cross_kick.merge(\n    punt_to_reception_speed,\n    left_on=['GameKey', 'PlayID', 'GSISID_k'], right_on=['GameKey', 'PlayID', 'GSISID'],\n    how='inner', validate='many_to_one'\n)\n\nadjusted_coverage_distances.to_feather('adjusted_coverage_distances.feather')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8464724f935a7a133733db79cdd4cf89fb856515"},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.7.2"}},"nbformat":4,"nbformat_minor":1}