Feedback

×

• Feedback
• Was this page helpful?*
  • Yes
  • No
• Do you have any additional feedback?
  • Yes
  • No
• Additional Feedback

Football

In this example, we use the football dataset to predict the outcomes of games between various teams. You can download the Jupyter Notebook of the study here and the dataset 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:

import verticapy as vp

vp.__version__
Out[2]: '1.1.0'

Connect to Vertica. This example uses an existing connection called VerticaDSN. For details on how to create a connection, see the Connection tutorial. You can skip the below cell if you already have an established connection.

vp.connect("VerticaDSN")

Let’s create a Virtual DataFrame of the dataset.

football = vp.read_csv("games.csv")
football.head(5)

	📅 date Date 100%	...	Abc country Varchar(64) 100%	010 neutral Boolean 100%
1	1876-03-25	...	Scotland	❌
2	1877-03-05	...	Wales	❌
3	1883-02-24	...	England	❌
4	1884-02-09	...	Wales	❌
5	1884-03-17	...	Wales	❌

Data Exploration and Preparation

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

football["date"].describe()

	value
name	"date"
dtype	date
count	41586
min	1872-11-30
max	2020-02-01

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.

football["tournament"].describe()

	value
	"tournament"
	varchar(84)
	112.0
	41586.0
	17029
	10630
	7236
	2582
	1672
	900
	813

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.

football.describe()

	...	approx_75%	max
"home_score"	...	2.0	31.0
"away_score"	...	2.0	21.0
"neutral"	...	0.0	1.0

football.describe(method = "categorical")

	...	top	top_percent
"date"	...	2012-02-29	0.159
"home_team"	...	Brazil	1.366
"away_team"	...	Uruguay	1.301
"home_score"	...	1	29.57
"away_score"	...	0	37.135
"tournament"	...	Friendly	40.949
"city"	...	Kuala Lumpur	1.416
"country"	...	United States	2.787
"neutral"	...	❌	75.275

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.

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.

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", method = "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,
)

	📅 date Date 100%	...	Abc country Varchar(64) 100%	Abc city Varchar(56) 100%
1	1876-03-25	...	Scotland	Glasgow
2	1877-03-05	...	Wales	Wrexham
3	1883-02-24	...	England	Liverpool
4	1884-02-09	...	Wales	Wrexham
5	1884-03-17	...	Wales	Wrexham
6	1885-03-14	...	Scotland	Glasgow
7	1887-02-19	...	Scotland	Glasgow
8	1888-03-24	...	Republic of Ireland	Belfast
9	1890-03-22	...	Scotland	Paisley
10	1890-04-05	...	Scotland	Glasgow
11	1891-02-07	...	Republic of Ireland	Belfast
12	1891-04-06	...	England	Blackburn
13	1892-02-27	...	Wales	Bangor
14	1893-03-18	...	Wales	Wrexham
15	1894-03-24	...	Scotland	Kilmarnock
16	1895-03-18	...	England	London
17	1897-02-20	...	England	Nottingham
18	1897-03-29	...	England	Sheffield
19	1898-03-05	...	Republic of Ireland	Belfast
20	1899-03-18	...	Wales	Wrexham

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.

import verticapy.sql.functions as fun

football.filter(fun.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,
)

	📅 date Date 100%	...	123 home_score Int 100%	Abc winner Varchar(64) 77%
1	1876-03-25	...	4	Scotland
2	1877-03-05	...	0	Scotland
3	1883-02-24	...	7	England
4	1884-02-09	...	6	Wales
5	1884-03-17	...	0	England
6	1885-03-14	...	8	Scotland
7	1887-02-19	...	4	Scotland
8	1888-03-24	...	2	Scotland
9	1890-03-22	...	5	Scotland
10	1890-04-05	...	1	[null]
11	1891-02-07	...	7	Northern Ireland
12	1891-04-06	...	2	England
13	1892-02-27	...	1	[null]
14	1893-03-18	...	0	Scotland
15	1894-03-24	...	5	Scotland
16	1895-03-18	...	1	[null]
17	1897-02-20	...	6	England
18	1897-03-29	...	4	England
19	1898-03-05	...	2	England
20	1899-03-18	...	0	Scotland

Let’s analyze the last game of each tournament.

football["year"] = fun.year(football["date"])
football.analytic(
    "row_number",
    order_by = {"date": "desc"},
    by = ["tournament", "year"] ,
    name = "order_tournament",
)

	📅 date Date 100%	...	123 home_score Int 100%	123 order_tournament Integer 100%
1	1892-04-02	...	1	1
2	1892-03-26	...	6	2
3	1892-03-19	...	2	3
4	1892-03-05	...	0	4
5	1892-03-05	...	0	5
6	1892-02-27	...	1	6
7	1929-09-20	...	2	1
8	1987-05-26	...	0	1
9	1987-05-23	...	0	2
10	1987-05-19	...	1	3
11	2006-12-27	...	2	1
12	2006-12-27	...	0	2
13	2006-12-24	...	4	3
14	2006-12-24	...	2	4
15	2006-12-21	...	0	5
16	2006-12-21	...	6	6
17	2006-12-20	...	4	7
18	2006-12-17	...	2	8
19	2006-12-14	...	2	9
20	1998-10-21	...	2	1

We can filter the data by only considering the last games and top tournaments.

football.filter(
    conditions = [
        football["order_tournament"] == 1,
        football["winner"] != None,
        football["tournament"]._in(
            [
                "FIFA World Cup",
                "UEFA Euro",
                "Copa América",
                "African Cup of Nations",
                "AFC Asian Cup",
                "Gold Cup",
            ]
        )
    ]
)

	📅 date Date 100%	...	123 home_score Int 100%	123 order_tournament Integer 100%
1	1937-02-01	...	2	1
2	1984-06-27	...	2	1
3	1997-06-29	...	1	1
4	1934-06-10	...	2	1
5	1990-07-08	...	1	1
6	1992-06-26	...	2	1
7	1946-02-10	...	2	1
8	1956-09-15	...	5	1
9	1978-03-16	...	2	1
10	1989-07-16	...	1	1
11	1923-12-02	...	2	1
12	1996-01-21	...	3	1
13	1990-03-16	...	1	1
14	2015-07-26	...	1	1
15	1980-09-30	...	3	1
16	1936-12-30	...	2	1
17	1958-06-29	...	2	1
18	1996-06-30	...	1	1
19	2004-08-07	...	1	1
20	1957-04-06	...	2	1

Let’s consider the World Cup as a special tournament. It is the only one where the confrontations between the top teams is possible.

football["Word_Cup"] = fun.decode(
    football["tournament"], "FIFA World Cup",
    1, 0,
)

	123 Word_Cup Integer
1	0
2	0
3	1
4	1
5	0
6	0
7	0
8	0
9	0
10	0
11	0
12	0
13	0
14	0
15	0
16	0
17	0
18	0
19	0
20	0

We can compute all the number of cup-wins by team. As expected, Brazil and Germany are the top football teams.

agg = [
    fun.sum(football["Word_Cup"])._as("nb_World_Cup"),
    fun.sum(1 - football["Word_Cup"])._as("nb_Continental_Cup"),
]
football_cup_winners = football.groupby(["winner"], agg)
football_cup_winners.sort(
    {
        "nb_World_Cup": "desc",
        "nb_Continental_Cup": "desc",
    }
).head(10)

	Abc winner Varchar(64) 100%	...	123 nb_World_Cup Integer 100%	123 nb_Continental_Cup Integer 100%
1	Brazil	...	4	8
2	Germany	...	4	3
3	Italy	...	3	1
4	Uruguay	...	2	9
5	Argentina	...	2	9
6	Spain	...	1	3
7	France	...	1	2
8	England	...	1	0
9	Egypt	...	0	6
10	Mexico	...	0	5

Let’s export the result to our Vertica database.

vp.drop(
    "football_cup_winners",
    method = "table",
)
football_cup_winners.to_db(
    "football_cup_winners",
    relation_type = "table",
)

	Abc winner Varchar(64) 100%	...	123 nb_World_Cup Integer 100%	123 nb_Continental_Cup Integer 100%
1	Brazil	...	4	8
2	Germany	...	4	3
3	Italy	...	3	1
4	Uruguay	...	2	9
5	Argentina	...	2	9
6	Spain	...	1	3
7	France	...	1	2
8	England	...	1	0
9	Egypt	...	0	6
10	Mexico	...	0	5
11	Japan	...	0	4
12	Nigeria	...	0	4
13	Peru	...	0	3
14	United States	...	0	3
15	Ecuador	...	0	2
16	Iran	...	0	2
17	Chile	...	0	2
18	Canada	...	0	2
19	Paraguay	...	0	2
20	South Korea	...	0	2

Team Confederations

Looking into team confederations could help our analysis. For example, this might help us quantify skill differences between different continents. A team that had played a qualification of a specific location can only belong to that tournament confederation.

First let’s encode the different continents so we can compute the correct aggregations.

football = vp.read_csv("games.csv")
football.case_when(
    'confederation',
    football["tournament"] == 'UEFA Euro qualification', 5,
    football["tournament"] == 'African Cup of Nations qualification', 4,
    football["tournament"] == 'AFC Asian Cup qualification', 3,
    football["tournament"] == 'Copa América', 2,
    football["tournament"] == 'Gold Cup', 1, 0,
)

	📅 date Date 100%	...	Abc home_team Varchar(64) 100%	123 confederation Integer 100%
1	1876-03-25	...	Scotland	0
2	1877-03-05	...	Wales	0
3	1883-02-24	...	England	0
4	1884-02-09	...	Wales	0
5	1884-03-17	...	Wales	0
6	1885-03-14	...	Scotland	0
7	1887-02-19	...	Scotland	0
8	1888-03-24	...	Northern Ireland	0
9	1890-03-22	...	Scotland	0
10	1890-04-05	...	Scotland	0
11	1891-02-07	...	Northern Ireland	0
12	1891-04-06	...	England	0
13	1892-02-27	...	Wales	0
14	1893-03-18	...	Wales	0
15	1894-03-24	...	Scotland	0
16	1895-03-18	...	England	0
17	1897-02-20	...	England	0
18	1897-03-29	...	England	0
19	1898-03-05	...	Northern Ireland	0
20	1899-03-18	...	Wales	0

We can aggregate the data and get each team’s continent.

confederation = football.groupby(
    ["home_team"],
    [fun.max(football["confederation"])._as("confederation")],
)
confederation.head(100)

	Abc home_team Varchar(64) 100%	123 confederation Integer 100%
