{"cells":[{"metadata":{"trusted":true},"cell_type":"code","source":"#imports \nimport pandas as pd\nimport re\nimport numpy as np\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"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 in \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 \"../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        \n\n#InjuryRecord = pd.read_csv(\"../input/nfl-playing-surface-analytics/InjuryRecord.csv\")\n#PlayList = pd.read_csv(\"../input/nfl-playing-surface-analytics/PlayList.csv\")\n#PlayerTrackData = pd.read_csv(\"../input/nfl-playing-surface-analytics/PlayerTrackData.csv\")        \n\n# Any results you write to the current directory are saved as output.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#reading files\n#1\nplayerTrackData=pd.read_csv(\"../input/nfl-playing-surface-analytics/PlayerTrackData.csv\")\n\n#2\nplayList=pd.read_csv(r\"../input/nfl-playing-surface-analytics/PlayList.csv\")\n#3\ninjuryRecord=pd.read_csv(r\"../input/nfl-playing-surface-analytics/InjuryRecord.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#File information\nprint(playerTrackData.info())\nprint(playList.info())\nprint(injuryRecord.info())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"##################################playerTrackData###########################################\nplay=playerTrackData.iloc[:,0]\nplay_gameid=[]\nfor i in play:\n    gameid_regex=re.findall(\"^[0-9]*-[0-9]*\",i)\n    play_gameid.extend(gameid_regex)\nplayerTrackData['GameId']=play_gameid\nplayerTrackData['event']=playerTrackData['event'].fillna(method='ffill')\nplayerTrackData['event']=playerTrackData['event'].replace(to_replace=['play_submit\\t'],value=\"play_submit\")\ndel play\ndel play_gameid","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"##################################playList###########################################\n\nplayList['StadiumType']=playList['StadiumType'].replace(to_replace=[\"Outdoors\",\"Oudoor\",\"Ourdoor\",\"Outddors\"\\\n                                                                    ,\"Outdor\",\"Outside\",\"Heinz Field\",\"Bowl\",\"Cloudy\"],value=\"Outdoor\")\nplayList['StadiumType']=playList['StadiumType'].replace(to_replace=[\"Indoors\",\"Indoor\",\"Indoor, Roof Closed\"],value=\"Indoor Closed\") \nplayList['StadiumType']=playList['StadiumType'].replace(to_replace=[\"Domed, closed\",\"Dome, closed\",\"Domed, open\",\"Domed, Open\",\"Dome\"],value=\"Domed\")\nplayList['StadiumType']=playList['StadiumType'].replace(to_replace=[\"Retr. Roof-Closed\",\"Retr. Roof - Closed\",\"Retr. Roof Closed\"],value=\"Retractable Closed\")\nplayList['StadiumType']=playList['StadiumType'].replace(to_replace=[\"Outdoor Retr Roof-Open\",\"Retr. Roof - Open\",\"Retr. Roof-Open\",\"Retractable Roof\"],value=\"Retractable Open\")\nplayList['StadiumType']=playList['StadiumType'].replace(to_replace=[\"Indoor, Open Roof\",\"Open\"],value=\"Indoor Open\")\nplayList['Weather']=playList['Weather'].replace(to_replace=[\"Sunny\",'Mostly sunny','Clear to Partly Cloudy','Sun & clouds','Clear and sunny','Clear and Sunny','Sunny, Windy','Mostly Sunny Skies','Sunny, highs to upper 80s','Heat Index 95','Partly clear','Fair','Sunny Skies','Clear skies','Sunny and clear','Partly sunny',\"Clear Skies\",\"Mostly Sunny\",\"Partly Sunny\",'Clear and warm',\"Sunny and warm\",\"Controlled Climate\"],value=\"Clear\")\nplayList['Weather']=playList['Weather'].replace(to_replace=['Clear and Cool','Sunny and cold','Cold','Clear and cold'],value=\"Clear and Cold\")\nplayList['Weather']=playList['Weather'].replace(to_replace=[\"Partly Cloudy\",'Mostly Coudy','Overcast','cloudy','Coudy','Partly Clouidy','Hazy','Party Cloudy','Partly cloudy','Cloudy and cold','Cloudy and Cool',\"Mostly cloudy\",'Mostly Cloudy',\"Cloudy, fog started developing in 2nd quarter\",\"Cloudy\"],value=\"Cloudy\")\nplayList['Weather']=playList['Weather'].replace(to_replace=['Showers','Rain shower','Cloudy, Rain','Light Rain','Rainy','Cloudy with periods of rain, thunder possible. Winds shifting to WNW, 10-20 mph.','Scattered Showers'],value=\"Rain\")\nplayList['Weather']=playList['Weather'].replace(to_replace=['Rain Chance 40%','Cloudy, chance of rain','30% Chance of Rain','10% Chance of Rain','Cloudy, 50% change of rain','Rain likely, temps in low 40s.'],value=\"Rain Chance\")\nplayList['Weather']=playList['Weather'].replace(to_replace=['Heavy lake effect snow','Cloudy, light snow accumulating 1-3\\\"'],value=\"Snow\")\nplayList['Weather']=playList['Weather'].replace(to_replace=['Indoor',\"Indoors\",'N/A Indoor',\"N/A (Indoors)\"],value=\"Unknown\")\nplayList['PlayType'].replace('0',np.nan,inplace=True)\nplayList['Position'].replace('Missing Data',np.nan,inplace=True)\n\n\n##################################injuryRecord###########################################\n\ninjuryRecord['DM_M28']=injuryRecord[\"DM_M28\"]-injuryRecord[\"DM_M42\"]\ninjuryRecord['DM_M7']=injuryRecord[\"DM_M7\"]-injuryRecord[\"DM_M42\"]\ninjuryRecord['DM_M1']=injuryRecord[\"DM_M1\"]-injuryRecord[\"DM_M42\"]\ninjuryRecord['DM_M7']=injuryRecord[\"DM_M7\"]-injuryRecord[\"DM_M28\"]\ninjuryRecord['DM_M1']=injuryRecord[\"DM_M1\"]-injuryRecord[\"DM_M28\"]\ninjuryRecord['DM_M1']=injuryRecord[\"DM_M1\"]-injuryRecord[\"DM_M7\"]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"complete cleaning code\n\n#imports \nimport pandas as pd\nimport re\nimport numpy as np\n#reading files\n#1\nplayerTrackData=pd.read_csv(r\"C:\\Users\\DBDA\\Music\\D\\project\\nfl-playing-surface-analytics\\PlayerTrackData.csv\",low_memory=False)\n#2\nplayList=pd.read_csv(r\"C:\\Users\\DBDA\\Music\\D\\project\\nfl-playing-surface-analytics\\PlayList.csv\",low_memory=False)\n#3\ninjuryRecord=pd.read_csv(r\"C:\\Users\\DBDA\\Music\\D\\project\\nfl-playing-surface-analytics\\InjuryRecord.csv\",low_memory=False)\n#File information\nprint(playerTrackData.info())\nprint(playList.info())\nprint(injuryRecord.info())\n##################################playerTrackData###########################################\nplay=playerTrackData.iloc[:,0]\nplay_gameid=[]\nfor i in play:\n    gameid_regex=re.findall(\"^[0-9]*-[0-9]*\",i)\n    play_gameid.extend(gameid_regex)\npp=playerTrackData\npp['GameId']=play_gameid\npp['event']=pp['event'].fillna(method='ffill')\npp['event']=pp['event'].replace(to_replace=['play_submit\\t'],value=\"play_submit\")\n#pp\ny=[]\nz=[]\nfor i in pp[\"GameId\"]:\n    x=re.findall(\"^\\d*-([0-9]*)\",i)\n    y.extend(x)\nfor i in y:\n    z.append(int(i)-1)\nq1=pp\nq1['count']=pd.DataFrame(z)\n\ndistance=[]\nkey=[]\ntime=[]\ncount=0\nfor i in range(len(q1)):\n    a = q1['time']\n    if a[i+1] > a[i]:\n        count=count+q1['dis'][i]\n    else:\n        lst.append(count)\n        key.append(q1['PlayKey'][i])\n        time.append(a[i])\n        count=0\n\ndf={'Key' : key ,'Distance' : distance,'Time' : time}\nnew=pd.DataFrame(data=df)\nnew['Speed']=new['Distance']/new['Time']\n\n##################################playList###########################################\n\nplayList['StadiumType']=playList['StadiumType'].replace(to_replace=[\"Outdoors\",\"Oudoor\",\"Ourdoor\",\"Outddors\"\\\n                                                                    ,\"Outdor\",\"Outside\",\"Heinz Field\",\"Bowl\",\"Cloudy\"],value=\"Outdoor\")\nplayList['StadiumType']=playList['StadiumType'].replace(to_replace=[\"Indoors\",\"Indoor\",\"Indoor, Roof Closed\"],value=\"Indoor Closed\") \nplayList['StadiumType']=playList['StadiumType'].replace(to_replace=[\"Domed, closed\",\"Dome, closed\",\"Domed, open\",\"Domed, Open\",\"Dome\"],value=\"Domed\")\nplayList['StadiumType']=playList['StadiumType'].replace(to_replace=[\"Retr. Roof-Closed\",\"Retr. Roof - Closed\",\"Retr. Roof Closed\"],value=\"Retractable Closed\")\nplayList['StadiumType']=playList['StadiumType'].replace(to_replace=[\"Outdoor Retr Roof-Open\",\"Retr. Roof - Open\",\"Retr. Roof-Open\",\"Retractable Roof\"],value=\"Retractable Open\")\nplayList['StadiumType']=playList['StadiumType'].replace(to_replace=[\"Indoor, Open Roof\",\"Open\"],value=\"Indoor Open\")\nplayList['Weather']=playList['Weather'].replace(to_replace=[\"Sunny\",'Mostly sunny','Clear to Partly Cloudy','Sun & clouds','Clear and sunny','Clear and Sunny','Sunny, Windy','Mostly Sunny Skies','Sunny, highs to upper 80s','Heat Index 95','Partly clear','Fair','Sunny Skies','Clear skies','Sunny and clear','Partly sunny',\"Clear Skies\",\"Mostly Sunny\",\"Partly Sunny\",'Clear and warm',\"Sunny and warm\",\"Controlled Climate\"],value=\"Clear\")\nplayList['Weather']=playList['Weather'].replace(to_replace=['Clear and Cool','Sunny and cold','Cold','Clear and cold'],value=\"Clear and Cold\")\nplayList['Weather']=playList['Weather'].replace(to_replace=[\"Partly Cloudy\",'Mostly Coudy','Overcast','cloudy','Coudy','Partly Clouidy','Hazy','Party Cloudy','Partly cloudy','Cloudy and cold','Cloudy and Cool',\"Mostly cloudy\",'Mostly Cloudy',\"Cloudy, fog started developing in 2nd quarter\",\"Cloudy\"],value=\"Cloudy\")\nplayList['Weather']=playList['Weather'].replace(to_replace=['Showers','Rain shower','Cloudy, Rain','Light Rain','Rainy','Cloudy with periods of rain, thunder possible. Winds shifting to WNW, 10-20 mph.','Scattered Showers'],value=\"Rain\")\nplayList['Weather']=playList['Weather'].replace(to_replace=['Rain Chance 40%','Cloudy, chance of rain','30% Chance of Rain','10% Chance of Rain','Cloudy, 50% change of rain','Rain likely, temps in low 40s.'],value=\"Rain Chance\")\nplayList['Weather']=playList['Weather'].replace(to_replace=['Heavy lake effect snow','Cloudy, light snow accumulating 1-3\\\"'],value=\"Snow\")\nplayList['Weather']=playList['Weather'].replace(to_replace=['Indoor',\"Indoors\",'N/A Indoor',\"N/A (Indoors)\"],value=\"Unknown\")\nplayList['PlayType'].replace('0',np.nan,inplace=True)\nplayList['Position'].replace('Missing Data',np.nan,inplace=True)\n\n##################################injuryRecord###########################################\n\ninjuryRecord['DM_M28']=injuryRecord[\"DM_M28\"]-injuryRecord[\"DM_M42\"]\ninjuryRecord['DM_M7']=injuryRecord[\"DM_M7\"]-injuryRecord[\"DM_M42\"]\ninjuryRecord['DM_M1']=injuryRecord[\"DM_M1\"]-injuryRecord[\"DM_M42\"]\ninjuryRecord['DM_M7']=injuryRecord[\"DM_M7\"]-injuryRecord[\"DM_M28\"]\ninjuryRecord['DM_M1']=injuryRecord[\"DM_M1\"]-injuryRecord[\"DM_M28\"]\ninjuryRecord['DM_M1']=injuryRecord[\"DM_M1\"]-injuryRecord[\"DM_M7\"]\n\n\n#joining injuryRecord and playList\nx=[\"PlayKey\",\"GameID\",\"PlayerKey\"]\nmerge23=pd.merge(injuryRecord,playList,on=x,how=\"left\")\n#joining merge(injuryRecord + playList) and playerTrackData ON Gameid\nmerge123=pd.merge(merge23,pp,left_on=\"PlayKey\",right_on=\"PlayKey\",how=\"left\")\n#joining playerTrackData and injuryRecord\nmerge13=pd.merge(injuryRecord,pp,left_on=\"PlayKey\",right_on=\"PlayKey\",how=\"left\")\n#joining playlist and playerTrackData\nmerge12=pd.merge(playList,pp,left_on=\"GameID\",right_on=\"GameId\",how=\"left\")\n#merging speed and merge(injuryRecord + playList + playerTrackData)\nmerge_speed=pd.merge(new,mod123,left_on=\"Key\",right_on=\"PlayKey\",how=\"inner\")\n\npp.to_csv(r\"C:\\Users\\DBDA\\Music\\D\\PlayerTrackData_mod_1.csv\",index=False)\nmerge13.to_csv(r\"C:\\Users\\DBDA\\Music\\D\\mod13.csv\",index=False)\nmerge23.to_csv(r\"C:\\Users\\DBDA\\Music\\D\\mod23.csv\",index=False)\nmerge123.to_csv(r\"C:\\Users\\DBDA\\Music\\D\\mod123.csv\",index=False)\nnew.to_csv(r\"C:\\Users\\DBDA\\Music\\D\\project\\nfl-playing-surface-analytics\\modified\\speeddd.csv\")\nmerge_speed.to_csv(r\"C:\\Users\\DBDA\\Music\\D\\project\\nfl-playing-surface-analytics\\modified\\speeddd_of_injured.csv\")\n\n#q1.to_csv(r\"C:\\Users\\DBDA\\Music\\D\\project\\nfl-playing-surface-analytics\\modified\\modismod.csv\")"}],"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.4"}},"nbformat":4,"nbformat_minor":1}