{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"markdown","source":"![](https://s3.amazonaws.com/nonwebstorage/headstrong/fieldbanner.jpg)\n\nThis script's purpose is to prepare data files provided by the NFL Punt Analytics Competition. We combine data into four tables - NGS data, Play data, Player data, Animation data - and store the tables for analysis. "},{"metadata":{"trusted":true,"_uuid":"d231c922ce6e1bc525c475078eb749a4f5cfe055"},"cell_type":"code","source":"# import\nimport numpy as np\nimport pandas as pd\nimport dask.dataframe as dd","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","collapsed":true,"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":false},"cell_type":"markdown","source":" <div style=\"background-color:steelblue; color:#FFFFFF; padding:8px; padding-right:300px; font-size:18px; max-width:1500px; margin: auto\">  NGS Data </div>"},{"metadata":{"trusted":true,"_uuid":"2068f80d14dd939b5d2a5741c29654851774cd53"},"cell_type":"code","source":"ndtypes = {'GameKey': 'int16',         \n           'PlayID': 'int16',         \n           'GSISID': 'float32',        \n           'Time': 'str',         \n           'x': 'float32',         \n           'y': 'float32',         \n           'dis': 'float32',\n           'o': 'float32',\n           'Event': 'str'}\n\nnddf = dd.read_csv('../input/NFL-Punt-Analytics-Competition/NGS*', \n                usecols=[n for n in ndtypes.keys()], dtype=ndtypes)\n\nmeta = ('Time', 'datetime64[ns]')\nnddf['Time'] = nddf.Time.map_partitions(pd.to_datetime, meta=meta)\nnddf['GSISID'] = nddf.GSISID.fillna(-1).astype('int32')\n\nnddf = nddf.compute()\nnddf.to_parquet('NGS.parq')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ec1ae8b7140746ade69c4ee829126be712628cc5"},"cell_type":"markdown","source":" <div style=\"background-color:steelblue; color:#FFFFFF; padding:8px; padding-right:300px; font-size:18px; max-width:1500px; margin: auto\">  Player-Level Data </div>"},{"metadata":{"trusted":true,"_uuid":"575cceb7193c71006765fa9603e264da9f139671"},"cell_type":"code","source":"# get base data and role types\nplayers = pd.read_csv('../input/NFL-Punt-Analytics-Competition/play_player_role_data.csv')\ndescrs = pd.read_csv('../input/nfl-competition-data/roledscrps.csv')\nplayers = players.merge(descrs, on='Role', how='left').drop('Season_Year',\\\n                        axis=1)\n\n# tag players involved in concussion events\nrevs = pd.read_csv('../input/NFL-Punt-Analytics-Competition/video_review.csv', \n                   usecols=['GameKey', 'PlayID', \n            'GSISID', 'Primary_Partner_GSISID'], na_values=['Unclear'])\\\n            .fillna(-99).astype(int)\nplayers = players.merge(revs, how='left', on=['GameKey', 'PlayID', 'GSISID'],\\\n                        sort='False')\nplayers['concussed'] = np.where(players.Primary_Partner_GSISID.isnull(), 0, 1)\n\nplayers = players.merge(revs, how='left', left_on=['GameKey', 'PlayID', \n            'GSISID'], right_on=['GameKey', 'PlayID', \n            'Primary_Partner_GSISID'], suffixes=(\"\", \"_dupe\"), sort='False')\nplayers['concussor'] = np.where(players.Primary_Partner_GSISID_dupe.isnull()\n                        , 0, 1)\n\n# add numbers and positions\nplayas = pd.read_csv('../input/NFL-Punt-Analytics-Competition/player_punt_data.csv')\nplayas_agg = playas.groupby('GSISID')['Number'].apply(' '.join).to_frame()\nplayers = players.merge(playas_agg, on='GSISID', how='left')\n\ndrops = ['Primary_Partner_GSISID'] + players.columns[players.columns.str\\\n        .contains('dupe')].tolist()\nplayers = players.drop(drops, axis=1).sort_values(['GSISID', 'GameKey', \n          'PlayID']).set_index('GSISID').reset_index()\n\nplayers.to_parquet('players.parq')\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f093d4a91578aa751a2acf6820267b415d48c7d3"},"cell_type":"markdown","source":" <div style=\"background-color:steelblue; color:#FFFFFF; padding:8px; padding-right:300px; font-size:18px; max-width:1500px; margin: auto\">  Play-Level Data </div>"},{"metadata":{"trusted":true,"_uuid":"fbbfdca6e97c4c1c5c6e498eeb599a72537d45b2"},"cell_type":"code","source":"#%% join play level and game level data\nplays = pd.read_csv('../input/NFL-Punt-Analytics-Competition/play_information.csv', \n                    index_col=['GameKey', 'PlayID'])\n\ngames = pd.read_csv('../input/NFL-Punt-Analytics-Competition/game_data.csv', \n                    index_col=['GameKey'])\ngames.Temperature.fillna(-999, inplace=True)\nplays_all = plays.join(games, rsuffix='_dupe', sort=False)\n\nrevs = pd.read_csv('../input/NFL-Punt-Analytics-Competition/video_review.csv', \n                   index_col=['GameKey', 'PlayID'])\nrevs.loc[revs.Primary_Partner_GSISID == 'Unclear', 'Primary_Partner_GSISID']\\\n             = np.nan\nrevs['Primary_Partner_GSISID'] = pd.to_numeric(revs.Primary_Partner_GSISID)\nplays_all = plays_all.join(revs, rsuffix='_dupe2', sort=False)\n\n\n#%% merge player numbers and positions for concussions\nplayernums = pd.read_csv('../input/NFL-Punt-Analytics-Competition/player_punt_data.csv')\nplayernums = playernums.groupby('GSISID').agg(' '.join) #combine dupes\n\nplays_all = plays_all.reset_index().merge(playernums, how='left', on='GSISID', \n            sort=False)\nplays_all = plays_all.merge(playernums, how='left', \n            left_on='Primary_Partner_GSISID', right_on='GSISID', \n            suffixes=(\"_player\", \"_partner\"), sort=False)\n\n\n#%% merge player level data for concussions\nroles = pd.read_csv('../input/NFL-Punt-Analytics-Competition/play_player_role_data.csv')\nroles_all = roles.merge(descrs, on='Role', how='left').drop('Season_Year', \n                        axis=1)\n\nplays_all = plays_all.merge(roles_all, how='left', on=['GameKey', 'PlayID', \n            'GSISID'], sort=False)\nplays_all = plays_all.merge(roles_all, how='left', left_on=['GameKey', \n            'PlayID', 'Primary_Partner_GSISID'], right_on=['GameKey', \n            'PlayID', 'GSISID'], suffixes=(\"_player\", \"_partner\"), sort=False)\n\nplays_all.set_index(['GameKey', 'PlayID'], inplace=True)\n\n\n# merge aggregated player level data for all plays\nroles_enc = pd.get_dummies(roles_all, columns=['Team', 'Area', 'Type'])\ncollist = list(roles_enc)[2:]\nagglist = ['size', pd.Series.nunique] + (len(collist)-3) * ['sum']\naggdict = dict(zip(collist, agglist))\n\n\nroles_agg = roles_enc.groupby(['GameKey', 'PlayID']).agg(aggdict)\nroles_agg.columns = [r + '_agg' for r in roles_agg.columns]\n\nplays_all = plays_all.join(roles_agg, rsuffix=\"_roles\")\n\n\n#%% make simple features\nplays_all['yard_number'] = plays_all.YardLine.str.split().str[1].astype(int)\nplays_all['dist_togoal'] = np.where(plays_all.Poss_Team == plays_all.YardLine\\\n            .str.split().str[0], plays_all.yard_number + 50, \n            plays_all.yard_number)\nplays_all['Rec_team'] = np.where(plays_all.Poss_Team == plays_all.HomeTeamCode, \n             plays_all.VisitTeamCode, plays_all.HomeTeamCode)\nplays_all['home_score'] = plays_all.Score_Home_Visiting.str.split(\" - \")\\\n            .str[0].astype(int)\nplays_all['visit_score'] = plays_all.Score_Home_Visiting.str.split(\" - \")\\\n            .str[1].astype(int)\nplays_all['concussion'] = np.where(plays_all.Primary_Impact_Type.isnull(), \n                                    0, 1)\n\n#%% clean up \ndrops = ['YardLine',\n         'Play_Type',\n         'Home_Team_Visit_Team',\n         'Primary_Partner_GSISID',\n         'Score_Home_Visiting']\\\n         + plays_all.columns[plays_all.columns.str.contains('dupe')].tolist()\nplays_all.drop(drops, axis=1, inplace=True)\n\nplays_all['GSISID_player'] = plays_all.GSISID_player.fillna(-99, \n                                downcast='infer')\nplays_all['GSISID_partner'] = plays_all.GSISID_partner.fillna(-99, \n                                downcast='infer')\n\nfloatcols = plays_all.select_dtypes('float').columns\nfor f in floatcols:\n    plays_all[f] = plays_all[f].fillna(-99).astype(int)\n\nplays_all.fillna('unspecified', inplace=True)\nplays_all.replace('SD', 'LAC', inplace=True, regex=True)\nplays_all['Game_Date'] = pd.to_datetime(plays_all.Game_Date, format='%m/%d/%Y')\n\nplays_all.sort_index(inplace=True)\nplays_all.reset_index(inplace=True) #avoid mulit-index for parquet\n\nplays_all.to_parquet('plays.parq')\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d1f6244c13072c13cb777e797b29937a47970291"},"cell_type":"markdown","source":" <div style=\"background-color:steelblue; color:#FFFFFF; padding:8px; padding-right:300px; font-size:18px; max-width:1500px; margin: auto\">  Animation Data </div>"},{"metadata":{"trusted":true,"_uuid":"7d09c865f8865162c65a3f36fff1b577ba2b5f9e"},"cell_type":"code","source":"# extract sample play\ncut = nddf[(nddf.GameKey == 585) & (nddf.PlayID == 733)].copy()\ncut = cut.merge(players, on=['GameKey', 'PlayID', 'GSISID'], how='inner', \n                sort=False)\n\n# set frameIDs\nlineset = cut.loc[cut.Event == \"line_set\", 'Time'].max()\ncut = cut[cut.Time >= lineset]\ncut.sort_values('Time', inplace=True)\ncut['frame_id'] = cut.Time.astype('category').cat.codes + 1\n\n# trim positions to field size\n# cut.y.clip(0, 160/3-0.001, inplace=True)\n# cut.x.clip(0, 200, inplace=True)\n\n# add concussion feature\ncut['involved'] = (cut.concussed+cut.concussor == 1).astype(int)\n\n# plot ball\nevdict = {\"line_set\": \"PLS\",\n          \"ball_snap\": \"PLS\",\n          \"punt\": \"P\",\n          \"punt_received\": \"PR\"}\nframelist = []\npointlist = []\nfor k,v in evdict.items():\n    frameend = cut.loc[cut.Event ==  k, 'frame_id'].mean() \n    point = cut.loc[(cut.Event ==  k)\n                  & (cut.Role == v), ['x', 'y']].values\n    framelist.append(frameend)\n    pointlist.append(point)\npointarray = np.concatenate(pointlist)\n\nseglist = []\nfor i in range(0, len(framelist)-1):\n    nframes = int(framelist[i+1]-framelist[i])\n    ends = pointarray[i:i+2]\n    seg =  np.vstack((np.linspace(ends[0, 0], ends[1, 0], nframes),\n                     np.linspace(ends[0, 1], ends[1, 1], nframes))).T\n    seglist.append(seg)\n\npostcatch = cut.loc[(cut.frame_id >= max(framelist))\n                    & (cut.Role == 'PR'), ['x', 'y']].values\nballxy = np.vstack((np.concatenate(seglist), postcatch))\n\n\n# cleanup and combine\ndrops = ['GameKey', 'PlayID',  'Time',  'dis',\n       'Event', 'Role', 'Area', 'Type', 'concussed',\n       'concussor']\ncut.drop(drops, axis=1, inplace=True)\n\nball_df = pd.DataFrame({'GSISID': 1, 'x': ballxy[:, 0], 'y': ballxy[:, 1],\n            'Team': 'ball', 'frame_id': np.arange(1, cut.frame_id.max()+1), \n            'involved': 0})\ncut = cut.append(ball_df, sort=False).fillna(\"\").sort_values(['GSISID', 'frame_id'])\n\ncut.to_csv('animate_585_733.csv', index=False)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"864c17452a580439fa9490b262ec1657a84a5158"},"cell_type":"markdown","source":"You can access the files via the \"Output\" tab."}],"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}