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")
football.head(5)

Out[9]:

	📅 date Date	Abc home_team Varchar(64)	Abc away_team Varchar(64)	123 home_score Int	123 away_score Int	Abc tournament Varchar(84)	Abc city Varchar(56)	Abc country Varchar(64)	010 neutral Boolean
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]:

	📅 date Date	123 home_score Int	Abc home_team Varchar(64)	Abc tournament Varchar(84)	Abc away_team Varchar(64)	123 away_score Int	010 neutral Boolean	Abc country Varchar(64)	Abc city Varchar(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]:

	📅 date Date	123 home_score Int	Abc home_team Varchar(64)	Abc tournament Varchar(84)	Abc away_team Varchar(64)	123 away_score Int	010 neutral Boolean	Abc country Varchar(64)	Abc city Varchar(56)	Abc winner Varchar(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	British Championship	Northern Ireland	1	❌	England	Birmingham	England
88	1893-03-13	6	England	British Championship	Wales	0	❌	England	Stoke-on-Trent	England
89	1893-03-18	0	Wales	British Championship	Scotland	8	❌	Wales	Wrexham	Scotland
90	1893-03-25	6	Scotland	British Championship	Northern Ireland	1	❌	Scotland	Glasgow	Scotland
91	1893-04-01	5	England	British Championship	Scotland	2	❌	England	Richmond	England
92	1894-02-24	4	Wales	British Championship	Northern Ireland	1	❌	Wales	Swansea	Wales
93	1894-03-03	2	Northern Ireland	British Championship	England	2	❌	Republic of Ireland	Belfast	[null]
94	1894-03-12	1	Wales	British Championship	England	5	❌	Wales	Wrexham	England
95	1894-03-24	5	Scotland	British Championship	Wales	2	❌	Scotland	Kilmarnock	Scotland
96	1894-03-31	1	Northern Ireland	British Championship	Scotland	2	❌	Republic of Ireland	Belfast	Scotland
97	1894-04-07	2	Scotland	British Championship	England	2	❌	Scotland	Glasgow	[null]
98	1895-03-09	9	England	British Championship	Northern Ireland	0	❌	England	Derby	England
99	1895-03-16	2	Northern Ireland	British Championship	Wales	2	❌	Republic of Ireland	Belfast	[null]
100	1895-03-18	1	England	British Championship	Wales	1	❌	England	London	[null]

Rows: 1-100 of 37525 | Columns: 10

Let's analyze the last game of each tournament.

In [14]:

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

Out[14]:

	📅 date Date	123 home_score Int	Abc home_team Varchar(64)	Abc tournament Varchar(84)	Abc away_team Varchar(64)	123 away_score Int	010 neutral Boolean	Abc country Varchar(64)	Abc city Varchar(56)	Abc winner Varchar(64)	123 year Integer	123 order_tournament Integer
1	2010-10-31	3	Aruba	ABCS Tournament	Bonaire	3	✅	Curaçao	Willemstad	[null]	2010	1
2	2010-10-31	2	Curaçao	ABCS Tournament	Suriname	2	❌	Curaçao	Willemstad	[null]	2010	2
3	2010-10-29	4	Suriname	ABCS Tournament	Bonaire	2	✅	Curaçao	Willemstad	Suriname	2010	3
4	2010-10-29	3	Curaçao	ABCS Tournament	Aruba	0	❌	Curaçao	Willemstad	Curaçao	2010	4
5	2011-12-04	2	Suriname	ABCS Tournament	Curaçao	0	❌	Suriname	Paramaribo	Suriname	2011	1
6	2011-12-04	2	Bonaire	ABCS Tournament	Aruba	2	✅	Suriname	Paramaribo	[null]	2011	2
7	2011-12-02	1	Curaçao	ABCS Tournament	Bonaire	3	✅	Suriname	Paramaribo	Bonaire	2011	3
8	2011-12-02	0	Suriname	ABCS Tournament	Aruba	0	❌	Suriname	Paramaribo	[null]	2011	4
9	2012-07-15	2	Bonaire	ABCS Tournament	Curaçao	9	✅	Aruba	Oranjestad	Curaçao	2012	1
10	2012-07-15	1	Aruba	ABCS Tournament	Suriname	0	❌	Aruba	Oranjestad	Aruba	2012	2
11	2012-07-13	8	Suriname	ABCS Tournament	Bonaire	0	✅	Aruba	Oranjestad	Suriname	2012	3
12	2012-07-13	3	Aruba	ABCS Tournament	Curaçao	2	❌	Aruba	Oranjestad	Aruba	2012	4
13	2013-11-16	1	Curaçao	ABCS Tournament	Suriname	3	❌	Curaçao	Willemstad	Suriname	2013	1
14	2013-11-16	1	Aruba	ABCS Tournament	Bonaire	2	✅	Curaçao	Willemstad	Bonaire	2013	2
15	2013-11-14	2	Curaçao	ABCS Tournament	Aruba	0	❌	Curaçao	Willemstad	Curaçao	2013	3
16	2013-11-14	0	Bonaire	ABCS Tournament	Suriname	2	✅	Curaçao	Willemstad	Suriname	2013	4
17	2015-02-01	4	Curaçao	ABCS Tournament	Bonaire	1	✅	Suriname	Paramaribo	Curaçao	2015	1
18	2015-02-01	1	Suriname	ABCS Tournament	Aruba	0	❌	Suriname	Paramaribo	Suriname	2015	2
19	2015-01-30	3	Suriname	ABCS Tournament	Bonaire	0	❌	Suriname	Paramaribo	Suriname	2015	3
20	2015-01-30	0	Curaçao	ABCS Tournament	Aruba	0	✅	Suriname	Paramaribo	[null]	2015	4
21	1956-09-15	5	South Korea	AFC Asian Cup	Vietnam Republic	3	✅	Hong Kong	So Kon Po	South Korea	1956	1
22	1956-09-12	2	Israel	AFC Asian Cup	Vietnam Republic	1	✅	Hong Kong	So Kon Po	Israel	1956	2
23	1956-09-09	2	Hong Kong	AFC Asian Cup	Vietnam Republic	2	❌	Hong Kong	So Kon Po	[null]	1956	3
24	1956-09-08	1	Israel	AFC Asian Cup	South Korea	2	✅	Hong Kong	So Kon Po	South Korea	1956	4
25	1956-09-06	2	Hong Kong	AFC Asian Cup	South Korea	2	❌	Hong Kong	So Kon Po	[null]	1956	5
26	1956-09-01	2	Hong Kong	AFC Asian Cup	Israel	3	❌	Hong Kong	So Kon Po	Israel	1956	6
27	1960-10-21	1	South Korea	AFC Asian Cup	Chinese Taipei	0	❌	South Korea	Seoul	South Korea	1960	1
28	1960-10-20	1	Israel	AFC Asian Cup	Chinese Taipei	0	✅	South Korea	Seoul	Israel	1960	2
29	1960-10-19	5	Israel	AFC Asian Cup	Vietnam Republic	1	✅	South Korea	Seoul	Israel	1960	3
30	1960-10-17	3	South Korea	AFC Asian Cup	Israel	0	❌	South Korea	Seoul	South Korea	1960	4
31	1960-10-17	2	Chinese Taipei	AFC Asian Cup	Vietnam Republic	0	✅	South Korea	Seoul	Chinese Taipei	1960	5
32	1960-10-14	5	South Korea	AFC Asian Cup	Vietnam Republic	1	❌	South Korea	Seoul	South Korea	1960	6
33	1964-06-03	2	Israel	AFC Asian Cup	South Korea	1	❌	Israel	Ramat-Gan	Israel	1964	1
34	1964-06-02	1	Hong Kong	AFC Asian Cup	India	3	✅	Israel	Jaffa	India	1964	2
35	1964-05-31	0	Hong Kong	AFC Asian Cup	South Korea	1	✅	Israel	Jerusalem	South Korea	1964	3
36	1964-05-29	2	Israel	AFC Asian Cup	India	0	❌	Israel	Jaffa	Israel	1964	4
37	1964-05-27	2	India	AFC Asian Cup	South Korea	0	✅	Israel	Haifa	India	1964	5
38	1964-05-26	1	Israel	AFC Asian Cup	Hong Kong	0	❌	Israel	Ramat-Gan	Israel	1964	6
39	1968-05-19	2	Iran	AFC Asian Cup	Israel	1	❌	Iran	Teheran	Iran	1968	1
40	1968-05-18	2	Burma	AFC Asian Cup	Hong Kong	0	✅	Iran	Teheran	Burma	1968	2
41	1968-05-17	4	Israel	AFC Asian Cup	Chinese Taipei	1	✅	Iran	Teheran	Israel	1968	3
42	1968-05-16	3	Iran	AFC Asian Cup	Burma	1	❌	Iran	Teheran	Iran	1968	4
43	1968-05-15	1	Hong Kong	AFC Asian Cup	Chinese Taipei	1	✅	Iran	Teheran	[null]	1968	5
44	1968-05-14	1	Burma	AFC Asian Cup	Israel	0	✅	Iran	Teheran	Burma	1968	6
45	1968-05-13	4	Iran	AFC Asian Cup	Chinese Taipei	0	❌	Iran	Teheran	Iran	1968	7
46	1968-05-12	1	Hong Kong	AFC Asian Cup	Israel	6	✅	Iran	Teheran	Israel	1968	8
47	1968-05-11	1	Burma	AFC Asian Cup	Chinese Taipei	1	✅	Iran	Teheran	[null]	1968	9
48	1968-05-10	2	Iran	AFC Asian Cup	Hong Kong	0	❌	Iran	Teheran	Iran	1968	10
49	1972-05-19	2	Thailand	AFC Asian Cup	Cambodia	2	❌	Thailand	Bangkok	[null]	1972	1
50	1972-05-19	2	Iran	AFC Asian Cup	South Korea	1	✅	Thailand	Bangkok	Iran	1972	2
51	1972-05-17	1	Thailand	AFC Asian Cup	South Korea	1	❌	Thailand	Bangkok	[null]	1972	3
52	1972-05-16	2	Iran	AFC Asian Cup	Cambodia	1	✅	Thailand	Bangkok	Iran	1972	4
53	1972-05-14	4	Cambodia	AFC Asian Cup	Kuwait	0	✅	Thailand	Bangkok	Cambodia	1972	5
54	1972-05-13	2	Thailand	AFC Asian Cup	Iran	3	❌	Thailand	Bangkok	Iran	1972	6
55	1972-05-12	1	South Korea	AFC Asian Cup	Kuwait	2	✅	Thailand	Bangkok	Kuwait	1972	7
56	1972-05-11	1	Thailand	AFC Asian Cup	Iraq	1	❌	Thailand	Bangkok	[null]	1972	8
57	1972-05-10	1	Cambodia	AFC Asian Cup	South Korea	4	✅	Thailand	Bangkok	South Korea	1972	9
58	1972-05-09	3	Iran	AFC Asian Cup	Iraq	0	✅	Thailand	Bangkok	Iran	1972	10
59	1972-05-08	0	Thailand	AFC Asian Cup	Kuwait	2	❌	Thailand	Bangkok	Kuwait	1972	11
60	1972-05-07	2	Iran	AFC Asian Cup	Cambodia	0	✅	Thailand	Bangkok	Iran	1972	12
61	1972-05-07	0	South Korea	AFC Asian Cup	Iraq	0	✅	Thailand	Bangkok	[null]	1972	13
62	1976-06-13	1	Iran	AFC Asian Cup	Kuwait	0	❌	Iran	Teheran	Iran	1976	1
63	1976-06-13	1	China PR	AFC Asian Cup	Iraq	0	✅	Iran	Teheran	China PR	1976	2
64	1976-06-10	2	Iraq	AFC Asian Cup	Kuwait	3	✅	Iran	Teheran	Kuwait	1976	3
65	1976-06-10	2	Iran	AFC Asian Cup	China PR	0	❌	Iran	Teheran	Iran	1976	4
66	1976-06-08	8	Iran	AFC Asian Cup	Yemen	0	❌	Iran	Teheran	Iran	1976	5
67	1976-06-07	0	China PR	AFC Asian Cup	Kuwait	1	✅	Iran	Teheran	Kuwait	1976	6
68	1976-06-06	1	Iraq	AFC Asian Cup	Yemen	0	✅	Iran	Teheran	Iraq	1976	7
69	1976-06-05	1	China PR	AFC Asian Cup	Malaysia	1	✅	Iran	Teheran	[null]	1976	8
70	1976-06-04	2	Iran	AFC Asian Cup	Iraq	0	❌	Iran	Teheran	Iran	1976	9
71	1976-06-03	2	Kuwait	AFC Asian Cup	Malaysia	0	✅	Iran	Teheran	Kuwait	1976	10
72	1980-09-30	3	Kuwait	AFC Asian Cup	South Korea	0	❌	Kuwait	Kuwait City	Kuwait	1980	1
73	1980-09-29	3	Iran	AFC Asian Cup	North Korea	0	✅	Kuwait	Kuwait City	Iran	1980	2
74	1980-09-28	2	South Korea	AFC Asian Cup	North Korea	1	✅	Kuwait	Kuwait City	South Korea	1980	3
75	1980-09-28	2	Kuwait	AFC Asian Cup	Iran	1	❌	Kuwait	Kuwait City	Kuwait	1980	4
76	1980-09-26	1	Syria	AFC Asian Cup	North Korea	2	✅	Kuwait	Kuwait City	North Korea	1980	5
77	1980-09-25	6	China PR	AFC Asian Cup	Bangladesh	0	✅	Kuwait	Kuwait City	China PR	1980	6
78	1980-09-25	4	Kuwait	AFC Asian Cup	Qatar	0	❌	Kuwait	Kuwait City	Kuwait	1980	7
79	1980-09-24	4	South Korea	AFC Asian Cup	United Arab Emirates	1	✅	Kuwait	Kuwait City	South Korea	1980	8
80	1980-09-24	3	Iran	AFC Asian Cup	North Korea	2	✅	Kuwait	Kuwait City	Iran	1980	9
81	1980-09-23	1	Syria	AFC Asian Cup	China PR	0	✅	Kuwait	Kuwait City	Syria	1980	10
82	1980-09-23	1	Malaysia	AFC Asian Cup	Qatar	1	✅	Kuwait	Kuwait City	[null]	1980	11
83	1980-09-22	0	Bangladesh	AFC Asian Cup	Iran	7	✅	Kuwait	Kuwait City	Iran	1980	12
84	1980-09-21	0	Kuwait	AFC Asian Cup	South Korea	3	❌	Kuwait	Kuwait City	South Korea	1980	13
85	1980-09-20	2	China PR	AFC Asian Cup	Iran	2	✅	Kuwait	Kuwait City	[null]	1980	14
86	1980-09-20	0	United Arab Emirates	AFC Asian Cup	Malaysia	2	✅	Kuwait	Kuwait City	Malaysia	1980	15
87	1980-09-19	0	Qatar	AFC Asian Cup	South Korea	2	✅	Kuwait	Kuwait City	South Korea	1980	16
88	1980-09-19	0	Bangladesh	AFC Asian Cup	Syria	1	✅	Kuwait	Kuwait City	Syria	1980	17
89	1980-09-18	3	Kuwait	AFC Asian Cup	Malaysia	1	❌	Kuwait	Kuwait City	Kuwait	1980	18
90	1980-09-18	2	North Korea	AFC Asian Cup	China PR	1	✅	Kuwait	Kuwait City	North Korea	1980	19
91	1980-09-17	2	Qatar	AFC Asian Cup	United Arab Emirates	1	✅	Kuwait	Kuwait City	Qatar	1980	20
92	1980-09-17	0	Iran	AFC Asian Cup	Syria	0	✅	Kuwait	Kuwait City	[null]	1980	21
93	1980-09-16	3	North Korea	AFC Asian Cup	Bangladesh	2	✅	Kuwait	Kuwait City	North Korea	1980	22
94	1980-09-16	1	Malaysia	AFC Asian Cup	South Korea	1	✅	Kuwait	Kuwait City	[null]	1980	23
95	1980-09-15	1	Kuwait	AFC Asian Cup	United Arab Emirates	1	❌	Kuwait	Kuwait City	[null]	1980	24
96	1984-12-16	2	Saudi Arabia	AFC Asian Cup	China PR	0	✅	Singapore	Kallang	Saudi Arabia	1984	1
97	1984-12-16	1	Iran	AFC Asian Cup	Kuwait	1	✅	Singapore	Kallang	[null]	1984	2
98	1984-12-14	0	Kuwait	AFC Asian Cup	China PR	1	✅	Singapore	Kallang	China PR	1984	3
99	1984-12-13	1	Saudi Arabia	AFC Asian Cup	Iran	1	✅	Singapore	Kallang	[null]	1984	4
100	1984-12-11	5	China PR	AFC Asian Cup	United Arab Emirates	0	✅	Singapore	Kallang	China PR	1984	5

Rows: 1-100 of 37525 | Columns: 12

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

In [15]:

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"])])

37418 elements were filtered

Out[15]:

	📅 date Date	123 home_score Int	Abc home_team Varchar(64)	Abc tournament Varchar(84)	Abc away_team Varchar(64)	123 away_score Int	010 neutral Boolean	Abc country Varchar(64)	Abc city Varchar(56)	Abc winner Varchar(64)	123 year Integer	123 order_tournament Integer
1	1956-09-15	5	South Korea	AFC Asian Cup	Vietnam Republic	3	✅	Hong Kong	So Kon Po	South Korea	1956	1
2	1960-10-21	1	South Korea	AFC Asian Cup	Chinese Taipei	0	❌	South Korea	Seoul	South Korea	1960	1
3	1964-06-03	2	Israel	AFC Asian Cup	South Korea	1	❌	Israel	Ramat-Gan	Israel	1964	1
4	1968-05-19	2	Iran	AFC Asian Cup	Israel	1	❌	Iran	Teheran	Iran	1968	1
5	1972-05-19	2	Iran	AFC Asian Cup	South Korea	1	✅	Thailand	Bangkok	Iran	1972	1
6	1976-06-13	1	China PR	AFC Asian Cup	Iraq	0	✅	Iran	Teheran	China PR	1976	1
7	1980-09-30	3	Kuwait	AFC Asian Cup	South Korea	0	❌	Kuwait	Kuwait City	Kuwait	1980	1
8	2000-10-29	0	China PR	AFC Asian Cup	South Korea	1	✅	Lebanon	Beirut	South Korea	2000	1
9	2004-08-07	1	China PR	AFC Asian Cup	Japan	3	❌	China PR	Beijing	Japan	2004	1
10	2007-07-29	1	Iraq	AFC Asian Cup	Saudi Arabia	0	✅	Indonesia	Jakarta	Iraq	2007	1
11	2011-01-29	0	Australia	AFC Asian Cup	Japan	1	✅	Qatar	Doha	Japan	2011	1
12	2015-01-31	2	Australia	AFC Asian Cup	South Korea	1	❌	Australia	Sydney	Australia	2015	1
13	1957-02-16	4	Egypt	African Cup of Nations	Ethiopia	0	✅	Sudan	Khartoum	Egypt	1957	1
14	1959-05-29	2	Egypt	African Cup of Nations	Sudan	1	✅	United Arab Republic	Cairo	Egypt	1959	1
15	1962-01-21	4	Ethiopia	African Cup of Nations	Egypt	2	❌	Ethiopia	Addis Abeba	Ethiopia	1962	1
16	1963-12-01	3	Ghana	African Cup of Nations	Sudan	0	❌	Ghana	Accra	Ghana	1963	1
17	1965-11-21	1	Ivory Coast	African Cup of Nations	Senegal	0	✅	Tunisia	Tunis	Ivory Coast	1965	1
18	1968-01-21	0	Ethiopia	African Cup of Nations	Ivory Coast	1	❌	Ethiopia	Addis Abeba	Ivory Coast	1968	1
19	1970-02-16	1	Sudan	African Cup of Nations	Ghana	0	❌	Sudan	Khartoum	Sudan	1970	1
20	1972-03-05	3	Congo	African Cup of Nations	Mali	2	✅	Cameroon	Yaoundé	Congo	1972	1
21	1974-03-14	2	DR Congo	African Cup of Nations	Zambia	0	✅	Egypt	Cairo	DR Congo	1974	1
22	1978-03-16	2	Ghana	African Cup of Nations	Uganda	0	❌	Ghana	Accra	Ghana	1978	1
23	1980-03-22	3	Nigeria	African Cup of Nations	Algeria	0	❌	Nigeria	Lagos	Nigeria	1980	1
24	1984-03-18	3	Cameroon	African Cup of Nations	Nigeria	1	✅	Ivory Coast	Abidjan	Cameroon	1984	1
25	1988-03-27	1	Cameroon	African Cup of Nations	Nigeria	0	✅	Morocco	Casablanca	Cameroon	1988	1
26	1990-03-16	1	Algeria	African Cup of Nations	Nigeria	0	❌	Algeria	Algiers	Algeria	1990	1
27	1994-04-10	2	Nigeria	African Cup of Nations	Zambia	1	✅	Tunisia	Tunis	Nigeria	1994	1
28	1996-02-03	1	Zambia	African Cup of Nations	Ghana	0	✅	South Africa	Johannesburg	Zambia	1996	1
29	1998-02-28	0	South Africa	African Cup of Nations	Egypt	2	✅	Burkina Faso	Ouagadougou	Egypt	1998	1
30	2004-02-14	2	Tunisia	African Cup of Nations	Morocco	1	❌	Tunisia	Radès	Tunisia	2004	1
31	2008-02-10	0	Cameroon	African Cup of Nations	Egypt	1	✅	Ghana	Accra	Egypt	2008	1
32	2010-01-31	0	Ghana	African Cup of Nations	Egypt	1	✅	Angola	Luanda	Egypt	2010	1
33	2013-02-10	1	Nigeria	African Cup of Nations	Burkina Faso	0	✅	South Africa	Johannesburg	Nigeria	2013	1
34	1917-10-14	1	Uruguay	Copa América	Argentina	0	❌	Uruguay	Montevideo	Uruguay	1917	1
35	1919-05-29	1	Brazil	Copa América	Uruguay	0	❌	Brazil	Rio de Janeiro	Brazil	1919	1
36	1920-10-03	1	Chile	Copa América	Uruguay	2	❌	Chile	Viña del Mar	Uruguay	1920	1
37	1921-10-30	1	Argentina	Copa América	Uruguay	0	❌	Argentina	Buenos Aires	Argentina	1921	1
38	1922-10-22	3	Brazil	Copa América	Paraguay	0	❌	Brazil	Rio de Janeiro	Brazil	1922	1
39	1923-12-02	2	Uruguay	Copa América	Argentina	0	❌	Uruguay	Montevideo	Uruguay	1923	1
40	1926-11-03	5	Chile	Copa América	Paraguay	1	❌	Chile	Santiago	Chile	1926	1
41	1927-11-27	1	Peru	Copa América	Argentina	5	❌	Peru	Lima	Argentina	1927	1
42	1929-11-17	2	Argentina	Copa América	Uruguay	0	❌	Argentina	Buenos Aires	Argentina	1929	1
43	1935-01-27	0	Argentina	Copa América	Uruguay	3	✅	Peru	Lima	Uruguay	1935	1
44	1936-12-30	2	Argentina	Copa América	Chile	1	❌	Argentina	Buenos Aires	Argentina	1936	1
45	1937-02-01	2	Argentina	Copa América	Brazil	0	❌	Argentina	Buenos Aires	Argentina	1937	1
46	1939-02-12	1	Ecuador	Copa América	Paraguay	3	✅	Peru	Lima	Paraguay	1939	1
47	1941-03-04	0	Chile	Copa América	Argentina	1	❌	Chile	Santiago	Argentina	1941	1
48	1945-02-28	0	Chile	Copa América	Brazil	1	❌	Chile	Santiago	Brazil	1945	1
49	1946-02-10	2	Argentina	Copa América	Brazil	0	❌	Argentina	Buenos Aires	Argentina	1946	1
50	1947-12-31	3	Bolivia	Copa América	Chile	4	✅	Ecuador	Guayaquil	Chile	1947	1
51	1949-05-11	7	Brazil	Copa América	Paraguay	0	❌	Brazil	Rio de Janeiro	Brazil	1949	1
52	1953-04-01	2	Brazil	Copa América	Paraguay	3	✅	Peru	Lima	Paraguay	1953	1
53	1956-02-15	1	Uruguay	Copa América	Argentina	0	❌	Uruguay	Montevideo	Uruguay	1956	1
54	1957-04-06	2	Peru	Copa América	Argentina	1	❌	Peru	Lima	Peru	1957	1
55	1959-12-25	3	Ecuador	Copa América	Paraguay	1	❌	Ecuador	Guayaquil	Ecuador	1959	1
56	1966-12-28	3	Paraguay	Copa América	Ecuador	1	❌	Paraguay	Asunción	Paraguay	1966	1
57	1967-02-02	1	Uruguay	Copa América	Argentina	0	❌	Uruguay	Montevideo	Uruguay	1967	1
58	1975-10-28	0	Colombia	Copa América	Peru	1	✅	Venezuela	Caracas	Peru	1975	1
59	1987-07-12	0	Chile	Copa América	Uruguay	1	✅	Argentina	Buenos Aires	Uruguay	1987	1
60	1991-07-21	0	Chile	Copa América	Brazil	2	❌	Chile	Santiago	Brazil	1991	1
61	1993-07-04	2	Argentina	Copa América	Mexico	1	✅	Ecuador	Guayaquil	Argentina	1993	1
62	1997-06-29	1	Bolivia	Copa América	Brazil	3	❌	Bolivia	La Paz	Brazil	1997	1
63	1999-07-18	3	Brazil	Copa América	Uruguay	0	✅	Paraguay	Asunción	Brazil	1999	1
64	2001-07-29	1	Colombia	Copa América	Mexico	0	❌	Colombia	Bogotá	Colombia	2001	1
65	2007-07-15	3	Brazil	Copa América	Argentina	0	✅	Venezuela	Maracaibo	Brazil	2007	1
66	2011-07-24	3	Uruguay	Copa América	Paraguay	0	✅	Argentina	Buenos Aires	Uruguay	2011	1
67	1930-07-30	4	Uruguay	FIFA World Cup	Argentina	2	❌	Uruguay	Montevideo	Uruguay	1930	1
68	1934-06-10	2	Italy	FIFA World Cup	Czech Republic	1	❌	Italy	Rome	Italy	1934	1
69	1938-06-19	2	Hungary	FIFA World Cup	Italy	4	✅	France	Colombes	Italy	1938	1
70	1950-07-16	1	Brazil	FIFA World Cup	Uruguay	2	❌	Brazil	Rio de Janeiro	Uruguay	1950	1
71	1954-07-04	3	Germany	FIFA World Cup	Hungary	2	✅	Switzerland	Berne	Germany	1954	1
72	1958-06-29	2	Sweden	FIFA World Cup	Brazil	5	❌	Sweden	Solna	Brazil	1958	1
73	1962-06-17	3	Brazil	FIFA World Cup	Czech Republic	1	✅	Chile	Santiago	Brazil	1962	1
74	1966-07-30	4	England	FIFA World Cup	Germany	2	❌	England	London	England	1966	1
75	1970-06-21	4	Brazil	FIFA World Cup	Italy	1	✅	Mexico	Mexico City	Brazil	1970	1
76	1974-07-07	2	Germany	FIFA World Cup	Netherlands	1	❌	Germany	Munich	Germany	1974	1
77	1978-06-25	3	Argentina	FIFA World Cup	Netherlands	1	❌	Argentina	Buenos Aires	Argentina	1978	1
78	1982-07-11	3	Italy	FIFA World Cup	Germany	1	✅	Spain	Madrid	Italy	1982	1
79	1986-06-29	3	Argentina	FIFA World Cup	Germany	2	✅	Mexico	Mexico City	Argentina	1986	1
80	1990-07-08	1	Germany	FIFA World Cup	Argentina	0	✅	Italy	Rome	Germany	1990	1
81	1998-07-12	3	France	FIFA World Cup	Brazil	0	❌	France	Saint-Denis	France	1998	1
82	2002-06-30	0	Germany	FIFA World Cup	Brazil	2	✅	Japan	Yokohama	Brazil	2002	1
83	2010-07-11	0	Netherlands	FIFA World Cup	Spain	1	✅	South Africa	Johannesburg	Spain	2010	1
84	2014-07-13	1	Germany	FIFA World Cup	Argentina	0	✅	Brazil	Rio de Janeiro	Germany	2014	1
85	1996-01-21	2	Mexico	Gold Cup	Brazil	0	✅	United States	Los Angeles	Mexico	1996	1
86	1998-02-15	0	United States	Gold Cup	Mexico	1	❌	United States	Los Angeles	Mexico	1998	1
87	2000-02-27	2	Canada	Gold Cup	Colombia	0	✅	United States	Los Angeles	Canada	2000	1
88	2002-02-02	2	Canada	Gold Cup	South Korea	1	✅	United States	Pasadena	Canada	2002	1
89	2003-07-27	1	Mexico	Gold Cup	Brazil	0	❌	Mexico	Mexico City	Mexico	2003	1
90	2007-06-24	2	United States	Gold Cup	Mexico	1	❌	United States	Chicago	United States	2007	1
91	2009-07-26	0	United States	Gold Cup	Mexico	5	❌	United States	East Rutherford	Mexico	2009	1
92	2011-06-25	2	United States	Gold Cup	Mexico	4	❌	United States	Pasadena	Mexico	2011	1
93	2013-07-28	1	United States	Gold Cup	Panama	0	❌	United States	Chicago	United States	2013	1
94	2015-07-26	1	Jamaica	Gold Cup	Mexico	3	✅	United States	Philadelphia	Mexico	2015	1
95	1960-07-10	2	Russia	UEFA Euro	Serbia	1	✅	France	Paris	Russia	1960	1
96	1964-06-21	2	Spain	UEFA Euro	Russia	1	❌	Spain	Madrid	Spain	1964	1
97	1968-06-10	2	Italy	UEFA Euro	Serbia	0	❌	Italy	Rome	Italy	1968	1
98	1972-06-18	3	Germany	UEFA Euro	Russia	0	✅	Belgium	Brussels	Germany	1972	1
99	1980-06-22	1	Belgium	UEFA Euro	Germany	2	✅	Italy	Rome	Germany	1980	1
100	1984-06-27	2	France	UEFA Euro	Spain	0	❌	France	Paris	France	1984	1

Rows: 1-100 of 107 | Columns: 12

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

In [16]:

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

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

In [17]:

agg = [st.sum(football["Word_Cup"])._as("nb_World_Cup"),
       st.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)

Out[17]:

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

Rows: 1-10 | Columns: 3

Let's export the result to our Vertica database.

In [19]:

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

Out[19]:

	Abc winner Varchar(64)	123 nb_World_Cup Integer	123 nb_Continental_Cup Integer
1	Brazil	4	8
2	Germany	4	3
3	Italy	3	1
4	Uruguay	2	8
5	Argentina	2	8
6	Spain	1	3
7	France	1	2
8	England	1	0
9	Mexico	0	6
10	Egypt	0	5
11	Paraguay	0	3
12	Nigeria	0	3
13	South Korea	0	3
14	Peru	0	2
15	Canada	0	2
16	Cameroon	0	2
17	Iran	0	2
18	Japan	0	2
19	Chile	0	2
20	Ivory Coast	0	2
21	Ghana	0	2
22	United States	0	2
23	Israel	0	1
24	Algeria	0	1
25	Netherlands	0	1
26	Australia	0	1
27	China PR	0	1
28	DR Congo	0	1
29	Denmark	0	1
30	Congo	0	1
31	Iraq	0	1
32	Ecuador	0	1
33	Kuwait	0	1
34	Colombia	0	1
35	Ethiopia	0	1
36	Zambia	0	1
37	Tunisia	0	1
38	Russia	0	1
39	Sudan	0	1
40	Greece	0	1

Rows: 1-40 | Columns: 3

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.

In [20]:

football = vp.read_csv("data/football.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)

Out[20]:

	📅 date Date	Abc home_team Varchar(64)	Abc away_team Varchar(64)	123 home_score Int	123 away_score Int	Abc tournament Varchar(84)	Abc city Varchar(56)	Abc country Varchar(64)	010 neutral Boolean	123 confederation Integer
1	1872-11-30	Scotland	England	0	0	Friendly	Glasgow	Scotland	❌	0
2	1873-03-08	England	Scotland	4	2	Friendly	London	England	❌	0
3	1874-03-07	Scotland	England	2	1	Friendly	Glasgow	Scotland	❌	0
4	1875-03-06	England	Scotland	2	2	Friendly	London	England	❌	0
5	1876-03-04	Scotland	England	3	0	Friendly	Glasgow	Scotland	❌	0
6	1876-03-25	Scotland	Wales	4	0	Friendly	Glasgow	Scotland	❌	0
7	1877-03-03	England	Scotland	1	3	Friendly	London	England	❌	0
8	1877-03-05	Wales	Scotland	0	2	Friendly	Wrexham	Wales	❌	0
9	1878-03-02	Scotland	England	7	2	Friendly	Glasgow	Scotland	❌	0
10	1878-03-23	Scotland	Wales	9	0	Friendly	Glasgow	Scotland	❌	0
11	1879-01-18	England	Wales	2	1	Friendly	London	England	❌	0
12	1879-04-05	England	Scotland	5	4	Friendly	London	England	❌	0
13	1879-04-07	Wales	Scotland	0	3	Friendly	Wrexham	Wales	❌	0
14	1880-03-13	Scotland	England	5	4	Friendly	Glasgow	Scotland	❌	0
15	1880-03-15	Wales	England	2	3	Friendly	Wrexham	Wales	❌	0
16	1880-03-27	Scotland	Wales	5	1	Friendly	Glasgow	Scotland	❌	0
17	1881-02-26	England	Wales	0	1	Friendly	Blackburn	England	❌	0
18	1881-03-12	England	Scotland	1	6	Friendly	London	England	❌	0
19	1881-03-14	Wales	Scotland	1	5	Friendly	Wrexham	Wales	❌	0
20	1882-02-18	Northern Ireland	England	0	13	Friendly	Belfast	Republic of Ireland	❌	0
21	1882-02-25	Wales	Northern Ireland	7	1	Friendly	Wrexham	Wales	❌	0
22	1882-03-11	Scotland	England	5	1	Friendly	Glasgow	Scotland	❌	0
23	1882-03-13	Wales	England	5	3	Friendly	Wrexham	Wales	❌	0
24	1882-03-25	Scotland	Wales	5	0	Friendly	Glasgow	Scotland	❌	0
25	1883-02-03	England	Wales	5	0	Friendly	London	England	❌	0
26	1883-02-24	England	Northern Ireland	7	0	Friendly	Liverpool	England	❌	0
27	1883-03-10	England	Scotland	2	3	Friendly	Sheffield	England	❌	0
28	1883-03-12	Wales	Scotland	0	3	Friendly	Wrexham	Wales	❌	0
29	1883-03-17	Northern Ireland	Wales	1	1	Friendly	Belfast	Republic of Ireland	❌	0
30	1884-01-26	Northern Ireland	Scotland	0	5	British Championship	Belfast	Republic of Ireland	❌	0
31	1884-02-09	Wales	Northern Ireland	6	0	British Championship	Wrexham	Wales	❌	0
32	1884-02-23	Northern Ireland	England	1	8	British Championship	Belfast	Republic of Ireland	❌	0
33	1884-03-15	Scotland	England	1	0	British Championship	Glasgow	Scotland	❌	0
34	1884-03-17	Wales	England	0	4	British Championship	Wrexham	Wales	❌	0
35	1884-03-29	Scotland	Wales	4	1	British Championship	Glasgow	Scotland	❌	0
36	1885-02-28	England	Northern Ireland	4	0	British Championship	Manchester	England	❌	0
37	1885-03-14	England	Wales	1	1	British Championship	Blackburn	England	❌	0
38	1885-03-14	Scotland	Northern Ireland	8	2	British Championship	Glasgow	Scotland	❌	0
39	1885-03-21	England	Scotland	1	1	British Championship	London	England	❌	0
40	1885-03-23	Wales	Scotland	1	8	British Championship	Wrexham	Wales	❌	0
41	1885-04-11	Northern Ireland	Wales	2	8	British Championship	Belfast	Republic of Ireland	❌	0
42	1885-11-28	United States	Canada	0	1	Friendly	Newark	United States	❌	0
43	1886-02-27	Wales	Northern Ireland	5	0	British Championship	Wrexham	Wales	❌	0
44	1886-03-13	Northern Ireland	England	1	6	British Championship	Belfast	Republic of Ireland	❌	0
45	1886-03-20	Northern Ireland	Scotland	2	7	British Championship	Belfast	Republic of Ireland	❌	0
46	1886-03-27	Scotland	England	1	1	British Championship	Glasgow	Scotland	❌	0
47	1886-03-29	Wales	England	1	3	British Championship	Wrexham	Wales	❌	0
48	1886-04-10	Scotland	Wales	4	1	British Championship	Glasgow	Scotland	❌	0
49	1886-11-25	United States	Canada	3	2	Friendly	Newark	United States	❌	0
50	1887-02-05	England	Northern Ireland	7	0	British Championship	Sheffield	England	❌	0
51	1887-02-19	Scotland	Northern Ireland	4	1	British Championship	Glasgow	Scotland	❌	0
52	1887-02-26	England	Wales	4	0	British Championship	London	England	❌	0
53	1887-03-12	Northern Ireland	Wales	4	1	British Championship	Belfast	Republic of Ireland	❌	0
54	1887-03-19	England	Scotland	2	3	British Championship	Blackburn	England	❌	0
55	1887-03-21	Wales	Scotland	0	2	British Championship	Wrexham	Wales	❌	0
56	1888-02-04	England	Wales	5	1	British Championship	Crewe	England	❌	0
57	1888-03-03	Wales	Northern Ireland	11	0	British Championship	Wrexham	Wales	❌	0
58	1888-03-10	Scotland	Wales	5	1	British Championship	Edinburgh	Scotland	❌	0
59	1888-03-17	Scotland	England	0	5	British Championship	Glasgow	Scotland	❌	0
60	1888-03-24	Northern Ireland	Scotland	2	10	British Championship	Belfast	Republic of Ireland	❌	0
61	1888-04-07	Northern Ireland	England	1	5	British Championship	Belfast	Republic of Ireland	❌	0
62	1888-09-19	Scotland	Canada	4	0	Friendly	Glasgow	Scotland	❌	0
63	1889-02-23	England	Wales	4	1	British Championship	Stoke-on-Trent	England	❌	0
64	1889-03-02	England	Northern Ireland	6	1	British Championship	Liverpool	England	❌	0
65	1889-03-09	Scotland	Northern Ireland	7	0	British Championship	Glasgow	Scotland	❌	0
66	1889-04-13	England	Scotland	2	3	British Championship	London	England	❌	0
67	1889-04-15	Wales	Scotland	0	0	British Championship	Wrexham	Wales	❌	0
68	1889-04-27	Northern Ireland	Wales	1	3	British Championship	Belfast	Republic of Ireland	❌	0
69	1890-02-08	Wales	Northern Ireland	5	2	British Championship	Shrewsbury	England	✅	0
70	1890-03-15	Northern Ireland	England	1	9	British Championship	Belfast	Republic of Ireland	❌	0
71	1890-03-15	Wales	England	1	3	British Championship	Wrexham	Wales	❌	0
72	1890-03-22	Scotland	Wales	5	0	British Championship	Paisley	Scotland	❌	0
73	1890-03-29	Northern Ireland	Scotland	1	4	British Championship	Belfast	Republic of Ireland	❌	0
74	1890-04-05	Scotland	England	1	1	British Championship	Glasgow	Scotland	❌	0
75	1891-02-07	Northern Ireland	Wales	7	2	British Championship	Belfast	Republic of Ireland	❌	0
76	1891-03-07	England	Northern Ireland	6	1	British Championship	Wolverhampton	England	❌	0
77	1891-03-07	England	Wales	4	1	British Championship	Sunderland	England	❌	0
78	1891-03-21	Wales	Scotland	3	4	British Championship	Wrexham	Wales	❌	0
79	1891-03-28	Scotland	Northern Ireland	2	1	British Championship	Glasgow	Scotland	❌	0
80	1891-04-06	England	Scotland	2	1	British Championship	Blackburn	England	❌	0
81	1892-02-27	Wales	Northern Ireland	1	1	British Championship	Bangor	Wales	❌	0
82	1892-03-05	Northern Ireland	England	0	2	British Championship	Belfast	Republic of Ireland	❌	0
83	1892-03-05	Wales	England	0	2	British Championship	Wrexham	Wales	❌	0
84	1892-03-19	Northern Ireland	Scotland	2	3	British Championship	Belfast	Republic of Ireland	❌	0
85	1892-03-26	Scotland	Wales	6	1	British Championship	Edinburgh	Scotland	❌	0
86	1892-04-02	Scotland	England	1	4	British Championship	Glasgow	Scotland	❌	0
87	1893-02-25	England	Northern Ireland	6	1	British Championship	Birmingham	England	❌	0
88	1893-03-13	England	Wales	6	0	British Championship	Stoke-on-Trent	England	❌	0
89	1893-03-18	Wales	Scotland	0	8	British Championship	Wrexham	Wales	❌	0
90	1893-03-25	Scotland	Northern Ireland	6	1	British Championship	Glasgow	Scotland	❌	0
91	1893-04-01	England	Scotland	5	2	British Championship	Richmond	England	❌	0
92	1894-02-24	Wales	Northern Ireland	4	1	British Championship	Swansea	Wales	❌	0
93	1894-03-03	Northern Ireland	England	2	2	British Championship	Belfast	Republic of Ireland	❌	0
94	1894-03-12	Wales	England	1	5	British Championship	Wrexham	Wales	❌	0
95	1894-03-24	Scotland	Wales	5	2	British Championship	Kilmarnock	Scotland	❌	0
96	1894-03-31	Northern Ireland	Scotland	1	2	British Championship	Belfast	Republic of Ireland	❌	0
97	1894-04-07	Scotland	England	2	2	British Championship	Glasgow	Scotland	❌	0
98	1895-03-09	England	Northern Ireland	9	0	British Championship	Derby	England	❌	0
99	1895-03-16	Northern Ireland	Wales	2	2	British Championship	Belfast	Republic of Ireland	❌	0
100	1895-03-18	England	Wales	1	1	British Championship	London	England	❌	0

Rows: 1-100 | Columns: 10

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

In [21]:

confederation = football.groupby(["home_team"],
                                 [st.max(football["confederation"])._as("confederation")])
display(confederation)

	Abc home_team Varchar(64)	123 confederation Integer
1	Kernow	0
2	Martinique	1
3	New Zealand	0
4	Orkney	0
5	Saint Helena	0
6	Iran	3
7	South Africa	4
8	Sápmi	0
9	Morocco	4
10	Gozo	0
11	Åland Islands	0
12	Honduras	2
13	Panjab	0
14	Artsakh	0
15	Scotland	5
16	Mauritania	4
17	Matabeleland	0
18	Falkland Islands	0
19	Eswatini	4
20	Poland	5
21	Monaco	0
22	Rwanda	4
23	Saint Vincent and the Grenadines	0
24	Serbia	5
25	Burma	3
26	Tonga	0
27	Saudi Arabia	3
28	Gibraltar	5
29	Denmark	5
30	Iceland	5
31	Northern Ireland	5
32	Cape Verde	4
33	Montserrat	0
34	Gabon	4
35	Angola	4
36	Uruguay	2
37	Réunion	0
38	North Macedonia	5
39	Croatia	5
40	Chameria	0
41	Tahiti	0
42	Singapore	3
43	Belize	0
44	United Arab Emirates	3
45	Vatican City	0
46	Republic of St. Pauli	0
47	Găgăuzia	0
48	Saint Pierre and Miquelon	0
49	Ynys Môn	0
50	Parishes of Jersey	0
51	Togo	4
52	Arameans Suryoye	0
53	Sweden	5
54	Crimea	0
55	South Sudan	4
56	Northern Cyprus	0
57	Brittany	0
58	Madagascar	4
59	Vanuatu	0
60	São Tomé and Príncipe	4
61	Italy	5
62	Papua New Guinea	0
63	Curaçao	1
64	Botswana	4
65	French Guiana	1
66	Székely Land	0
67	Padania	0
68	Macau	3
69	Kosovo	5
70	Liberia	4
71	Sudan	4
72	Nepal	3
73	Iraqi Kurdistan	0
74	Isle of Wight	0
75	Gambia	4
76	Lesotho	4
77	Hitra	0
78	Bahamas	0
79	Romania	5
80	United Koreans in Japan	0
81	Central African Republic	4
82	Malaysia	3
83	Tanzania	4
84	Estonia	5
85	Aruba	0
86	Guinea	4
87	Ellan Vannin	0
88	Yorkshire	0
89	Zambia	4
90	Chad	4
91	Uzbekistan	3
92	Turkey	5
93	Solomon Islands	0
94	Australia	3
95	India	3
96	Somaliland	0
97	Cyprus	5
98	Micronesia	0
99	Saarland	0
100	Russia	5

Rows: 1-100 | Columns: 2

We can decode the previous label encoding.

In [22]:

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

Out[22]:

	Abc home_team Varchar(64)	Abc confederation Varchar(8)
1	Kernow	OFC
2	Martinique	CONCACAF
3	New Zealand	OFC
4	Orkney	OFC
5	Saint Helena	OFC
6	Iran	AFC
7	South Africa	CAF
8	Sápmi	OFC
9	Morocco	CAF
10	Gozo	OFC
11	Åland Islands	OFC
12	Honduras	CONMEBOL
13	Panjab	OFC
14	Artsakh	OFC
15	Scotland	UEFA
16	Mauritania	CAF
17	Matabeleland	OFC
18	Falkland Islands	OFC
19	Eswatini	CAF
20	Poland	UEFA
21	Monaco	OFC
22	Rwanda	CAF
23	Saint Vincent and the Grenadines	OFC
24	Serbia	UEFA
25	Burma	AFC
26	Tonga	OFC
27	Saudi Arabia	AFC
28	Gibraltar	UEFA
29	Denmark	UEFA
30	Iceland	UEFA
31	Northern Ireland	UEFA
32	Cape Verde	CAF
33	Montserrat	OFC
34	Gabon	CAF
35	Angola	CAF
36	Uruguay	CONMEBOL
37	Réunion	OFC
38	North Macedonia	UEFA
39	Croatia	UEFA
40	Chameria	OFC
41	Tahiti	OFC
42	Singapore	AFC
43	Belize	OFC
44	United Arab Emirates	AFC
45	Vatican City	OFC
46	Republic of St. Pauli	OFC
47	Găgăuzia	OFC
48	Saint Pierre and Miquelon	OFC
49	Ynys Môn	OFC
50	Parishes of Jersey	OFC
51	Togo	CAF
52	Arameans Suryoye	OFC
53	Sweden	UEFA
54	Crimea	OFC
55	South Sudan	CAF
56	Northern Cyprus	OFC
57	Brittany	OFC
58	Madagascar	CAF
59	Vanuatu	OFC
60	São Tomé and Príncipe	CAF
61	Italy	UEFA
62	Papua New Guinea	OFC
63	Curaçao	CONCACAF
64	Botswana	CAF
65	French Guiana	CONCACAF
66	Székely Land	OFC
67	Padania	OFC
68	Macau	AFC
69	Kosovo	UEFA
70	Liberia	CAF
71	Sudan	CAF
72	Nepal	AFC
73	Iraqi Kurdistan	OFC
74	Isle of Wight	OFC
75	Gambia	CAF
76	Lesotho	CAF
77	Hitra	OFC
78	Bahamas	OFC
79	Romania	UEFA
80	United Koreans in Japan	OFC
81	Central African Republic	CAF
82	Malaysia	AFC
83	Tanzania	CAF
84	Estonia	UEFA
85	Aruba	OFC
86	Guinea	CAF
87	Ellan Vannin	OFC
88	Yorkshire	OFC
89	Zambia	CAF
90	Chad	CAF
91	Uzbekistan	AFC
92	Turkey	UEFA
93	Solomon Islands	OFC
94	Australia	AFC
95	India	AFC
96	Somaliland	OFC
97	Cyprus	UEFA
98	Micronesia	OFC
99	Saarland	OFC
100	Russia	UEFA

Rows: 1-100 | Columns: 2

Let's export the result to our Vertica database.

In [23]:

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

Out[23]:

	Abc confederation Varchar(8)	Abc team Varchar(64)
1	OFC	Kernow
2	CONCACAF	Martinique
3	OFC	New Zealand
4	OFC	Orkney
5	OFC	Saint Helena
6	AFC	Iran
7	CAF	South Africa
8	OFC	Sápmi
9	CAF	Morocco
10	OFC	Gozo
11	OFC	Åland Islands
12	CONMEBOL	Honduras
13	OFC	Panjab
14	OFC	Artsakh
15	UEFA	Scotland
16	CAF	Mauritania
17	OFC	Matabeleland
18	OFC	Falkland Islands
19	CAF	Eswatini
20	UEFA	Poland
21	OFC	Monaco
22	CAF	Rwanda
23	OFC	Saint Vincent and the Grenadines
24	UEFA	Serbia
25	AFC	Burma
26	OFC	Tonga
27	AFC	Saudi Arabia
28	UEFA	Gibraltar
29	UEFA	Denmark
30	UEFA	Iceland
31	UEFA	Northern Ireland
32	CAF	Cape Verde
33	OFC	Montserrat
34	CAF	Gabon
35	CAF	Angola
36	CONMEBOL	Uruguay
37	OFC	Réunion
38	UEFA	North Macedonia
39	UEFA	Croatia
40	OFC	Chameria
41	OFC	Tahiti
42	AFC	Singapore
43	OFC	Belize
44	AFC	United Arab Emirates
45	OFC	Vatican City
46	OFC	Republic of St. Pauli
47	OFC	Găgăuzia
48	OFC	Saint Pierre and Miquelon
49	OFC	Ynys Môn
50	OFC	Parishes of Jersey
51	CAF	Togo
52	OFC	Arameans Suryoye
53	UEFA	Sweden
54	OFC	Crimea
55	CAF	South Sudan
56	OFC	Northern Cyprus
57	OFC	Brittany
58	CAF	Madagascar
59	OFC	Vanuatu
60	CAF	São Tomé and Príncipe
61	UEFA	Italy
62	OFC	Papua New Guinea
63	CONCACAF	Curaçao
64	CAF	Botswana
65	CONCACAF	French Guiana
66	OFC	Székely Land
67	OFC	Padania
68	AFC	Macau
69	UEFA	Kosovo
70	CAF	Liberia
71	CAF	Sudan
72	AFC	Nepal
73	OFC	Iraqi Kurdistan
74	OFC	Isle of Wight
75	CAF	Gambia
76	CAF	Lesotho
77	OFC	Hitra
78	OFC	Bahamas
79	UEFA	Romania
80	OFC	United Koreans in Japan
81	CAF	Central African Republic
82	AFC	Malaysia
83	CAF	Tanzania
84	UEFA	Estonia
85	OFC	Aruba
86	CAF	Guinea
87	OFC	Ellan Vannin
88	OFC	Yorkshire
89	CAF	Zambia
90	CAF	Chad
91	AFC	Uzbekistan
92	UEFA	Turkey
93	OFC	Solomon Islands
94	AFC	Australia
95	AFC	India
96	OFC	Somaliland
97	UEFA	Cyprus
98	OFC	Micronesia
99	OFC	Saarland
100	UEFA	Russia

Rows: 1-100 | Columns: 2

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.

In [24]:

football = vp.vDataFrame("football_clean")
football.filter(st.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(st.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")

3884 elements were filtered
3884 elements were filtered

Out[24]:

	📅 date Date	Abc tournament Varchar(84)	Abc country Varchar(64)	Abc city Varchar(56)	Abc team1 Varchar(64)	123 team1_score Integer	Abc team2 Varchar(64)	123 team2_score Integer	123 home_team_id Integer
1	1872-11-30	Friendly	Scotland	Glasgow	Scotland	0	England	0	1
2	1873-03-08	Friendly	England	London	England	4	Scotland	2	1
3	1874-03-07	Friendly	Scotland	Glasgow	Scotland	2	England	1	1
4	1875-03-06	Friendly	England	London	England	2	Scotland	2	1
5	1876-03-04	Friendly	Scotland	Glasgow	Scotland	3	England	0	1
6	1876-03-25	Friendly	Scotland	Glasgow	Scotland	4	Wales	0	1
7	1877-03-03	Friendly	England	London	England	1	Scotland	3	1
8	1877-03-05	Friendly	Wales	Wrexham	Wales	0	Scotland	2	1
9	1878-03-02	Friendly	Scotland	Glasgow	Scotland	7	England	2	1
10	1878-03-23	Friendly	Scotland	Glasgow	Scotland	9	Wales	0	1
11	1879-01-18	Friendly	England	London	England	2	Wales	1	1
12	1879-04-05	Friendly	England	London	England	5	Scotland	4	1
13	1879-04-07	Friendly	Wales	Wrexham	Wales	0	Scotland	3	1
14	1880-03-13	Friendly	Scotland	Glasgow	Scotland	5	England	4	1
15	1880-03-15	Friendly	Wales	Wrexham	Wales	2	England	3	1
16	1880-03-27	Friendly	Scotland	Glasgow	Scotland	5	Wales	1	1
17	1881-02-26	Friendly	England	Blackburn	England	0	Wales	1	1
18	1881-03-12	Friendly	England	London	England	1	Scotland	6	1
19	1881-03-14	Friendly	Wales	Wrexham	Wales	1	Scotland	5	1
20	1882-02-18	Friendly	Republic of Ireland	Belfast	Northern Ireland	0	England	13	1
21	1882-02-25	Friendly	Wales	Wrexham	Wales	7	Northern Ireland	1	1
22	1882-03-11	Friendly	Scotland	Glasgow	Scotland	5	England	1	1
23	1882-03-13	Friendly	Wales	Wrexham	Wales	5	England	3	1
24	1882-03-25	Friendly	Scotland	Glasgow	Scotland	5	Wales	0	1
25	1883-02-03	Friendly	England	London	England	5	Wales	0	1
26	1883-02-24	Friendly	England	Liverpool	England	7	Northern Ireland	0	1
27	1883-03-10	Friendly	England	Sheffield	England	2	Scotland	3	1
28	1883-03-12	Friendly	Wales	Wrexham	Wales	0	Scotland	3	1
29	1883-03-17	Friendly	Republic of Ireland	Belfast	Northern Ireland	1	Wales	1	1
30	1884-01-26	British Championship	Republic of Ireland	Belfast	Northern Ireland	0	Scotland	5	1
31	1884-02-09	British Championship	Wales	Wrexham	Wales	6	Northern Ireland	0	1
32	1884-02-23	British Championship	Republic of Ireland	Belfast	Northern Ireland	1	England	8	1
33	1884-03-15	British Championship	Scotland	Glasgow	Scotland	1	England	0	1
34	1884-03-17	British Championship	Wales	Wrexham	Wales	0	England	4	1
35	1884-03-29	British Championship	Scotland	Glasgow	Scotland	4	Wales	1	1
36	1885-02-28	British Championship	England	Manchester	England	4	Northern Ireland	0	1
37	1885-03-14	British Championship	England	Blackburn	England	1	Wales	1	1
38	1885-03-14	British Championship	Scotland	Glasgow	Scotland	8	Northern Ireland	2	1
39	1885-03-21	British Championship	England	London	England	1	Scotland	1	1
40	1885-03-23	British Championship	Wales	Wrexham	Wales	1	Scotland	8	1
41	1885-04-11	British Championship	Republic of Ireland	Belfast	Northern Ireland	2	Wales	8	1
42	1885-11-28	Friendly	United States	Newark	United States	0	Canada	1	1
43	1886-02-27	British Championship	Wales	Wrexham	Wales	5	Northern Ireland	0	1
44	1886-03-13	British Championship	Republic of Ireland	Belfast	Northern Ireland	1	England	6	1
45	1886-03-20	British Championship	Republic of Ireland	Belfast	Northern Ireland	2	Scotland	7	1
46	1886-03-27	British Championship	Scotland	Glasgow	Scotland	1	England	1	1
47	1886-03-29	British Championship	Wales	Wrexham	Wales	1	England	3	1
48	1886-04-10	British Championship	Scotland	Glasgow	Scotland	4	Wales	1	1
49	1886-11-25	Friendly	United States	Newark	United States	3	Canada	2	1
50	1887-02-05	British Championship	England	Sheffield	England	7	Northern Ireland	0	1
51	1887-02-19	British Championship	Scotland	Glasgow	Scotland	4	Northern Ireland	1	1
52	1887-02-26	British Championship	England	London	England	4	Wales	0	1
53	1887-03-12	British Championship	Republic of Ireland	Belfast	Northern Ireland	4	Wales	1	1
54	1887-03-19	British Championship	England	Blackburn	England	2	Scotland	3	1
55	1887-03-21	British Championship	Wales	Wrexham	Wales	0	Scotland	2	1
56	1888-02-04	British Championship	England	Crewe	England	5	Wales	1	1
57	1888-03-03	British Championship	Wales	Wrexham	Wales	11	Northern Ireland	0	1
58	1888-03-10	British Championship	Scotland	Edinburgh	Scotland	5	Wales	1	1
59	1888-03-17	British Championship	Scotland	Glasgow	Scotland	0	England	5	1
60	1888-03-24	British Championship	Republic of Ireland	Belfast	Northern Ireland	2	Scotland	10	1
61	1888-04-07	British Championship	Republic of Ireland	Belfast	Northern Ireland	1	England	5	1
62	1888-09-19	Friendly	Scotland	Glasgow	Scotland	4	Canada	0	1
63	1889-02-23	British Championship	England	Stoke-on-Trent	England	4	Wales	1	1
64	1889-03-02	British Championship	England	Liverpool	England	6	Northern Ireland	1	1
65	1889-03-09	British Championship	Scotland	Glasgow	Scotland	7	Northern Ireland	0	1
66	1889-04-13	British Championship	England	London	England	2	Scotland	3	1
67	1889-04-15	British Championship	Wales	Wrexham	Wales	0	Scotland	0	1
68	1889-04-27	British Championship	Republic of Ireland	Belfast	Northern Ireland	1	Wales	3	1
69	1890-02-08	British Championship	England	Shrewsbury	Wales	5	Northern Ireland	2	0
70	1890-03-15	British Championship	Republic of Ireland	Belfast	Northern Ireland	1	England	9	1
71	1890-03-15	British Championship	Wales	Wrexham	Wales	1	England	3	1
72	1890-03-22	British Championship	Scotland	Paisley	Scotland	5	Wales	0	1
73	1890-03-29	British Championship	Republic of Ireland	Belfast	Northern Ireland	1	Scotland	4	1
74	1890-04-05	British Championship	Scotland	Glasgow	Scotland	1	England	1	1
75	1891-02-07	British Championship	Republic of Ireland	Belfast	Northern Ireland	7	Wales	2	1
76	1891-03-07	British Championship	England	Sunderland	England	4	Wales	1	1
77	1891-03-07	British Championship	England	Wolverhampton	England	6	Northern Ireland	1	1
78	1891-03-21	British Championship	Wales	Wrexham	Wales	3	Scotland	4	1
79	1891-03-28	British Championship	Scotland	Glasgow	Scotland	2	Northern Ireland	1	1
80	1891-04-06	British Championship	England	Blackburn	England	2	Scotland	1	1
81	1892-02-27	British Championship	Wales	Bangor	Wales	1	Northern Ireland	1	1
82	1892-03-05	British Championship	Republic of Ireland	Belfast	Northern Ireland	0	England	2	1
83	1892-03-05	British Championship	Wales	Wrexham	Wales	0	England	2	1
84	1892-03-19	British Championship	Republic of Ireland	Belfast	Northern Ireland	2	Scotland	3	1
85	1892-03-26	British Championship	Scotland	Edinburgh	Scotland	6	Wales	1	1
86	1892-04-02	British Championship	Scotland	Glasgow	Scotland	1	England	4	1
87	1893-02-25	British Championship	England	Birmingham	England	6	Northern Ireland	1	1
88	1893-03-13	British Championship	England	Stoke-on-Trent	England	6	Wales	0	1
89	1893-03-18	British Championship	Wales	Wrexham	Wales	0	Scotland	8	1
90	1893-03-25	British Championship	Scotland	Glasgow	Scotland	6	Northern Ireland	1	1
91	1893-04-01	British Championship	England	Richmond	England	5	Scotland	2	1
92	1894-02-24	British Championship	Wales	Swansea	Wales	4	Northern Ireland	1	1
93	1894-03-03	British Championship	Republic of Ireland	Belfast	Northern Ireland	2	England	2	1
94	1894-03-12	British Championship	Wales	Wrexham	Wales	1	England	5	1
95	1894-03-24	British Championship	Scotland	Kilmarnock	Scotland	5	Wales	2	1
96	1894-03-31	British Championship	Republic of Ireland	Belfast	Northern Ireland	1	Scotland	2	1
97	1894-04-07	British Championship	Scotland	Glasgow	Scotland	2	England	2	1
98	1895-03-09	British Championship	England	Derby	England	9	Northern Ireland	0	1
99	1895-03-16	British Championship	Republic of Ireland	Belfast	Northern Ireland	2	Wales	2	1
100	1895-03-18	British Championship	England	London	England	1	Wales	1	1

Rows: 1-100 | Columns: 9

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

In [25]:

all_matchs["World_Tournament"] = st.case_when(all_matchs["tournament"]._in(["FIFA World Cup",
                                                                            "Confederations Cup"]), 1, 0)
all_matchs["Continental_Tournament"] = st.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")

Out[25]:

	📅 date Date	Abc tournament Varchar(84)	Abc country Varchar(64)	Abc city Varchar(56)	Abc team1 Varchar(64)	123 team1_score Integer	Abc team2 Varchar(64)	123 team2_score Integer	123 home_team_id Integer	123 World_Tournament Integer	123 Continental_Tournament Integer	123 Victory_team1 Int	123 Draw Int
1	1872-11-30	Friendly	Scotland	Glasgow	Scotland	0	England	0	1	0	0	0	1
2	1873-03-08	Friendly	England	London	England	4	Scotland	2	1	0	0	1	0
3	1874-03-07	Friendly	Scotland	Glasgow	Scotland	2	England	1	1	0	0	1	0
4	1875-03-06	Friendly	England	London	England	2	Scotland	2	1	0	0	0	1
5	1876-03-04	Friendly	Scotland	Glasgow	Scotland	3	England	0	1	0	0	1	0
6	1876-03-25	Friendly	Scotland	Glasgow	Scotland	4	Wales	0	1	0	0	1	0
7	1877-03-03	Friendly	England	London	England	1	Scotland	3	1	0	0	0	0
8	1877-03-05	Friendly	Wales	Wrexham	Wales	0	Scotland	2	1	0	0	0	0
9	1878-03-02	Friendly	Scotland	Glasgow	Scotland	7	England	2	1	0	0	1	0
10	1878-03-23	Friendly	Scotland	Glasgow	Scotland	9	Wales	0	1	0	0	1	0
11	1879-01-18	Friendly	England	London	England	2	Wales	1	1	0	0	1	0
12	1879-04-05	Friendly	England	London	England	5	Scotland	4	1	0	0	1	0
13	1879-04-07	Friendly	Wales	Wrexham	Wales	0	Scotland	3	1	0	0	0	0
14	1880-03-13	Friendly	Scotland	Glasgow	Scotland	5	England	4	1	0	0	1	0
15	1880-03-15	Friendly	Wales	Wrexham	Wales	2	England	3	1	0	0	0	0
16	1880-03-27	Friendly	Scotland	Glasgow	Scotland	5	Wales	1	1	0	0	1	0
17	1881-02-26	Friendly	England	Blackburn	England	0	Wales	1	1	0	0	0	0
18	1881-03-12	Friendly	England	London	England	1	Scotland	6	1	0	0	0	0
19	1881-03-14	Friendly	Wales	Wrexham	Wales	1	Scotland	5	1	0	0	0	0
20	1882-02-18	Friendly	Republic of Ireland	Belfast	Northern Ireland	0	England	13	1	0	0	0	0
21	1882-02-25	Friendly	Wales	Wrexham	Wales	7	Northern Ireland	1	1	0	0	1	0
22	1882-03-11	Friendly	Scotland	Glasgow	Scotland	5	England	1	1	0	0	1	0
23	1882-03-13	Friendly	Wales	Wrexham	Wales	5	England	3	1	0	0	1	0
24	1882-03-25	Friendly	Scotland	Glasgow	Scotland	5	Wales	0	1	0	0	1	0
25	1883-02-03	Friendly	England	London	England	5	Wales	0	1	0	0	1	0
26	1883-02-24	Friendly	England	Liverpool	England	7	Northern Ireland	0	1	0	0	1	0
27	1883-03-10	Friendly	England	Sheffield	England	2	Scotland	3	1	0	0	0	0
28	1883-03-12	Friendly	Wales	Wrexham	Wales	0	Scotland	3	1	0	0	0	0
29	1883-03-17	Friendly	Republic of Ireland	Belfast	Northern Ireland	1	Wales	1	1	0	0	0	1
30	1884-01-26	British Championship	Republic of Ireland	Belfast	Northern Ireland	0	Scotland	5	1	0	0	0	0
31	1884-02-09	British Championship	Wales	Wrexham	Wales	6	Northern Ireland	0	1	0	0	1	0
32	1884-02-23	British Championship	Republic of Ireland	Belfast	Northern Ireland	1	England	8	1	0	0	0	0
33	1884-03-15	British Championship	Scotland	Glasgow	Scotland	1	England	0	1	0	0	1	0
34	1884-03-17	British Championship	Wales	Wrexham	Wales	0	England	4	1	0	0	0	0
35	1884-03-29	British Championship	Scotland	Glasgow	Scotland	4	Wales	1	1	0	0	1	0
36	1885-02-28	British Championship	England	Manchester	England	4	Northern Ireland	0	1	0	0	1	0
37	1885-03-14	British Championship	England	Blackburn	England	1	Wales	1	1	0	0	0	1
38	1885-03-14	British Championship	Scotland	Glasgow	Scotland	8	Northern Ireland	2	1	0	0	1	0
39	1885-03-21	British Championship	England	London	England	1	Scotland	1	1	0	0	0	1
40	1885-03-23	British Championship	Wales	Wrexham	Wales	1	Scotland	8	1	0	0	0	0
41	1885-04-11	British Championship	Republic of Ireland	Belfast	Northern Ireland	2	Wales	8	1	0	0	0	0
42	1885-11-28	Friendly	United States	Newark	United States	0	Canada	1	1	0	0	0	0
43	1886-02-27	British Championship	Wales	Wrexham	Wales	5	Northern Ireland	0	1	0	0	1	0
44	1886-03-13	British Championship	Republic of Ireland	Belfast	Northern Ireland	1	England	6	1	0	0	0	0
45	1886-03-20	British Championship	Republic of Ireland	Belfast	Northern Ireland	2	Scotland	7	1	0	0	0	0
46	1886-03-27	British Championship	Scotland	Glasgow	Scotland	1	England	1	1	0	0	0	1
47	1886-03-29	British Championship	Wales	Wrexham	Wales	1	England	3	1	0	0	0	0
48	1886-04-10	British Championship	Scotland	Glasgow	Scotland	4	Wales	1	1	0	0	1	0
49	1886-11-25	Friendly	United States	Newark	United States	3	Canada	2	1	0	0	1	0
50	1887-02-05	British Championship	England	Sheffield	England	7	Northern Ireland	0	1	0	0	1	0
51	1887-02-19	British Championship	Scotland	Glasgow	Scotland	4	Northern Ireland	1	1	0	0	1	0
52	1887-02-26	British Championship	England	London	England	4	Wales	0	1	0	0	1	0
53	1887-03-12	British Championship	Republic of Ireland	Belfast	Northern Ireland	4	Wales	1	1	0	0	1	0
54	1887-03-19	British Championship	England	Blackburn	England	2	Scotland	3	1	0	0	0	0
55	1887-03-21	British Championship	Wales	Wrexham	Wales	0	Scotland	2	1	0	0	0	0
56	1888-02-04	British Championship	England	Crewe	England	5	Wales	1	1	0	0	1	0
57	1888-03-03	British Championship	Wales	Wrexham	Wales	11	Northern Ireland	0	1	0	0	1	0
58	1888-03-10	British Championship	Scotland	Edinburgh	Scotland	5	Wales	1	1	0	0	1	0
59	1888-03-17	British Championship	Scotland	Glasgow	Scotland	0	England	5	1	0	0	0	0
60	1888-03-24	British Championship	Republic of Ireland	Belfast	Northern Ireland	2	Scotland	10	1	0	0	0	0
61	1888-04-07	British Championship	Republic of Ireland	Belfast	Northern Ireland	1	England	5	1	0	0	0	0
62	1888-09-19	Friendly	Scotland	Glasgow	Scotland	4	Canada	0	1	0	0	1	0
63	1889-02-23	British Championship	England	Stoke-on-Trent	England	4	Wales	1	1	0	0	1	0
64	1889-03-02	British Championship	England	Liverpool	England	6	Northern Ireland	1	1	0	0	1	0
65	1889-03-09	British Championship	Scotland	Glasgow	Scotland	7	Northern Ireland	0	1	0	0	1	0
66	1889-04-13	British Championship	England	London	England	2	Scotland	3	1	0	0	0	0
67	1889-04-15	British Championship	Wales	Wrexham	Wales	0	Scotland	0	1	0	0	0	1
68	1889-04-27	British Championship	Republic of Ireland	Belfast	Northern Ireland	1	Wales	3	1	0	0	0	0
69	1890-02-08	British Championship	England	Shrewsbury	Wales	5	Northern Ireland	2	0	0	0	1	0
70	1890-03-15	British Championship	Republic of Ireland	Belfast	Northern Ireland	1	England	9	1	0	0	0	0
71	1890-03-15	British Championship	Wales	Wrexham	Wales	1	England	3	1	0	0	0	0
72	1890-03-22	British Championship	Scotland	Paisley	Scotland	5	Wales	0	1	0	0	1	0
73	1890-03-29	British Championship	Republic of Ireland	Belfast	Northern Ireland	1	Scotland	4	1	0	0	0	0
74	1890-04-05	British Championship	Scotland	Glasgow	Scotland	1	England	1	1	0	0	0	1
75	1891-02-07	British Championship	Republic of Ireland	Belfast	Northern Ireland	7	Wales	2	1	0	0	1	0
76	1891-03-07	British Championship	England	Sunderland	England	4	Wales	1	1	0	0	1	0
77	1891-03-07	British Championship	England	Wolverhampton	England	6	Northern Ireland	1	1	0	0	1	0
78	1891-03-21	British Championship	Wales	Wrexham	Wales	3	Scotland	4	1	0	0	0	0
79	1891-03-28	British Championship	Scotland	Glasgow	Scotland	2	Northern Ireland	1	1	0	0	1	0
80	1891-04-06	British Championship	England	Blackburn	England	2	Scotland	1	1	0	0	1	0
81	1892-02-27	British Championship	Wales	Bangor	Wales	1	Northern Ireland	1	1	0	0	0	1
82	1892-03-05	British Championship	Republic of Ireland	Belfast	Northern Ireland	0	England	2	1	0	0	0	0
83	1892-03-05	British Championship	Wales	Wrexham	Wales	0	England	2	1	0	0	0	0
84	1892-03-19	British Championship	Republic of Ireland	Belfast	Northern Ireland	2	Scotland	3	1	0	0	0	0
85	1892-03-26	British Championship	Scotland	Edinburgh	Scotland	6	Wales	1	1	0	0	1	0
86	1892-04-02	British Championship	Scotland	Glasgow	Scotland	1	England	4	1	0	0	0	0
87	1893-02-25	British Championship	England	Birmingham	England	6	Northern Ireland	1	1	0	0	1	0
88	1893-03-13	British Championship	England	Stoke-on-Trent	England	6	Wales	0	1	0	0	1	0
89	1893-03-18	British Championship	Wales	Wrexham	Wales	0	Scotland	8	1	0	0	0	0
90	1893-03-25	British Championship	Scotland	Glasgow	Scotland	6	Northern Ireland	1	1	0	0	1	0
91	1893-04-01	British Championship	England	Richmond	England	5	Scotland	2	1	0	0	1	0
92	1894-02-24	British Championship	Wales	Swansea	Wales	4	Northern Ireland	1	1	0	0	1	0
93	1894-03-03	British Championship	Republic of Ireland	Belfast	Northern Ireland	2	England	2	1	0	0	0	1
94	1894-03-12	British Championship	Wales	Wrexham	Wales	1	England	5	1	0	0	0	0
95	1894-03-24	British Championship	Scotland	Kilmarnock	Scotland	5	Wales	2	1	0	0	1	0
96	1894-03-31	British Championship	Republic of Ireland	Belfast	Northern Ireland	1	Scotland	2	1	0	0	0	0
97	1894-04-07	British Championship	Scotland	Glasgow	Scotland	2	England	2	1	0	0	0	1
98	1895-03-09	British Championship	England	Derby	England	9	Northern Ireland	0	1	0	0	1	0
99	1895-03-16	British Championship	Republic of Ireland	Belfast	Northern Ireland	2	Wales	2	1	0	0	0	1
100	1895-03-18	British Championship	England	London	England	1	Wales	1	1	0	0	0	1

Rows: 1-100 | Columns: 13

Now we can compute each team's KPI.

In [26]:

teams_kpi = all_matchs.groupby(["team1"],
   [st.sum(all_matchs["World_Tournament"])._as("Number_Games_World_Tournament"),
    st.sum(all_matchs["Continental_Tournament"])._as("Number_Games_Continental_Tournament"),
    st.avg(st.decode(all_matchs["World_Tournament"], 
                     1, all_matchs["Victory_team1"]))._as("Percent_Victory_World_Tournament"),
    st.avg(st.decode(all_matchs["Continental_Tournament"], 
                     1, all_matchs["Victory_team1"]))._as("Percent_Victory_Continental_Tournament"),
    st.avg(st.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"),
    st.avg(st.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"),
    st.avg(all_matchs["Victory_team1"])._as("Percent_Victory"),
    st.avg(all_matchs["Draw"])._as("Percent_Draw"),
    st.avg(all_matchs["team1_score"])._as("Avg_goals"),
    st.avg(all_matchs["team2_score"])._as("Avg_goals_conceded")]).sort({"Number_Games_World_Tournament": "desc"})
display(teams_kpi)

	Abc team1 Varchar(64)	123 Number_Games_World_Tournament Integer	123 Integer	123 Percent_Victory_World_Tournament Float	123 Float	123 Percent_Victory_Home Float	123 Percent_Victory_Away Float	123 Percent_Victory Float	123 Percent_Draw Float	123 Avg_goals Float	123 Avg_goals_conceded Float
1	Brazil	137		0.678832116788321		0.733333333333333	0.59375	0.635091496232508	0.20021528525296	2.19375672766416	0.93756727664155
2	Germany	120		0.6		0.608391608391608	0.470212765957447	0.555369127516778	0.208053691275168	2.0998322147651	1.17953020134228
3	Italy	91		0.527472527472527		0.643884892086331	0.346456692913386	0.524568393094289	0.282868525896414	1.69588313413015	0.98273572377158
4	Argentina	87		0.540229885057471		0.638095238095238	0.406060606060606	0.536060279870829	0.248654467168999	1.86544671689989	1.05059203444564
5	Mexico	75		0.306666666666667		0.542483660130719	0.423566878980892	0.495	0.2375	1.7475	1.07625
6	Spain	69		0.521739130434783		0.676855895196507	0.465686274509804	0.581395348837209	0.224806201550388	1.96744186046512	0.908527131782946
7	France	69		0.536231884057971		0.532110091743119	0.363636363636364	0.480818414322251	0.214833759590793	1.75831202046036	1.34271099744246
8	England	62		0.419354838709677		0.61878453038674	0.530864197530864	0.565762004175365	0.241127348643006	2.19937369519833	0.992693110647182
9	Uruguay	61		0.409836065573771		0.5	0.300724637681159	0.430274135876043	0.245530393325387	1.57568533969011	1.26460071513707
10	Netherlands	50		0.54		0.542253521126761	0.385214007782101	0.503373819163293	0.228070175438596	2.06207827260459	1.24966261808367
11	United States	48		0.291666666666667		0.406542056074766	0.262068965517241	0.419614147909968	0.212218649517685	1.41961414790997	1.37138263665595
12	Sweden	46		0.347826086956522		0.580555555555556	0.410926365795724	0.494288681204569	0.217030114226376	2.00519210799585	1.30633437175493
13	Serbia	43		0.395348837209302		0.544502617801047	0.38150289017341	0.456460674157303	0.219101123595506	1.81601123595506	1.37921348314607
14	Belgium	41		0.341463414634146		0.478571428571429	0.284615384615385	0.410614525139665	0.21927374301676	1.68435754189944	1.60614525139665
15	Russia	40		0.425		0.593220338983051	0.456140350877193	0.521671826625387	0.264705882352941	1.71981424148607	0.93343653250774
16	Czech Republic	38		0.368421052631579		0.654320987654321	0.34185303514377	0.483311081441923	0.221628838451268	1.8437917222964	1.23497997329773
17	South Korea	34		0.205882352941176		0.578947368421053	0.512261580381471	0.528678304239402	0.256857855361596	1.78304239401496	0.897755610972569
18	Chile	33		0.333333333333333		0.558282208588957	0.257142857142857	0.38048090523338	0.206506364922207	1.42149929278642	1.46534653465347
19	Switzerland	33		0.333333333333333		0.413333333333333	0.227586206896552	0.339095744680851	0.216755319148936	1.44148936170213	1.7313829787234
20	Japan	33		0.272727272727273		0.526315789473684	0.400943396226415	0.471830985915493	0.234154929577465	1.72007042253521	1.16021126760563
21	Hungary	32		0.46875		0.580924855491329	0.382278481012658	0.474099099099099	0.219594594594595	2.07207207207207	1.4954954954955
22	Poland	31		0.483870967741935		0.5	0.352112676056338	0.425974025974026	0.251948051948052	1.68441558441558	1.37012987012987
23	Cameroon	31		0.258064516129032		0.588235294117647	0.320574162679426	0.44258872651357	0.30062630480167	1.42171189979123	1.05427974947808
24	Austria	29		0.413793103448276		0.483766233766234	0.306338028169014	0.41112618724559	0.219810040705563	1.79782903663501	1.59294436906377
25	Paraguay	27		0.259259259259259		0.442307692307692	0.290983606557377	0.363770250368188	0.262150220913108	1.33578792341679	1.43888070692194
26	Portugal	26		0.5		0.544502617801047	0.346153846153846	0.471223021582734	0.23021582733813	1.63129496402878	1.20143884892086
27	Australia	26		0.269230769230769		0.439393939393939	0.51123595505618	0.498942917547569	0.211416490486258	2.03171247357294	1.11205073995772
28	Bulgaria	26		0.115384615384615		0.481081081081081	0.292763157894737	0.37245696400626	0.251956181533646	1.43192488262911	1.47104851330203
29	Saudi Arabia	25		0.2		0.56043956043956	0.417061611374408	0.483245149911817	0.227513227513228	1.59611992945326	1.04585537918871
30	Nigeria	24		0.291666666666667		0.62037037037037	0.319047619047619	0.458015267175573	0.295801526717557	1.49618320610687	1.00381679389313
31	Colombia	23		0.391304347826087		0.46875	0.357142857142857	0.378	0.272	1.206	1.21
32	Scotland	23		0.173913043478261		0.58477508650519	0.394230769230769	0.476510067114094	0.21744966442953	1.7503355704698	1.2255033557047
33	Romania	21		0.380952380952381		0.592233009708738	0.311864406779661	0.440184049079755	0.256134969325153	1.64570552147239	1.28834355828221
34	Denmark	19		0.526315789473684		0.552542372881356	0.346666666666667	0.44607190412783	0.203728362183755	1.77762982689747	1.42876165113182
35	South Africa	17		0.176470588235294		0.52755905511811	0.370967741935484	0.444759206798867	0.274787535410765	1.3399433427762	1.00849858356941
36	Croatia	16		0.4375		0.670588235294118	0.425742574257426	0.533834586466165	0.270676691729323	1.75187969924812	0.981203007518797
37	Turkey	15		0.466666666666667		0.45398773006135	0.338028169014085	0.381957773512476	0.238003838771593	1.3320537428023	1.42802303262956
38	Peru	15		0.266666666666667		0.428571428571429	0.22360248447205	0.312937062937063	0.243006993006993	1.22902097902098	1.46853146853147
39	Tunisia	15		0.133333333333333		0.531578947368421	0.277777777777778	0.416666666666667	0.291666666666667	1.43371212121212	1.06439393939394
40	Costa Rica	15		0.333333333333333		0.623853211009174	0.331730769230769	0.429872495446266	0.256830601092896	1.67030965391621	1.16757741347905
41	New Zealand	15		0.0		0.486842105263158	0.346820809248555	0.404761904761905	0.181547619047619	1.74702380952381	1.61309523809524
42	Morocco	13		0.153846153846154		0.612244897959184	0.354838709677419	0.451680672268908	0.300420168067227	1.38865546218487	0.873949579831933
43	Algeria	13		0.230769230769231		0.580645161290323	0.31638418079096	0.415217391304348	0.280434782608696	1.35434782608696	1.0304347826087
44	Northern Ireland	13		0.230769230769231		0.290178571428571	0.172131147540984	0.243781094527363	0.232172470978441	1.04311774461028	1.95356550580431
45	Greece	13		0.153846153846154		0.459016393442623	0.262626262626263	0.365019011406844	0.252851711026616	1.23954372623574	1.42015209125475
46	Republic of Ireland	13		0.153846153846154		0.487562189054726	0.310344827586207	0.391969407265774	0.277246653919694	1.40152963671128	1.24665391969407
47	Iran	12		0.0833333333333333		0.641509433962264	0.430379746835443	0.534368070953437	0.261640798226164	1.84035476718404	0.835920177383592
48	Ghana	12		0.333333333333333		0.672727272727273	0.384057971014493	0.48220640569395	0.256227758007117	1.6423487544484	1.04092526690391
49	Ivory Coast	11		0.272727272727273		0.691729323308271	0.4	0.502946954813359	0.24950884086444	1.63457760314342	1.05304518664047
50	Ecuador	10		0.4		0.59375	0.24025974025974	0.294491525423729	0.245762711864407	1.19491525423729	1.65042372881356
51	Egypt	10		0.1		0.582352941176471	0.373913043478261	0.486301369863014	0.25	1.64383561643836	1.03595890410959
52	Honduras	9		0.0		0.548387096774194	0.33160621761658	0.408510638297872	0.261702127659574	1.49148936170213	1.22340425531915
53	Bolivia	9		0.0		0.378787878787879	0.138613861386139	0.221957040572792	0.248210023866348	1.0381861575179	1.94272076372315
54	Norway	8		0.25		0.394822006472492	0.334346504559271	0.359947643979058	0.227748691099476	1.50130890052356	1.68455497382199
55	North Korea	7		0.142857142857143		0.733333333333333	0.408839779005525	0.426666666666667	0.27	1.61	1.03666666666667
56	Canada	6		0.0		0.338709677419355	0.281481481481481	0.331412103746398	0.239193083573487	1.04899135446686	1.39769452449568
57	El Salvador	6		0.0		0.481927710843373	0.200913242009132	0.321120689655172	0.226293103448276	1.22629310344828	1.48706896551724
58	United Arab Emirates	6		0.166666666666667		0.47682119205298	0.336842105263158	0.395744680851064	0.248936170212766	1.41489361702128	1.28510638297872
59	Iraq	6		0.0		0.666666666666667	0.436241610738255	0.467611336032389	0.277327935222672	1.62145748987854	0.945344129554656
60	Slovenia	6		0.166666666666667		0.426470588235294	0.285714285714286	0.352112676056338	0.234741784037559	1.23943661971831	1.27230046948357
61	Wales	5		0.2		0.367088607594937	0.265151515151515	0.310064935064935	0.212662337662338	1.25324675324675	1.69318181818182
62	Ukraine	5		0.4		0.528571428571429	0.337078651685393	0.440528634361233	0.290748898678414	1.39647577092511	0.973568281938326
63	Senegal	5		0.4		0.648	0.336065573770492	0.422916666666667	0.26875	1.2875	1.00208333333333
64	Slovakia	4		0.25		0.45	0.333333333333333	0.401606425702811	0.236947791164659	1.44176706827309	1.30120481927711
65	Israel	3		0.0		0.389261744966443	0.30327868852459	0.349246231155779	0.251256281407035	1.45477386934673	1.45226130653266
66	DR Congo	3		0.0		0.615384615384615	0.308108108108108	0.391304347826087	0.268115942028986	1.5024154589372	1.2536231884058
67	Tahiti	3		0.0		0.483870967741935	0.584615384615385	0.518324607329843	0.12565445026178	2.51832460732984	1.70157068062827
68	Bosnia and Herzegovina	3		0.333333333333333		0.428571428571429	0.304878048780488	0.384615384615385	0.214285714285714	1.42857142857143	1.39010989010989
69	Haiti	3		0.0		0.530434782608696	0.358585858585859	0.413875598086124	0.232057416267943	1.5622009569378	1.31818181818182
70	Trinidad and Tobago	3		0.0		0.622448979591837	0.376344086021505	0.452648475120385	0.210272873194221	1.73996789727127	1.27447833065811
71	Cuba	3		0.333333333333333		0.45	0.372881355932203	0.346153846153846	0.25	1.32051282051282	1.43910256410256
72	Angola	3		0.0		0.5625	0.253521126760563	0.352750809061489	0.352750809061489	1.1747572815534	1.042071197411
73	China PR	3		0.0		0.579617834394904	0.395833333333333	0.488117001828154	0.226691042047532	1.83729433272395	1.08775137111517
74	Jamaica	3		0.333333333333333		0.530487804878049	0.309734513274336	0.387715930902111	0.228406909788868	1.31861804222649	1.33781190019194
75	Togo	3		0.0		0.484536082474227	0.236363636363636	0.314285714285714	0.245714285714286	1.08285714285714	1.39142857142857
76	Kuwait	3		0.0		0.457317073170732	0.379166666666667	0.424074074074074	0.272222222222222	1.55555555555556	1.07962962962963
77	Indonesia	1		0.0		0.492647058823529	0.359060402684564	0.377176015473888	0.187620889748549	1.67311411992263	1.67504835589942
78	Northern Mariana Islands	0		[null]		0.0	0.133333333333333	0.111111111111111	0.0555555555555556	0.888888888888889	4.16666666666667
79	Ellan Vannin	0		[null]		[null]	0.0	0.0	0.666666666666667	0.666666666666667	1.0
80	Montserrat	0		[null]		1.0	0.105263157894737	0.133333333333333	0.0666666666666667	1.03333333333333	4.4
81	Georgia	0		[null]		0.490909090909091	0.23	0.293532338308458	0.189054726368159	1.12935323383085	1.48258706467662
82	Lesotho	0		[null]		0.216867469879518	0.150943396226415	0.169014084507042	0.309859154929577	0.779342723004695	1.61971830985915
83	Anguilla	0		[null]		0.0	0.09375	0.0625	0.0625	0.666666666666667	4.04166666666667
84	Venezuela	0		[null]		0.470588235294118	0.238636363636364	0.23598820058997	0.206489675516224	0.994100294985251	2.01769911504425
85	Gotland	0		[null]		0.6	0.285714285714286	0.346153846153846	0.153846153846154	2.46153846153846	2.0
86	Mongolia	0		[null]		1.0	0.241379310344828	0.266666666666667	0.0888888888888889	1.08888888888889	3.22222222222222
87	Vietnam Republic	0		[null]		0.615384615384615	0.39047619047619	0.431372549019608	0.176470588235294	1.83660130718954	1.73202614379085
88	Curaçao	0		[null]		0.666666666666667	0.294444444444444	0.356435643564356	0.267326732673267	1.54785478547855	1.67656765676568
89	Jordan	0		[null]		0.411214953271028	0.286624203821656	0.347953216374269	0.286549707602339	1.18421052631579	1.1140350877193
90	Central African Republic	0		[null]		0.352941176470588	0.12	0.168831168831169	0.207792207792208	1.02597402597403	2.06493506493507
91	Nepal	0		[null]		0.394736842105263	0.146341463414634	0.206666666666667	0.14	0.813333333333333	2.58666666666667
92	Syria	0		[null]		0.431372549019608	0.331818181818182	0.373595505617978	0.23314606741573	1.48033707865169	1.3314606741573
93	Zanzibar	0		[null]		0.172413793103448	0.19047619047619	0.187817258883249	0.192893401015228	0.888324873096447	2.16243654822335
94	Raetia	0		[null]		[null]	0.0	0.0	0.0	0.0	5.0
95	Liechtenstein	0		[null]		0.148148148148148	0.05	0.0792682926829268	0.121951219512195	0.439024390243902	2.83536585365854
96	Arameans Suryoye	0		[null]		[null]	0.571428571428571	0.571428571428571	0.142857142857143	1.42857142857143	1.14285714285714
97	Tuvalu	0		[null]		[null]	0.210526315789474	0.210526315789474	0.105263157894737	1.15789473684211	5.31578947368421
98	Tamil Eelam	0		[null]		[null]	0.166666666666667	0.166666666666667	0.0	1.0	4.16666666666667
99	Cyprus	0		[null]		0.301470588235294	0.0909090909090909	0.185975609756098	0.167682926829268	0.86890243902439	2.2469512195122
100	Kosovo	0		[null]		0.4	0.5	0.428571428571429	0.285714285714286	2.0	1.71428571428571

Rows: 1-100 | Columns: 11

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.

In [27]:

vp.drop("teams_kpi", relation_type = "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")
display(teams_kpi)

	Abc team1 Varchar(64)	123 Number_Games_World_Tournament Integer	123 Integer	123 Percent_Victory_World_Tournament Float	123 Float	123 Percent_Victory_Home Float	123 Percent_Victory_Away Float	123 Percent_Victory Float	123 Percent_Draw Float	123 Avg_goals Float	123 Avg_goals_conceded Float	123 nb_World_Cup Integer	123 nb_Continental_Cup Integer
1	Brazil	137		0.678832116788321		0.733333333333333	0.59375	0.635091496232508	0.20021528525296	2.19375672766416	0.93756727664155	4	8
2	Germany	120		0.6		0.608391608391608	0.470212765957447	0.555369127516778	0.208053691275168	2.0998322147651	1.17953020134228	4	3
3	Italy	91		0.527472527472527		0.643884892086331	0.346456692913386	0.524568393094289	0.282868525896414	1.69588313413015	0.98273572377158	3	1
4	Argentina	87		0.540229885057471		0.638095238095238	0.406060606060606	0.536060279870829	0.248654467168999	1.86544671689989	1.05059203444564	2	8
5	Mexico	75		0.306666666666667		0.542483660130719	0.423566878980892	0.495	0.2375	1.7475	1.07625	0	6
6	France	69		0.536231884057971		0.532110091743119	0.363636363636364	0.480818414322251	0.214833759590793	1.75831202046036	1.34271099744246	1	2
7	Spain	69		0.521739130434783		0.676855895196507	0.465686274509804	0.581395348837209	0.224806201550388	1.96744186046512	0.908527131782946	1	3
8	England	62		0.419354838709677		0.61878453038674	0.530864197530864	0.565762004175365	0.241127348643006	2.19937369519833	0.992693110647182	1	0
9	Uruguay	61		0.409836065573771		0.5	0.300724637681159	0.430274135876043	0.245530393325387	1.57568533969011	1.26460071513707	2	8
10	Netherlands	50		0.54		0.542253521126761	0.385214007782101	0.503373819163293	0.228070175438596	2.06207827260459	1.24966261808367	0	1
11	United States	48		0.291666666666667		0.406542056074766	0.262068965517241	0.419614147909968	0.212218649517685	1.41961414790997	1.37138263665595	0	2
12	Sweden	46		0.347826086956522		0.580555555555556	0.410926365795724	0.494288681204569	0.217030114226376	2.00519210799585	1.30633437175493	[null]	[null]
13	Serbia	43		0.395348837209302		0.544502617801047	0.38150289017341	0.456460674157303	0.219101123595506	1.81601123595506	1.37921348314607	[null]	[null]
14	Belgium	41		0.341463414634146		0.478571428571429	0.284615384615385	0.410614525139665	0.21927374301676	1.68435754189944	1.60614525139665	[null]	[null]
15	Russia	40		0.425		0.593220338983051	0.456140350877193	0.521671826625387	0.264705882352941	1.71981424148607	0.93343653250774	0	1
16	Czech Republic	38		0.368421052631579		0.654320987654321	0.34185303514377	0.483311081441923	0.221628838451268	1.8437917222964	1.23497997329773	[null]	[null]
17	South Korea	34		0.205882352941176		0.578947368421053	0.512261580381471	0.528678304239402	0.256857855361596	1.78304239401496	0.897755610972569	0	3
18	Chile	33		0.333333333333333		0.558282208588957	0.257142857142857	0.38048090523338	0.206506364922207	1.42149929278642	1.46534653465347	0	2
19	Switzerland	33		0.333333333333333		0.413333333333333	0.227586206896552	0.339095744680851	0.216755319148936	1.44148936170213	1.7313829787234	[null]	[null]
20	Japan	33		0.272727272727273		0.526315789473684	0.400943396226415	0.471830985915493	0.234154929577465	1.72007042253521	1.16021126760563	0	2
21	Hungary	32		0.46875		0.580924855491329	0.382278481012658	0.474099099099099	0.219594594594595	2.07207207207207	1.4954954954955	[null]	[null]
22	Cameroon	31		0.258064516129032		0.588235294117647	0.320574162679426	0.44258872651357	0.30062630480167	1.42171189979123	1.05427974947808	0	2
23	Poland	31		0.483870967741935		0.5	0.352112676056338	0.425974025974026	0.251948051948052	1.68441558441558	1.37012987012987	[null]	[null]
24	Austria	29		0.413793103448276		0.483766233766234	0.306338028169014	0.41112618724559	0.219810040705563	1.79782903663501	1.59294436906377	[null]	[null]
25	Paraguay	27		0.259259259259259		0.442307692307692	0.290983606557377	0.363770250368188	0.262150220913108	1.33578792341679	1.43888070692194	0	3
26	Portugal	26		0.5		0.544502617801047	0.346153846153846	0.471223021582734	0.23021582733813	1.63129496402878	1.20143884892086	[null]	[null]
27	Bulgaria	26		0.115384615384615		0.481081081081081	0.292763157894737	0.37245696400626	0.251956181533646	1.43192488262911	1.47104851330203	[null]	[null]
28	Australia	26		0.269230769230769		0.439393939393939	0.51123595505618	0.498942917547569	0.211416490486258	2.03171247357294	1.11205073995772	0	1
29	Saudi Arabia	25		0.2		0.56043956043956	0.417061611374408	0.483245149911817	0.227513227513228	1.59611992945326	1.04585537918871	[null]	[null]
30	Nigeria	24		0.291666666666667		0.62037037037037	0.319047619047619	0.458015267175573	0.295801526717557	1.49618320610687	1.00381679389313	0	3
31	Scotland	23		0.173913043478261		0.58477508650519	0.394230769230769	0.476510067114094	0.21744966442953	1.7503355704698	1.2255033557047	[null]	[null]
32	Colombia	23		0.391304347826087		0.46875	0.357142857142857	0.378	0.272	1.206	1.21	0	1
33	Romania	21		0.380952380952381		0.592233009708738	0.311864406779661	0.440184049079755	0.256134969325153	1.64570552147239	1.28834355828221	[null]	[null]
34	Denmark	19		0.526315789473684		0.552542372881356	0.346666666666667	0.44607190412783	0.203728362183755	1.77762982689747	1.42876165113182	0	1
35	South Africa	17		0.176470588235294		0.52755905511811	0.370967741935484	0.444759206798867	0.274787535410765	1.3399433427762	1.00849858356941	[null]	[null]
36	Croatia	16		0.4375		0.670588235294118	0.425742574257426	0.533834586466165	0.270676691729323	1.75187969924812	0.981203007518797	[null]	[null]
37	Costa Rica	15		0.333333333333333		0.623853211009174	0.331730769230769	0.429872495446266	0.256830601092896	1.67030965391621	1.16757741347905	[null]	[null]
38	Turkey	15		0.466666666666667		0.45398773006135	0.338028169014085	0.381957773512476	0.238003838771593	1.3320537428023	1.42802303262956	[null]	[null]
39	Peru	15		0.266666666666667		0.428571428571429	0.22360248447205	0.312937062937063	0.243006993006993	1.22902097902098	1.46853146853147	0	2
40	Tunisia	15		0.133333333333333		0.531578947368421	0.277777777777778	0.416666666666667	0.291666666666667	1.43371212121212	1.06439393939394	0	1
41	New Zealand	15		0.0		0.486842105263158	0.346820809248555	0.404761904761905	0.181547619047619	1.74702380952381	1.61309523809524	[null]	[null]
42	Northern Ireland	13		0.230769230769231		0.290178571428571	0.172131147540984	0.243781094527363	0.232172470978441	1.04311774461028	1.95356550580431	[null]	[null]
43	Morocco	13		0.153846153846154		0.612244897959184	0.354838709677419	0.451680672268908	0.300420168067227	1.38865546218487	0.873949579831933	[null]	[null]
44	Republic of Ireland	13		0.153846153846154		0.487562189054726	0.310344827586207	0.391969407265774	0.277246653919694	1.40152963671128	1.24665391969407	[null]	[null]
45	Greece	13		0.153846153846154		0.459016393442623	0.262626262626263	0.365019011406844	0.252851711026616	1.23954372623574	1.42015209125475	0	1
46	Algeria	13		0.230769230769231		0.580645161290323	0.31638418079096	0.415217391304348	0.280434782608696	1.35434782608696	1.0304347826087	0	1
47	Iran	12		0.0833333333333333		0.641509433962264	0.430379746835443	0.534368070953437	0.261640798226164	1.84035476718404	0.835920177383592	0	2
48	Ghana	12		0.333333333333333		0.672727272727273	0.384057971014493	0.48220640569395	0.256227758007117	1.6423487544484	1.04092526690391	0	2
49	Ivory Coast	11		0.272727272727273		0.691729323308271	0.4	0.502946954813359	0.24950884086444	1.63457760314342	1.05304518664047	0	2
50	Egypt	10		0.1		0.582352941176471	0.373913043478261	0.486301369863014	0.25	1.64383561643836	1.03595890410959	0	5
51	Ecuador	10		0.4		0.59375	0.24025974025974	0.294491525423729	0.245762711864407	1.19491525423729	1.65042372881356	0	1
52	Honduras	9		0.0		0.548387096774194	0.33160621761658	0.408510638297872	0.261702127659574	1.49148936170213	1.22340425531915	[null]	[null]
53	Bolivia	9		0.0		0.378787878787879	0.138613861386139	0.221957040572792	0.248210023866348	1.0381861575179	1.94272076372315	[null]	[null]
54	Norway	8		0.25		0.394822006472492	0.334346504559271	0.359947643979058	0.227748691099476	1.50130890052356	1.68455497382199	[null]	[null]
55	North Korea	7		0.142857142857143		0.733333333333333	0.408839779005525	0.426666666666667	0.27	1.61	1.03666666666667	[null]	[null]
56	United Arab Emirates	6		0.166666666666667		0.47682119205298	0.336842105263158	0.395744680851064	0.248936170212766	1.41489361702128	1.28510638297872	[null]	[null]
57	Canada	6		0.0		0.338709677419355	0.281481481481481	0.331412103746398	0.239193083573487	1.04899135446686	1.39769452449568	0	2
58	Iraq	6		0.0		0.666666666666667	0.436241610738255	0.467611336032389	0.277327935222672	1.62145748987854	0.945344129554656	0	1
59	Slovenia	6		0.166666666666667		0.426470588235294	0.285714285714286	0.352112676056338	0.234741784037559	1.23943661971831	1.27230046948357	[null]	[null]
60	El Salvador	6		0.0		0.481927710843373	0.200913242009132	0.321120689655172	0.226293103448276	1.22629310344828	1.48706896551724	[null]	[null]
61	Senegal	5		0.4		0.648	0.336065573770492	0.422916666666667	0.26875	1.2875	1.00208333333333	[null]	[null]
62	Wales	5		0.2		0.367088607594937	0.265151515151515	0.310064935064935	0.212662337662338	1.25324675324675	1.69318181818182	[null]	[null]
63	Ukraine	5		0.4		0.528571428571429	0.337078651685393	0.440528634361233	0.290748898678414	1.39647577092511	0.973568281938326	[null]	[null]
64	Slovakia	4		0.25		0.45	0.333333333333333	0.401606425702811	0.236947791164659	1.44176706827309	1.30120481927711	[null]	[null]
65	Angola	3		0.0		0.5625	0.253521126760563	0.352750809061489	0.352750809061489	1.1747572815534	1.042071197411	[null]	[null]
66	Trinidad and Tobago	3		0.0		0.622448979591837	0.376344086021505	0.452648475120385	0.210272873194221	1.73996789727127	1.27447833065811	[null]	[null]
67	Haiti	3		0.0		0.530434782608696	0.358585858585859	0.413875598086124	0.232057416267943	1.5622009569378	1.31818181818182	[null]	[null]
68	Jamaica	3		0.333333333333333		0.530487804878049	0.309734513274336	0.387715930902111	0.228406909788868	1.31861804222649	1.33781190019194	[null]	[null]
69	Cuba	3		0.333333333333333		0.45	0.372881355932203	0.346153846153846	0.25	1.32051282051282	1.43910256410256	[null]	[null]
70	Bosnia and Herzegovina	3		0.333333333333333		0.428571428571429	0.304878048780488	0.384615384615385	0.214285714285714	1.42857142857143	1.39010989010989	[null]	[null]
71	China PR	3		0.0		0.579617834394904	0.395833333333333	0.488117001828154	0.226691042047532	1.83729433272395	1.08775137111517	0	1
72	Israel	3		0.0		0.389261744966443	0.30327868852459	0.349246231155779	0.251256281407035	1.45477386934673	1.45226130653266	0	1
73	Kuwait	3		0.0		0.457317073170732	0.379166666666667	0.424074074074074	0.272222222222222	1.55555555555556	1.07962962962963	0	1
74	Tahiti	3		0.0		0.483870967741935	0.584615384615385	0.518324607329843	0.12565445026178	2.51832460732984	1.70157068062827	[null]	[null]
75	DR Congo	3		0.0		0.615384615384615	0.308108108108108	0.391304347826087	0.268115942028986	1.5024154589372	1.2536231884058	0	1
76	Togo	3		0.0		0.484536082474227	0.236363636363636	0.314285714285714	0.245714285714286	1.08285714285714	1.39142857142857	[null]	[null]
77	Indonesia	1		0.0		0.492647058823529	0.359060402684564	0.377176015473888	0.187620889748549	1.67311411992263	1.67504835589942	[null]	[null]
78	Montenegro	0		[null]		0.62962962962963	0.153846153846154	0.356164383561644	0.26027397260274	1.20547945205479	1.35616438356164	[null]	[null]
79	Antigua and Barbuda	0		[null]		0.470588235294118	0.233333333333333	0.310734463276836	0.209039548022599	1.3954802259887	1.70056497175141	[null]	[null]
80	Gabon	0		[null]		0.472727272727273	0.27027027027027	0.36085626911315	0.2782874617737	1.19266055045872	1.13149847094801	[null]	[null]
81	Guyana	0		[null]		0.387096774193548	0.256410256410256	0.301652892561983	0.206611570247934	1.19834710743802	1.74793388429752	[null]	[null]
82	Gambia	0		[null]		0.343283582089552	0.16	0.235602094240838	0.287958115183246	0.93717277486911	1.45026178010471	[null]	[null]
83	Luxembourg	0		[null]		0.110294117647059	0.0588235294117647	0.069060773480663	0.11878453038674	0.566298342541437	2.8121546961326	[null]	[null]
84	Azerbaijan	0		[null]		0.361702127659574	0.150442477876106	0.182692307692308	0.264423076923077	0.697115384615385	1.69711538461538	[null]	[null]
85	Tonga	0		[null]		0.0	0.258064516129032	0.270833333333333	0.125	1.1875	3.375	[null]	[null]
86	Northern Cyprus	0		[null]		0.8	0.4	0.533333333333333	0.0666666666666667	2.26666666666667	1.2	[null]	[null]
87	Silesia	0		[null]		0.375	[null]	0.375	0.25	3.0	2.125	[null]	[null]
88	Benin	0		[null]		0.276315789473684	0.137254901960784	0.206140350877193	0.228070175438596	1.00438596491228	1.95175438596491	[null]	[null]
89	Burma	0		[null]		0.51219512195122	0.45	0.449685534591195	0.172955974842767	1.71383647798742	1.4811320754717	[null]	[null]
90	Somalia	0		[null]		0.333333333333333	0.0833333333333333	0.0891089108910891	0.118811881188119	0.504950495049505	2.8019801980198	[null]	[null]
91	Nicaragua	0		[null]		0.6	0.0989010989010989	0.158333333333333	0.0666666666666667	0.825	3.19166666666667	[null]	[null]
92	Malawi	0		[null]		0.441048034934498	0.307692307692308	0.35673624288425	0.280834914611006	1.23908918406072	1.29981024667932	[null]	[null]
93	British Virgin Islands	0		[null]		0.363636363636364	0.16	0.197530864197531	0.160493827160494	0.876543209876543	3.11111111111111	[null]	[null]
94	Andalusia	0		[null]		0.636363636363636	[null]	0.636363636363636	0.363636363636364	2.0	0.818181818181818	[null]	[null]
95	Zambia	0		[null]		0.585798816568047	0.408408408408408	0.463636363636364	0.254545454545455	1.59090909090909	1.02727272727273	0	1
96	Qatar	0		[null]		0.493670886075949	0.314606741573034	0.402127659574468	0.248936170212766	1.4	1.18297872340426	[null]	[null]
97	Basque Country	0		[null]		0.633333333333333	0.695652173913043	0.660377358490566	0.169811320754717	2.75471698113208	1.33962264150943	[null]	[null]
98	Libya	0		[null]		0.587628865979381	0.233576642335766	0.378378378378378	0.246621621621622	1.29054054054054	1.21621621621622	[null]	[null]
99	Guinea	0		[null]		0.65	0.254237288135593	0.370892018779343	0.291079812206573	1.36384976525822	1.22065727699531	[null]	[null]
100	Artsakh	0		[null]		1.0	[null]	1.0	0.0	3.0	0.0	[null]	[null]

Rows: 1-100 | Columns: 13

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

In [28]:

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

	Abc team1 Varchar(64)	123 Number_Games_World_Tournament Integer	123 Integer	123 Percent_Victory_World_Tournament Float	123 Float	123 Percent_Victory_Home Float	123 Percent_Victory_Away Float	123 Percent_Victory Float	123 Percent_Draw Float	123 Avg_goals Float	123 Avg_goals_conceded Float	123 nb_World_Cup Integer	123 nb_Continental_Cup Integer	Abc confederation Varchar(8)
1	Madagascar	0		[null]		0.464285714285714	0.32183908045977	0.366666666666667	0.194444444444444	1.33333333333333	1.63333333333333	[null]	[null]	CAF
2	North Vietnam	0		[null]		[null]	0.210526315789474	0.210526315789474	0.157894736842105	1.63157894736842	2.26315789473684	[null]	[null]	OFC
3	Abkhazia	0		[null]		[null]	0.166666666666667	0.166666666666667	0.5	1.0	1.5	[null]	[null]	OFC
4	Canada	6		0.0		0.338709677419355	0.281481481481481	0.331412103746398	0.239193083573487	1.04899135446686	1.39769452449568	0	2	CONCACAF
5	Monaco	0		[null]		[null]	0.166666666666667	0.166666666666667	0.166666666666667	1.16666666666667	8.33333333333333	[null]	[null]	OFC
6	Saint Pierre and Miquelon	0		[null]		[null]	0.0	0.0	0.0	0.166666666666667	10.3333333333333	[null]	[null]	OFC
7	Algeria	13		0.230769230769231		0.580645161290323	0.31638418079096	0.415217391304348	0.280434782608696	1.35434782608696	1.0304347826087	0	1	CAF
8	Iran	12		0.0833333333333333		0.641509433962264	0.430379746835443	0.534368070953437	0.261640798226164	1.84035476718404	0.835920177383592	0	2	AFC
9	Comoros	0		[null]		0.0	0.142857142857143	0.0714285714285714	0.285714285714286	0.5	1.71428571428571	[null]	[null]	CAF
10	Namibia	0		[null]		0.351851851851852	0.195402298850575	0.236842105263158	0.284210526315789	1.01578947368421	1.53157894736842	[null]	[null]	CAF
11	South Sudan	0		[null]		0.5	0.142857142857143	0.166666666666667	0.277777777777778	0.555555555555556	1.55555555555556	[null]	[null]	CAF
12	Belize	0		[null]		0.5	0.037037037037037	0.186440677966102	0.169491525423729	1.03389830508475	2.23728813559322	[null]	[null]	OFC
13	Vietnam	0		[null]		0.46551724137931	0.384615384615385	0.376543209876543	0.203703703703704	1.66666666666667	1.59259259259259	[null]	[null]	AFC
14	Puerto Rico	0		[null]		0.178571428571429	0.196078431372549	0.188118811881188	0.178217821782178	1.00990099009901	2.2970297029703	[null]	[null]	OFC
15	Andorra	0		[null]		0.0571428571428571	0.0	0.0229007633587786	0.0763358778625954	0.282442748091603	2.82442748091603	[null]	[null]	UEFA
16	France	69		0.536231884057971		0.532110091743119	0.363636363636364	0.480818414322251	0.214833759590793	1.75831202046036	1.34271099744246	1	2	UEFA
17	Belarus	0		[null]		0.392857142857143	0.285714285714286	0.30622009569378	0.258373205741627	1.25358851674641	1.44019138755981	[null]	[null]	UEFA
18	Ynys Môn	0		[null]		[null]	0.470588235294118	0.470588235294118	0.215686274509804	1.70588235294118	1.49019607843137	[null]	[null]	OFC
19	Guatemala	0		[null]		0.468085106382979	0.285714285714286	0.341232227488152	0.258293838862559	1.32938388625592	1.3696682464455	[null]	[null]	CONCACAF
20	Turkmenistan	0		[null]		0.538461538461538	0.365079365079365	0.384615384615385	0.170940170940171	1.63247863247863	1.47863247863248	[null]	[null]	AFC
21	China PR	3		0.0		0.579617834394904	0.395833333333333	0.488117001828154	0.226691042047532	1.83729433272395	1.08775137111517	0	1	AFC
22	Serbia	43		0.395348837209302		0.544502617801047	0.38150289017341	0.456460674157303	0.219101123595506	1.81601123595506	1.37921348314607	[null]	[null]	UEFA
23	Italy	91		0.527472527472527		0.643884892086331	0.346456692913386	0.524568393094289	0.282868525896414	1.69588313413015	0.98273572377158	3	1	UEFA
24	Uganda	0		[null]		0.591954022988506	0.382789317507418	0.440497335701599	0.253996447602131	1.61811722912966	1.2113676731794	[null]	[null]	CAF
25	Bermuda	0		[null]		0.403846153846154	0.244444444444444	0.353846153846154	0.184615384615385	1.67692307692308	1.56153846153846	[null]	[null]	CONCACAF
26	Niger	0		[null]		0.448275862068966	0.0898876404494382	0.234972677595628	0.256830601092896	0.934426229508197	1.6448087431694	[null]	[null]	CAF
27	Rwanda	0		[null]		0.446428571428571	0.324786324786325	0.333333333333333	0.253521126760563	1.05164319248826	1.22535211267606	[null]	[null]	CAF
28	Brunei	0		[null]		0.5	0.111111111111111	0.1	0.0666666666666667	0.65	3.76666666666667	[null]	[null]	OFC
29	Guadeloupe	0		[null]		0.507692307692308	0.380281690140845	0.415525114155251	0.182648401826484	1.67123287671233	1.48401826484018	[null]	[null]	CONCACAF
30	Panama	0		[null]		0.5	0.216049382716049	0.310160427807487	0.254010695187166	1.16310160427807	1.56417112299465	[null]	[null]	CONMEBOL
31	United States	48		0.291666666666667		0.406542056074766	0.262068965517241	0.419614147909968	0.212218649517685	1.41961414790997	1.37138263665595	0	2	CONMEBOL
32	Sierra Leone	0		[null]		0.541666666666667	0.194915254237288	0.308333333333333	0.241666666666667	0.958333333333333	1.3625	[null]	[null]	CAF
33	Costa Rica	15		0.333333333333333		0.623853211009174	0.331730769230769	0.429872495446266	0.256830601092896	1.67030965391621	1.16757741347905	[null]	[null]	CONMEBOL
34	Hitra	0		[null]		[null]	0.166666666666667	0.166666666666667	0.0	1.16666666666667	4.83333333333333	[null]	[null]	OFC
35	Iraqi Kurdistan	0		[null]		1.0	0.352941176470588	0.45	0.25	2.1	1.15	[null]	[null]	OFC
36	Philippines	0		[null]		0.476190476190476	0.227272727272727	0.274611398963731	0.145077720207254	1.16580310880829	2.27979274611399	[null]	[null]	AFC
37	Singapore	0		[null]		0.406779661016949	0.244360902255639	0.304093567251462	0.192982456140351	1.33918128654971	1.87329434697856	[null]	[null]	AFC
38	Macau	0		[null]		0.217391304347826	0.142857142857143	0.141509433962264	0.0943396226415094	0.764150943396226	3.26415094339623	[null]	[null]	AFC
39	Mali	0		[null]		0.568	0.325581395348837	0.399563318777293	0.246724890829694	1.27292576419214	1.17685589519651	[null]	[null]	CAF
40	Czech Republic	38		0.368421052631579		0.654320987654321	0.34185303514377	0.483311081441923	0.221628838451268	1.8437917222964	1.23497997329773	[null]	[null]	UEFA
41	Bangladesh	0		[null]		0.551724137931034	0.179245283018868	0.232432432432432	0.210810810810811	0.87027027027027	2.02702702702703	[null]	[null]	AFC
42	U.S. Virgin Islands	0		[null]		0.2	0.0	0.1	0.15	0.5	4.875	[null]	[null]	OFC
43	Zimbabwe	0		[null]		0.540322580645161	0.372093023255814	0.41871921182266	0.25615763546798	1.35221674876847	1.16748768472906	[null]	[null]	CAF
44	Papua New Guinea	0		[null]		0.333333333333333	0.24	0.268041237113402	0.175257731958763	1.90721649484536	2.22680412371134	[null]	[null]	OFC
45	Greece	13		0.153846153846154		0.459016393442623	0.262626262626263	0.365019011406844	0.252851711026616	1.23954372623574	1.42015209125475	0	1	UEFA
46	Réunion	0		[null]		0.212121212121212	0.416666666666667	0.333333333333333	0.160493827160494	1.66666666666667	2.35802469135802	[null]	[null]	OFC
47	Mauritania	0		[null]		0.3	0.0857142857142857	0.15028901734104	0.260115606936416	0.734104046242775	1.64161849710983	[null]	[null]	CAF
48	Eswatini	0		[null]		0.240963855421687	0.177083333333333	0.207070707070707	0.272727272727273	0.828282828282828	1.74242424242424	[null]	[null]	CAF
49	Iceland	0		[null]		0.430769230769231	0.23780487804878	0.297435897435897	0.18974358974359	1.16410256410256	1.7	[null]	[null]	UEFA
50	Germany	120		0.6		0.608391608391608	0.470212765957447	0.555369127516778	0.208053691275168	2.0998322147651	1.17953020134228	4	3	UEFA
51	Botswana	0		[null]		0.382352941176471	0.147058823529412	0.265560165975104	0.302904564315353	0.796680497925311	1.29460580912863	[null]	[null]	CAF
52	Eritrea	0		[null]		0.416666666666667	0.12	0.157142857142857	0.228571428571429	0.671428571428571	1.74285714285714	[null]	[null]	CAF
53	Sudan	0		[null]		0.592105263157895	0.278301886792453	0.336	0.248	1.14133333333333	1.368	0	1	CAF
54	Turks and Caicos Islands	0		[null]		[null]	0.222222222222222	0.176470588235294	0.0588235294117647	0.588235294117647	3.41176470588235	[null]	[null]	OFC
55	Grenada	0		[null]		0.4	0.353982300884956	0.352631578947368	0.221052631578947	1.78947368421053	1.76315789473684	[null]	[null]	CONCACAF
56	Kosovo	0		[null]		0.4	0.5	0.428571428571429	0.285714285714286	2.0	1.71428571428571	[null]	[null]	UEFA
57	Tuvalu	0		[null]		[null]	0.210526315789474	0.210526315789474	0.105263157894737	1.15789473684211	5.31578947368421	[null]	[null]	OFC
58	Nepal	0		[null]		0.394736842105263	0.146341463414634	0.206666666666667	0.14	0.813333333333333	2.58666666666667	[null]	[null]	AFC
59	Kiribati	0		[null]		[null]	0.0	0.0	0.0	0.333333333333333	9.66666666666667	[null]	[null]	OFC
60	Guinea-Bissau	0		[null]		0.451612903225806	0.230769230769231	0.272727272727273	0.223140495867769	1.03305785123967	1.56198347107438	[null]	[null]	CAF
61	Bahamas	0		[null]		0.333333333333333	0.181818181818182	0.259259259259259	0.148148148148148	1.14814814814815	3.33333333333333	[null]	[null]	OFC
62	New Zealand	15		0.0		0.486842105263158	0.346820809248555	0.404761904761905	0.181547619047619	1.74702380952381	1.61309523809524	[null]	[null]	OFC
63	Mozambique	0		[null]		0.530612244897959	0.2	0.315018315018315	0.252747252747253	1.05494505494505	1.28571428571429	[null]	[null]	CAF
64	Uruguay	61		0.409836065573771		0.5	0.300724637681159	0.430274135876043	0.245530393325387	1.57568533969011	1.26460071513707	2	8	CONMEBOL
65	Saint Martin	0		[null]		0.75	0.16	0.303030303030303	0.0909090909090909	1.12121212121212	2.90909090909091	[null]	[null]	OFC
66	Albania	0		[null]		0.404255319148936	0.173076923076923	0.23972602739726	0.222602739726027	0.900684931506849	1.5958904109589	[null]	[null]	UEFA
67	Angola	3		0.0		0.5625	0.253521126760563	0.352750809061489	0.352750809061489	1.1747572815534	1.042071197411	[null]	[null]	CAF
68	Brazil	137		0.678832116788321		0.733333333333333	0.59375	0.635091496232508	0.20021528525296	2.19375672766416	0.93756727664155	4	8	CONMEBOL
69	Scotland	23		0.173913043478261		0.58477508650519	0.394230769230769	0.476510067114094	0.21744966442953	1.7503355704698	1.2255033557047	[null]	[null]	UEFA
70	Dominican Republic	0		[null]		0.428571428571429	0.333333333333333	0.362637362637363	0.142857142857143	1.57142857142857	1.85714285714286	[null]	[null]	OFC
71	Alderney	0		[null]		[null]	0.0833333333333333	0.0833333333333333	0.0	0.583333333333333	4.33333333333333	[null]	[null]	OFC
72	Colombia	23		0.391304347826087		0.46875	0.357142857142857	0.378	0.272	1.206	1.21	0	1	CONMEBOL
73	Bonaire	0		[null]		[null]	0.307692307692308	0.307692307692308	0.153846153846154	1.61538461538462	3.38461538461538	[null]	[null]	OFC
74	Palestine	0		[null]		0.0	0.25	0.251655629139073	0.264900662251656	1.28476821192053	1.60264900662252	[null]	[null]	AFC
75	Kazakhstan	0		[null]		0.394736842105263	0.16	0.239263803680982	0.239263803680982	1.03680981595092	1.6441717791411	[null]	[null]	UEFA
76	Arameans Suryoye	0		[null]		[null]	0.571428571428571	0.571428571428571	0.142857142857143	1.42857142857143	1.14285714285714	[null]	[null]	OFC
77	Togo	3		0.0		0.484536082474227	0.236363636363636	0.314285714285714	0.245714285714286	1.08285714285714	1.39142857142857	[null]	[null]	CAF
78	Central African Republic	0		[null]		0.352941176470588	0.12	0.168831168831169	0.207792207792208	1.02597402597403	2.06493506493507	[null]	[null]	CAF
79	South Africa	17		0.176470588235294		0.52755905511811	0.370967741935484	0.444759206798867	0.274787535410765	1.3399433427762	1.00849858356941	[null]	[null]	CAF
80	Zanzibar	0		[null]		0.172413793103448	0.19047619047619	0.187817258883249	0.192893401015228	0.888324873096447	2.16243654822335	[null]	[null]	OFC
81	Pakistan	0		[null]		0.366666666666667	0.204918032786885	0.197802197802198	0.186813186813187	0.934065934065934	2.39010989010989	[null]	[null]	AFC
82	Mongolia	0		[null]		1.0	0.241379310344828	0.266666666666667	0.0888888888888889	1.08888888888889	3.22222222222222	[null]	[null]	OFC
83	Vietnam Republic	0		[null]		0.615384615384615	0.39047619047619	0.431372549019608	0.176470588235294	1.83660130718954	1.73202614379085	[null]	[null]	AFC
84	Curaçao	0		[null]		0.666666666666667	0.294444444444444	0.356435643564356	0.267326732673267	1.54785478547855	1.67656765676568	[null]	[null]	CONCACAF
85	Venezuela	0		[null]		0.470588235294118	0.238636363636364	0.23598820058997	0.206489675516224	0.994100294985251	2.01769911504425	[null]	[null]	CONMEBOL
86	Raetia	0		[null]		[null]	0.0	0.0	0.0	0.0	5.0	[null]	[null]	OFC
87	Anguilla	0		[null]		0.0	0.09375	0.0625	0.0625	0.666666666666667	4.04166666666667	[null]	[null]	OFC
88	Gotland	0		[null]		0.6	0.285714285714286	0.346153846153846	0.153846153846154	2.46153846153846	2.0	[null]	[null]	OFC
89	Jordan	0		[null]		0.411214953271028	0.286624203821656	0.347953216374269	0.286549707602339	1.18421052631579	1.1140350877193	[null]	[null]	AFC
90	Tamil Eelam	0		[null]		[null]	0.166666666666667	0.166666666666667	0.0	1.0	4.16666666666667	[null]	[null]	OFC
91	Sri Lanka	0		[null]		0.333333333333333	0.20353982300885	0.211111111111111	0.177777777777778	1.03888888888889	2.43888888888889	[null]	[null]	AFC
92	Nigeria	24		0.291666666666667		0.62037037037037	0.319047619047619	0.458015267175573	0.295801526717557	1.49618320610687	1.00381679389313	0	3	CAF
93	Ellan Vannin	0		[null]		[null]	0.0	0.0	0.666666666666667	0.666666666666667	1.0	[null]	[null]	OFC
94	Northern Mariana Islands	0		[null]		0.0	0.133333333333333	0.111111111111111	0.0555555555555556	0.888888888888889	4.16666666666667	[null]	[null]	OFC
95	Montserrat	0		[null]		1.0	0.105263157894737	0.133333333333333	0.0666666666666667	1.03333333333333	4.4	[null]	[null]	OFC
96	Georgia	0		[null]		0.490909090909091	0.23	0.293532338308458	0.189054726368159	1.12935323383085	1.48258706467662	[null]	[null]	UEFA
97	Lesotho	0		[null]		0.216867469879518	0.150943396226415	0.169014084507042	0.309859154929577	0.779342723004695	1.61971830985915	[null]	[null]	CAF
98	El Salvador	6		0.0		0.481927710843373	0.200913242009132	0.321120689655172	0.226293103448276	1.22629310344828	1.48706896551724	[null]	[null]	CONCACAF
99	South Ossetia	0		[null]		[null]	0.0	0.0	0.333333333333333	0.666666666666667	2.33333333333333	[null]	[null]	OFC
100	Latvia	0		[null]		0.416666666666667	0.25	0.301136363636364	0.227272727272727	1.25568181818182	1.64488636363636	[null]	[null]	UEFA

Rows: 1-100 | Columns: 14

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.

In [29]:

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")

Out[29]:

	Abc team1 Varchar(64)	123 Number_Games_World_Tournament Float	123 Float	123 Percent_Victory_World_Tournament Float	123 Float	123 Percent_Victory_Home Float	123 Percent_Victory_Away Float	123 Percent_Victory Float	123 Percent_Draw Float	123 Avg_goals Float	123 Avg_goals_conceded Float	123 nb_World_Cup Integer	123 nb_Continental_Cup Float	Abc confederation Varchar(8)	123 Word_Cup_Victory Int
1	Madagascar	0.0		[null]		0.464285714285714	0.32183908045977	0.366666666666667	0.194444444444444	1.33333333333333	1.63333333333333	[null]	[null]	CAF	[null]
2	North Vietnam	0.0		[null]		[null]	0.210526315789474	0.210526315789474	0.157894736842105	1.63157894736842	2.26315789473684	[null]	[null]	OFC	[null]
3	Abkhazia	0.0		[null]		[null]	0.166666666666667	0.166666666666667	0.5	1.0	1.5	[null]	[null]	OFC	[null]
4	Canada	0.043795620437956		0.0		0.338709677419355	0.281481481481481	0.331412103746398	0.239193083573487	1.04899135446686	1.39769452449568	0	0.25	CONCACAF	0
5	Monaco	0.0		[null]		[null]	0.166666666666667	0.166666666666667	0.166666666666667	1.16666666666667	8.33333333333333	[null]	[null]	OFC	[null]
6	Saint Pierre and Miquelon	0.0		[null]		[null]	0.0	0.0	0.0	0.166666666666667	10.3333333333333	[null]	[null]	OFC	[null]
7	Algeria	0.094890510948905		0.230769230769231		0.580645161290323	0.31638418079096	0.415217391304348	0.280434782608696	1.35434782608696	1.0304347826087	0	0.125	CAF	0
8	Iran	0.087591240875912		0.0833333333333333		0.641509433962264	0.430379746835443	0.534368070953437	0.261640798226164	1.84035476718404	0.835920177383592	0	0.25	AFC	0
9	Comoros	0.0		[null]		0.0	0.142857142857143	0.0714285714285714	0.285714285714286	0.5	1.71428571428571	[null]	[null]	CAF	[null]
10	Namibia	0.0		[null]		0.351851851851852	0.195402298850575	0.236842105263158	0.284210526315789	1.01578947368421	1.53157894736842	[null]	[null]	CAF	[null]
11	South Sudan	0.0		[null]		0.5	0.142857142857143	0.166666666666667	0.277777777777778	0.555555555555556	1.55555555555556	[null]	[null]	CAF	[null]
12	Belize	0.0		[null]		0.5	0.037037037037037	0.186440677966102	0.169491525423729	1.03389830508475	2.23728813559322	[null]	[null]	OFC	[null]
13	Vietnam	0.0		[null]		0.46551724137931	0.384615384615385	0.376543209876543	0.203703703703704	1.66666666666667	1.59259259259259	[null]	[null]	AFC	[null]
14	Puerto Rico	0.0		[null]		0.178571428571429	0.196078431372549	0.188118811881188	0.178217821782178	1.00990099009901	2.2970297029703	[null]	[null]	OFC	[null]
15	Andorra	0.0		[null]		0.0571428571428571	0.0	0.0229007633587786	0.0763358778625954	0.282442748091603	2.82442748091603	[null]	[null]	UEFA	[null]
16	France	0.503649635036496		0.536231884057971		0.532110091743119	0.363636363636364	0.480818414322251	0.214833759590793	1.75831202046036	1.34271099744246	1	0.25	UEFA	1
17	Belarus	0.0		[null]		0.392857142857143	0.285714285714286	0.30622009569378	0.258373205741627	1.25358851674641	1.44019138755981	[null]	[null]	UEFA	[null]
18	Ynys Môn	0.0		[null]		[null]	0.470588235294118	0.470588235294118	0.215686274509804	1.70588235294118	1.49019607843137	[null]	[null]	OFC	[null]
19	Guatemala	0.0		[null]		0.468085106382979	0.285714285714286	0.341232227488152	0.258293838862559	1.32938388625592	1.3696682464455	[null]	[null]	CONCACAF	[null]
20	Turkmenistan	0.0		[null]		0.538461538461538	0.365079365079365	0.384615384615385	0.170940170940171	1.63247863247863	1.47863247863248	[null]	[null]	AFC	[null]
21	China PR	0.021897810218978		0.0		0.579617834394904	0.395833333333333	0.488117001828154	0.226691042047532	1.83729433272395	1.08775137111517	0	0.125	AFC	0
22	Serbia	0.313868613138686		0.395348837209302		0.544502617801047	0.38150289017341	0.456460674157303	0.219101123595506	1.81601123595506	1.37921348314607	[null]	[null]	UEFA	[null]
23	Italy	0.664233576642336		0.527472527472527		0.643884892086331	0.346456692913386	0.524568393094289	0.282868525896414	1.69588313413015	0.98273572377158	3	0.125	UEFA	1
24	Uganda	0.0		[null]		0.591954022988506	0.382789317507418	0.440497335701599	0.253996447602131	1.61811722912966	1.2113676731794	[null]	[null]	CAF	[null]
25	Bermuda	0.0		[null]		0.403846153846154	0.244444444444444	0.353846153846154	0.184615384615385	1.67692307692308	1.56153846153846	[null]	[null]	CONCACAF	[null]
26	Niger	0.0		[null]		0.448275862068966	0.0898876404494382	0.234972677595628	0.256830601092896	0.934426229508197	1.6448087431694	[null]	[null]	CAF	[null]
27	Rwanda	0.0		[null]		0.446428571428571	0.324786324786325	0.333333333333333	0.253521126760563	1.05164319248826	1.22535211267606	[null]	[null]	CAF	[null]
28	Brunei	0.0		[null]		0.5	0.111111111111111	0.1	0.0666666666666667	0.65	3.76666666666667	[null]	[null]	OFC	[null]
29	Guadeloupe	0.0		[null]		0.507692307692308	0.380281690140845	0.415525114155251	0.182648401826484	1.67123287671233	1.48401826484018	[null]	[null]	CONCACAF	[null]
30	Panama	0.0		[null]		0.5	0.216049382716049	0.310160427807487	0.254010695187166	1.16310160427807	1.56417112299465	[null]	[null]	CONMEBOL	[null]
31	United States	0.35036496350365		0.291666666666667		0.406542056074766	0.262068965517241	0.419614147909968	0.212218649517685	1.41961414790997	1.37138263665595	0	0.25	CONMEBOL	0
32	Sierra Leone	0.0		[null]		0.541666666666667	0.194915254237288	0.308333333333333	0.241666666666667	0.958333333333333	1.3625	[null]	[null]	CAF	[null]
33	Costa Rica	0.109489051094891		0.333333333333333		0.623853211009174	0.331730769230769	0.429872495446266	0.256830601092896	1.67030965391621	1.16757741347905	[null]	[null]	CONMEBOL	[null]
34	Hitra	0.0		[null]		[null]	0.166666666666667	0.166666666666667	0.0	1.16666666666667	4.83333333333333	[null]	[null]	OFC	[null]
35	Iraqi Kurdistan	0.0		[null]		1.0	0.352941176470588	0.45	0.25	2.1	1.15	[null]	[null]	OFC	[null]
36	Philippines	0.0		[null]		0.476190476190476	0.227272727272727	0.274611398963731	0.145077720207254	1.16580310880829	2.27979274611399	[null]	[null]	AFC	[null]
37	Singapore	0.0		[null]		0.406779661016949	0.244360902255639	0.304093567251462	0.192982456140351	1.33918128654971	1.87329434697856	[null]	[null]	AFC	[null]
38	Macau	0.0		[null]		0.217391304347826	0.142857142857143	0.141509433962264	0.0943396226415094	0.764150943396226	3.26415094339623	[null]	[null]	AFC	[null]
39	Mali	0.0		[null]		0.568	0.325581395348837	0.399563318777293	0.246724890829694	1.27292576419214	1.17685589519651	[null]	[null]	CAF	[null]
40	Czech Republic	0.277372262773723		0.368421052631579		0.654320987654321	0.34185303514377	0.483311081441923	0.221628838451268	1.8437917222964	1.23497997329773	[null]	[null]	UEFA	[null]
41	Bangladesh	0.0		[null]		0.551724137931034	0.179245283018868	0.232432432432432	0.210810810810811	0.87027027027027	2.02702702702703	[null]	[null]	AFC	[null]
42	U.S. Virgin Islands	0.0		[null]		0.2	0.0	0.1	0.15	0.5	4.875	[null]	[null]	OFC	[null]
43	Zimbabwe	0.0		[null]		0.540322580645161	0.372093023255814	0.41871921182266	0.25615763546798	1.35221674876847	1.16748768472906	[null]	[null]	CAF	[null]
44	Papua New Guinea	0.0		[null]		0.333333333333333	0.24	0.268041237113402	0.175257731958763	1.90721649484536	2.22680412371134	[null]	[null]	OFC	[null]
45	Greece	0.094890510948905		0.153846153846154		0.459016393442623	0.262626262626263	0.365019011406844	0.252851711026616	1.23954372623574	1.42015209125475	0	0.125	UEFA	0
46	Réunion	0.0		[null]		0.212121212121212	0.416666666666667	0.333333333333333	0.160493827160494	1.66666666666667	2.35802469135802	[null]	[null]	OFC	[null]
47	Mauritania	0.0		[null]		0.3	0.0857142857142857	0.15028901734104	0.260115606936416	0.734104046242775	1.64161849710983	[null]	[null]	CAF	[null]
48	Eswatini	0.0		[null]		0.240963855421687	0.177083333333333	0.207070707070707	0.272727272727273	0.828282828282828	1.74242424242424	[null]	[null]	CAF	[null]
49	Iceland	0.0		[null]		0.430769230769231	0.23780487804878	0.297435897435897	0.18974358974359	1.16410256410256	1.7	[null]	[null]	UEFA	[null]
50	Germany	0.875912408759124		0.6		0.608391608391608	0.470212765957447	0.555369127516778	0.208053691275168	2.0998322147651	1.17953020134228	4	0.375	UEFA	1
51	Botswana	0.0		[null]		0.382352941176471	0.147058823529412	0.265560165975104	0.302904564315353	0.796680497925311	1.29460580912863	[null]	[null]	CAF	[null]
52	Eritrea	0.0		[null]		0.416666666666667	0.12	0.157142857142857	0.228571428571429	0.671428571428571	1.74285714285714	[null]	[null]	CAF	[null]
53	Sudan	0.0		[null]		0.592105263157895	0.278301886792453	0.336	0.248	1.14133333333333	1.368	0	0.125	CAF	0
54	Turks and Caicos Islands	0.0		[null]		[null]	0.222222222222222	0.176470588235294	0.0588235294117647	0.588235294117647	3.41176470588235	[null]	[null]	OFC	[null]
55	Grenada	0.0		[null]		0.4	0.353982300884956	0.352631578947368	0.221052631578947	1.78947368421053	1.76315789473684	[null]	[null]	CONCACAF	[null]
56	Kosovo	0.0		[null]		0.4	0.5	0.428571428571429	0.285714285714286	2.0	1.71428571428571	[null]	[null]	UEFA	[null]
57	Tuvalu	0.0		[null]		[null]	0.210526315789474	0.210526315789474	0.105263157894737	1.15789473684211	5.31578947368421	[null]	[null]	OFC	[null]
58	Nepal	0.0		[null]		0.394736842105263	0.146341463414634	0.206666666666667	0.14	0.813333333333333	2.58666666666667	[null]	[null]	AFC	[null]
59	Kiribati	0.0		[null]		[null]	0.0	0.0	0.0	0.333333333333333	9.66666666666667	[null]	[null]	OFC	[null]
60	Guinea-Bissau	0.0		[null]		0.451612903225806	0.230769230769231	0.272727272727273	0.223140495867769	1.03305785123967	1.56198347107438	[null]	[null]	CAF	[null]
61	Bahamas	0.0		[null]		0.333333333333333	0.181818181818182	0.259259259259259	0.148148148148148	1.14814814814815	3.33333333333333	[null]	[null]	OFC	[null]
62	New Zealand	0.109489051094891		0.0		0.486842105263158	0.346820809248555	0.404761904761905	0.181547619047619	1.74702380952381	1.61309523809524	[null]	[null]	OFC	[null]
63	Mozambique	0.0		[null]		0.530612244897959	0.2	0.315018315018315	0.252747252747253	1.05494505494505	1.28571428571429	[null]	[null]	CAF	[null]
64	Uruguay	0.445255474452555		0.409836065573771		0.5	0.300724637681159	0.430274135876043	0.245530393325387	1.57568533969011	1.26460071513707	2	1.0	CONMEBOL	1
65	Saint Martin	0.0		[null]		0.75	0.16	0.303030303030303	0.0909090909090909	1.12121212121212	2.90909090909091	[null]	[null]	OFC	[null]
66	Albania	0.0		[null]		0.404255319148936	0.173076923076923	0.23972602739726	0.222602739726027	0.900684931506849	1.5958904109589	[null]	[null]	UEFA	[null]
67	Angola	0.021897810218978		0.0		0.5625	0.253521126760563	0.352750809061489	0.352750809061489	1.1747572815534	1.042071197411	[null]	[null]	CAF	[null]
68	Brazil	1.0		0.678832116788321		0.733333333333333	0.59375	0.635091496232508	0.20021528525296	2.19375672766416	0.93756727664155	4	1.0	CONMEBOL	1
69	Scotland	0.167883211678832		0.173913043478261		0.58477508650519	0.394230769230769	0.476510067114094	0.21744966442953	1.7503355704698	1.2255033557047	[null]	[null]	UEFA	[null]
70	Dominican Republic	0.0		[null]		0.428571428571429	0.333333333333333	0.362637362637363	0.142857142857143	1.57142857142857	1.85714285714286	[null]	[null]	OFC	[null]
71	Alderney	0.0		[null]		[null]	0.0833333333333333	0.0833333333333333	0.0	0.583333333333333	4.33333333333333	[null]	[null]	OFC	[null]
72	Colombia	0.167883211678832		0.391304347826087		0.46875	0.357142857142857	0.378	0.272	1.206	1.21	0	0.125	CONMEBOL	0
73	Bonaire	0.0		[null]		[null]	0.307692307692308	0.307692307692308	0.153846153846154	1.61538461538462	3.38461538461538	[null]	[null]	OFC	[null]
74	Palestine	0.0		[null]		0.0	0.25	0.251655629139073	0.264900662251656	1.28476821192053	1.60264900662252	[null]	[null]	AFC	[null]
75	Kazakhstan	0.0		[null]		0.394736842105263	0.16	0.239263803680982	0.239263803680982	1.03680981595092	1.6441717791411	[null]	[null]	UEFA	[null]
76	Arameans Suryoye	0.0		[null]		[null]	0.571428571428571	0.571428571428571	0.142857142857143	1.42857142857143	1.14285714285714	[null]	[null]	OFC	[null]
77	Togo	0.021897810218978		0.0		0.484536082474227	0.236363636363636	0.314285714285714	0.245714285714286	1.08285714285714	1.39142857142857	[null]	[null]	CAF	[null]
78	Central African Republic	0.0		[null]		0.352941176470588	0.12	0.168831168831169	0.207792207792208	1.02597402597403	2.06493506493507	[null]	[null]	CAF	[null]
79	South Africa	0.124087591240876		0.176470588235294		0.52755905511811	0.370967741935484	0.444759206798867	0.274787535410765	1.3399433427762	1.00849858356941	[null]	[null]	CAF	[null]
80	Zanzibar	0.0		[null]		0.172413793103448	0.19047619047619	0.187817258883249	0.192893401015228	0.888324873096447	2.16243654822335	[null]	[null]	OFC	[null]
81	Pakistan	0.0		[null]		0.366666666666667	0.204918032786885	0.197802197802198	0.186813186813187	0.934065934065934	2.39010989010989	[null]	[null]	AFC	[null]
82	Mongolia	0.0