1	Saudi Arabia	3
2	Thailand	3
3	Tamil Eelam	0
4	Netherlands	5
5	Haiti	2
6	Gotland	0
7	Kabylia	0
8	British Virgin Islands	0
9	Ethiopia	4
10	Niue	0
11	North Korea	3
12	Iraqi Kurdistan	0
13	Dominican Republic	0
14	Chinese Taipei	3
15	Laos	3
16	Ukraine	5
17	Mali	4
18	Austria	5
19	Iraq	3
20	Guadeloupe	1
21	Matabeleland	0
22	Saint Kitts and Nevis	0
23	Crimea	0
24	Curaçao	1
25	Shetland	0
26	Egypt	4
27	Bangladesh	3
28	Székely Land	0
29	El Salvador	1
30	Réunion	0
31	Bhutan	3
32	Western Sahara	0
33	Sri Lanka	3
34	Paraguay	2
35	Canary Islands	0
36	Tanzania	4
37	Peru	2
38	Cape Verde	4
39	Albania	5
40	Guatemala	1
41	Antigua and Barbuda	0
42	Abkhazia	0
43	South Ossetia	0
44	Frøya	0
45	Samoa	0
46	Jordan	3
47	Comoros	4
48	Tibet	0
49	Kenya	4
50	Provence	0
51	Iceland	5
52	Nigeria	4
53	Burkina Faso	4
54	Pakistan	3
55	Rwanda	4
56	England	5
57	Latvia	5
58	Silesia	0
59	Raetia	0
60	Kernow	0
61	Russia	5
62	Puerto Rico	0
63	Cayman Islands	0
64	Norway	5
65	Guinea-Bissau	4
66	Azerbaijan	5
67	Malaysia	3
68	Central Spain	0
69	Moldova	5
70	Italy	5
71	Slovenia	5
72	Hong Kong	3
73	France	5
74	United Koreans in Japan	0
75	Saint Lucia	0
76	Arameans Suryoye	0
77	German DR	5
78	Burma	3
79	Corsica	0
80	Manchukuo	0
81	Zambia	4
82	Bermuda	1
83	Madagascar	4
84	Togo	4
85	Andorra	5
86	Suriname	0
87	Chagos Islands	0
88	Northern Cyprus	0
89	São Tomé and Príncipe	4
90	Western Armenia	0
91	Tuvalu	0
92	Gozo	0
93	Romania	5
94	Vatican City	0
95	U.S. Virgin Islands	0
96	Cook Islands	0
97	Zimbabwe	4
98	Poland	5
99	Artsakh	0
100	Saarland	0

We can decode the previous label encoding.

confederation["confederation"].decode(
    5, "UEFA",
    4, "CAF",
    3, "AFC",
    2, "CONMEBOL",
    1, "CONCACAF",
    "OFC",
)

	Abc home_team Varchar(64) 100%	Abc confederation Varchar(8) 100%
1	Dominica	OFC
2	Isle of Wight	OFC
3	Finland	UEFA
4	DR Congo	CAF
5	Somalia	CAF
6	Botswana	CAF
7	Uruguay	CONMEBOL
8	U.S. Virgin Islands	OFC
9	Cook Islands	OFC
10	Zimbabwe	CAF
11	Poland	UEFA
12	Artsakh	OFC
13	Ecuador	CONMEBOL
14	Tuvalu	OFC
15	Chagos Islands	OFC
16	Northern Cyprus	OFC
17	São Tomé and Príncipe	CAF
18	Western Armenia	OFC
19	Gozo	OFC
20	Romania	UEFA

Let’s export the result to our Vertica database.

vp.drop("confederation")
confederation["home_team"].rename("team")
confederation.to_db(
    name = "confederation",
    relation_type = "table",
)

	Abc confederation Varchar(8) 100%	Abc team Varchar(64) 100%
1	OFC	Crimea
2	AFC	Saudi Arabia
3	AFC	Thailand
4	OFC	Gotland
5	OFC	Kabylia
6	OFC	British Virgin Islands
7	CAF	Ethiopia
8	OFC	Niue
9	AFC	Chinese Taipei
10	AFC	Laos
11	UEFA	Ukraine
12	CAF	Mali
13	CONCACAF	Guadeloupe
14	OFC	Matabeleland
15	OFC	Saint Kitts and Nevis
16	OFC	Tamil Eelam
17	UEFA	Netherlands
18	CONMEBOL	Haiti
19	CAF	Tanzania
20	CONMEBOL	Peru

Team KPIs

We use just two variables to track teams: away_team and home_team. This makes it a bit difficult to compute new features. We need to duplicate the dataset and intervert the two teams. This way, we can compute KPIs using a partition by the first team to avoid double-counting any games.

football = vp.vDataFrame("football_clean")
football.filter(fun.year(football["date"]) <= 2015)
football["home_team"].rename("team1")
football["home_score"].rename("team1_score")
football["away_team"].rename("team2")
football["away_score"].rename("team2_score")
football["neutral"].decode(True, 0, 1)

football2 = vp.vDataFrame("football_clean")
football2.filter(fun.year(football["date"]) <= 2015)
football2["home_team"].rename("team2")
football2["home_score"].rename("team2_score")
football2["away_team"].rename("team1")
football2["away_score"].rename("team1_score")
football2["neutral"].decode(True, 0, 2)

# Merging the 2 interverted datasets
all_matchs = football.append(football2)
all_matchs["neutral"].rename("home_team_id")

	📅 date Date 100%	...	123 team2_score Integer 100%	123 home_team_id Integer 100%
1	1875-03-06	...	2	1
2	1878-03-23	...	0	1
3	1879-04-07	...	3	1
4	1880-03-13	...	4	1
5	1880-03-15	...	3	1
6	1883-03-10	...	3	1
7	1883-03-17	...	1	1
8	1884-03-15	...	0	1
9	1886-03-13	...	6	1
10	1886-03-27	...	1	1
11	1886-11-25	...	2	1
12	1887-02-26	...	0	1
13	1888-03-10	...	1	1
14	1888-03-17	...	5	1
15	1888-04-07	...	5	1
16	1891-03-07	...	1	1
17	1892-03-05	...	2	1
18	1892-03-19	...	3	1
19	1893-03-13	...	0	1
20	1893-04-01	...	2	1

To compute the different aggregations, we need to add dummies which indicate the type of game and winner.

all_matchs["World_Tournament"] = fun.case_when(all_matchs["tournament"]._in(
    [
        "FIFA World Cup",
        "Confederations Cup"
    ],
), 1, 0)
all_matchs["Continental_Tournament"] = fun.case_when(
    all_matchs["tournament"]._in(
        [
            "UEFA Euro",
            "Copa América",
            "African Cup of Nations",
            "AFC Asian Cup",
            "Gold Cup",
            "FIFA World Cup qualification",
        ]
    ), 1, 0)
all_matchs["Victory_team1"] = (all_matchs["team1_score"] > all_matchs["team2_score"])
all_matchs["Victory_team1"].astype("int")
all_matchs["Draw"] = (all_matchs["team1_score"] == all_matchs["team2_score"])
all_matchs["Draw"].astype("int")

	📅 date Date 100%	...	123 Victory_team1 Int 100%	123 Draw Int 100%
1	1875-03-06	...	0	1
2	1878-03-23	...	1	0
3	1879-04-07	...	0	0
4	1880-03-13	...	1	0
5	1880-03-15	...	0	0
6	1883-03-10	...	0	0
7	1883-03-17	...	0	1
8	1884-03-15	...	1	0
9	1886-03-13	...	0	0
10	1886-03-27	...	0	1
11	1886-11-25	...	1	0
12	1887-02-26	...	1	0
13	1888-03-10	...	1	0
14	1888-03-17	...	0	0
15	1888-04-07	...	0	0
16	1891-03-07	...	1	0
17	1892-03-05	...	0	0
18	1892-03-19	...	0	0
19	1893-03-13	...	1	0
20	1893-04-01	...	1	0

Now we can compute each team’s KPI.

teams_kpi = all_matchs.groupby(
    ["team1"],
    [
        fun.sum(all_matchs["World_Tournament"])._as("Number_Games_World_Tournament"),
        fun.sum(all_matchs["Continental_Tournament"])._as("Number_Games_Continental_Tournament"),
        fun.avg(fun.decode(all_matchs["World_Tournament"], 1, all_matchs["Victory_team1"]))._as("Percent_Victory_World_Tournament"),
        fun.avg(fun.decode(all_matchs["Continental_Tournament"], 1, all_matchs["Victory_team1"]))._as("Percent_Victory_Continental_Tournament"),
        fun.avg(fun.case_when((all_matchs["home_team_id"] == 1) & (all_matchs["World_Tournament"] == 0) & (all_matchs["Continental_Tournament"] == 0), all_matchs["Victory_team1"], None))._as("Percent_Victory_Home"),
        fun.avg(fun.case_when((all_matchs["home_team_id"] != 1) & (all_matchs["World_Tournament"] == 0) & (all_matchs["Continental_Tournament"] == 0), all_matchs["Victory_team1"], None))._as("Percent_Victory_Away"),
        fun.avg(all_matchs["Victory_team1"])._as("Percent_Victory"),
        fun.avg(all_matchs["Draw"])._as("Percent_Draw"),
        fun.avg(all_matchs["team1_score"])._as("Avg_goals"),
        fun.avg(all_matchs["team2_score"])._as("Avg_goals_conceded"),
    ],
).sort({"Number_Games_World_Tournament": "desc"})
teams_kpi.head(100)

	Abc team1 Varchar(64) 100%	...	123 Avg_goals Float(22) 100%	123 Avg_goals_conceded Float(22) 100%
