# Football¶

In this exaample, we use the 'football' dataset to predict the outcomes of games between various teams. You can download the Jupyter Notebook of the study here.

• date: Date of the game
• home_team: Home Team
• home_score: Home Team number of goals
• away_team: Away Team
• away_score: Away Team number of goals
• tournament: Game Type (World Cup, Friendly...)
• city: City where the game took place
• country: Country where the game took place
• neutral: If the event took place to a neutral location

We will follow the data science cycle (Data Exploration - Data Preparation - Data Modeling - Model Evaluation - Model Deployment) to solve this problem.

## Initialization¶

This example uses the following version of VerticaPy:

In [1]:
```import verticapy as vp
vp.__version__
```
Out[1]:
`'0.9.0'`

Connect to Vertica. This example uses an existing connection called "VerticaDSN." For details on how to create a connection, use see the connection tutorial.

In [2]:
```vp.connect("VerticaDSN")
```

Let's create a Virtual DataFrame of the dataset. The dataset is available here.

In [9]:
```football = vp.read_csv("data/football.csv")
```
Out[9]:
 📅dateDate Abchome_teamVarchar(64) Abcaway_teamVarchar(64) 123home_scoreInt 123away_scoreInt AbctournamentVarchar(84) AbccityVarchar(56) AbccountryVarchar(64) 010neutralBoolean 1 1872-11-30 Scotland England 0 0 Friendly Glasgow Scotland ❌ 2 1873-03-08 England Scotland 4 2 Friendly London England ❌ 3 1874-03-07 Scotland England 2 1 Friendly Glasgow Scotland ❌ 4 1875-03-06 England Scotland 2 2 Friendly London England ❌ 5 1876-03-04 Scotland England 3 0 Friendly Glasgow Scotland ❌
Rows: 1-5 | Columns: 9

## Data Exploration¶

Let's explore the data by displaying descriptive statistics of all the columns.

In [4]:
```football["date"].describe()
```
Out[4]:
 value name "date" dtype date count 41586 min 1872-11-30 max 2020-02-01
Rows: 1-5 | Columns: 2

The dataset includes a total of 41,586 games, which take place between 1872 and 2020. Let's look at our game types and teams.

In [5]:
```football["tournament"].describe()
```
Out[5]:
 value "tournament" varchar(84) 112.0 41586.0 17029 10630 7236 2582 1672 900 813
Rows: 1-11 | Columns: 2

Different types of tournaments took place (FIFA World Cup, UEFA Euro, etc.) aand most of the games in our data are friendlies or qualifiers for international tournaments.

In [6]:
```display(football.describe())
football.describe(method = "categorical")
```
 count mean std min approx_25% approx_50% approx_75% max "home_score" 41586 1.74575578319628 1.75378034047699 0.0 1.0 1.0 2.0 31.0 "away_score" 41586 1.18758716875872 1.40532346833589 0.0 0.0 1.0 2.0 21.0 "neutral" 41586 0.247246669552252 0.431416538275649 0.0 0.0 0.0 0.0 1.0
Rows: 1-3 | Columns: 9
Out[6]:
 dtype count top top_percent "date" date 41586 2012-02-29 0.159 "home_team" varchar(64) 41586 Brazil 1.366 "away_team" varchar(64) 41586 Uruguay 1.301 "home_score" int 41586 1 29.57 "away_score" int 41586 0 37.135 "tournament" varchar(84) 41586 Friendly 40.949 "city" varchar(56) 41586 Kuala Lumpur 1.416 "country" varchar(64) 41586 United States 2.787 "neutral" boolean 41586 ❌ 75.275
Rows: 1-9 | Columns: 5

The dataset includes 308 national teams. For most of the games, the home team scores better than the away team. Since some games take place in a neutral location, we can ensure this hypothesis using the variable 'neutral.' Notice also that the number of goals per match is pretty low (median of 1 for both away and home teams).

Goal

Our goal for the study will be to predict the outcomes of games after 2015.

Before doing the study, we can notice that some teams names have changed over time. We need to change the old names by the new names otherwise it will add too much bias in the data.

