merge_all_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>home_team</th>\n",
       "      <th>away_team</th>\n",
       "      <th>home_score</th>\n",
       "      <th>away_score</th>\n",
       "      <th>tournament</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>neutral</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1872-11-30</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>England</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Glasgow</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>False</td>\n",
       "      <td>1872</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1873-03-08</td>\n",
       "      <td>England</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>London</td>\n",
       "      <td>England</td>\n",
       "      <td>False</td>\n",
       "      <td>1873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1874-03-07</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>England</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Glasgow</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>False</td>\n",
       "      <td>1874</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1875-03-06</td>\n",
       "      <td>England</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>London</td>\n",
       "      <td>England</td>\n",
       "      <td>False</td>\n",
       "      <td>1875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1876-03-04</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>England</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Glasgow</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>False</td>\n",
       "      <td>1876</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date home_team away_team  home_score  away_score tournament     city  \\\n",
       "0 1872-11-30  Scotland   England           0           0   Friendly  Glasgow   \n",
       "1 1873-03-08   England  Scotland           4           2   Friendly   London   \n",
       "2 1874-03-07  Scotland   England           2           1   Friendly  Glasgow   \n",
       "3 1875-03-06   England  Scotland           2           2   Friendly   London   \n",
       "4 1876-03-04  Scotland   England           3           0   Friendly  Glasgow   \n",
       "\n",
       "    country  neutral  year  \n",
       "0  Scotland    False  1872  \n",
       "1   England    False  1873  \n",
       "2  Scotland    False  1874  \n",
       "3   England    False  1875  \n",
       "4  Scotland    False  1876  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "match_results = pd.read_csv('data/original/results.csv')\n",
    "match_results['date'] =  pd.to_datetime(match_results['date'], format='%Y-%m-%d')\n",
    "match_results[\"year\"] = match_results[\"date\"].dt.year\n",
    "match_results.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>team</th>\n",
       "      <th>elo</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Scotland</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>1872-11-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>England</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>1872-11-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Wales</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>1872-11-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Northern Ireland</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>1872-11-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>United States</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>1872-11-30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               team     elo       date\n",
       "0          Scotland  1500.0 1872-11-30\n",
       "1           England  1500.0 1872-11-30\n",
       "2             Wales  1500.0 1872-11-30\n",
       "3  Northern Ireland  1500.0 1872-11-30\n",
       "4     United States  1500.0 1872-11-30"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "elo_ranking = pd.read_csv('data/generated/elo_ranking.csv', )\n",
    "elo_ranking['date'] =  pd.to_datetime(elo_ranking['date'], format='%Y-%m-%d')\n",
    "elo_ranking = elo_ranking.drop(['Unnamed: 0'], axis=1)\n",
    "elo_ranking.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nationality</th>\n",
       "      <th>year</th>\n",
       "      <th>overall_rating_max</th>\n",
       "      <th>overall_rating_min</th>\n",
       "      <th>overall_rating_avg</th>\n",
       "      <th>potential_max</th>\n",
       "      <th>potential_min</th>\n",
       "      <th>potential_avg</th>\n",
       "      <th>height</th>\n",
       "      <th>weight</th>\n",
       "      <th>...</th>\n",
       "      <th>Sprint_Speed</th>\n",
       "      <th>Reactions</th>\n",
       "      <th>Shot_Power</th>\n",
       "      <th>Stamina</th>\n",
       "      <th>Strength</th>\n",
       "      <th>Long_Shots</th>\n",
       "      <th>Aggression</th>\n",
       "      <th>Penalties</th>\n",
       "      <th>Marking</th>\n",
       "      <th>Standing_Tackle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2014</td>\n",
       "      <td>77</td>\n",
       "      <td>64</td>\n",
       "      <td>67.608696</td>\n",
       "      <td>80</td>\n",
       "      <td>66</td>\n",
       "      <td>71.434783</td>\n",
       "      <td>181.521739</td>\n",
       "      <td>76.260870</td>\n",
       "      <td>...</td>\n",
       "      <td>85.666667</td>\n",
       "      <td>72.2</td>\n",
       "      <td>77.2</td>\n",
       "      <td>79.4</td>\n",
       "      <td>79.0</td>\n",
       "      <td>66.8</td>\n",
       "      <td>82.4</td>\n",
       "      <td>70.666667</td>\n",
       "      <td>73.333333</td>\n",
       "      <td>70.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2015</td>\n",
       "      <td>76</td>\n",
       "      <td>63</td>\n",
       "      <td>67.565217</td>\n",
       "      <td>78</td>\n",
       "      <td>63</td>\n",
       "      <td>71.695652</td>\n",
       "      <td>180.652174</td>\n",
       "      <td>75.739130</td>\n",
       "      <td>...</td>\n",
       "      <td>86.666667</td>\n",
       "      <td>73.6</td>\n",
       "      <td>77.8</td>\n",
       "      <td>79.9</td>\n",
       "      <td>78.8</td>\n",
       "      <td>66.0</td>\n",
       "      <td>84.4</td>\n",
       "      <td>72.333333</td>\n",
       "      <td>73.333333</td>\n",
       "      <td>71.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2016</td>\n",
       "      <td>77</td>\n",
       "      <td>64</td>\n",
       "      <td>70.304348</td>\n",
       "      <td>82</td>\n",
       "      <td>66</td>\n",
       "      <td>72.434783</td>\n",
       "      <td>179.739130</td>\n",
       "      <td>75.391304</td>\n",
       "      <td>...</td>\n",
       "      <td>84.666667</td>\n",
       "      <td>74.8</td>\n",
       "      <td>79.4</td>\n",
       "      <td>81.8</td>\n",
       "      <td>77.5</td>\n",
       "      <td>69.8</td>\n",
       "      <td>85.6</td>\n",
       "      <td>73.333333</td>\n",
       "      <td>75.000000</td>\n",
       "      <td>76.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2017</td>\n",
       "      <td>79</td>\n",
       "      <td>66</td>\n",
       "      <td>71.347826</td>\n",
       "      <td>83</td>\n",
       "      <td>68</td>\n",
       "      <td>74.304348</td>\n",
       "      <td>180.826087</td>\n",
       "      <td>75.043478</td>\n",
       "      <td>...</td>\n",
       "      <td>84.666667</td>\n",
       "      <td>74.8</td>\n",
       "      <td>76.4</td>\n",
       "      <td>83.2</td>\n",
       "      <td>75.6</td>\n",
       "      <td>68.3</td>\n",
       "      <td>83.0</td>\n",
       "      <td>69.666667</td>\n",
       "      <td>77.333333</td>\n",
       "      <td>77.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2018</td>\n",
       "      <td>80</td>\n",
       "      <td>67</td>\n",
       "      <td>71.434783</td>\n",
       "      <td>89</td>\n",
       "      <td>69</td>\n",
       "      <td>73.478261</td>\n",
       "      <td>181.304348</td>\n",
       "      <td>75.521739</td>\n",
       "      <td>...</td>\n",
       "      <td>83.666667</td>\n",
       "      <td>74.0</td>\n",
       "      <td>78.2</td>\n",
       "      <td>82.6</td>\n",
       "      <td>76.4</td>\n",
       "      <td>69.7</td>\n",
       "      <td>84.6</td>\n",
       "      <td>69.333333</td>\n",
       "      <td>75.000000</td>\n",
       "      <td>75.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 33 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  nationality  year  overall_rating_max  overall_rating_min  \\\n",
       "0     Albania  2014                  77                  64   \n",
       "1     Albania  2015                  76                  63   \n",
       "2     Albania  2016                  77                  64   \n",
       "3     Albania  2017                  79                  66   \n",
       "4     Albania  2018                  80                  67   \n",
       "\n",
       "   overall_rating_avg  potential_max  potential_min  potential_avg  \\\n",
       "0           67.608696             80             66      71.434783   \n",
       "1           67.565217             78             63      71.695652   \n",
       "2           70.304348             82             66      72.434783   \n",
       "3           71.347826             83             68      74.304348   \n",
       "4           71.434783             89             69      73.478261   \n",
       "\n",
       "       height     weight       ...         Sprint_Speed  Reactions  \\\n",
       "0  181.521739  76.260870       ...            85.666667       72.2   \n",
       "1  180.652174  75.739130       ...            86.666667       73.6   \n",
       "2  179.739130  75.391304       ...            84.666667       74.8   \n",
       "3  180.826087  75.043478       ...            84.666667       74.8   \n",
       "4  181.304348  75.521739       ...            83.666667       74.0   \n",
       "\n",
       "   Shot_Power  Stamina  Strength  Long_Shots  Aggression  Penalties  \\\n",
       "0        77.2     79.4      79.0        66.8        82.4  70.666667   \n",
       "1        77.8     79.9      78.8        66.0        84.4  72.333333   \n",
       "2        79.4     81.8      77.5        69.8        85.6  73.333333   \n",
       "3        76.4     83.2      75.6        68.3        83.0  69.666667   \n",
       "4        78.2     82.6      76.4        69.7        84.6  69.333333   \n",
       "\n",
       "     Marking  Standing_Tackle  \n",
       "0  73.333333             70.2  \n",
       "1  73.333333             71.8  \n",
       "2  75.000000             76.0  \n",
       "3  77.333333             77.8  \n",
       "4  75.000000             75.6  \n",
       "\n",
       "[5 rows x 33 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_stats = pd.read_csv('data/generated/team_level_player_data.csv')\n",
    "player_stats = player_stats.drop(['Unnamed: 0'], axis=1)\n",
    "player_stats.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>away_goal_mean</th>\n",
       "      <th>away_goals_with_home</th>\n",
       "      <th>away_team</th>\n",
       "      <th>date</th>\n",
       "      <th>goal_diff_with_away</th>\n",
       "      <th>home_goal_mean</th>\n",
       "      <th>home_goals_with_away</th>\n",
       "      <th>home_team</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>England</td>\n",
       "      <td>1872-11-30</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Scotland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>1873-03-08</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>England</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>England</td>\n",
       "      <td>1874-03-07</td>\n",
       "      <td>-2</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>Scotland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.333333</td>\n",
       "      <td>1.333333</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>1875-03-06</td>\n",
       "      <td>1</td>\n",
       "      <td>1.666667</td>\n",
       "      <td>1.666667</td>\n",
       "      <td>England</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.750000</td>\n",
       "      <td>1.750000</td>\n",
       "      <td>England</td>\n",
       "      <td>1876-03-04</td>\n",
       "      <td>-1</td>\n",
       "      <td>1.500000</td>\n",
       "      <td>1.500000</td>\n",
       "      <td>Scotland</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   away_goal_mean  away_goals_with_home away_team       date  \\\n",
       "0        0.000000              0.000000   England 1872-11-30   \n",
       "1        0.000000              0.000000  Scotland 1873-03-08   \n",
       "2        2.000000              2.000000   England 1874-03-07   \n",
       "3        1.333333              1.333333  Scotland 1875-03-06   \n",
       "4        1.750000              1.750000   England 1876-03-04   \n",
       "\n",
       "   goal_diff_with_away  home_goal_mean  home_goals_with_away home_team  \n",
       "0                    0        0.000000              0.000000  Scotland  \n",
       "1                    0        0.000000              0.000000   England  \n",
       "2                   -2        1.000000              1.000000  Scotland  \n",
       "3                    1        1.666667              1.666667   England  \n",
       "4                   -1        1.500000              1.500000  Scotland  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goal_history = pd.read_csv('data/generated/goal_history_data.csv')\n",
    "goal_history['date'] =  pd.to_datetime(goal_history['date'], format='%Y-%m-%d')\n",
    "goal_history = goal_history.drop(['Unnamed: 0'], axis=1)\n",
    "goal_history.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>home_team</th>\n",
       "      <th>away_team</th>\n",
       "      <th>home_score</th>\n",
       "      <th>away_score</th>\n",
       "      <th>tournament</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>neutral</th>\n",
       "      <th>year</th>\n",
       "      <th>home_elo</th>\n",
       "      <th>away_elo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1872-11-30</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>England</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Glasgow</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>False</td>\n",
       "      <td>1872</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>1500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1872-11-30</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>England</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Glasgow</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>False</td>\n",
       "      <td>1872</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>1500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1872-11-30</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>England</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Glasgow</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>False</td>\n",
       "      <td>1872</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>1500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1872-11-30</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>England</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Glasgow</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>False</td>\n",
       "      <td>1872</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>1500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1873-03-08</td>\n",
       "      <td>England</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>London</td>\n",
       "      <td>England</td>\n",
       "      <td>False</td>\n",
       "      <td>1873</td>\n",
       "      <td>1522.5</td>\n",
       "      <td>1477.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date home_team away_team  home_score  away_score tournament     city  \\\n",
       "0 1872-11-30  Scotland   England           0           0   Friendly  Glasgow   \n",
       "1 1872-11-30  Scotland   England           0           0   Friendly  Glasgow   \n",
       "2 1872-11-30  Scotland   England           0           0   Friendly  Glasgow   \n",
       "3 1872-11-30  Scotland   England           0           0   Friendly  Glasgow   \n",
       "4 1873-03-08   England  Scotland           4           2   Friendly   London   \n",
       "\n",
       "    country  neutral  year  home_elo  away_elo  \n",
       "0  Scotland    False  1872    1500.0    1500.0  \n",
       "1  Scotland    False  1872    1500.0    1500.0  \n",
       "2  Scotland    False  1872    1500.0    1500.0  \n",
       "3  Scotland    False  1872    1500.0    1500.0  \n",
       "4   England    False  1873    1522.5    1477.5  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset = pd.merge(match_results, elo_ranking,  how='left', left_on=['date','home_team'], right_on = ['date','team'])\n",
    "dataset = dataset.drop(['team'], axis=1)\n",
    "dataset.rename(columns = {\"elo\": \"home_elo\"}, inplace=True)\n",
    "\n",
    "dataset = pd.merge(dataset, elo_ranking,  how='left', left_on=['date','away_team'], right_on = ['date','team'])\n",
    "dataset = dataset.drop(['team'], axis=1)\n",
    "dataset.rename(columns = {\"elo\": \"away_elo\"}, inplace=True)\n",
    "dataset.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>home_team</th>\n",
       "      <th>away_team</th>\n",
       "      <th>home_score</th>\n",
       "      <th>away_score</th>\n",
       "      <th>tournament</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>neutral</th>\n",
       "      <th>year</th>\n",
       "      <th>...</th>\n",
       "      <th>away_Sprint_Speed</th>\n",
       "      <th>away_Reactions</th>\n",
       "      <th>away_Shot_Power</th>\n",
       "      <th>away_Stamina</th>\n",
       "      <th>away_Strength</th>\n",
       "      <th>away_Long_Shots</th>\n",
       "      <th>away_Aggression</th>\n",
       "      <th>away_Penalties</th>\n",
       "      <th>away_Marking</th>\n",
       "      <th>away_Standing_Tackle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>39238</th>\n",
       "      <td>2018-06-04</td>\n",
       "      <td>Italy</td>\n",
       "      <td>Netherlands</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Turin</td>\n",
       "      <td>Italy</td>\n",
       "      <td>False</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>89.333333</td>\n",
       "      <td>84.4</td>\n",
       "      <td>84.4</td>\n",
       "      <td>84.500000</td>\n",
       "      <td>83.100000</td>\n",
       "      <td>79.100000</td>\n",
       "      <td>80.8</td>\n",
       "      <td>84.333333</td>\n",
       "      <td>83.000000</td>\n",
       "      <td>83.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39239</th>\n",
       "      <td>2018-06-04</td>\n",
       "      <td>Serbia</td>\n",
       "      <td>Chile</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Graz</td>\n",
       "      <td>Austria</td>\n",
       "      <td>True</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>81.333333</td>\n",
       "      <td>83.0</td>\n",
       "      <td>82.8</td>\n",
       "      <td>81.500000</td>\n",
       "      <td>77.800000</td>\n",
       "      <td>79.500000</td>\n",
       "      <td>85.0</td>\n",
       "      <td>82.000000</td>\n",
       "      <td>76.333333</td>\n",
       "      <td>80.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39240</th>\n",
       "      <td>2018-06-04</td>\n",
       "      <td>Slovakia</td>\n",
       "      <td>Morocco</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Geneva</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>True</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>80.666667</td>\n",
       "      <td>79.6</td>\n",
       "      <td>81.2</td>\n",
       "      <td>78.000000</td>\n",
       "      <td>77.000000</td>\n",
       "      <td>75.700000</td>\n",
       "      <td>80.8</td>\n",
       "      <td>79.000000</td>\n",
       "      <td>81.333333</td>\n",
       "      <td>78.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39241</th>\n",
       "      <td>2018-06-04</td>\n",
       "      <td>Armenia</td>\n",
       "      <td>Moldova</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Kematen</td>\n",
       "      <td>Austria</td>\n",
       "      <td>True</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>72.000000</td>\n",
       "      <td>68.4</td>\n",
       "      <td>67.4</td>\n",
       "      <td>61.087500</td>\n",
       "      <td>65.362500</td>\n",
       "      <td>42.975000</td>\n",
       "      <td>72.2</td>\n",
       "      <td>61.333333</td>\n",
       "      <td>73.333333</td>\n",
       "      <td>73.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39242</th>\n",
       "      <td>2018-06-04</td>\n",
       "      <td>India</td>\n",
       "      <td>Kenya</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Mumbai</td>\n",
       "      <td>India</td>\n",
       "      <td>False</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>81.000000</td>\n",
       "      <td>64.2</td>\n",
       "      <td>69.4</td>\n",
       "      <td>60.171429</td>\n",
       "      <td>67.757143</td>\n",
       "      <td>46.671429</td>\n",
       "      <td>73.2</td>\n",
       "      <td>57.666667</td>\n",
       "      <td>68.000000</td>\n",
       "      <td>63.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 74 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            date home_team    away_team  home_score  away_score tournament  \\\n",
       "39238 2018-06-04     Italy  Netherlands           1           1   Friendly   \n",
       "39239 2018-06-04    Serbia        Chile           0           1   Friendly   \n",
       "39240 2018-06-04  Slovakia      Morocco           1           2   Friendly   \n",
       "39241 2018-06-04   Armenia      Moldova           0           0   Friendly   \n",
       "39242 2018-06-04     India        Kenya           3           0   Friendly   \n",
       "\n",
       "          city      country  neutral  year          ...           \\\n",
       "39238    Turin        Italy    False  2018          ...            \n",
       "39239     Graz      Austria     True  2018          ...            \n",
       "39240   Geneva  Switzerland     True  2018          ...            \n",
       "39241  Kematen      Austria     True  2018          ...            \n",
       "39242   Mumbai        India    False  2018          ...            \n",
       "\n",
       "       away_Sprint_Speed  away_Reactions  away_Shot_Power  away_Stamina  \\\n",
       "39238          89.333333            84.4             84.4     84.500000   \n",
       "39239          81.333333            83.0             82.8     81.500000   \n",
       "39240          80.666667            79.6             81.2     78.000000   \n",
       "39241          72.000000            68.4             67.4     61.087500   \n",
       "39242          81.000000            64.2             69.4     60.171429   \n",
       "\n",
       "       away_Strength  away_Long_Shots  away_Aggression  away_Penalties  \\\n",
       "39238      83.100000        79.100000             80.8       84.333333   \n",
       "39239      77.800000        79.500000             85.0       82.000000   \n",
       "39240      77.000000        75.700000             80.8       79.000000   \n",
       "39241      65.362500        42.975000             72.2       61.333333   \n",
       "39242      67.757143        46.671429             73.2       57.666667   \n",
       "\n",
       "       away_Marking  away_Standing_Tackle  \n",
       "39238     83.000000                  83.4  \n",
       "39239     76.333333                  80.8  \n",
       "39240     81.333333                  78.4  \n",
       "39241     73.333333                  73.6  \n",
       "39242     68.000000                  63.2  \n",
       "\n",
       "[5 rows x 74 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset_w = dataset.shape[1]\n",
    "dataset = pd.merge(dataset, player_stats,  how='left', left_on=['year','home_team'], right_on = ['year','nationality'])\n",
    "new_names = [(i, 'home_' + i) for i in dataset.iloc[:, dataset_w:].columns.values]\n",
    "dataset.rename(columns = dict(new_names), inplace=True)\n",
    "dataset = dataset.drop(['home_nationality'], axis=1)\n",
    "\n",
    "dataset_w = dataset.shape[1]\n",
    "dataset = pd.merge(dataset, player_stats,  how='left', left_on=['year','away_team'], right_on = ['year','nationality'])\n",
    "new_names = [(i, 'away_' + i) for i in dataset.iloc[:, dataset_w:].columns.values]\n",
    "dataset.rename(columns = dict(new_names), inplace=True)\n",
    "dataset = dataset.drop(['away_nationality'], axis=1)\n",
    "\n",
    "dataset.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>home_team</th>\n",
       "      <th>away_team</th>\n",
       "      <th>home_score</th>\n",
       "      <th>away_score</th>\n",
       "      <th>tournament</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>neutral</th>\n",
       "      <th>year</th>\n",
       "      <th>...</th>\n",
       "      <th>away_Long_Shots</th>\n",
       "      <th>away_Aggression</th>\n",
       "      <th>away_Penalties</th>\n",
       "      <th>away_Marking</th>\n",
       "      <th>away_Standing_Tackle</th>\n",
       "      <th>away_goal_mean</th>\n",
       "      <th>away_goals_with_home</th>\n",
       "      <th>goal_diff_with_away</th>\n",
       "      <th>home_goal_mean</th>\n",
       "      <th>home_goals_with_away</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>39350</th>\n",
       "      <td>2018-06-04</td>\n",
       "      <td>Italy</td>\n",
       "      <td>Netherlands</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Turin</td>\n",
       "      <td>Italy</td>\n",
       "      <td>False</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>79.100000</td>\n",
       "      <td>80.8</td>\n",
       "      <td>84.333333</td>\n",
       "      <td>83.000000</td>\n",
       "      <td>83.4</td>\n",
       "      <td>1.914894</td>\n",
       "      <td>1.00</td>\n",
       "      <td>7</td>\n",
       "      <td>1.530612</td>\n",
       "      <td>1.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39351</th>\n",
       "      <td>2018-06-04</td>\n",
       "      <td>Serbia</td>\n",
       "      <td>Chile</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Graz</td>\n",
       "      <td>Austria</td>\n",
       "      <td>True</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>79.500000</td>\n",
       "      <td>85.0</td>\n",
       "      <td>82.000000</td>\n",
       "      <td>76.333333</td>\n",
       "      <td>80.8</td>\n",
       "      <td>1.574074</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2</td>\n",
       "      <td>1.361111</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39352</th>\n",
       "      <td>2018-06-04</td>\n",
       "      <td>Slovakia</td>\n",
       "      <td>Morocco</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Geneva</td>\n",
       "      <td>Switzerland</td>\n",
       "      <td>True</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>75.700000</td>\n",
       "      <td>80.8</td>\n",
       "      <td>79.000000</td>\n",
       "      <td>81.333333</td>\n",
       "      <td>78.4</td>\n",
       "      <td>1.659574</td>\n",
       "      <td>2.00</td>\n",
       "      <td>-1</td>\n",
       "      <td>1.575000</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39353</th>\n",
       "      <td>2018-06-04</td>\n",
       "      <td>Armenia</td>\n",
       "      <td>Moldova</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Kematen</td>\n",
       "      <td>Austria</td>\n",
       "      <td>True</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>42.975000</td>\n",
       "      <td>72.2</td>\n",
       "      <td>61.333333</td>\n",
       "      <td>73.333333</td>\n",
       "      <td>73.6</td>\n",
       "      <td>0.486486</td>\n",
       "      <td>1.75</td>\n",
       "      <td>-2</td>\n",
       "      <td>1.250000</td>\n",
       "      <td>1.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39354</th>\n",
       "      <td>2018-06-04</td>\n",
       "      <td>India</td>\n",
       "      <td>Kenya</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Mumbai</td>\n",
       "      <td>India</td>\n",
       "      <td>False</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>46.671429</td>\n",
       "      <td>73.2</td>\n",
       "      <td>57.666667</td>\n",
       "      <td>68.000000</td>\n",
       "      <td>63.2</td>\n",
       "      <td>0.911111</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>1.870968</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 79 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            date home_team    away_team  home_score  away_score tournament  \\\n",
       "39350 2018-06-04     Italy  Netherlands           1           1   Friendly   \n",
       "39351 2018-06-04    Serbia        Chile           0           1   Friendly   \n",
       "39352 2018-06-04  Slovakia      Morocco           1           2   Friendly   \n",
       "39353 2018-06-04   Armenia      Moldova           0           0   Friendly   \n",
       "39354 2018-06-04     India        Kenya           3           0   Friendly   \n",
       "\n",
       "          city      country  neutral  year          ...           \\\n",
       "39350    Turin        Italy    False  2018          ...            \n",
       "39351     Graz      Austria     True  2018          ...            \n",
       "39352   Geneva  Switzerland     True  2018          ...            \n",
       "39353  Kematen      Austria     True  2018          ...            \n",
       "39354   Mumbai        India    False  2018          ...            \n",
       "\n",
       "       away_Long_Shots  away_Aggression  away_Penalties  away_Marking  \\\n",
       "39350        79.100000             80.8       84.333333     83.000000   \n",
       "39351        79.500000             85.0       82.000000     76.333333   \n",
       "39352        75.700000             80.8       79.000000     81.333333   \n",
       "39353        42.975000             72.2       61.333333     73.333333   \n",
       "39354        46.671429             73.2       57.666667     68.000000   \n",
       "\n",
       "       away_Standing_Tackle  away_goal_mean  away_goals_with_home  \\\n",
       "39350                  83.4        1.914894                  1.00   \n",
       "39351                  80.8        1.574074                  1.00   \n",
       "39352                  78.4        1.659574                  2.00   \n",
       "39353                  73.6        0.486486                  1.75   \n",
       "39354                  63.2        0.911111                  0.00   \n",
       "\n",
       "       goal_diff_with_away  home_goal_mean  home_goals_with_away  \n",
       "39350                    7        1.530612                  1.35  \n",
       "39351                    2        1.361111                  3.00  \n",
       "39352                   -1        1.575000                  1.00  \n",
       "39353                   -2        1.250000                  1.25  \n",
       "39354                    0        1.870968                  0.00  \n",
       "\n",
       "[5 rows x 79 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset = pd.merge(dataset, goal_history,  how='left', left_on=['date','home_team'], right_on = ['date','home_team'])\n",
    "dataset.rename(columns = {\"home_team_x\": \"home_team\", \"away_team_x\": \"away_team\"}, inplace=True)\n",
    "dataset = dataset.drop(['away_team_y'], axis=1)\n",
    "dataset.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset.to_csv(\"data/generated/master_data.csv\", index=False)"
   ]
  }
 ],
 "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}