1	Brazil	...	2.19375672766416	0.93756727664155
2	Germany	...	2.0998322147651	1.17953020134228
3	Italy	...	1.69588313413015	0.98273572377158
4	Argentina	...	1.86544671689989	1.05059203444564
5	Mexico	...	1.7475	1.07625
6	France	...	1.75831202046036	1.34271099744246
7	Spain	...	1.96744186046512	0.908527131782946
8	England	...	2.19937369519833	0.992693110647182
9	Uruguay	...	1.57568533969011	1.26460071513707
10	Netherlands	...	2.06207827260459	1.24966261808367
11	United States	...	1.41961414790997	1.37138263665595
12	Sweden	...	2.00519210799585	1.30633437175493
13	Serbia	...	1.81601123595506	1.37921348314607
14	Belgium	...	1.68435754189944	1.60614525139665
15	Russia	...	1.71981424148607	0.93343653250774
16	Czech Republic	...	1.8437917222964	1.23497997329773
17	South Korea	...	1.78304239401496	0.897755610972569
18	Chile	...	1.42149929278642	1.46534653465347
19	Switzerland	...	1.44148936170213	1.7313829787234
20	Japan	...	1.72007042253521	1.16021126760563
21	Hungary	...	2.07207207207207	1.4954954954955
22	Cameroon	...	1.42171189979123	1.05427974947808
23	Poland	...	1.68441558441558	1.37012987012987
24	Austria	...	1.79782903663501	1.59294436906377
25	Paraguay	...	1.33578792341679	1.43888070692194
26	Australia	...	2.03171247357294	1.11205073995772
27	Bulgaria	...	1.43192488262911	1.47104851330203
28	Portugal	...	1.63129496402878	1.20143884892086
29	Saudi Arabia	...	1.59611992945326	1.04585537918871
30	Nigeria	...	1.49618320610687	1.00381679389313
31	Colombia	...	1.206	1.21
32	Scotland	...	1.7503355704698	1.2255033557047
33	Romania	...	1.64570552147239	1.28834355828221
34	Denmark	...	1.77762982689747	1.42876165113182
35	South Africa	...	1.3399433427762	1.00849858356941
36	Croatia	...	1.75187969924812	0.981203007518797
37	Costa Rica	...	1.67030965391621	1.16757741347905
38	New Zealand	...	1.74702380952381	1.61309523809524
39	Peru	...	1.22902097902098	1.46853146853147
40	Tunisia	...	1.43371212121212	1.06439393939394
41	Turkey	...	1.3320537428023	1.42802303262956
42	Greece	...	1.23954372623574	1.42015209125475
43	Morocco	...	1.38865546218487	0.873949579831933
44	Algeria	...	1.35434782608696	1.0304347826087
45	Northern Ireland	...	1.04311774461028	1.95356550580431
46	Republic of Ireland	...	1.40152963671128	1.24665391969407
47	Ghana	...	1.6423487544484	1.04092526690391
48	Iran	...	1.84035476718404	0.835920177383592
49	Ivory Coast	...	1.63457760314342	1.05304518664047
50	Ecuador	...	1.19491525423729	1.65042372881356
51	Egypt	...	1.64383561643836	1.03595890410959
52	Bolivia	...	1.0381861575179	1.94272076372315
53	Honduras	...	1.49148936170213	1.22340425531915
54	Norway	...	1.50130890052356	1.68455497382199
55	North Korea	...	1.61	1.03666666666667
56	Canada	...	1.04899135446686	1.39769452449568
57	Iraq	...	1.62145748987854	0.945344129554656
58	Slovenia	...	1.23943661971831	1.27230046948357
59	El Salvador	...	1.22629310344828	1.48706896551724
60	United Arab Emirates	...	1.41489361702128	1.28510638297872
61	Senegal	...	1.2875	1.00208333333333
62	Ukraine	...	1.39647577092511	0.973568281938326
63	Wales	...	1.25324675324675	1.69318181818182
64	Slovakia	...	1.44176706827309	1.30120481927711
65	Angola	...	1.1747572815534	1.042071197411
66	DR Congo	...	1.5024154589372	1.2536231884058
67	Israel	...	1.45477386934673	1.45226130653266
68	Kuwait	...	1.55555555555556	1.07962962962963
69	Togo	...	1.08285714285714	1.39142857142857
70	Haiti	...	1.5622009569378	1.31818181818182
71	Trinidad and Tobago	...	1.73996789727127	1.27447833065811
72	China PR	...	1.83729433272395	1.08775137111517
73	Tahiti	...	2.51832460732984	1.70157068062827
74	Cuba	...	1.32051282051282	1.43910256410256
75	Bosnia and Herzegovina	...	1.42857142857143	1.39010989010989
76	Jamaica	...	1.31861804222649	1.33781190019194
77	Indonesia	...	1.67311411992263	1.67504835589942
78	British Virgin Islands	...	0.876543209876543	3.11111111111111
79	Kazakhstan	...	1.03680981595092	1.6441717791411
80	Libya	...	1.29054054054054	1.21621621621622
81	Madagascar	...	1.33333333333333	1.63333333333333
82	Gotland	...	2.46153846153846	2.0
83	Mali	...	1.27292576419214	1.17685589519651
84	Burma	...	1.71383647798742	1.4811320754717
85	Hong Kong	...	1.52957746478873	1.68450704225352
86	Arameans Suryoye	...	1.42857142857143	1.14285714285714
87	Nicaragua	...	0.825	3.19166666666667
88	Qatar	...	1.4	1.18297872340426
89	Liechtenstein	...	0.439024390243902	2.83536585365854
90	Northern Mariana Islands	...	0.888888888888889	4.16666666666667
91	Mayotte	...	1.61111111111111	2.05555555555556
92	Oman	...	1.26485148514851	1.28960396039604
93	Papua New Guinea	...	1.90721649484536	2.22680412371134
94	North Macedonia	...	1.08415841584158	1.38118811881188
95	Lithuania	...	1.09756097560976	1.78353658536585
96	East Timor	...	0.894736842105263	3.68421052631579
97	Bahamas	...	1.14814814814815	3.33333333333333
98	Hitra	...	1.16666666666667	4.83333333333333
99	Belarus	...	1.25358851674641	1.44019138755981
100	Saarland	...	0.833333333333333	3.0

We can join the different information about the cup winners to enrich our dataset. We’ll be using this later, so let’s export it to our Vertica database.

vp.drop("teams_kpi", method = "table")
teams_kpi = teams_kpi.join(
    football_cup_winners,
    on = {"team1": "winner"},
    how = "left",
    expr2 = [
        "nb_World_Cup",
        "nb_Continental_Cup",
    ],
).to_db("teams_kpi", relation_type = "table")
teams_kpi.head(100)

	Abc team1 Varchar(64) 100%	...	123 nb_World_Cup Integer 14%	123 nb_Continental_Cup Integer 14%
1	Italy	...	3	1
2	France	...	1	2
3	England	...	1	0
4	Netherlands	...	0	1
5	Russia	...	0	1
6	Austria	...	[null]	[null]
7	Paraguay	...	0	2
8	Saudi Arabia	...	[null]	[null]
9	Nigeria	...	0	5
10	Peru	...	0	3
11	Egypt	...	0	6
12	Norway	...	[null]	[null]
13	North Korea	...	[null]	[null]
14	El Salvador	...	[null]	[null]
15	Slovenia	...	[null]	[null]
16	Iraq	...	0	1
17	Ukraine	...	[null]	[null]
18	Togo	...	[null]	[null]
19	Haiti	...	[null]	[null]
20	Western Australia	...	[null]	[null]
21	Dominican Republic	...	[null]	[null]
22	Raetia	...	[null]	[null]
23	Kernow	...	[null]	[null]
24	Guadeloupe	...	[null]	[null]
25	Sri Lanka	...	[null]	[null]
26	South Ossetia	...	[null]	[null]
27	Arameans Suryoye	...	[null]	[null]
28	Abkhazia	...	[null]	[null]
29	Samoa	...	[null]	[null]
30	Andorra	...	[null]	[null]
31	Puerto Rico	...	[null]	[null]
32	Bhutan	...	[null]	[null]
33	Moldova	...	[null]	[null]
34	Réunion	...	[null]	[null]
35	Mali	...	[null]	[null]
36	Hong Kong	...	[null]	[null]
37	Saint Kitts and Nevis	...	[null]	[null]
38	Saint Lucia	...	[null]	[null]
39	Frøya	...	[null]	[null]
40	Tamil Eelam	...	[null]	[null]
41	Jordan	...	[null]	[null]
42	Burma	...	[null]	[null]
43	Kenya	...	[null]	[null]
44	Guinea-Bissau	...	[null]	[null]
45	Iceland	...	[null]	[null]
46	Burkina Faso	...	[null]	[null]
47	Antigua and Barbuda	...	[null]	[null]
48	Bermuda	...	[null]	[null]
49	Cape Verde	...	[null]	[null]
50	Pakistan	...	[null]	[null]
51	Albania	...	[null]	[null]
52	Laos	...	[null]	[null]
53	Latvia	...	[null]	[null]
54	Chinese Taipei	...	[null]	[null]
55	Iraqi Kurdistan	...	[null]	[null]
56	Silesia	...	[null]	[null]
57	Shetland	...	[null]	[null]
58	Curaçao	...	[null]	[null]
59	Suriname	...	[null]	[null]
60	Cayman Islands	...	[null]	[null]
61	Azerbaijan	...	[null]	[null]
62	Thailand	...	[null]	[null]
63	Székely Land	...	[null]	[null]
64	Ethiopia	...	0	1
65	British Virgin Islands	...	[null]	[null]
66	Malaysia	...	[null]	[null]
67	Provence	...	[null]	[null]
68	Tibet	...	[null]	[null]
69	Bangladesh	...	[null]	[null]
70	Gotland	...	[null]	[null]
71	Comoros	...	[null]	[null]
72	Zambia	...	0	1
73	Corsica	...	[null]	[null]
74	Guatemala	...	[null]	[null]
75	Tanzania	...	[null]	[null]
76	Madagascar	...	[null]	[null]
77	Rwanda	...	[null]	[null]
78	Argentina	...	2	8
79	Uruguay	...	2	9
80	Belgium	...	[null]	[null]
81	Czech Republic	...	[null]	[null]
82	Poland	...	[null]	[null]
83	Bulgaria	...	[null]	[null]
84	Scotland	...	[null]	[null]
85	Romania	...	[null]	[null]
86	Denmark	...	0	1
87	Turkey	...	[null]	[null]
88	Ecuador	...	0	2
89	Senegal	...	[null]	[null]
90	DR Congo	...	0	1
91	Estonia	...	[null]	[null]
92	Saarland	...	[null]	[null]
93	Rhodes	...	[null]	[null]
94	Sápmi	...	[null]	[null]
95	Guyana	...	[null]	[null]
96	Bahrain	...	[null]	[null]
97	Isle of Wight	...	[null]	[null]
98	Liberia	...	[null]	[null]
99	Malawi	...	[null]	[null]
100	São Tomé and Príncipe	...	[null]	[null]

Let’s add each team’s confederation to our dataset.

teams_kpi = teams_kpi.join(
    confederation,
    how = "left",
    on = {"team1": "team"},
    expr2 = ["confederation"],
)
teams_kpi.head(100)

	Abc team1 Varchar(64) 100%	...	123 nb_Continental_Cup Integer 14%	Abc confederation Varchar(8) 99%