In [10]:
```for team in ["home_team", "away_team"]:
football[team].decode('German DR', 'Germany',
'Czechoslovakia', 'Czech Republic',
'Yugoslavia', 'Serbia',
'Yemen DPR', 'Yemen',
football[team])
```

Let's just consider teams that have played more than five home and away games.

In [11]:
```football["cnt_games_1"] = "COUNT(*) OVER (PARTITION BY home_team)"
football["cnt_games_2"] = "COUNT(*) OVER (PARTITION BY away_team)"
football.filter((football["cnt_games_2"] > 5) & (football["cnt_games_1"] > 5))
vp.drop("football_clean", relation_type = "table")
football.to_db(name = "football_clean",
usecols = ["date",
"home_score",
"home_team",
"tournament",
"away_team",
"away_score",
"neutral",
"country",
"city"],
relation_type = "table",
inplace = True)
```
```177 elements were filtered
```
Out[11]:
 📅dateDate 123home_scoreInt Abchome_teamVarchar(64) AbctournamentVarchar(84) Abcaway_teamVarchar(64) 123away_scoreInt 010neutralBoolean AbccountryVarchar(64) AbccityVarchar(56) 1 1872-11-30 0 Scotland Friendly England 0 ❌ Scotland Glasgow 2 1873-03-08 4 England Friendly Scotland 2 ❌ England London 3 1874-03-07 2 Scotland Friendly England 1 ❌ Scotland Glasgow 4 1875-03-06 2 England Friendly Scotland 2 ❌ England London 5 1876-03-04 3 Scotland Friendly England 0 ❌ Scotland Glasgow 6 1876-03-25 4 Scotland Friendly Wales 0 ❌ Scotland Glasgow 7 1877-03-03 1 England Friendly Scotland 3 ❌ England London 8 1877-03-05 0 Wales Friendly Scotland 2 ❌ Wales Wrexham 9 1878-03-02 7 Scotland Friendly England 2 ❌ Scotland Glasgow 10 1878-03-23 9 Scotland Friendly Wales 0 ❌ Scotland Glasgow 11 1879-01-18 2 England Friendly Wales 1 ❌ England London 12 1879-04-05 5 England Friendly Scotland 4 ❌ England London 13 1879-04-07 0 Wales Friendly Scotland 3 ❌ Wales Wrexham 14 1880-03-13 5 Scotland Friendly England 4 ❌ Scotland Glasgow 15 1880-03-15 2 Wales Friendly England 3 ❌ Wales Wrexham 16 1880-03-27 5 Scotland Friendly Wales 1 ❌ Scotland Glasgow 17 1881-02-26 0 England Friendly Wales 1 ❌ England Blackburn 18 1881-03-12 1 England Friendly Scotland 6 ❌ England London 19 1881-03-14 1 Wales Friendly Scotland 5 ❌ Wales Wrexham 20 1882-02-18 0 Northern Ireland Friendly England 13 ❌ Republic of Ireland Belfast 21 1882-02-25 7 Wales Friendly Northern Ireland 1 ❌ Wales Wrexham 22 1882-03-11 5 Scotland Friendly England 1 ❌ Scotland Glasgow 23 1882-03-13 5 Wales Friendly England 3 ❌ Wales Wrexham 24 1882-03-25 5 Scotland Friendly Wales 0 ❌ Scotland Glasgow 25 1883-02-03 5 England Friendly Wales 0 ❌ England London 26 1883-02-24 7 England Friendly Northern Ireland 0 ❌ England Liverpool 27 1883-03-10 2 England Friendly Scotland 3 ❌ England Sheffield 28 1883-03-12 0 Wales Friendly Scotland 3 ❌ Wales Wrexham 29 1883-03-17 1 Northern Ireland Friendly Wales 1 ❌ Republic of Ireland Belfast 30 1884-01-26 0 Northern Ireland British Championship Scotland 5 ❌ Republic of Ireland Belfast 31 1884-02-09 6 Wales British Championship Northern Ireland 0 ❌ Wales Wrexham 32 1884-02-23 1 Northern Ireland British Championship England 8 ❌ Republic of Ireland Belfast 33 1884-03-15 1 Scotland British Championship England 0 ❌ Scotland Glasgow 34 1884-03-17 0 Wales British Championship England 4 ❌ Wales Wrexham 35 1884-03-29 4 Scotland British Championship Wales 1 ❌ Scotland Glasgow 36 1885-02-28 4 England British Championship Northern Ireland 0 ❌ England Manchester 37 1885-03-14 1 England British Championship Wales 1 ❌ England Blackburn 38 1885-03-14 8 Scotland British Championship Northern Ireland 2 ❌ Scotland Glasgow 39 1885-03-21 1 England British Championship Scotland 1 ❌ England London 40 1885-03-23 1 Wales British Championship Scotland 8 ❌ Wales Wrexham 41 1885-04-11 2 Northern Ireland British Championship Wales 8 ❌ Republic of Ireland Belfast 42 1885-11-28 0 United States Friendly Canada 1 ❌ United States Newark 43 1886-02-27 5 Wales British Championship Northern Ireland 0 ❌ Wales Wrexham 44 1886-03-13 1 Northern Ireland British Championship England 6 ❌ Republic of Ireland Belfast 45 1886-03-20 2 Northern Ireland British Championship Scotland 7 ❌ Republic of Ireland Belfast 46 1886-03-27 1 Scotland British Championship England 1 ❌ Scotland Glasgow 47 1886-03-29 1 Wales British Championship England 3 ❌ Wales Wrexham 48 1886-04-10 4 Scotland British Championship Wales 1 ❌ Scotland Glasgow 49 1886-11-25 3 United States Friendly Canada 2 ❌ United States Newark 50 1887-02-05 7 England British Championship Northern Ireland 0 ❌ England Sheffield 51 1887-02-19 4 Scotland British Championship Northern Ireland 1 ❌ Scotland Glasgow 52 1887-02-26 4 England British Championship Wales 0 ❌ England London 53 1887-03-12 4 Northern Ireland British Championship Wales 1 ❌ Republic of Ireland Belfast 54 1887-03-19 2 England British Championship Scotland 3 ❌ England Blackburn 55 1887-03-21 0 Wales British Championship Scotland 2 ❌ Wales Wrexham 56 1888-02-04 5 England British Championship Wales 1 ❌ England Crewe 57 1888-03-03 11 Wales British Championship Northern Ireland 0 ❌ Wales Wrexham 58 1888-03-10 5 Scotland British Championship Wales 1 ❌ Scotland Edinburgh 59 1888-03-17 0 Scotland British Championship England 5 ❌ Scotland Glasgow 60 1888-03-24 2 Northern Ireland British Championship Scotland 10 ❌ Republic of Ireland Belfast 61 1888-04-07 1 Northern Ireland British Championship England 5 ❌ Republic of Ireland Belfast 62 1888-09-19 4 Scotland Friendly Canada 0 ❌ Scotland Glasgow 63 1889-02-23 4 England British Championship Wales 1 ❌ England Stoke-on-Trent 64 1889-03-02 6 England British Championship Northern Ireland 1 ❌ England Liverpool 65 1889-03-09 7 Scotland British Championship Northern Ireland 0 ❌ Scotland Glasgow 66 1889-04-13 2 England British Championship Scotland 3 ❌ England London 67 1889-04-15 0 Wales British Championship Scotland 0 ❌ Wales Wrexham 68 1889-04-27 1 Northern Ireland British Championship Wales 3 ❌ Republic of Ireland Belfast 69 1890-02-08 5 Wales British Championship Northern Ireland 2 ✅ England Shrewsbury 70 1890-03-15 1 Northern Ireland British Championship England 9 ❌ Republic of Ireland Belfast 71 1890-03-15 1 Wales British Championship England 3 ❌ Wales Wrexham 72 1890-03-22 5 Scotland British Championship Wales 0 ❌ Scotland Paisley 73 1890-03-29 1 Northern Ireland British Championship Scotland 4 ❌ Republic of Ireland Belfast 74 1890-04-05 1 Scotland British Championship England 1 ❌ Scotland Glasgow 75 1891-02-07 7 Northern Ireland British Championship Wales 2 ❌ Republic of Ireland Belfast 76 1891-03-07 4 England British Championship Wales 1 ❌ England Sunderland 77 1891-03-07 6 England British Championship Northern Ireland 1 ❌ England Wolverhampton 78 1891-03-21 3 Wales British Championship Scotland 4 ❌ Wales Wrexham 79 1891-03-28 2 Scotland British Championship Northern Ireland 1 ❌ Scotland Glasgow 80 1891-04-06 2 England British Championship Scotland 1 ❌ England Blackburn 81 1892-02-27 1 Wales British Championship Northern Ireland 1 ❌ Wales Bangor 82 1892-03-05 0 Northern Ireland British Championship England 2 ❌ Republic of Ireland Belfast 83 1892-03-05 0 Wales British Championship England 2 ❌ Wales Wrexham 84 1892-03-19 2 Northern Ireland British Championship Scotland 3 ❌ Republic of Ireland Belfast 85 1892-03-26 6 Scotland British Championship Wales 1 ❌ Scotland Edinburgh 86 1892-04-02 1 Scotland British Championship England 4 ❌ Scotland Glasgow 87 1893-02-25 6 England British Championship Northern Ireland 1 ❌ England Birmingham 88 1893-03-13 6 England British Championship Wales 0 ❌ England Stoke-on-Trent 89 1893-03-18 0 Wales British Championship Scotland 8 ❌ Wales Wrexham 90 1893-03-25 6 Scotland British Championship Northern Ireland 1 ❌ Scotland Glasgow 91 1893-04-01 5 England British Championship Scotland 2 ❌ England Richmond 92 1894-02-24 4 Wales British Championship Northern Ireland 1 ❌ Wales Swansea 93 1894-03-03 2 Northern Ireland British Championship England 2 ❌ Republic of Ireland Belfast 94 1894-03-12 1 Wales British Championship England 5 ❌ Wales Wrexham 95 1894-03-24 5 Scotland British Championship Wales 2 ❌ Scotland Kilmarnock 96 1894-03-31 1 Northern Ireland British Championship Scotland 2 ❌ Republic of Ireland Belfast 97 1894-04-07 2 Scotland British Championship England 2 ❌ Scotland Glasgow 98 1895-03-09 9 England British Championship Northern Ireland 0 ❌ England Derby 99 1895-03-16 2 Northern Ireland British Championship Wales 2 ❌ Republic of Ireland Belfast 100 1895-03-18 1 England British Championship Wales 1 ❌ England London
