{"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":"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 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 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\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-04-16T11:41:22.295083Z","iopub.execute_input":"2022-04-16T11:41:22.295793Z","iopub.status.idle":"2022-04-16T11:41:22.324215Z","shell.execute_reply.started":"2022-04-16T11:41:22.295692Z","shell.execute_reply":"2022-04-16T11:41:22.323358Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"!pip install swifter","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:47:35.719619Z","iopub.execute_input":"2022-04-16T11:47:35.720631Z","iopub.status.idle":"2022-04-16T11:47:49.305709Z","shell.execute_reply.started":"2022-04-16T11:47:35.720558Z","shell.execute_reply":"2022-04-16T11:47:49.304813Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from plotly import express as ex\nfrom plotly.subplots import make_subplots\nfrom plotly import graph_objects as go\n\n\nimport seaborn as sns\nfrom matplotlib import pyplot as plt\n%matplotlib inline\nfrom termcolor import colored\n\nplt.style.use('ggplot')\nsns.set_palette('dark')\npd.set_option('display.max_columns', None)\n\nimport warnings\nwarnings.simplefilter('ignore')\n\nimport swifter # to enable DataFrame.apply() method to use multi cores parallel","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:48:03.451477Z","iopub.execute_input":"2022-04-16T11:48:03.45226Z","iopub.status.idle":"2022-04-16T11:48:04.266291Z","shell.execute_reply.started":"2022-04-16T11:48:03.452208Z","shell.execute_reply":"2022-04-16T11:48:04.265677Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"TRAIN_PATH = '/kaggle/input/football-match-probability-prediction/train.csv'\nTEST_PATH = '/kaggle/input/football-match-probability-prediction/test.csv'\nTRAIN_TARGET = '/kaggle/input/football-match-probability-prediction/train_target_and_scores.csv'","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:48:06.880893Z","iopub.execute_input":"2022-04-16T11:48:06.881717Z","iopub.status.idle":"2022-04-16T11:48:06.886202Z","shell.execute_reply.started":"2022-04-16T11:48:06.881672Z","shell.execute_reply":"2022-04-16T11:48:06.885434Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train = pd.read_csv(TRAIN_PATH)\ntest = pd.read_csv(TEST_PATH)\ntarget = pd.read_csv(TRAIN_TARGET)","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:48:07.463564Z","iopub.execute_input":"2022-04-16T11:48:07.464438Z","iopub.status.idle":"2022-04-16T11:48:18.301585Z","shell.execute_reply.started":"2022-04-16T11:48:07.464391Z","shell.execute_reply":"2022-04-16T11:48:18.300889Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"target.head()","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:48:18.303031Z","iopub.execute_input":"2022-04-16T11:48:18.303246Z","iopub.status.idle":"2022-04-16T11:48:18.32322Z","shell.execute_reply.started":"2022-04-16T11:48:18.303219Z","shell.execute_reply":"2022-04-16T11:48:18.322622Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.head()","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:48:22.1037Z","iopub.execute_input":"2022-04-16T11:48:22.104318Z","iopub.status.idle":"2022-04-16T11:48:22.306912Z","shell.execute_reply.started":"2022-04-16T11:48:22.104282Z","shell.execute_reply":"2022-04-16T11:48:22.305922Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"target['home_team_scored_goals'] = target.score.swifter.apply(lambda x : str(x).split('-')[0])\ntarget['home_team_received_goals'] = target.score.swifter.apply(lambda x : str(x).split('-')[1])\n\ntrain.insert(2, 'home_team_scored_goals', target['home_team_scored_goals'])\ntrain.insert(3, 'home_team_received_goals', target['home_team_received_goals'])\n\ntest.insert(2, 'home_team_scored_goals', np.NaN)\ntest.insert(3, 'home_team_received_goals', np.NaN)\n\ndel target","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:48:23.447436Z","iopub.execute_input":"2022-04-16T11:48:23.44772Z","iopub.status.idle":"2022-04-16T11:48:24.156617Z","shell.execute_reply.started":"2022-04-16T11:48:23.447688Z","shell.execute_reply":"2022-04-16T11:48:24.155724Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"

Univariate Analysis

","metadata":{}},{"cell_type":"code","source":"'''\nTarget Distribution\n'''\ntarget_percentage = train.target.value_counts()\nfig = ex.pie(values=target_percentage.values, names=target_percentage.index, title='Percentage of wins of home, away or draw')\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:48:26.177298Z","iopub.execute_input":"2022-04-16T11:48:26.177909Z","iopub.status.idle":"2022-04-16T11:48:27.193346Z","shell.execute_reply.started":"2022-04-16T11:48:26.177869Z","shell.execute_reply":"2022-04-16T11:48:27.192698Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"So, we find that home team will likely win, it seems logic as he has the advantage of his stadium and his fans","metadata":{}},{"cell_type":"markdown","source":" Is there more than one team having the same team name?","metadata":{}},{"cell_type":"code","source":"a = train.loc[:, ['home_team_name', 'league_id']]\nprint(a.home_team_name.nunique())\na = a.drop(a[a.duplicated()].index)\nunique_team_league = a.shape[0]\nprint('different teams in different leagues', unique_team_league)\nprint('number of multiple-leagues teams', unique_team_league - a.home_team_name.nunique())\n\nteams_No_leagues = a.groupby('home_team_name')['league_id'].count()\nteams_No_leagues.sort_values(ascending=False)[:20]\n","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:48:29.226016Z","iopub.execute_input":"2022-04-16T11:48:29.226734Z","iopub.status.idle":"2022-04-16T11:48:29.502141Z","shell.execute_reply.started":"2022-04-16T11:48:29.226684Z","shell.execute_reply":"2022-04-16T11:48:29.501315Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del a, teams_No_leagues","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:48:34.557754Z","iopub.execute_input":"2022-04-16T11:48:34.558082Z","iopub.status.idle":"2022-04-16T11:48:34.562168Z","shell.execute_reply.started":"2022-04-16T11:48:34.558045Z","shell.execute_reply":"2022-04-16T11:48:34.561472Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"There is no real team plays in different 9 leagues","metadata":{}},{"cell_type":"code","source":"b = train.groupby('home_team_name')['id'].count().sort_values(ascending=False)\nfig, ax = plt.subplots(1, figsize=(15, 5))\nsns.distplot(b, ax=ax)\nplt.xticks(range(0, 100, 5))\nax.set(title='distribution of number of matches for each team', ylabel='No.Matches');\n\ndel b, fig, ax","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:48:35.632029Z","iopub.execute_input":"2022-04-16T11:48:35.632619Z","iopub.status.idle":"2022-04-16T11:48:36.161845Z","shell.execute_reply.started":"2022-04-16T11:48:35.63258Z","shell.execute_reply":"2022-04-16T11:48:36.160923Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(colored('Number of played home matches for every team:', 'red'))\nprint(train.home_team_name.value_counts())\nprint(colored(f'{100*\"#\"}\\n{100*\"#\"}', 'green'))\nprint(colored('Number of played away matches for every team:', 'red'))\nprint(train.away_team_name.value_counts())","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:48:38.46222Z","iopub.execute_input":"2022-04-16T11:48:38.462479Z","iopub.status.idle":"2022-04-16T11:48:38.528462Z","shell.execute_reply.started":"2022-04-16T11:48:38.462452Z","shell.execute_reply":"2022-04-16T11:48:38.527486Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Top 5 teams in home and away counting matches are the same, so they must be the top playing teams in our dataset","metadata":{}},{"cell_type":"markdown","source":"

Match date univariate analysis

","metadata":{"execution":{"iopub.status.busy":"2022-03-25T15:35:39.461374Z","iopub.execute_input":"2022-03-25T15:35:39.461687Z","iopub.status.idle":"2022-03-25T15:35:39.467646Z","shell.execute_reply.started":"2022-03-25T15:35:39.461653Z","shell.execute_reply":"2022-03-25T15:35:39.466465Z"}}},{"cell_type":"code","source":"train['match_date'] = pd.to_datetime(train['match_date'])\ntest['match_date'] = pd.to_datetime(test['match_date'])\nfor dataset in [train, test]:\n dataset.insert(12, 'day', dataset['match_date'].swifter.apply(lambda x : x.day))\n dataset.insert(13, 'hour', dataset['match_date'].swifter.apply(lambda x : x.hour))\n dataset.insert(14, 'month', dataset['match_date'].swifter.apply(lambda x : x.month))\n dataset.insert(15, 'year', dataset['match_date'].swifter.apply(lambda x : x.year))\n \ntrain[['match_date', 'hour', 'day', 'month', 'year']]","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:48:41.606069Z","iopub.execute_input":"2022-04-16T11:48:41.606771Z","iopub.status.idle":"2022-04-16T11:48:48.881002Z","shell.execute_reply.started":"2022-04-16T11:48:41.606707Z","shell.execute_reply":"2022-04-16T11:48:48.880014Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"date_segments = ['hour', 'day', 'month', 'year']\nfig = make_subplots(rows=2, cols=2, subplot_titles=date_segments)\n\nfor idx, col in enumerate(date_segments):\n fig.add_trace(\n go.Histogram(x=train[col]), row=int(1 if idx<2 else 2), col= 1 if idx%2==0 else 2 \n )\n fig.update_layout({'title':{'text':'date Ditributions'}})\n\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:48:48.882723Z","iopub.execute_input":"2022-04-16T11:48:48.883121Z","iopub.status.idle":"2022-04-16T11:48:48.993713Z","shell.execute_reply.started":"2022-04-16T11:48:48.883079Z","shell.execute_reply":"2022-04-16T11:48:48.99291Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"

League_id & League_name Analysis

","metadata":{}},{"cell_type":"code","source":"'''\nis there a league has more than one name ?\nif so, \nis there a league has more than one ID?\n'''\ndf = pd.concat([train, test], axis=0)\na = df[['league_id', 'league_name']]\nprint(a.groupby('league_id')['league_name'].nunique().sort_values(ascending=False))\nprint(colored(f'{100*\"#\"}\\n{100*\"#\"}', 'green'))\nprint(a.groupby('league_name')['league_id'].nunique().sort_values(ascending=False))","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:48:54.560074Z","iopub.execute_input":"2022-04-16T11:48:54.560329Z","iopub.status.idle":"2022-04-16T11:48:55.48452Z","shell.execute_reply.started":"2022-04-16T11:48:54.560302Z","shell.execute_reply":"2022-04-16T11:48:55.483559Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"we find that there are 18 leagues with two names,\n\nand multiple leagues have more than one ID(sometimes reaches 34)!!!!!!!\n\nin this case we should fix only league_name column","metadata":{}},{"cell_type":"code","source":"doublenamed_IDs = a.groupby('league_id')['league_name'].nunique().sort_values(ascending=False)[:18].index\na['isdoubled_name'] = a.league_id.apply(lambda x : x in doublenamed_IDs)\n\nprint('Number of unique league names before replcing repeated names = ', df.league_name.nunique())\n\nfor id in doublenamed_IDs:\n most_used_name = a.loc[a['league_id'] == id, 'league_name'].mode()[0]\n df.loc[a.league_id == id, 'league_name'] = most_used_name\n \nprint('Number of unique league names after replacing repeated names = ', df.league_name.nunique())\n\ndel a, doublenamed_IDs","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:48:58.144166Z","iopub.execute_input":"2022-04-16T11:48:58.145016Z","iopub.status.idle":"2022-04-16T11:48:58.819157Z","shell.execute_reply.started":"2022-04-16T11:48:58.14496Z","shell.execute_reply":"2022-04-16T11:48:58.818406Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"'''\ntop 20 recorded leagues\n'''\na = train.league_name.value_counts()\nfig = plt.figure(figsize=(10, 5))\nsns.barplot(x=a.values[:20], y=a.index[:20])\n\ndel a, fig","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:49:00.944712Z","iopub.execute_input":"2022-04-16T11:49:00.945029Z","iopub.status.idle":"2022-04-16T11:49:01.309689Z","shell.execute_reply.started":"2022-04-16T11:49:00.944988Z","shell.execute_reply":"2022-04-16T11:49:01.30879Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.countplot(train.is_cup)\n# most matches are league matches\n# Note: cups include Champions league, Europa league, ....","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:49:01.4892Z","iopub.execute_input":"2022-04-16T11:49:01.489513Z","iopub.status.idle":"2022-04-16T11:49:01.975765Z","shell.execute_reply.started":"2022-04-16T11:49:01.489471Z","shell.execute_reply":"2022-04-16T11:49:01.975209Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"

Feature Engineering

","metadata":{}},{"cell_type":"code","source":"print((df.columns[16:196:10]))","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:49:04.632218Z","iopub.execute_input":"2022-04-16T11:49:04.633005Z","iopub.status.idle":"2022-04-16T11:49:04.638198Z","shell.execute_reply.started":"2022-04-16T11:49:04.632951Z","shell.execute_reply":"2022-04-16T11:49:04.63752Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"'''\n Creating New features \n'''\nextract_year = lambda x : x.year\nextract_month = lambda x : x.month\nextract_day = lambda x : x.day\nextract_hour = lambda x : x.hour\ndate_segments = {'year':extract_year, 'month':extract_month, 'day':extract_day, 'hour':extract_hour}\n\ndf['home_team_last_10_matches_average_rating'] = 0\ndf['away_team_last_10_matches_average_rating'] = 0\n\ndef get_result(x):\n if x > 0:\n x = 2 # win\n elif x == 0 :\n x = 1 # Draw\n else:\n x = 0 # lose\n return x\n \nfor i in range(1, 11):\n \n #team_history_match_date_i columns\n \n df[f'home_team_history_match_date_{i}'] = pd.to_datetime(df[f'home_team_history_match_date_{i}'])\n df[f'away_team_history_match_date_{i}'] = pd.to_datetime(df[f'away_team_history_match_date_{i}'])\n\n for segment in date_segments:\n # for home team\n date_segment = df[f'home_team_history_match_date_{i}'].apply(date_segments[segment])\n df[f'home_team_history_match_{segment}_{i}'] = date_segment\n \n # for away team\n date_segment = df[f'away_team_history_match_date_{i}'].apply(date_segments[segment])\n df[f'away_team_history_match_{segment}_{i}'] = date_segment\n \n # add Goal difference column\n \n home_team_scored_goals = df[f'home_team_history_goal_{i}']\n home_team_received_goals = df[f'home_team_history_opponent_goal_{i}']\n df[f'home_team_history_goal_Diff_{i}'] = home_team_scored_goals - home_team_received_goals\n \n away_team_scored_goals = df[f'away_team_history_goal_{i}']\n away_team_received_goals = df[f'away_team_history_opponent_goal_{i}']\n df[f'away_team_history_goal_Diff_{i}'] = away_team_scored_goals - away_team_received_goals\n \n # add is same couch column\n \n if i!=10:\n home_last_match_coach = df[f'home_team_history_coach_{i+1}']\n home_current_match_coach = df[f'home_team_history_coach_{i}']\n df[f'home_team_history_is_same_coach_{i}'] = home_current_match_coach==home_last_match_coach\n \n away_last_match_coach = df[f'away_team_history_coach_{i+1}']\n away_current_match_coach = df[f'away_team_history_coach_{i}']\n df[f'away_team_history_is_same_coach_{i}'] = away_current_match_coach==away_last_match_coach\n else:\n home_last_match_coach = df['home_team_history_coach_1']\n home_current_match_coach = df['home_team_coach_id']\n df['home_team_is_same_coach'] = home_current_match_coach==home_last_match_coach\n \n away_last_match_coach = df['away_team_history_coach_1']\n away_current_match_coach = df['away_team_coach_id']\n df['away_team_is_same_coach'] = away_current_match_coach==away_last_match_coach\n \n # add is same league\n \n if i!=10:\n home_last_match_league = df[f'home_team_history_league_id_{i+1}']\n home_current_match_league = df[f'home_team_history_league_id_{i}']\n df[f'home_team_history_is_same_league_{i}'] = home_last_match_league==home_current_match_league\n \n away_last_match_league = df[f'away_team_history_league_id_{i+1}']\n away_current_match_league = df[f'away_team_history_league_id_{i}']\n df[f'away_team_history_is_same_league_{i}'] = away_last_match_league==away_current_match_league\n else:\n home_last_match_league = df['home_team_history_league_id_1']\n current_match_league = df['league_id']\n df['home_team_is_same_league'] = home_last_match_league == current_match_league\n \n away_last_match_league = df['away_team_history_league_id_1']\n df['away_team_is_same_league'] = away_last_match_league == current_match_league\n \n # add last 10 matches average rating\n \n df['home_team_last_10_matches_average_rating'] += df[f'home_team_history_rating_{i}'] / 10\n df['away_team_last_10_matches_average_rating'] += df[f'away_team_history_rating_{i}'] / 10\n \n # add is result feature\n \n df[f'home_team_history_result_{i}'] = df[f'home_team_history_goal_Diff_{i}'].swifter.apply(get_result)\n df[f'away_team_history_result_{i}'] = df[f'home_team_history_goal_Diff_{i}'].swifter.apply(get_result)\n \ndel extract_day, extract_hour, extract_month, extract_year\ndel get_result, date_segment, date_segments, segment, i, ","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:49:06.939838Z","iopub.execute_input":"2022-04-16T11:49:06.940341Z","iopub.status.idle":"2022-04-16T11:50:55.781383Z","shell.execute_reply.started":"2022-04-16T11:49:06.940306Z","shell.execute_reply":"2022-04-16T11:50:55.780493Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# drop not important features:\n\nfor i in range(1, 11):\n df.drop(f'home_team_history_match_date_{i}', axis=1, inplace=True)\n df.drop(f'home_team_history_goal_{i}', axis=1, inplace=True)\n df.drop(f'home_team_history_opponent_goal_{i}', axis=1, inplace=True)\n df.drop(f'home_team_history_rating_{i}', axis=1, inplace=True)\n \n df.drop(f'away_team_history_match_date_{i}', axis=1, inplace=True)\n df.drop(f'away_team_history_goal_{i}', axis=1, inplace=True)\n df.drop(f'away_team_history_opponent_goal_{i}', axis=1, inplace=True)\n df.drop(f'away_team_history_rating_{i}', axis=1, inplace=True)\n","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:51:34.211365Z","iopub.execute_input":"2022-04-16T11:51:34.212424Z","iopub.status.idle":"2022-04-16T11:51:34.348668Z","shell.execute_reply.started":"2022-04-16T11:51:34.212358Z","shell.execute_reply":"2022-04-16T11:51:34.347519Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# another feature engineering\n# '''\n# Adding Goal difference in the last 10 teams for each team\n# '''\n# history = df.iloc[:, 12:-4]\n# home_away_columns = map(lambda x:x.split('_')[0]=='home' ,history.columns)\n# home_away_columns = list(home_away_columns)\n\n# history_home_team = history.loc[:, home_away_columns]\n# history_away_team = history.drop(history_home_team.columns, axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:51:39.352955Z","iopub.execute_input":"2022-04-16T11:51:39.353273Z","iopub.status.idle":"2022-04-16T11:51:39.357212Z","shell.execute_reply.started":"2022-04-16T11:51:39.353237Z","shell.execute_reply":"2022-04-16T11:51:39.356346Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# # history of home team\n# '''\n# first I will delete date columns from history dataset, as model may overfit on them\n# second, I will delete is cup columns also as they are already defined from league_id\n# '''\n\n# date_features = [*map(lambda x : 'date' in x, history_home_team.columns)]\n# history_home_team = history_home_team.drop(history_home_team.loc[:, date_features].columns, axis=1)\n\n# is_cup_features = [*map(lambda x : 'is_cup' in x, history_home_team.columns)]\n# history_home_team = history_home_team.drop(history_home_team.loc[:, is_cup_features].columns, axis=1)\n\n# '''\n# Second we will split hsitory table into two tables, with respect to is_home columns,\n# So, Last home and away matches will be in two different tables\n# '''\n# is_home_features = [*map(lambda x : 'is_play_home' in x, history_home_team.columns)]\n# history_home_team.loc[:, is_home_features] = history_home_team.loc[:, is_home_features].replace({1.0:True, 0.0:False})\n\n# rename_dict = dict(zip(history_home_team.iloc[:, :10].columns, np.arange(1, 11)))\n# history_home_team = history_home_team.rename(columns=rename_dict)\n# history_home_team['Home_matches_numbers'] = history_home_team.iloc[:, :10].apply(lambda x : )\n\n# dict(history_home_team.iloc[:, :10])\n# history_home_team.iloc[:, :10].apply(lambda x : dict(x))\n\n# for idx, row in zip(history_home_team.index, history_home_team.iloc[:, :10]):\n# row = dict(row)\n# history_home_team.iloc[idx, 'home_away_matches'] = row\n \n# history_home_team\n#history_home_team.iloc[:20, :10].T\n\n# for i, j in zip(history_home_team.iloc[:15, :10].values, history_home_team.iloc[:, :10].columns):\n# print(i)\n# print(50*'#')\n# print(j)\n# print(50*'#')\n\n# home_history_home_team = history_home_team.loc[:, ColumnsOfHomeMatches]\n","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:51:52.74347Z","iopub.execute_input":"2022-04-16T11:51:52.743778Z","iopub.status.idle":"2022-04-16T11:51:52.749724Z","shell.execute_reply.started":"2022-04-16T11:51:52.743743Z","shell.execute_reply":"2022-04-16T11:51:52.74878Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"

Impute Nulls

","metadata":{}},{"cell_type":"code","source":"nulls = pd.DataFrame({'No. Nulls':df.isnull().sum()})\nnulls['Nulls_Percentage'] = nulls['No. Nulls'].apply(lambda x : x / len(df) * 100)\n\nnulls.drop(nulls[nulls['No. Nulls']==0].index, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:51:55.189547Z","iopub.execute_input":"2022-04-16T11:51:55.190294Z","iopub.status.idle":"2022-04-16T11:51:55.417979Z","shell.execute_reply.started":"2022-04-16T11:51:55.19025Z","shell.execute_reply":"2022-04-16T11:51:55.417103Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nulls = nulls.sort_values(by='No. Nulls', ascending=False)\nnulls.tail(20)","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:51:56.401432Z","iopub.execute_input":"2022-04-16T11:51:56.402369Z","iopub.status.idle":"2022-04-16T11:51:56.415005Z","shell.execute_reply.started":"2022-04-16T11:51:56.402315Z","shell.execute_reply":"2022-04-16T11:51:56.414067Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.drop(df[df.home_team_name.isnull()].index, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:51:58.510756Z","iopub.execute_input":"2022-04-16T11:51:58.511842Z","iopub.status.idle":"2022-04-16T11:51:58.739775Z","shell.execute_reply.started":"2022-04-16T11:51:58.511777Z","shell.execute_reply":"2022-04-16T11:51:58.738846Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# home team history nulls for first_match\n# drop rows\nhome_team_history_nulls = df.loc[df.home_team_history_goal_Diff_1.isnull()].index\ndf.drop(home_team_history_nulls, inplace=True, axis=0)","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:52:00.52202Z","iopub.execute_input":"2022-04-16T11:52:00.522404Z","iopub.status.idle":"2022-04-16T11:52:00.821936Z","shell.execute_reply.started":"2022-04-16T11:52:00.522363Z","shell.execute_reply":"2022-04-16T11:52:00.820914Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# away team history nulls for first_match\n# drop rows\naway_team_history_nulls = df.loc[df.away_team_history_goal_Diff_1.isnull()].index\ndf.drop(away_team_history_nulls, inplace=True, axis=0)","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:52:02.285884Z","iopub.execute_input":"2022-04-16T11:52:02.286469Z","iopub.status.idle":"2022-04-16T11:52:02.510556Z","shell.execute_reply.started":"2022-04-16T11:52:02.28643Z","shell.execute_reply":"2022-04-16T11:52:02.509633Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Impute opponent rating for home & away team for 01 match\n\nhome_GD_OpponentRate = df.groupby('home_team_history_goal_Diff_1')['home_team_history_opponent_rating_1'].mean()\naway_GD_OpponentRate = df.groupby('away_team_history_goal_Diff_1')['away_team_history_opponent_rating_1'].mean()\ndef GD_OppRateTransform(x):\n return home_GD_OpponentRate[x]\n\nhome_GD_Null_rates = df.loc[df.home_team_history_opponent_rating_1.isnull(), 'home_team_history_goal_Diff_1']\nhome_empty_rates = home_GD_Null_rates.apply(GD_OppRateTransform)\ndf.loc[df.home_team_history_opponent_rating_1.isnull(), 'home_team_history_opponent_rating_1'] = home_empty_rates\n\n\naway_GD_Null_rates = df.loc[df.away_team_history_opponent_rating_1.isnull(), 'away_team_history_goal_Diff_1']\naway_empty_rates = away_GD_Null_rates.apply(lambda x : away_GD_OpponentRate[x])\ndf.loc[df.away_team_history_opponent_rating_1.isnull(), 'away_team_history_opponent_rating_1'] = away_empty_rates","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:52:04.007906Z","iopub.execute_input":"2022-04-16T11:52:04.008223Z","iopub.status.idle":"2022-04-16T11:52:04.036829Z","shell.execute_reply.started":"2022-04-16T11:52:04.008188Z","shell.execute_reply":"2022-04-16T11:52:04.036202Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# impute nulls for is_cup column with (False)\nfor i in range(1, 11):\n df[f'home_team_history_is_cup_{i}'].fillna(False, inplace=True)\n df[f'away_team_history_is_cup_{i}'].fillna(False, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:52:05.544795Z","iopub.execute_input":"2022-04-16T11:52:05.545349Z","iopub.status.idle":"2022-04-16T11:52:08.010353Z","shell.execute_reply.started":"2022-04-16T11:52:05.545304Z","shell.execute_reply":"2022-04-16T11:52:08.00962Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# impute day columns\n# fill missing values with most common match-days\n\nfrom sklearn.impute import SimpleImputer\nSI = SimpleImputer(strategy='most_frequent', missing_values=np.NaN)\n\nday_columns_name = df.columns[[*map(lambda x : 'day' in x, df.columns)]] \n\ndf[day_columns_name] = SI.fit_transform(df[day_columns_name])","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:52:08.011732Z","iopub.execute_input":"2022-04-16T11:52:08.011944Z","iopub.status.idle":"2022-04-16T11:52:08.807169Z","shell.execute_reply.started":"2022-04-16T11:52:08.011919Z","shell.execute_reply":"2022-04-16T11:52:08.806218Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# impute league ID nulls for home team\n\nLEAGUE_ID_HOME_TEAM_NAME = pd.pivot_table(data=df, index='home_team_name', values='league_id', aggfunc=lambda x : x.mode()[0])\nhome_league_id_columns_name = df.columns[[*map(lambda x : 'league_id' in x and 'home' in x, df.columns)]] \n\nfor col in home_league_id_columns_name:\n teams_league_IDs = df.loc[df[col].isnull(), 'home_team_name'].apply(lambda x : LEAGUE_ID_HOME_TEAM_NAME.loc[x].values)\n df.loc[df[col].isnull(), col] = teams_league_IDs","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:52:10.829911Z","iopub.execute_input":"2022-04-16T11:52:10.83024Z","iopub.status.idle":"2022-04-16T11:52:20.263641Z","shell.execute_reply.started":"2022-04-16T11:52:10.830204Z","shell.execute_reply":"2022-04-16T11:52:20.262594Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# impute League ID nulls for away team\n\nLEAGUE_ID_AWAY_TEAM_NAME = pd.pivot_table(data=df, index='away_team_name', values='league_id', aggfunc=lambda x : x.mode()[0])\naway_league_id_columns_name = df.columns[[*map(lambda x : 'league_id' in x and 'away' in x, df.columns)]] \n\nfor col in away_league_id_columns_name:\n teams_league_IDs = df.loc[df[col].isnull(), 'away_team_name'].apply(lambda x : LEAGUE_ID_AWAY_TEAM_NAME.loc[x].values)\n df.loc[df[col].isnull(), col] = teams_league_IDs","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:52:21.092287Z","iopub.execute_input":"2022-04-16T11:52:21.092554Z","iopub.status.idle":"2022-04-16T11:52:31.03965Z","shell.execute_reply.started":"2022-04-16T11:52:21.092526Z","shell.execute_reply":"2022-04-16T11:52:31.039041Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# impute nulls for match hour\n\nfrom sklearn.impute import SimpleImputer\nSI = SimpleImputer(strategy='most_frequent', missing_values=np.NaN)\n\nhour_columns_name = df.columns[[*map(lambda x : 'hour' in x, df.columns)]] \n\ndf[hour_columns_name] = SI.fit_transform(df[hour_columns_name])","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:52:32.286453Z","iopub.execute_input":"2022-04-16T11:52:32.28708Z","iopub.status.idle":"2022-04-16T11:52:32.903285Z","shell.execute_reply.started":"2022-04-16T11:52:32.28704Z","shell.execute_reply":"2022-04-16T11:52:32.902641Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# impute year columns\ndef yearimputer(x):\n if x != 2021:\n x += 1\n return x\n\nfor i in range(1, 11):\n if i !=1:\n yeardf = df.loc[df[f'home_team_history_match_year_{i}'].isnull(), f'home_team_history_match_year_{i - 1}']\n yeardf = yeardf.apply(yearimputer)\n df.loc[df[f'home_team_history_match_year_{i}'].isnull(), f'home_team_history_match_year_{i}'] = yeardf\n\n \n yeardf = df.loc[df[f'away_team_history_match_year_{i}'].isnull(), f'away_team_history_match_year_{i - 1}']\n yeardf = yeardf.apply(yearimputer)\n df.loc[df[f'away_team_history_match_year_{i}'].isnull(), f'away_team_history_match_year_{i}'] = yeardf","metadata":{"execution":{"iopub.status.busy":"2022-04-16T11:52:34.57828Z","iopub.execute_input":"2022-04-16T11:52:34.579396Z","iopub.status.idle":"2022-04-16T11:52:34.682463Z","shell.execute_reply.started":"2022-04-16T11:52:34.57934Z","shell.execute_reply":"2022-04-16T11:52:34.681539Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# impute nulls in (home&away)_team_history_goal_Diff columns\n\n'''\naggregate the average Goal Difference value for each team\n'''\nhome_teams_history_GD = pd.pivot_table(data=df\n , index='home_team_name'\n , values=[f'home_team_history_goal_Diff_{i}' for i in range(1,11)]\n , aggfunc='mean')\nhome_teams_history_GD = home_teams_history_GD.mean(axis=1)\n\n\naway_teams_history_GD = pd.pivot_table(data=df\n , index='away_team_name'\n , values=[f'away_team_history_goal_Diff_{i}' for i in range(1,11)]\n , aggfunc='mean')\naway_teams_history_GD = away_teams_history_GD.mean(axis=1)\n\n\ndef home_impute_GD_with_name(name):\n return home_teams_history_GD[name]\n \ndef away_impute_GD_with_name(name):\n return away_teams_history_GD[name]\n\n\nfor i in range(1, 11):\n # home team\n \n home_GD_nulls_DF = df.loc[df[f'home_team_history_goal_Diff_{i}'].isnull()]\n average_goal_difference = home_impute_GD_with_name(home_GD_nulls_DF['home_team_name'])\n home_GD_nulls_DF[f'home_team_history_goal_Diff_{i}'] = average_goal_difference.values\n df.loc[df[f'home_team_history_goal_Diff_{i}'].isnull()] = home_GD_nulls_DF\n \n #away team\n \n away_GD_nulls_DF = df.loc[df[f'away_team_history_goal_Diff_{i}'].isnull()]\n average_goal_difference = away_impute_GD_with_name(away_GD_nulls_DF['away_team_name'])\n away_GD_nulls_DF[f'away_team_history_goal_Diff_{i}'] = average_goal_difference.values\n df.loc[df[f'away_team_history_goal_Diff_{i}'].isnull()] = away_GD_nulls_DF","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# impute nulls in (home/away)_is_paly_home\nis_play_home_columns = map(lambda x : \"is_play_home\" in x, df.columns)\nis_play_home_columns = df.columns[[*is_play_home_columns]]\nis_play_home_df = df[is_play_home_columns]\n\nis_play_home_df.describe().loc['50%']","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n The median of each column shows that almost each team play 1 match home and 1 match away\n \n , So for home_team, the first history match will be away (even matches will be home)\n , for away_team, the first history match will be home (even matches will be away)\n","metadata":{}},{"cell_type":"code","source":"for i in range(1, 11):\n # home_team\n \n if (i%2==0):\n df.loc[df[f'home_team_history_is_play_home_{i}'].isnull(), f'home_team_history_is_play_home_{i}'] = 1.0\n else:\n df.loc[df[f'home_team_history_is_play_home_{i}'].isnull(), f'home_team_history_is_play_home_{i}'] = 0.0\n \n \n # away_team\n \n if (i%2==0):\n df.loc[df[f'away_team_history_is_play_home_{i}'].isnull(), f'away_team_history_is_play_home_{i}'] = 0.0\n else:\n df.loc[df[f'away_team_history_is_play_home_{i}'].isnull(), f'away_team_history_is_play_home_{i}'] = 1.0","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# impute nulls in (home&away)_opponent_rating columns\nopponent_rating_columns =[*map(lambda x:'opponent_rating' in x, df.columns)]\nopponent_rating_columns = df.columns[opponent_rating_columns]\ndf[opponent_rating_columns].describe()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# there are many outliers, so we will impute with median\ndf[opponent_rating_columns] = df[opponent_rating_columns].fillna(df[opponent_rating_columns].median())","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# is_same_coach\nis_same_coach_columns =[*map(lambda x:'is_same_coach' in x, df.columns)]\nis_same_coach_columns = df.columns[is_same_coach_columns]\ndf[is_same_coach_columns] = df[is_same_coach_columns].fillna(True)","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"

Still in progress !!!

","metadata":{}}]}