1	Wallis Islands and Futuna	...	[null]	OFC
2	Maldives	...	[null]	AFC
3	Kuwait	...	1	AFC
4	Basque Country	...	[null]	OFC
5	Yemen	...	[null]	AFC
6	Belarus	...	[null]	UEFA
7	Saint Vincent and the Grenadines	...	[null]	OFC
8	Orkney	...	[null]	OFC
9	Uganda	...	[null]	CAF
10	Eritrea	...	[null]	CAF
11	Benin	...	[null]	CAF
12	Japan	...	4	AFC
13	Australia	...	1	AFC
14	Sudan	...	[null]	CAF
15	Angola	...	[null]	CAF
16	Wales	...	[null]	UEFA
17	Liechtenstein	...	[null]	UEFA
18	Sierra Leone	...	[null]	CAF
19	United States	...	2	CONMEBOL
20	Morocco	...	[null]	CAF
21	Sweden	...	[null]	UEFA
22	Cambodia	...	[null]	AFC
23	Cameroon	...	2	CAF
24	Uzbekistan	...	[null]	AFC
25	Alderney	...	[null]	OFC
26	North Macedonia	...	[null]	UEFA
27	Bahamas	...	[null]	OFC
28	Saint Martin	...	[null]	OFC
29	Barbados	...	[null]	OFC
30	Algeria	...	1	CAF
31	Switzerland	...	[null]	UEFA
32	Mayotte	...	[null]	OFC
33	Honduras	...	[null]	CONMEBOL
34	Tonga	...	[null]	OFC
35	Gabon	...	[null]	CAF
36	Lithuania	...	[null]	UEFA
37	East Timor	...	[null]	OFC
38	Djibouti	...	[null]	CAF
39	India	...	[null]	AFC
40	Åland Islands	...	[null]	OFC
41	Serbia	...	[null]	UEFA
42	Republic of Ireland	...	[null]	UEFA
43	Northern Mariana Islands	...	[null]	OFC
44	Ellan Vannin	...	[null]	OFC
45	Costa Rica	...	[null]	CONMEBOL
46	Hitra	...	[null]	OFC
47	Ghana	...	2	CAF
48	Ivory Coast	...	2	CAF
49	South Korea	...	2	AFC
50	Luxembourg	...	[null]	UEFA
51	Seychelles	...	[null]	CAF
52	Solomon Islands	...	[null]	OFC
53	Gibraltar	...	[null]	UEFA
54	Lebanon	...	[null]	AFC
55	United Arab Emirates	...	[null]	AFC
56	Kosovo	...	[null]	UEFA
57	Aruba	...	[null]	OFC
58	Spain	...	3	UEFA
59	Germany	...	3	UEFA
60	Oman	...	[null]	AFC
61	Qatar	...	[null]	AFC
62	South Sudan	...	[null]	CAF
63	Israel	...	1	UEFA
64	Ynys Môn	...	[null]	OFC
65	Papua New Guinea	...	[null]	OFC
66	Turkmenistan	...	[null]	AFC
67	Galicia	...	[null]	OFC
68	Artsakh	...	[null]	OFC
69	Palestine	...	[null]	AFC
70	Turkey	...	[null]	UEFA
71	Sápmi	...	[null]	OFC
72	Ecuador	...	2	CONMEBOL
73	Cook Islands	...	[null]	OFC
74	Catalonia	...	[null]	OFC
75	Denmark	...	1	UEFA
76	Nicaragua	...	[null]	CONCACAF
77	Turks and Caicos Islands	...	[null]	OFC
78	Montserrat	...	[null]	OFC
79	Saarland	...	[null]	OFC
80	Belgium	...	[null]	UEFA
81	Venezuela	...	[null]	CONMEBOL
82	Botswana	...	[null]	CAF
83	Argentina	...	9	CONMEBOL
84	Dominica	...	[null]	OFC
85	Kazakhstan	...	[null]	UEFA
86	San Marino	...	[null]	UEFA
87	French Guiana	...	[null]	CONCACAF
88	Scotland	...	[null]	UEFA
89	Bulgaria	...	[null]	UEFA
90	Guam	...	[null]	AFC
91	Northern Cyprus	...	[null]	OFC
92	Poland	...	[null]	UEFA
93	São Tomé and Príncipe	...	[null]	CAF
94	Romania	...	[null]	UEFA
95	Liberia	...	[null]	CAF
96	Tuvalu	...	[null]	OFC
97	Saint Pierre and Miquelon	...	[null]	OFC
98	Namibia	...	[null]	CAF
99	American Samoa	...	[null]	OFC
100	Malawi	...	[null]	CAF

Since clustering will use different statistics, we need to normalize the data. We’ll also create a dummy that will equal 1 if the team won at least one World Cup.

teams_kpi.normalize(
    columns = [
        "Number_Games_Continental_Tournament",
        "Number_Games_World_Tournament",
        "nb_Continental_Cup",
    ],
    method = "minmax",
)
teams_kpi["Word_Cup_Victory"] = teams_kpi["nb_World_Cup"] > 0
teams_kpi["Word_Cup_Victory"].astype("int")

	Abc team1 Varchar(64) 100%	...	Abc confederation Varchar(8) 99%	123 Word_Cup_Victory Int 14%
1	Suriname	...	OFC	[null]
2	Norway	...	UEFA	[null]
3	Raetia	...	OFC	[null]
4	British Virgin Islands	...	OFC	[null]
5	Cape Verde	...	CAF	[null]
6	Guinea-Bissau	...	CAF	[null]
7	Kernow	...	OFC	[null]
8	Frøya	...	OFC	[null]
9	Sri Lanka	...	AFC	[null]
10	Curaçao	...	CONCACAF	[null]
11	Mali	...	CAF	[null]
12	Arameans Suryoye	...	OFC	[null]
13	Togo	...	CAF	[null]
14	Azerbaijan	...	UEFA	[null]
15	Russia	...	UEFA	0
16	Netherlands	...	UEFA	0
17	England	...	UEFA	1
18	Shetland	...	OFC	[null]
19	Comoros	...	CAF	[null]
20	Cayman Islands	...	OFC	[null]

Some data is missing; this is because only top teams won major tournaments. Besides, some non-professional teams may not have a stadium.

teams_kpi.count()

	count
	272.0
	272.0
	272.0
	77.0
	213.0
	243.0
	263.0
	272.0
	272.0
	272.0
	272.0
	39.0
	39.0
	271.0
	39.0

Let’s impute the missing values by 0.

teams_kpi.fillna(
    {
        "Percent_Victory_Away": 0,
        "Percent_Victory_Home": 0,
        "Percent_Victory_Continental_Tournament": 0,
        "Percent_Victory_World_Tournament": 0,
        "nb_World_Cup": 0,
        "Word_Cup_Victory": 0,
        "nb_Continental_Cup": 0,
        "confederation": "OFC",
    },
)

	Abc team1 Varchar(64) 100%	...	Abc confederation Varchar(8) 100%	123 Word_Cup_Victory Int 100%
1	French Guiana	...	CONCACAF	0
2	Scotland	...	UEFA	0
3	Menorca	...	OFC	0
4	Macau	...	AFC	0
5	Turks and Caicos Islands	...	OFC	0
6	Argentina	...	CONMEBOL	1
7	Dominica	...	OFC	0
8	Kazakhstan	...	UEFA	0
9	San Marino	...	UEFA	0
10	Montserrat	...	OFC	0
11	Saarland	...	OFC	0
12	Belgium	...	UEFA	0
13	Venezuela	...	CONMEBOL	0
14	Botswana	...	CAF	0
15	Galicia	...	OFC	0
16	Artsakh	...	OFC	0
17	Palestine	...	AFC	0
18	Bulgaria	...	UEFA	0
19	Denmark	...	UEFA	0
20	Nicaragua	...	CONCACAF	0

Let’s export the result to our Vertica database.

vp.drop("football_clustering", method = "table")
teams_kpi.to_db(
    "football_clustering",
    relation_type = "table",
    inplace = True,
)

	Abc team1 Varchar(64) 100%	...	Abc confederation Varchar(8) 100%	123 Word_Cup_Victory Int 100%
1	Alderney	...	OFC	0
2	Algeria	...	CAF	0
3	Angola	...	CAF	0
4	Aruba	...	OFC	0
5	Australia	...	AFC	0
6	Bahamas	...	OFC	0
7	Barbados	...	OFC	0
8	Basque Country	...	OFC	0
9	Belarus	...	UEFA	0
10	Benin	...	CAF	0
11	Cambodia	...	AFC	0
12	Cameroon	...	CAF	0
13	Costa Rica	...	CONMEBOL	0
14	Djibouti	...	CAF	0
15	East Timor	...	OFC	0
16	Ellan Vannin	...	OFC	0
17	Eritrea	...	CAF	0
18	Gabon	...	CAF	0
19	Germany	...	UEFA	1
20	Ghana	...	CAF	0

Team Rankings with k-means

To compute a KMeans model, we need to find a value for k. Let’s draw an elbow() curve to find a suitable number of clusters.

from verticapy.machine_learning.model_selection import elbow

predictors = [
    'Word_Cup_Victory',
    'nb_Continental_Cup',
    'Number_Games_World_Tournament',
    'Number_Games_Continental_Tournament',
    'Percent_Victory_World_Tournament',
    'Percent_Victory_Continental_Tournament',
    'Percent_Victory_Home',
    'Percent_Victory_Away',
]
elbow(
    "football_clustering",
    predictors,
    n_cluster = (1, 11),
)

6 seems to be a good number of clusters. To help the algorithm to converge to meaningful clusters, we can initialize the clusters with different types of centroid levels. For example, we can associate very good teams (champions) to World Cups Winners, good teams to continental Cup Winners, etc. This will let us to properly weigh the performance of each team relatve to the strength of their region.

from verticapy.machine_learning.vertica import KMeans

    # w_cup c_cup w_games c_games w_vict c_vict h_vict a_vict
init =  [
    (0,    0,       0,  0.05,      0,    0,      0, 0.05), # very bad
    (0,    0,       0,  0.30,      0, 0.25,   0.30, 0.10), # bad
    (0,    0,    0.05,  0.40,   0.15, 0.35,   0.40, 0.20), # outsiders
    (0, 0.10,    0.15,  0.50,   0.20, 0.45,   0.50, 0.30), # good
    (0, 0.20,    0.30,  0.40,   0.40, 0.55,   0.60, 0.40), # strong
    (1,  0.5,       1,  0.80,   0.70, 0.65,   0.75, 0.55), # champions
]


model_kmeans = KMeans(
    n_cluster = 6,
    init = init,
)


model_kmeans.fit("football_clustering", predictors)


=======
centers
=======
word_cup_victory|nb_continental_cup|number_games_world_tournament|number_games_continental_tournament|percent_victory_world_tournament|percent_victory_continental_tournament|percent_victory_home|percent_victory_away
----------------+------------------+-----------------------------+-----------------------------------+--------------------------------+--------------------------------------+--------------------+--------------------
     0.00000    |      0.00000     |           0.00000           |              0.03559              |             0.00000            |                0.03892               |       0.06897      |       0.17548      
     0.00000    |      0.00000     |           0.00000           |              0.03447              |             0.00000            |                0.06034               |       0.55445      |       0.23662      
     0.00000    |      0.00253     |           0.00406           |              0.16676              |             0.01458            |                0.30350               |       0.45371      |       0.27334      
     0.00000    |      0.09091     |           0.07211           |              0.50548              |             0.12824            |                0.39520               |       0.51557      |       0.31154      
     0.00000    |      0.11877     |           0.21369           |              0.42376              |             0.35454            |                0.51460               |       0.55522      |       0.36849      
     1.00000    |      0.47222     |           0.63504           |              0.61032              |             0.52921            |                0.58711               |       0.61893      |       0.43467      