Rows: 1-100 | Columns: 9

A lot of things could influence the outcome of a game. Since we only have access to the score, teams, and type of game, we can't consider external factors like, weather or temperature, which would otherwise help our prediction.

To create a good model using this dataset, we could compute each team's key performance indicator (KPI), ranking (clusters computed using the number of games in important tournaments like the World Cup, the percentage of victory...), shape (moving windows using the last games information), and other factors.

Here's our plan:

• Identify cup winners
• Rank the teams with clustering
• Compute teams' KPIs
• Create a machine learning model

## Data Preparation for Clustering¶

To create clusters, we need to find which teams are the winners of main tournaments (mainly the World Cups and Continental Cups). Since all tournaments took place the same year, we could partition by tournament and year to identify the last game of the tournament.

We'll ignore ties for our analysis since there's no way to determine a winner.

### Cup Winner¶

Let's start by creating the feature 'winner' to indicate the winner of a game.

In [13]:
```import verticapy.stats as st
football.filter(st.year(football["date"]) <= 2015)
football.case_when("winner",
football["home_score"] > football["away_score"], football["home_team"],
football["home_score"] < football["away_score"], football["away_team"],
None)
```
```3884 elements were filtered
```
Out[13]:
 📅dateDate 123home_scoreInt Abchome_teamVarchar(64) AbctournamentVarchar(84) Abcaway_teamVarchar(64) 123away_scoreInt 010neutralBoolean AbccountryVarchar(64) AbccityVarchar(56) AbcwinnerVarchar(64) 1 1872-11-30 0 Scotland Friendly England 0 ❌ Scotland Glasgow [null] 2 1873-03-08 4 England Friendly Scotland 2 ❌ England London England 3 1874-03-07 2 Scotland Friendly England 1 ❌ Scotland Glasgow Scotland 4 1875-03-06 2 England Friendly Scotland 2 ❌ England London [null] 5 1876-03-04 3 Scotland Friendly England 0 ❌ Scotland Glasgow Scotland 6 1876-03-25 4 Scotland Friendly Wales 0 ❌ Scotland Glasgow Scotland 7 1877-03-03 1 England Friendly Scotland 3 ❌ England London Scotland 8 1877-03-05 0 Wales Friendly Scotland 2 ❌ Wales Wrexham Scotland 9 1878-03-02 7 Scotland Friendly England 2 ❌ Scotland Glasgow Scotland 10 1878-03-23 9 Scotland Friendly Wales 0 ❌ Scotland Glasgow Scotland 11 1879-01-18 2 England Friendly Wales 1 ❌ England London England 12 1879-04-05 5 England Friendly Scotland 4 ❌ England London England 13 1879-04-07 0 Wales Friendly Scotland 3 ❌ Wales Wrexham Scotland 14 1880-03-13 5 Scotland Friendly England 4 ❌ Scotland Glasgow Scotland 15 1880-03-15 2 Wales Friendly England 3 ❌ Wales Wrexham England 16 1880-03-27 5 Scotland Friendly Wales 1 ❌ Scotland Glasgow Scotland 17 1881-02-26 0 England Friendly Wales 1 ❌ England Blackburn Wales 18 1881-03-12 1 England Friendly Scotland 6 ❌ England London Scotland 19 1881-03-14 1 Wales Friendly Scotland 5 ❌ Wales Wrexham Scotland 20 1882-02-18 0 Northern Ireland Friendly England 13 ❌ Republic of Ireland Belfast England 21 1882-02-25 7 Wales Friendly Northern Ireland 1 ❌ Wales Wrexham Wales 22 1882-03-11 5 Scotland Friendly England 1 ❌ Scotland Glasgow Scotland 23 1882-03-13 5 Wales Friendly England 3 ❌ Wales Wrexham Wales 24 1882-03-25 5 Scotland Friendly Wales 0 ❌ Scotland Glasgow Scotland 25 1883-02-03 5 England Friendly Wales 0 ❌ England London England 26 1883-02-24 7 England Friendly Northern Ireland 0 ❌ England Liverpool England 27 1883-03-10 2 England Friendly Scotland 3 ❌ England Sheffield Scotland 28 1883-03-12 0 Wales Friendly Scotland 3 ❌ Wales Wrexham Scotland 29 1883-03-17 1 Northern Ireland Friendly Wales 1 ❌ Republic of Ireland Belfast [null] 30 1884-01-26 0 Northern Ireland British Championship Scotland 5 ❌ Republic of Ireland Belfast Scotland 31 1884-02-09 6 Wales British Championship Northern Ireland 0 ❌ Wales Wrexham Wales 32 1884-02-23 1 Northern Ireland British Championship England 8 ❌ Republic of Ireland Belfast England 33 1884-03-15 1 Scotland British Championship England 0 ❌ Scotland Glasgow Scotland 34 1884-03-17 0 Wales British Championship England 4 ❌ Wales Wrexham England 35 1884-03-29 4 Scotland British Championship Wales 1 ❌ Scotland Glasgow Scotland 36 1885-02-28 4 England British Championship Northern Ireland 0 ❌ England Manchester England 37 1885-03-14 1 England British Championship Wales 1 ❌ England Blackburn [null] 38 1885-03-14 8 Scotland British Championship Northern Ireland 2 ❌ Scotland Glasgow Scotland 39 1885-03-21 1 England British Championship Scotland 1 ❌ England London [null] 40 1885-03-23 1 Wales British Championship Scotland 8 ❌ Wales Wrexham Scotland 41 1885-04-11 2 Northern Ireland British Championship Wales 8 ❌ Republic of Ireland Belfast Wales 42 1885-11-28 0 United States Friendly Canada 1 ❌ United States Newark Canada 43 1886-02-27 5 Wales British Championship Northern Ireland 0 ❌ Wales Wrexham Wales 44 1886-03-13 1 Northern Ireland British Championship England 6 ❌ Republic of Ireland Belfast England 45 1886-03-20 2 Northern Ireland British Championship Scotland 7 ❌ Republic of Ireland Belfast Scotland 46 1886-03-27 1 Scotland British Championship England 1 ❌ Scotland Glasgow [null] 47 1886-03-29 1 Wales British Championship England 3 ❌ Wales Wrexham England 48 1886-04-10 4 Scotland British Championship Wales 1 ❌ Scotland Glasgow Scotland 49 1886-11-25 3 United States Friendly Canada 2 ❌ United States Newark United States 50 1887-02-05 7 England British Championship Northern Ireland 0 ❌ England Sheffield England 51 1887-02-19 4 Scotland British Championship Northern Ireland 1 ❌ Scotland Glasgow Scotland 52 1887-02-26 4 England British Championship Wales 0 ❌ England London England 53 1887-03-12 4 Northern Ireland British Championship Wales 1 ❌ Republic of Ireland Belfast Northern Ireland 54 1887-03-19 2 England British Championship Scotland 3 ❌ England Blackburn Scotland 55 1887-03-21 0 Wales British Championship Scotland 2 ❌ Wales Wrexham Scotland 56 1888-02-04 5 England British Championship Wales 1 ❌ England Crewe England 57 1888-03-03 11 Wales British Championship Northern Ireland 0 ❌ Wales Wrexham Wales 58 1888-03-10 5 Scotland British Championship Wales 1 ❌ Scotland Edinburgh Scotland 59 1888-03-17 0 Scotland British Championship England 5 ❌ Scotland Glasgow England 60 1888-03-24 2 Northern Ireland British Championship Scotland 10 ❌ Republic of Ireland Belfast Scotland 61 1888-04-07 1 Northern Ireland British Championship England 5 ❌ Republic of Ireland Belfast England 62 1888-09-19 4 Scotland Friendly Canada 0 ❌ Scotland Glasgow Scotland 63 1889-02-23 4 England British Championship Wales 1 ❌ England Stoke-on-Trent England 64 1889-03-02 6 England British Championship Northern Ireland 1 ❌ England Liverpool England 65 1889-03-09 7 Scotland British Championship Northern Ireland 0 ❌ Scotland Glasgow Scotland 66 1889-04-13 2 England British Championship Scotland 3 ❌ England London Scotland 67 1889-04-15 0 Wales British Championship Scotland 0 ❌ Wales Wrexham [null] 68 1889-04-27 1 Northern Ireland British Championship Wales 3 ❌ Republic of Ireland Belfast Wales 69 1890-02-08 5 Wales British Championship Northern Ireland 2 ✅ England Shrewsbury Wales 70 1890-03-15 1 Northern Ireland British Championship England 9 ❌ Republic of Ireland Belfast England 71 1890-03-15 1 Wales British Championship England 3 ❌ Wales Wrexham England 72 1890-03-22 5 Scotland British Championship Wales 0 ❌ Scotland Paisley Scotland 73 1890-03-29 1 Northern Ireland British Championship Scotland 4 ❌ Republic of Ireland Belfast Scotland 74 1890-04-05 1 Scotland British Championship England 1 ❌ Scotland Glasgow [null] 75 1891-02-07 7 Northern Ireland British Championship Wales 2 ❌ Republic of Ireland Belfast Northern Ireland 76 1891-03-07 4 England British Championship Wales 1 ❌ England Sunderland England 77 1891-03-07 6 England British Championship Northern Ireland 1 ❌ England Wolverhampton England 78 1891-03-21 3 Wales British Championship Scotland 4 ❌ Wales Wrexham Scotland 79 1891-03-28 2 Scotland British Championship Northern Ireland 1 ❌ Scotland Glasgow Scotland 80 1891-04-06 2 England British Championship Scotland 1 ❌ England Blackburn England 81 1892-02-27 1 Wales British Championship Northern Ireland 1 ❌ Wales Bangor [null] 82 1892-03-05 0 Northern Ireland British Championship England 2 ❌ Republic of Ireland Belfast England 83 1892-03-05 0 Wales British Championship England 2 ❌ Wales Wrexham England 84 1892-03-19 2 Northern Ireland British Championship Scotland 3 ❌ Republic of Ireland Belfast Scotland 85 1892-03-26 6 Scotland British Championship Wales 1 ❌ Scotland Edinburgh Scotland 86 1892-04-02 1 Scotland British Championship England 4 ❌ Scotland Glasgow England 87 1893-02-25 6 England