{"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":"markdown","source":"## This notebook include load data, convert to the true format and fill na-values","metadata":{}},{"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 pandas as pd\nimport numpy as np\nfrom datetime import datetime, timedelta\nimport random\nfrom sklearn.preprocessing import LabelEncoder, StandardScaler\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\nimport warnings\nwarnings.filterwarnings('ignore')\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-03-21T16:51:46.27733Z","iopub.execute_input":"2022-03-21T16:51:46.277611Z","iopub.status.idle":"2022-03-21T16:51:46.288644Z","shell.execute_reply.started":"2022-03-21T16:51:46.277582Z","shell.execute_reply":"2022-03-21T16:51:46.287656Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Load data","metadata":{"execution":{"iopub.status.busy":"2022-03-21T16:41:01.57582Z","iopub.execute_input":"2022-03-21T16:41:01.576334Z","iopub.status.idle":"2022-03-21T16:41:01.582337Z","shell.execute_reply.started":"2022-03-21T16:41:01.576285Z","shell.execute_reply":"2022-03-21T16:41:01.581207Z"}}},{"cell_type":"code","source":"# immediately convert 'match_date' to datetime and set up 'id' as index_col\ncustom_date_parser = lambda x: datetime.strptime(x, \"%Y-%m-%d %H:%M:%S\")\npath_to_datasets = '/kaggle/input/football-match-probability-prediction/'\ntrainset = pd.read_csv(path_to_datasets+'train.csv', index_col='id',parse_dates=['match_date'],\n                date_parser=custom_date_parser)\ntestset = pd.read_csv(path_to_datasets+'test.csv', index_col='id',parse_dates=['match_date'],\n                date_parser=custom_date_parser)\ntrainset.shape, testset.shape","metadata":{"execution":{"iopub.status.busy":"2022-03-21T16:51:50.967564Z","iopub.execute_input":"2022-03-21T16:51:50.968582Z","iopub.status.idle":"2022-03-21T16:52:01.806887Z","shell.execute_reply.started":"2022-03-21T16:51:50.968498Z","shell.execute_reply":"2022-03-21T16:52:01.805977Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"trainset.info(verbose=True, null_counts=True);","metadata":{"_kg_hide-input":false,"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Fill NA-values","metadata":{}},{"cell_type":"code","source":"class Fill_NA:\n    def __init__(self, dataset):\n        self.dataset = dataset\n        # descriptive_columns\n        self.descriptive_columns = ['home_team_name','away_team_name','match_date','league_name',\n                       'league_id','is_cup']\n        # coachs_id_columns\n        self.coachs_columns = ['home_team_coach_id','away_team_coach_id']\\\n                + [f'home_team_history_coach_{i}' for i in range(1,11)]\\\n                + [f'away_team_history_coach_{i}' for i in range(1,11)]\n        \n        # times_columns\n        self.home_team_history_match_dates = ['match_date']+[f'home_team_history_match_date_{i}' for i in range(1,11)]\n        self.away_team_history_match_dates = ['match_date']+[f'away_team_history_match_date_{i}' for i in range(1,11)]\n        \n        # team_is_play_home_columns\n        self.home_team_is_play_home = [f'home_team_history_is_play_home_{i}' for i in range(1,11)]\n        self.away_team_is_play_home = [f'away_team_history_is_play_home_{i}' for i in range(1,11)]\n        self.all_team_is_play_home = self.home_team_is_play_home + self.away_team_is_play_home\n        \n        # team_history_is_cup_columns\n        self.home_team_history_is_cup = [f'home_team_history_is_cup_{i}' for i in range(1,11)]\n        self.away_team_history_is_cup = [f'away_team_history_is_cup_{i}' for i in range(1,11)]\n        \n        # team_history_goal_columns\n        self.home_team_history_goal = [f'home_team_history_goal_{i}' for i in range(1,11)]\n        self.away_team_history_goal = [f'away_team_history_goal_{i}' for i in range(1,11)]\n        self.home_team_history_opponent_goal = [f'home_team_history_opponent_goal_{i}' for i in range(1,11)]\n        self.away_team_history_opponent_goal = [f'away_team_history_opponent_goal_{i}' for i in range(1,11)]\n        \n        # team_history_rating_columns\n        self.home_team_history_rating = [f'home_team_history_rating_{i}' for i in range(1,11)]\n        self.away_team_history_rating = [f'away_team_history_rating_{i}' for i in range(1,11)]\n        self.home_team_history_opponent_rating = [f'home_team_history_opponent_rating_{i}' for i in range(1,11)]\n        self.away_team_history_opponent_rating = [f'away_team_history_opponent_rating_{i}' for i in range(1,11)]\n        \n        # team_history_league_id_columns\n        self.away_team_history_league = [f'away_team_history_league_id_{i}' for i in range(1,11)]\n        self.home_team_history_league = [f'home_team_history_league_id_{i}' for i in range(1,11)] \n        \n    def filling_descr_col(self):\n        '''There is one position with NA values in columns \"home_team_name\" and \"away_team_name\". \n        So we can delete the whole position from dataset'''\n        drop_index = self.dataset[self.dataset['home_team_name'].isna()].index\n        self.dataset.drop(drop_index, axis = 0, inplace = True)\n        assert self.dataset[self.descriptive_columns].isna().sum().sum() == 0\n        \n    def filling_coachs_col(self, astype_int = True, lb_encoder = False):\n        ''' Fill NA-values with the values of the previous 'coach_history_id'. And finnaly with zeros\n        astype_int:[True, False] - convert to type 'int64'\n        lb_encoder:[True, False] - apply LabelEncoder()\n        '''\n        home_coachs_columns = ['home_team_coach_id']+[f'home_team_history_coach_{i}' for i in range(1,11)]\n        away_coachs_columns = ['away_team_coach_id']+[f'away_team_history_coach_{i}' for i in range(1,11)]\n        \n        #fill NA-values in main columns - \"home_team_coach_id\", \"away_team_coach_id\" with other columns\n        for i in home_coachs_columns[1:]:\n            self.dataset['home_team_coach_id'].fillna(self.dataset[i], inplace=True)\n        self.dataset['home_team_coach_id'].fillna(0, inplace=True)\n        for i in away_coachs_columns[1:]:\n            self.dataset['away_team_coach_id'].fillna(self.dataset[i], inplace=True)\n        self.dataset['away_team_coach_id'].fillna(0, inplace=True)\n        \n        # fill Na-values in history's columns\n        for i in home_coachs_columns[1:]:\n            self.dataset[i].fillna(self.dataset['home_team_coach_id'], inplace=True)\n        for i in away_coachs_columns[1:]:\n            self.dataset[i].fillna(self.dataset['away_team_coach_id'], inplace=True)\n        #check\n        assert self.dataset[self.coachs_columns].isna().sum().sum() == 0\n        \n        # type to int\n        if astype_int == True:\n            self.dataset[self.coachs_columns] = self.dataset[self.coachs_columns].astype('int64')\n        # LabelEncoder\n        if lb_encoder == True:\n            all_coach_id = list(set(self.dataset[self.coachs_columns].stack()))\n            le = LabelEncoder()\n            le.fit(all_coach_id)\n            for i in self.coachs_columns:\n                self.dataset[i] = le.transform(self.dataset[i])\n                \n    def filling_times_col(self):\n        ''' Convert to pandas datetime and fill NA-values with the previous data-values + 7-days timedelta \n        '''\n        # identify function - convert to pandas datetime\n        function_to_date = lambda x: datetime.strptime(str(x),\"%Y-%m-%d %H:%M:%S\")\n        \n        \n        # fill NA-values with the previous values + 7 days\n        for num, i in enumerate(self.home_team_history_match_dates[1:]):\n            values_for_compl = self.dataset[self.dataset[i].isna()][self.home_team_history_match_dates[num]]\n            self.dataset[i].fillna(values_for_compl.apply(function_to_date)- timedelta(days=7), inplace=True)\n            \n        for num, i in enumerate(self.away_team_history_match_dates[1:]):\n            values_for_compl = self.dataset[self.dataset[i].isna()][self.away_team_history_match_dates[num]]\n            self.dataset[i].fillna(values_for_compl.apply(function_to_date)- timedelta(days=7), inplace=True)\n            \n        # convert to datetime    \n        for i in self.home_team_history_match_dates[1:] + self.away_team_history_match_dates[1:]:\n            self.dataset[i] = self.dataset[i].apply(function_to_date)\n            \n        # check\n        all_times_collumns = self.home_team_history_match_dates+self.away_team_history_match_dates[1:]\n        assert self.dataset[all_times_collumns].isna().sum().sum() == 0\n        \n    def filling_history_is_play_home(self, astype_int=True):\n        '''\n        Filling the NA-values with interleaving\n        astype_int:[True, False] - convert to type 'int64'\n        '''\n              \n        \n        self.dataset[self.home_team_is_play_home[0]].fillna(0.0, inplace=True)\n        for num, i in enumerate(self.home_team_is_play_home[1:]):\n            values_for_compl = self.dataset[self.dataset[i].isna()][self.home_team_is_play_home[num]]\n            self.dataset[i].fillna(values_for_compl.apply(lambda x: 0.0 if x == 1.0 else 1.0), inplace=True)\n        assert self.dataset[self.home_team_is_play_home].isna().sum().sum() == 0\n\n        self.dataset[self.away_team_is_play_home[0]].fillna(0.0, inplace=True)\n        for num, i in enumerate(self.away_team_is_play_home[1:]):\n            values_for_compl = self.dataset[self.dataset[i].isna()][self.away_team_is_play_home[num]]\n            self.dataset[i].fillna(values_for_compl.apply(lambda x: 0.0 if x == 1.0 else 1.0), inplace=True)\n        assert self.dataset[self.away_team_is_play_home].isna().sum().sum() == 0\n\n        if astype_int == True:\n            self.dataset[self.all_team_is_play_home] = self.dataset[self.all_team_is_play_home].astype('int64')\n    \n    def filling_history_is_cup(self, astype_int=True):\n        '''\n        Filling NA-values with random with probabilities 0.92 (is not cup game) and 0.08 (is cup game)\n        astype_int:[True, False] - convert to type 'int64'\n        '''\n        team_history_is_cup = self.home_team_history_is_cup + self.away_team_history_is_cup\n        for i in team_history_is_cup:\n            self.dataset[i] = self.dataset[i].fillna(np.random.choice([1.0,0.0], p=[0.08,0.92]))\n        # from bool into (1,0)\n        self.dataset['is_cup'] = self.dataset['is_cup'].map({True:1,False:0})\n        \n        if astype_int == True:\n            self.dataset[team_history_is_cup] = self.dataset[team_history_is_cup].astype('int64')\n        assert self.dataset[team_history_is_cup].isna().sum().sum() == 0\n        \n    def filling_history_goal(self, astype_int=True):\n        '''\n        Filling NA-values with random with \n        probabilities (0.33,0.29,0.21,0.10,0.04,0.02,0.007,0.002,0.001) of goals (1,0,2,3,4,5,6,7,8)\n        astype_int:[True, False] - convert to type 'int64'\n        '''\n        \n        pvalues_goals = {1:0.33,0:0.29,2:0.21,3:0.10,4:0.04,5:0.02,6:0.007,7:0.002,8:0.001}\n        team_history_goal = self.home_team_history_goal + self.away_team_history_goal \\\n                            + self.home_team_history_opponent_goal + self.away_team_history_opponent_goal\n        for i in team_history_goal:\n            self.dataset[i] = self.dataset[i]\\\n                                .fillna(np.random.choice(list(pvalues_goals.keys()), p=list(pvalues_goals.values())))\n        if astype_int == True:\n            self.dataset[team_history_goal] = self.dataset[team_history_goal].astype('int64')\n        assert self.dataset[team_history_goal].isna().sum().sum() == 0\n    \n    \n    def filling_history_rating(self, to_log = True, standardization = False):\n        '''\n        Filling the NA-values with normal distribution (random.normalvariate)\n        to_log:[True, False] - convert to type log + 1\n        standardization:[True, False] - standardization witk StandardScaler()\n        '''\n        history_rating = self.home_team_history_rating + self.away_team_history_rating \\\n                + self.home_team_history_opponent_rating + self.away_team_history_opponent_rating\n        mean = self.dataset[history_rating].stack().mean()\n        std = self.dataset[history_rating].stack().std()\n        self.dataset[history_rating] = self.dataset[history_rating].fillna(random.normalvariate(mean,std))\n        assert self.dataset[history_rating].isna().sum().sum() == 0\n        # to log\n        if to_log==True:\n            self.dataset[history_rating] = np.log(self.dataset[history_rating]+1)\n        # standardization\n        if standardization == True:\n            scaler = StandardScaler()\n            self.dataset[history_rating] = scaler.fit_transform(self.dataset[history_rating])\n            \n    def filling_history_league(self,astype_int=True):\n        '''\n        Filling NA-values with the previous data-values\n        astype_int:[True, False] - convert to type 'int64'\n        '''\n        history_league = self.home_team_history_league + self.away_team_history_league\n        for num, i in enumerate(history_league):\n            if num == 0 or num ==10:\n                values_for_compl = self.dataset[self.dataset[i].isna()]['league_id']\n                self.dataset[i].fillna(values_for_compl, inplace=True)\n            else:\n                values_for_compl = self.dataset[self.dataset[i].isna()][history_league[num-1]]\n                self.dataset[i].fillna(values_for_compl, inplace=True)\n        assert self.dataset[history_league].isna().sum().sum() == 0\n        if astype_int == True:\n            self.dataset[history_league] = self.dataset[history_league].astype('int64')","metadata":{"execution":{"iopub.status.busy":"2022-03-21T16:42:51.206278Z","iopub.execute_input":"2022-03-21T16:42:51.206591Z","iopub.status.idle":"2022-03-21T16:42:51.263847Z","shell.execute_reply.started":"2022-03-21T16:42:51.206559Z","shell.execute_reply":"2022-03-21T16:42:51.262913Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Concatenate trainset and testset\ndataset = pd.concat([trainset.drop('target', axis=1), testset], axis=0)\nindex_split_dataset = trainset.shape[0]\ndataset.shape","metadata":{"execution":{"iopub.status.busy":"2022-03-21T16:43:07.478712Z","iopub.execute_input":"2022-03-21T16:43:07.479063Z","iopub.status.idle":"2022-03-21T16:43:07.926448Z","shell.execute_reply.started":"2022-03-21T16:43:07.479026Z","shell.execute_reply":"2022-03-21T16:43:07.925509Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%time\nclass_na = Fill_NA(dataset)\nclass_na.filling_coachs_col(lb_encoder=True)\nclass_na.filling_descr_col()\nclass_na.filling_history_goal()\nclass_na.filling_history_is_cup()\nclass_na.filling_history_is_play_home()\nclass_na.filling_history_league()\nclass_na.filling_history_rating()\nclass_na.filling_times_col()","metadata":{"execution":{"iopub.status.busy":"2022-03-21T16:46:03.334112Z","iopub.execute_input":"2022-03-21T16:46:03.335311Z","iopub.status.idle":"2022-03-21T16:47:19.704349Z","shell.execute_reply.started":"2022-03-21T16:46:03.335252Z","shell.execute_reply":"2022-03-21T16:47:19.70332Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"trainset = class_na.dataset.iloc[:index_split_dataset-1,:].copy()  # -1 because one position have been deleted\ntestset = class_na.dataset.iloc[index_split_dataset-1:,:].copy()\ntrainset.shape, testset.shape","metadata":{"execution":{"iopub.status.busy":"2022-03-21T16:47:58.843646Z","iopub.execute_input":"2022-03-21T16:47:58.843951Z","iopub.status.idle":"2022-03-21T16:47:59.044543Z","shell.execute_reply.started":"2022-03-21T16:47:58.843918Z","shell.execute_reply":"2022-03-21T16:47:59.04333Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"assert trainset.isna().sum().sum() == 0\nassert testset.isna().sum().sum() == 0","metadata":{"execution":{"iopub.status.busy":"2022-03-21T16:49:04.40055Z","iopub.execute_input":"2022-03-21T16:49:04.400825Z","iopub.status.idle":"2022-03-21T16:49:04.527432Z","shell.execute_reply.started":"2022-03-21T16:49:04.400796Z","shell.execute_reply":"2022-03-21T16:49:04.52666Z"},"trusted":true},"execution_count":null,"outputs":[]}]}