=======
metrics
=======
Evaluation metrics:
     Total Sum of Squares: 64.504819
     Within-Cluster Sum of Squares: 
         Cluster 0: 2.6477373
         Cluster 1: 4.2616057
         Cluster 2: 3.1519907
         Cluster 3: 2.932969
         Cluster 4: 2.3812356
         Cluster 5: 2.0500045
     Total Within-Cluster Sum of Squares: 17.425543
     Between-Cluster Sum of Squares: 47.079276
     Between-Cluster SS / Total SS: 72.99%
 Number of iterations performed: 12
 Converged: True
 Call:
kmeans('"public"."_verticapy_tmp_kmeans_v_mldb_2792e83297b111efa8720242ac120002_"', 'football_clustering', '"Word_Cup_Victory", "nb_Continental_Cup", "Number_Games_World_Tournament", "Number_Games_Continental_Tournament", "Percent_Victory_World_Tournament", "Percent_Victory_Continental_Tournament", "Percent_Victory_Home", "Percent_Victory_Away"', 6
USING PARAMETERS max_iterations=300, epsilon=0.0001, initial_centers_table='"public"."_verticapy_tmp_kmeans_init_v_mldb_27e16cf097b111efa8720242ac120002_"', distance_method='euclidean')

model_kmeans.clusters_
Out[7]: 
array([[0.        , 0.        , 0.        , 0.03559237, 0.        ,
        0.03892218, 0.06897399, 0.17548141],
       [0.        , 0.        , 0.        , 0.03447122, 0.        ,
        0.06033562, 0.55445231, 0.23662273],
       [0.        , 0.00252525, 0.00406437, 0.16675794, 0.01458333,
        0.30349671, 0.45371031, 0.27334108],
       [0.        , 0.09090909, 0.07210794, 0.50547645, 0.12823886,
        0.39519921, 0.51556972, 0.31153749],
       [0.        , 0.11877395, 0.21369242, 0.42376402, 0.35453761,
        0.5146026 , 0.55522045, 0.3684891 ],
       [1.        , 0.47222222, 0.6350365 , 0.61031627, 0.52921206,
        0.58710705, 0.61893195, 0.43467394]])

Let’s add the prediction to the vDataFrame.

model_kmeans.predict(
    teams_kpi,
    name = "fifa_rank",
)

	Abc team1 Varchar(64) 100%	...	123 Number_Games_World_Tournament Numeric(34,15) 100%	123 fifa_rank Integer 100%
1	Alderney	...	0.0	0
2	Algeria	...	0.094890510948905	3
3	Angola	...	0.021897810218978	2
4	Aruba	...	0.0	2
5	Australia	...	0.18978102189781	4
6	Bahamas	...	0.0	2
7	Barbados	...	0.0	2
8	Basque Country	...	0.0	1
9	Belarus	...	0.0	2
10	Benin	...	0.0	2
11	Cambodia	...	0.0	1
12	Cameroon	...	0.226277372262774	4
13	Costa Rica	...	0.109489051094891	3
14	Djibouti	...	0.0	0
15	East Timor	...	0.0	0
16	Ellan Vannin	...	0.0	0
17	Eritrea	...	0.0	1
18	Gabon	...	0.0	2
19	Germany	...	0.875912408759124	5
20	Ghana	...	0.087591240875912	4

Let’s look at the strongest group, which includes well-known teams like Argentina, Brazil, and France.

teams_kpi.search(
    conditions = [teams_kpi["fifa_rank"] == 5],
    usecols = ["team1", "fifa_rank"],
    order_by = ["fifa_rank"],
).head(10)

	Abc team1 Varchar(64) 100%	123 fifa_rank Integer 100%
1	Argentina	5
2	Uruguay	5
3	Brazil	5
4	Germany	5
5	Spain	5
6	England	5
7	France	5
8	Italy	5

The weakest group includes less well-known teams.

teams_kpi.search(
    conditions = [teams_kpi["fifa_rank"] == 0],
    usecols = ["team1", "fifa_rank"],
    order_by = ["fifa_rank"],
).head(10)

	Abc team1 Varchar(64) 100%	123 fifa_rank Integer 100%
1	U.S. Virgin Islands	0
2	Tuvalu	0
3	Turks and Caicos Islands	0
4	São Tomé and Príncipe	0
5	San Marino	0
6	Saint Pierre and Miquelon	0
7	Saarland	0
8	Saare County	0
9	Palestine	0
10	Menorca	0

A bubble plot will let us visualize the differences in strength between each confederation.

We can see the strongest group at the top right of the graphic and weakest teams at the bottom left. Some teams may be very good in their location but very bad in World Tournaments. They are mainly at the bottom right of the graph.

teams_kpi.scatter(
    [
        "Percent_Victory_Continental_Tournament",
        "Percent_Victory_World_Tournament",
    ],
    size = "fifa_rank",
    by = "confederation",
)

We can also look at the Percent of Victory by rank to confirm our hypothesis.

teams_kpi.scatter(
    [
        "Percent_Victory_Continental_Tournament",
        "Percent_Victory_World_Tournament",
    ],
    size = "Percent_Victory",
    by = "fifa_rank",
)

A box plot can also show us the differences in skill between teams. We can look at rank 1, where the percent of victory is high because of the confederation.

Note that the best team in a weaker confederation might not be particularly strong, but still have a high Percent of Victory.

teams_kpi["Percent_Victory"].boxplot(by = "fifa_rank")

Let’s export the KPIs to our Vertica database.

vp.drop(
    "team_kpi",
    method = "table",
)
teams_kpi.to_db(
    name = "team_kpi",
    relation_type = "table",
    inplace = True,
)

	Abc team1 Varchar(64) 100%	...	123 Number_Games_World_Tournament Numeric(34,15) 100%	123 fifa_rank Int 100%
1	Alderney	...	0.0	0
2	Algeria	...	0.094890510948905	3
3	Angola	...	0.021897810218978	2
4	Aruba	...	0.0	2
5	Australia	...	0.18978102189781	4
6	Bahamas	...	0.0	2
7	Barbados	...	0.0	2
8	Basque Country	...	0.0	1
9	Belarus	...	0.0	2
10	Benin	...	0.0	2
11	Cambodia	...	0.0	1
12	Cameroon	...	0.226277372262774	4
13	Costa Rica	...	0.109489051094891	3
14	Djibouti	...	0.0	0
15	East Timor	...	0.0	0
16	Ellan Vannin	...	0.0	0
17	Eritrea	...	0.0	1
18	Gabon	...	0.0	2
19	Germany	...	0.875912408759124	5
20	Ghana	...	0.087591240875912	4

Features Engineering

Many very interesting features can be to use to evaluate each team. Moving windows of the previous games can drastically improve our model.

Since a team can by a home or away team, we’ll intervert the away and home teams. By using this technique, we will never get twice the same game and we will get the proper moving windows.

football = vp.vDataFrame("football_clean")

football["home_team"].rename("team1");

football["home_score"].rename("team1_score");

football["away_team"].rename("team2");

football["away_score"].rename("team2_score");

# will be to use to filter the data after the features engineering
football["match_sample"] = "1";

football2 = vp.vDataFrame("football_clean");

football2["home_team"].rename("team2");

football2["home_score"].rename("team2_score");

football2["away_team"].rename("team1");

football2["away_score"].rename("team1_score");

# will be to use to filter the data after the features engineering
football2["match_sample"] = "2";

# Merging the 2 interverted datasets
all_matchs = football.append(football2);

Let’s add the different KPIs to our dataset.

all_matchs = all_matchs.join(
    teams_kpi,
    on = {"team1": "team1"},
    how = "left",
    expr2 = [
        "nb_World_Cup AS nb_World_Cup_1",
        "fifa_rank AS fifa_rank_1",
        "Avg_goals AS Avg_goals_1",
        "Percent_Draw AS Percent_Draw_1",
        "Number_Games_World_Tournament AS Number_Games_World_Tournament_1",
        "Percent_Victory_World_Tournament AS Percent_Victory_World_Tournament_1",
        "Percent_Victory_Away AS Percent_Victory_Away_1",
        "Percent_Victory_Continental_Tournament AS Percent_Victory_Continental_Tournament_1",
        "confederation AS confederation_1",
        "Percent_Victory_Home AS Percent_Victory_Home_1",
        "Avg_goals_conceded AS Avg_goals_conceded_1",
        "Number_Games_Continental_Tournament AS Number_Games_Continental_Tournament_1",
        "nb_Continental_Cup AS nb_Continental_Cup_1",
        "Percent_Victory AS Percent_Victory_1",
    ],
)


all_matchs = all_matchs.join(
    teams_kpi,
    on = {"team2": "team1"},
    how = "left",
    expr2 = [
        "nb_World_Cup AS nb_World_Cup_2",
        "fifa_rank AS fifa_rank_2",
        "Avg_goals AS Avg_goals_2",
        "Percent_Draw AS Percent_Draw_2",
        "Number_Games_World_Tournament AS Number_Games_World_Tournament_2",
        "Percent_Victory_World_Tournament AS Percent_Victory_World_Tournament_2",
        "Percent_Victory_Away AS Percent_Victory_Away_2",
        "Percent_Victory_Continental_Tournament AS Percent_Victory_Continental_Tournament_2",
        "confederation AS confederation_2",
        "Percent_Victory_Home AS Percent_Victory_Home_2",
        "Avg_goals_conceded AS Avg_goals_conceded_2",
        "Number_Games_Continental_Tournament AS Number_Games_Continental_Tournament_2",
        "nb_Continental_Cup AS nb_Continental_Cup_2",
        "Percent_Victory AS Percent_Victory_2",
    ],
)

We can add dumies to do aggregations on the different games.

all_matchs["victory_team1"] = all_matchs["team1_score"] > all_matchs["team2_score"]
all_matchs["victory_team1"].astype("int")
all_matchs["draw"] = all_matchs["team1_score"] == all_matchs["team2_score"]
all_matchs["draw"].astype("int")
all_matchs["victory_team2"] = all_matchs["team1_score"] < all_matchs["team2_score"]
all_matchs["victory_team2"].astype("int")

	📅 date Date 100%	...	123 draw Int 100%	123 victory_team2 Int 100%
1	2017-10-10	...	0	1
2	2017-10-10	...	0	0
3	2017-10-10	...	1	0
4	2017-10-10	...	0	1
5	2017-10-10	...	0	0
6	2017-10-10	...	0	0
7	2017-10-10	...	0	0
8	2017-10-10	...	0	0
9	2017-10-10	...	0	0
10	2017-10-10	...	1	0
11	2017-10-10	...	0	0
12	2017-10-10	...	0	0
13	2017-10-11	...	0	0
14	2017-11-08	...	1	0
15	2017-11-09	...	0	1
16	2017-11-09	...	0	1
17	2017-11-09	...	0	0
18	2017-11-09	...	0	0
19	2017-11-10	...	0	0
20	2017-11-11	...	1	0

Let’s use moving windows to compute some additional features.

The teams’ performance in their recent games

# TEAM 1

# Victory 10 previous games
all_matchs.rolling(
    func = "avg",
    window = (-10, -1),
    columns = "victory_team1",
    by = ["team1"],
    order_by = ["date"],
    name = "avg_victory_team1_1_10",
)
# Victory 3 previous games
all_matchs.rolling(
    func = "avg",
    window = (-3, -1),
    columns = "victory_team1",
    by = ["team1"],
    order_by = ["date"],
    name = "avg_victory_team1_1_3",
)
# Draw 5 previous games
all_matchs.rolling(
    func = "avg",
    window = (-5, -1),
    columns = "draw",
    by = ["team1"],
    order_by = ["date"],
    name = "avg_draw_team1_1_5",
)

# TEAM 2

# Victory 10 previous games
all_matchs.rolling(
    func = "avg",
    window = (-10, -1),
    columns = "victory_team2",
    by = ["team2"],
    order_by = ["date"],
    name = "avg_victory_team2_1_10",
)
# Victory 3 previous games
all_matchs.rolling(
    func = "avg",
    window = (-3, -1),
    columns = "victory_team2",
    by = ["team2"],
    order_by = ["date"],
    name = "avg_victory_team2_1_3",
)
# Draw 5 previous games
all_matchs.rolling(
    func = "avg",
    window = (-5, -1),
    columns = "draw",
    by = ["team2"],
    order_by = ["date"],
    name = "avg_draw_team2_1_5",
)

	📅 date Date 100%	...	123 avg_victory_team2_1_3 Float(22) 99%	123 avg_draw_team2_1_5 Float(22) 99%
1	2001-06-30	...	[null]	[null]
2	2006-05-31	...	0.0	0.0
3	2006-11-21	...	0.0	0.0
4	2007-11-02	...	0.0	0.0
5	2018-05-31	...	0.0	0.0
6	2018-06-02	...	0.0	0.0
7	1915-01-03	...	[null]	[null]
8	1915-02-07	...	1.0	0.0
9	1915-05-13	...	0.5	0.5
10	1916-05-21	...	0.666666666666667	0.333333333333333
11	1916-05-22	...	0.666666666666667	0.25
12	1916-06-04	...	0.666666666666667	0.4
13	1924-06-15	...	0.666666666666667	0.4
14	1930-06-08	...	0.666666666666667	0.2
15	1931-01-01	...	1.0	0.2
16	1937-05-06	...	1.0	0.2
17	1937-06-05	...	0.666666666666667	0.0
18	1937-06-09	...	0.333333333333333	0.0
19	1937-08-22	...	0.333333333333333	0.0
20	1937-08-29	...	0.666666666666667	0.0

The teams’ performance in the last same tournament

# TEAM 1

# Victory 10 previous games
all_matchs.rolling(
    func = "avg",
    window = (-10, -1),
    columns = "victory_team1",
    by = ["team1", "tournament"],
    order_by = ["date"],
    name = "avg_victory_same_tournament_team1_1_10",
)
# Victory 3 previous games
all_matchs.rolling(
    func = "avg",
    window = (-3, -1),
    columns = "victory_team1",
    by = ["team1", "tournament"],
    order_by = ["date"],
    name = "avg_victory_same_tournament_team1_1_3",
)
# Draw 5 previous games
all_matchs.rolling(
    func = "avg",
    window = (-5, -1),
    columns = "draw",
    by = ["team1", "tournament"],
    order_by = ["date"],
    name = "avg_draw_same_tournament_team1_1_5",
)

# TEAM 2

# Victory 10 previous games
all_matchs.rolling(
    func = "avg",
    window = (-10, -1),
    columns = "victory_team2",
    by = ["team2", "tournament"],
    order_by = ["date"],
    name = "avg_victory_same_tournament_team2_1_10",
)
# Victory 3 previous games
all_matchs.rolling(
    func = "avg",
    window = (-3, -1),
    columns = "victory_team2",
    by = ["team2", "tournament"],
    order_by = ["date"],
    name = "avg_victory_same_tournament_team2_1_3",
)
# Draw 5 previous games
all_matchs.rolling(
    func = "avg",
    window = (-5, -1),
    columns = "draw",
    by = ["team2", "tournament"],
    order_by = ["date"],
    name = "avg_draw_same_tournament_team2_1_5",
)

	📅 date Date 100%	...	123 Float(22) 97%	123 Float(22) 97%
1	1946-10-07	...
2	1946-10-09	...
3	1946-10-13	...
4	1947-05-25	...
5	1947-06-15	...
6	1947-08-20	...
7	1947-09-14	...
8	1948-05-02	...
9	1948-05-23	...
10	1948-06-27	...
11	1955-08-14	...
12	1955-08-16	...
13	1955-08-19	...
14	1955-08-22	...
15	1955-08-24	...
16	1967-01-12	...
17	1967-01-14	...
18	1967-01-17	...
19	1967-01-20	...
20	1971-10-01	...

Direct Confrontation

# Victory 5 previous games
all_matchs.rolling(
    func = "avg",
    window = (-5, -1),
    columns = "victory_team1",
    by = ["team1", "team2"],
    order_by = ["date"],
    name = "avg_victory_direct_team1_1_5",
)
# Victory 3 previous games
all_matchs.rolling(
    func = "avg",
    window = (-3, -1),
    columns = "victory_team1",
    by = ["team1", "team2"],
    order_by = ["date"],
    name = "avg_victory_direct_team1_1_3",
)
# Draw 5 previous games
all_matchs.rolling(
    func = "avg",
    window = (-5, -1),
    columns = "draw",
    by = ["team1", "team2"],
    order_by = ["date"],
    name = "avg_draw_direct_team1_1_5",
)

	📅 date Date 100%	...	Abc tournament Varchar(84) 100%	123 avg_draw_direct_team1_1_5 Float(22) 84%
1	2002-10-12	...	UEFA Euro qualification	[null]
2	2003-06-11	...	UEFA Euro qualification	0.0
3	2014-10-10	...	UEFA Euro qualification	0.0
4	2015-10-10	...	UEFA Euro qualification	0.0
5	1934-05-27	...	FIFA World Cup	[null]
6	2002-06-12	...	FIFA World Cup	0.0
7	2013-02-06	...	Friendly	0.5
8	1998-08-18	...	Friendly	[null]
9	1987-06-09	...	Korea Cup	[null]
10	1996-06-02	...	FIFA World Cup qualification	[null]
11	1997-09-06	...	FIFA World Cup qualification	0.0
12	2014-11-16	...	UEFA Euro qualification	0.0
13	2015-06-12	...	UEFA Euro qualification	0.0
14	2016-10-08	...	FIFA World Cup qualification	0.25
15	2017-09-01	...	FIFA World Cup qualification	0.2
16	1936-12-27	...	Copa América	[null]
17	1942-01-21	...	Copa América	0.0
18	1949-04-24	...	Copa América	0.0
19	1952-04-10	...	Pan American Championship	0.0
20	1953-03-19	...	Copa América	0.25

Games against an opponents with the same rank

# TEAM 1

# Victory 5 previous games
all_matchs.rolling(
    func = "avg",
    window = (-5, -1),
    columns = "victory_team1",
    by = ["team1", "fifa_rank_2"],
    order_by = ["date"],
    name = "avg_victory_rank2_team1_1_5",
)
# Draw 5 previous games
all_matchs.rolling(
    func = "avg",
    window = (-5, -1),
    columns = "draw",
    by = ["team1", "fifa_rank_2"],
    order_by = ["date"],
    name = "avg_draw_rank2_team1_1_5",
)

# TEAM 2

# Victory 5 previous games
all_matchs.rolling(
    func = "avg",
    window = (-5, -1),
    columns = "victory_team2",
    by = ["team2", "fifa_rank_1"],
    order_by = ["date"],
    name = "avg_victory_rank1_team2_1_5",
)
# Draw 5 previous games
all_matchs.rolling(
    func = "avg",
    window = (-5, -1),
    columns = "draw",
    by = ["team2", "fifa_rank_1"],
    order_by = ["date"],
    name = "avg_draw_rank1_team2_1_5",
)

	📅 date Date 100%	...	Abc tournament Varchar(84) 100%	123 avg_draw_rank1_team2_1_5 Float(22) 98%
1	1937-10-13	...	Friendly	[null]
2	1952-11-29	...	Friendly	0.0
3	1957-05-01	...	FIFA World Cup qualification	0.0
4	1957-05-26	...	FIFA World Cup qualification	0.0
5	1970-10-07	...	UEFA Euro qualification	0.0
6	1971-04-21	...	UEFA Euro qualification	0.2
7	1971-06-16	...	UEFA Euro qualification	0.2
8	1971-10-27	...	UEFA Euro qualification	0.2
9	1975-04-20	...	UEFA Euro qualification	0.2
10	1975-11-23	...	UEFA Euro qualification	0.2
11	1977-03-30	...	FIFA World Cup qualification	0.0
12	1977-11-16	...	FIFA World Cup qualification	0.0
13	1980-11-19	...	FIFA World Cup qualification	0.0
14	1981-05-27	...	FIFA World Cup qualification	0.0
15	1981-09-09	...	FIFA World Cup qualification	0.0
16	1981-09-23	...	FIFA World Cup qualification	0.0
17	1983-03-27	...	UEFA Euro qualification	0.2
18	1983-04-16	...	UEFA Euro qualification	0.4
19	1986-05-29	...	Friendly	0.4
20	1986-10-15	...	UEFA Euro qualification	0.4

Games between teams with rank 1 and rank 2

# Victory 5 previous games
all_matchs.rolling(
    func = "avg",
    window = (-5, -1),
    columns = "victory_team1",
    by = ["fifa_rank_1", "fifa_rank_2"],
    order_by = ["date"],
    name = "avg_victory_rank1_rank2_team1_1_5",
)
# Draw 5 previous games
all_matchs.rolling(
    func = "avg",
    window = (-5, -1),
    columns = "draw",
    by = ["fifa_rank_1", "fifa_rank_2"],
    order_by = ["date"],
    name = "avg_draw_rank1_rank2_team1_1_5",
)

	📅 date Date 100%	...	Abc tournament Varchar(84) 100%	123 avg_draw_rank1_rank2_team1_1_5 Float(22) 99%
1	2016-08-26	...	World Unity Cup	[null]
2	2016-08-26	...	World Unity Cup	0.0
3	2018-04-08	...	Friendly	0.0
4	2018-04-08	...	Friendly	0.0
5	2018-05-31	...	CONIFA World Football Cup	0.0
6	2018-05-31	...	CONIFA World Football Cup	0.2
7	2018-06-07	...	CONIFA World Football Cup	0.4
8	2018-06-07	...	CONIFA World Football Cup	0.4
9	2018-06-09	...	CONIFA World Football Cup	0.4
10	2018-06-09	...	CONIFA World Football Cup	0.4
11	2016-05-29	...	CONIFA World Football Cup	[null]
12	2016-05-30	...	CONIFA World Football Cup	0.0
13	2016-05-31	...	CONIFA World Football Cup	0.0
14	2016-06-05	...	CONIFA World Football Cup	0.0
15	2016-08-25	...	World Unity Cup	0.25
16	2018-05-31	...	CONIFA World Football Cup	0.2
17	2018-06-03	...	CONIFA World Football Cup	0.2
18	2018-06-05	...	CONIFA World Football Cup	0.2
19	2019-06-03	...	CONIFA European Football Cup	0.2
20	2019-06-06	...	CONIFA European Football Cup	0.0

Before we use the neutral variable with our model, we should convert it to an integer.

We need also to create our response column: the outcome of the game.

all_matchs["neutral"].astype("int")
all_matchs.case_when(
    "result",
    all_matchs["team1_score"] > all_matchs["team2_score"], "1",
    all_matchs["team1_score"] < all_matchs["team2_score"], "2",
    "X",
)

	📅 date Date 100%	...	123 avg_draw_rank1_rank2_team1_1_5 Float(22) 99%	Abc result Varchar(1) 100%
1	1912-02-10	...	[null]	1
2	1912-12-01	...	0.0	2
3	1924-03-13	...	0.0	1
4	1934-02-02	...	0.0	1
5	1934-06-17	...	0.0	2
6	1934-06-24	...	0.0	X
7	1947-10-19	...	0.2	2
8	1953-08-09	...	0.2	1
9	1964-01-12	...	0.2	1
10	1990-05-08	...	0.2	X
11	1995-09-06	...	0.4	1
12	1998-12-22	...	0.2	2
13	2002-05-18	...	0.2	1
14	2002-09-07	...	0.2	1
15	2003-10-11	...	0.2	1
16	2003-12-27	...	0.0	2
17	2004-05-25	...	0.0	1
18	2004-10-13	...	0.0	1
19	2004-12-29	...	0.0	1
20	2005-03-30	...	0.0	1

We have some missing values here. This might be because the two teams never played together, the competition was one or both teams’ first, etc.

all_matchs.count()

	count
	82818.0
	82818.0
	82818.0
	82818.0
	82818.0
	82818.0
	82818.0
	82818.0
	82818.0
	82818.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82765.0
	82818.0
	82818.0
	82818.0
	82539.0
	82539.0
	82539.0
	82539.0
	82539.0
	82539.0
	80706.0
	80706.0
	80706.0
	80706.0
	80706.0
	80706.0
	69759.0
	69759.0
	69759.0
	81443.0
	81443.0
	81443.0
	81443.0
	82771.0
	82771.0
	82818.0

We need to impute these missing values.

all_matchs["avg_victory_direct_team1_1_5"] = fun.coalesce(
    all_matchs["avg_victory_direct_team1_1_5"],
    all_matchs["avg_victory_rank2_team1_1_5"],
    all_matchs["avg_victory_rank1_rank2_team1_1_5"],
)
all_matchs["avg_victory_direct_team1_1_3"] = fun.coalesce(
    all_matchs["avg_victory_direct_team1_1_3"],
    all_matchs["avg_victory_rank2_team1_1_5"],
    all_matchs["avg_victory_rank1_rank2_team1_1_5"],
)
all_matchs["avg_draw_direct_team1_1_5"] = fun.coalesce(
    all_matchs["avg_draw_direct_team1_1_5"],
    all_matchs["avg_draw_rank2_team1_1_5"],
    all_matchs["avg_draw_rank1_rank2_team1_1_5"],
)
all_matchs["avg_victory_same_tournament_team1_1_10"].fillna(expr = "avg_victory_team1_1_10")
all_matchs["avg_victory_same_tournament_team1_1_3"].fillna(expr = "avg_victory_team1_1_3")
all_matchs["avg_draw_same_tournament_team1_1_5"].fillna(expr = "avg_draw_team1_1_5")
all_matchs["avg_victory_same_tournament_team2_1_10"].fillna(expr = "avg_victory_team2_1_10")
all_matchs["avg_victory_same_tournament_team2_1_3"].fillna(expr = "avg_victory_team2_1_3")
all_matchs["avg_draw_same_tournament_team2_1_5"].fillna(expr = "avg_draw_team2_1_5")

	📅 date Date 100%	...	123 avg_draw_rank1_rank2_team1_1_5 Float(22) 99%	Abc result Varchar(1) 100%
1	1912-02-10	...	[null]	1
2	1912-12-01	...	0.0	2
3	1924-03-13	...	0.0	1
4	1934-02-02	...	0.0	1
5	1934-06-17	...	0.0	2
6	1934-06-24	...	0.0	X
7	1947-10-19	...	0.2	2
8	1953-08-09	...	0.2	1
9	1964-01-12	...	0.2	1
10	1990-05-08	...	0.2	X
11	1995-09-06	...	0.4	1
12	1998-12-22	...	0.2	2
13	2002-05-18	...	0.2	1
14	2002-09-07	...	0.2	1
15	2003-10-11	...	0.2	1
16	2003-12-27	...	0.0	2
17	2004-05-25	...	0.0	1
18	2004-10-13	...	0.0	1
19	2004-12-29	...	0.0	1
20	2005-03-30	...	0.0	1

Let’s export the result to our Vertica database using the variable match_sample to avoid counting the same game twice.

vp.drop("football_train", method = "table")
all_matchs.to_db(
    name = "football_train",
    relation_type = "table",
    db_filter = (fun.year(all_matchs["date"]) <= 2015) & (fun.year(all_matchs["date"]) > 1980) & (all_matchs["match_sample"] == 1),
)

vp.drop("football_test", method = "table")
all_matchs.to_db(
    name = "football_test",
    relation_type = "table",
    db_filter = (fun.year(all_matchs["date"]) > 2015) & (all_matchs["match_sample"] == 1),
)

	📅 date Date 100%	...	123 avg_draw_rank1_rank2_team1_1_5 Float(22) 99%	Abc result Varchar(1) 100%
1	1911-10-29	...	[null]	1
2	1913-04-20	...	0.0	1
3	1914-02-08	...	0.0	2
4	1927-05-21	...	0.0	1
5	1934-03-11	...	0.0	1
6	1934-04-15	...	0.0	1
7	1935-08-18	...	0.0	1
8	1936-09-27	...	0.0	1
9	1937-03-21	...	0.0	1
10	1938-03-20	...	0.0	1
11	1939-03-26	...	0.0	2
12	1951-12-23	...	0.0	1
13	1952-04-20	...	0.0	1
14	1953-09-20	...	0.0	1
15	1953-12-17	...	0.0	1
16	1954-03-28	...	0.0	1
17	1954-06-05	...	0.0	1
18	1957-03-10	...	0.0	1
19	1960-10-19	...	0.0	1
20	1961-09-28	...	0.0	1

Machine Learning

It’s time to make predictions about the outcomes of games. We have a lot of variables, so we need trees deep enough to pick up the most important features. We also need to consider a minimum number of games in each leaf to avoid over-fitting.

predictors = all_matchs.get_columns(
    exclude_columns = [
        "match_sample",
        "team2_score",
        "team1_score",
        "date",
        "city",
        "country",
        "result",
        "victory_team1",
        "victory_team2",
        "draw",
    ],
)


from verticapy.machine_learning.vertica import RandomForestClassifier

model = RandomForestClassifier(
    max_depth = 25,
    n_estimators = 20,
    sample = 0.7,
    nbins = 50,
    max_leaf_nodes = 11000,
    min_samples_leaf = 3,
)


model.fit(
    "football_train",
    predictors,
    "result",
    "football_test",
)



===========
call_string
===========
SELECT rf_classifier('"public"."_verticapy_tmp_randomforestclassifier_v_mldb_89b4b22097b111efa8720242ac120002_"', '"public"."_verticapy_tmp_view_v_mldb_8a08551a97b111efa8720242ac120002_"', 'result', '"tournament", "neutral", "team1", "team2", "nb_World_Cup_1", "fifa_rank_1", "Avg_goals_1", "Percent_Draw_1", "Number_Games_World_Tournament_1", "Percent_Victory_World_Tournament_1", "Percent_Victory_Away_1", "Percent_Victory_Continental_Tournament_1", "confederation_1", "Percent_Victory_Home_1", "Avg_goals_conceded_1", "Number_Games_Continental_Tournament_1", "nb_Continental_Cup_1", "Percent_Victory_1", "nb_World_Cup_2", "fifa_rank_2", "Avg_goals_2", "Percent_Draw_2", "Number_Games_World_Tournament_2", "Percent_Victory_World_Tournament_2", "Percent_Victory_Away_2", "Percent_Victory_Continental_Tournament_2", "confederation_2", "Percent_Victory_Home_2", "Avg_goals_conceded_2", "Number_Games_Continental_Tournament_2", "nb_Continental_Cup_2", "Percent_Victory_2", "avg_victory_team1_1_10", "avg_victory_team1_1_3", "avg_draw_team1_1_5", "avg_victory_team2_1_10", "avg_victory_team2_1_3", "avg_draw_team2_1_5", "avg_victory_same_tournament_team1_1_10", "avg_victory_same_tournament_team1_1_3", "avg_draw_same_tournament_team1_1_5", "avg_victory_same_tournament_team2_1_10", "avg_victory_same_tournament_team2_1_3", "avg_draw_same_tournament_team2_1_5", "avg_victory_direct_team1_1_5", "avg_victory_direct_team1_1_3", "avg_draw_direct_team1_1_5", "avg_victory_rank2_team1_1_5", "avg_draw_rank2_team1_1_5", "avg_victory_rank1_team2_1_5", "avg_draw_rank1_team2_1_5", "avg_victory_rank1_rank2_team1_1_5", "avg_draw_rank1_rank2_team1_1_5"' USING PARAMETERS exclude_columns='', ntree=20, mtry=18, sampling_size=0.7, max_depth=25, max_breadth=11000, min_leaf_size=3, min_info_gain=0, nbins=50);

=======
details
=======
               predictor                |      type      
----------------------------------------+----------------
               tournament               |char or varchar 
                neutral                 |      int       
                 team1                  |char or varchar 
                 team2                  |char or varchar 
             nb_world_cup_1             |      int       
              fifa_rank_1               |      int       
              avg_goals_1               |float or numeric
             percent_draw_1             |float or numeric
    number_games_world_tournament_1     |float or numeric
   percent_victory_world_tournament_1   |float or numeric
         percent_victory_away_1         |float or numeric
percent_victory_continental_tournament_1|float or numeric
            confederation_1             |char or varchar 
         percent_victory_home_1         |float or numeric
          avg_goals_conceded_1          |float or numeric
 number_games_continental_tournament_1  |float or numeric
          nb_continental_cup_1          |float or numeric
           percent_victory_1            |float or numeric
             nb_world_cup_2             |      int       
              fifa_rank_2               |      int       
              avg_goals_2               |float or numeric
             percent_draw_2             |float or numeric
    number_games_world_tournament_2     |float or numeric
   percent_victory_world_tournament_2   |float or numeric
         percent_victory_away_2         |float or numeric
percent_victory_continental_tournament_2|float or numeric
            confederation_2             |char or varchar 
         percent_victory_home_2         |float or numeric
          avg_goals_conceded_2          |float or numeric
 number_games_continental_tournament_2  |float or numeric
          nb_continental_cup_2          |float or numeric
           percent_victory_2            |float or numeric
         avg_victory_team1_1_10         |float or numeric
         avg_victory_team1_1_3          |float or numeric
           avg_draw_team1_1_5           |float or numeric
         avg_victory_team2_1_10         |float or numeric
         avg_victory_team2_1_3          |float or numeric
           avg_draw_team2_1_5           |float or numeric
 avg_victory_same_tournament_team1_1_10 |float or numeric
 avg_victory_same_tournament_team1_1_3  |float or numeric
   avg_draw_same_tournament_team1_1_5   |float or numeric
 avg_victory_same_tournament_team2_1_10 |float or numeric
 avg_victory_same_tournament_team2_1_3  |float or numeric
   avg_draw_same_tournament_team2_1_5   |float or numeric
      avg_victory_direct_team1_1_5      |float or numeric
      avg_victory_direct_team1_1_3      |float or numeric
       avg_draw_direct_team1_1_5        |float or numeric
      avg_victory_rank2_team1_1_5       |float or numeric
        avg_draw_rank2_team1_1_5        |float or numeric
      avg_victory_rank1_team2_1_5       |float or numeric
        avg_draw_rank1_team2_1_5        |float or numeric
   avg_victory_rank1_rank2_team1_1_5    |float or numeric
     avg_draw_rank1_rank2_team1_1_5     |float or numeric


===============
Additional Info
===============
       Name       |Value
------------------+-----
    tree_count    | 20  
rejected_row_count| 478 
accepted_row_count|25465

model.classification_report()

	...	avg_weighted	avg_micro
auc	...	0.719654661347637	[null]
prc_auc	...	0.586595555622269	[null]
accuracy	...	0.6963658093979823	0.7097196751375425
log_loss	...	0.2409562168930823	[null]
precision	...	0.5275577796685457	0.5645795127063139
recall	...	0.5645795127063139	0.5645795127063139
f1_score	...	0.5214833752976378	0.5645795127063139
mcc	...	0.277039975154262	0.3468692690594708
informedness	...	0.2571720491275245	0.3468692690594708
markedness	...	0.3019689951643432	0.3468692690594708
csi	...	0.375995498672392	0.3933199488957839

Our model is excellent! 57% of accuracy on 3 categories - it’s almost twice as good as a random model.

model.score(metric = "accuracy")
Out[27]: 0.5645795127063139

Looking at the importance of each feature, it seems like direct confrontations and victories against teams of another rank seem to be the strongest indicators of a team’s success.

model.features_importance()

Let’s add the predictions to the vDataFrame.

Draws are pretty rare, so we’ll only consider them if a tie was very likely to occur.

test = vp.vDataFrame("football_test")
model.predict_proba(test, name = "prob_1", pos_label = "1")
model.predict_proba(test, name = "prob_X", pos_label = "X")
model.predict_proba(test, name = "prob_2", pos_label = "2")
test.case_when(
    "prediction",
    test["prob_1"] > test["prob_2"] + 0.05, "1",
    test["prob_2"] > test["prob_1"] + 0.05, "2",
    (test["prob_X"] > test["prob_1"]) & (test["prob_X"] > test["prob_2"]), "X",
    fun.abs(test["prob_1"] - test["prob_2"]) < 0.03, "X",
    test["prob_1"] > test["prob_2"], "1",
    test["prob_1"] < test["prob_2"], "2",
)

	📅 date Date 100%	...	Abc prob_2 Varchar(128) 98%	Abc prediction Varchar(1) 98%
1	2016-05-31	...	[null]	[null]
2	2016-06-01	...	[null]	[null]
3	2016-06-03	...	[null]	[null]
4	2016-06-04	...	[null]	[null]
5	2018-06-05	...	[null]	[null]
6	2018-11-12	...	[null]	[null]
7	2019-03-19	...	[null]	[null]
8	2019-11-19	...	[null]	[null]
9	2018-10-13	...	[null]	[null]
10	2016-05-22	...	[null]	[null]
11	2016-05-31	...	0.892862	2
12	2016-06-03	...	0.583194	2
13	2016-09-04	...	0.850423	2
14	2016-09-06	...	0.857527	2
15	2016-10-06	...	0.611026	2
16	2016-10-07	...	0.902463	2
17	2016-10-10	...	0.917926	2
18	2016-10-10	...	0.920581	2
19	2016-11-13	...	0.919299	2
20	2017-03-26	...	0.923645	2

Let’s look at our predictions for the 2018 World Cup.

test.search(
    conditions = [test["tournament"] == 'FIFA World Cup'],
    usecols = [
        "date",
        "team1",
        "result",
        "prediction",
        "team2",
        "prob_1",
        "prob_X",
        "prob_2",
    ],
    order_by = ["date"],
).head(128)

	📅 date Date 100%	...	Abc prob_X Varchar(128) 100%	Abc prob_2 Varchar(128) 100%
1	2018-06-14	...	0.112169	0.141219
2	2018-06-15	...	0.44308	0.211021
3	2018-06-15	...	0.389803	0.402894
4	2018-06-15	...	0.230963	0.54255
5	2018-06-16	...	0.156613	0.0941131
6	2018-06-16	...	0.229146	0.133139
7	2018-06-16	...	0.2637	0.396381
8	2018-06-16	...	0.282537	0.203966
9	2018-06-17	...	0.330522	0.132605
10	2018-06-17	...	0.201289	0.17434
11	2018-06-17	...	0.335948	0.316127
12	2018-06-18	...	0.509656	0.369847
13	2018-06-18	...	0.301883	0.133133
14	2018-06-18	...	0.283382	0.403889
15	2018-06-19	...	0.195266	0.154328
16	2018-06-19	...	0.24874	0.268448
17	2018-06-19	...	0.233489	0.225991
18	2018-06-20	...	0.299991	0.254443
19	2018-06-20	...	0.269269	0.193197
20	2018-06-20	...	0.193842	0.662316
21	2018-06-21	...	0.33371	0.209679
22	2018-06-21	...	0.197554	0.178763
23	2018-06-21	...	0.239419	0.188169
24	2018-06-22	...	0.0964599	0.0964599
25	2018-06-22	...	0.167057	0.352703
26	2018-06-22	...	0.299254	0.357587
27	2018-06-23	...	0.176943	0.274156
28	2018-06-23	...	0.433873	0.171731
29	2018-06-23	...	0.283584	0.349558
30	2018-06-24	...	0.188604	0.0923535
31	2018-06-24	...	0.347334	0.266015
32	2018-06-24	...	0.240391	0.283395
33	2018-06-25	...	0.306332	0.21782
34	2018-06-25	...	0.301819	0.180569
35	2018-06-25	...	0.213182	0.364591
36	2018-06-25	...	0.205417	0.128935
37	2018-06-26	...	0.239203	0.130096
38	2018-06-26	...	0.295467	0.502459
39	2018-06-26	...	0.218051	0.51336
40	2018-06-26	...	0.1751	0.52045
41	2018-06-27	...	0.225821	0.29528
42	2018-06-27	...	0.284893	0.382993
43	2018-06-27	...	0.228965	0.562647
44	2018-06-27	...	0.366963	0.349914
45	2018-06-28	...	0.333226	0.223011
46	2018-06-28	...	0.287183	0.398119
47	2018-06-28	...	0.560289	0.154843
48	2018-06-28	...	0.290935	0.206162
49	2018-06-30	...	0.464658	0.205698
50	2018-06-30	...	0.329075	0.249336
51	2018-07-01	...	0.383564	0.266147
52	2018-07-01	...	0.408006	0.27107
53	2018-07-02	...	0.139732	0.0897325
54	2018-07-02	...	0.189841	0.239945
55	2018-07-03	...	0.367382	0.42055
56	2018-07-03	...	0.408207	0.259183
57	2018-07-06	...	0.22513	0.158969
58	2018-07-06	...	0.258165	0.3237
59	2018-07-07	...	0.554583	0.180844
60	2018-07-07	...	0.263855	0.273384
61	2018-07-10	...	0.235566	0.302676
62	2018-07-11	...	0.457346	0.259006
63	2018-07-14	...	0.245901	0.364848
64	2018-07-15	...	0.306994	0.318836

Fantastic: we built a very efficient model which predicted that France will win almost all of its games (except the game against Argentina which is really hard to predict). In reality, France did indeed win the 2018 World Cup!

test.search(
    conditions = [
        test["tournament"] == 'FIFA World Cup',
        (test["team1"] == 'France') | (test["team2"] == 'France'),
    ],
    usecols = [
        "date",
        "team1",
        "result",
        "prediction",
        "team2",
        "prob_1",
        "prob_X",
        "prob_2",
    ],
    order_by = ["date"],
).head(128)

	📅 date Date 100%	...	Abc prob_X Varchar(128) 100%	Abc prob_2 Varchar(128) 100%
1	2018-06-16	...	0.229146	0.133139
2	2018-06-21	...	0.197554	0.178763
3	2018-06-26	...	0.218051	0.51336
4	2018-06-30	...	0.329075	0.249336
5	2018-07-06	...	0.258165	0.3237
6	2018-07-10	...	0.235566	0.302676
7	2018-07-15	...	0.306994	0.318836

Conclusion

We’ve solved our problem in a Pandas-like way, all without ever loading data into memory!