Pokemon¶

This example uses the pokemon and combats datasets to predict the winner of a 1-on-1 Pokemon battle. You can download the Jupyter Notebook of the study here and two datasets:

pokemon

Name: The name of the Pokemon.
Generation: Pokemon’s generation.
Legendary: True if the Pokemon is legendary.
HP: Number of hit points.
Attack: Attack stat.
Sp_Atk: Special attack stat.
Defense: Defense stat.
Sp_Def: Special defense stat.
Speed: Speed stat.
Type_1: Pokemon’s first type.
Type_2: Pokemon’s second type.

fights

First_pokemon: Pokemon of trainer 1.
Second_pokemon: Pokemon of trainer 2.
Winner: Winner of the battle.

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

Initialization¶

This example uses the following version of VerticaPy:

import verticapy as vp

vp.__version__
Out[2]: '1.1.0'

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

vp.connect("VerticaDSN")

Let’s ingest the datasets.

import verticapy.sql.functions as fun

combats = vp.read_csv("fights.csv")
combats.head(5)

	123 First_pokemon Int 100%	...	123 Second_pokemon Int 100%	123 Winner Int 100%
1	1	...	26	26
2	1	...	112	112
3	1	...	191	1
4	1	...	215	1
5	1	...	219	219

pokemon = vp.read_csv("pokemons.csv")
pokemon.head(5)

	123 ID Int 100%	...	123 Generation Int 100%	010 Legendary Boolean 100%
1	5	...	1	❌
2	9	...	1	❌
3	13	...	1	❌
4	20	...	1	❌
5	26	...	1	❌

Data Exploration and Preparation¶

The table combats will be joined to the table pokemon to predict the winner.

The pokemon table contains the information on each Pokemon. Let’s describe this table.

pokemon.describe(method = "categorical", unique = True)

	...	top_percent	unique
"ID"	...	0.125	800.0
"Name"	...	0.125	799.0
"Type_1"	...	14.0	18.0
"Type_2"	...	48.25	18.0
"HP"	...	8.375	94.0
"Attack"	...	5.0	111.0
"Defense"	...	6.75	103.0
"Sp_Atk"	...	6.375	105.0
"Sp_Def"	...	6.5	92.0
"Speed"	...	5.75	108.0
"Generation"	...	20.75	6.0
"Legendary"	...	91.875	2.0

The pokemon’s Name, Generation, and whether or not it’s Legendary will never influence the outcome of the battle, so we can drop these columns.

pokemon.drop(
    [
        "Generation",
        "Legendary",
        "Name",
    ]
)

	123 ID Int 100%	...	123 Sp_Def Int 100%	123 Speed Int 100%
1	5	...	50	65
2	9	...	115	100
3	13	...	115	78
4	20	...	80	145
5	26	...	70	97
6	34	...	55	65
7	42	...	90	60
8	46	...	50	45
9	50	...	75	40
10	57	...	70	120
11	62	...	45	70
12	71	...	95	120
13	75	...	85	55
14	78	...	70	70
15	82	...	45	35
16	83	...	65	45
17	89	...	55	45
18	98	...	25	40
19	99	...	45	70
20	106	...	115	67

The ID will be the key to join the data. By joining the data, we will be able to create more relevant features.

fights = pokemon.join(
    combats,
    on = {"ID": "First_Pokemon"},
    how = "inner",
    expr1 = [
        "Sp_Atk AS Sp_Atk_1",
        "Speed AS Speed_1",
        "Sp_Def AS Sp_Def_1",
        "Defense AS Defense_1",
        "Type_1 AS Type_1_1",
        "Type_2 AS Type_2_1",
        "HP AS HP_1",
        "Attack AS Attack_1",
    ],
    expr2 = [
        "First_Pokemon",
        "Second_Pokemon",
        "Winner",
    ]).join(pokemon,
    on = {"Second_Pokemon": "ID"},
    how = "inner",
    expr2 = [
        "Sp_Atk AS Sp_Atk_2",
        "Speed AS Speed_2",
        "Sp_Def AS Sp_Def_2",
        "Defense AS Defense_2",
        "Type_1 AS Type_1_2",
        "Type_2 AS Type_2_2",
        "HP AS HP_2",
        "Attack AS Attack_2",
    ],
    expr1 =
        [
            "Sp_Atk_1",
            "Speed_1",
            "Sp_Def_1",
            "Defense_1",
            "Type_1_1",
            "Type_2_1",
            "HP_1",
            "Attack_1",
            "Winner",
            "Second_pokemon",
        ]
)

Features engineering is the key. Here, we can create features that describe the stat differences between the first and second Pokemon. We can also change winner to a binary value: 1 if the first pokemon won and 0 otherwise.

fights["Sp_Atk_diff"] = fights["Sp_Atk_1"] - fights["Sp_Atk_2"]

fights["Speed_diff"] = fights["Speed_1"] - fights["Speed_2"]

fights["Sp_Def_diff"] = fights["Sp_Def_1"] - fights["Sp_Def_2"]

fights["Defense_diff"] = fights["Defense_1"] - fights["Defense_2"]

fights["HP_diff"] = fights["HP_1"] - fights["HP_2"]

fights["Attack_diff"] = fights["Attack_1"] - fights["Attack_2"]

fights["Winner"] = fun.case_when(fights["Winner"] == fights["Second_pokemon"], 0, 1)

fights = fights[
    [
        "Sp_Atk_diff",
        "Speed_diff",
        "Sp_Def_diff",
        "Defense_diff",
        "HP_diff",
        "Attack_diff",
        "Type_1_1",
        "Type_1_2",
        "Type_2_1",
        "Type_2_2",
        "Winner",
    ]
]

Missing values can not be handled by most machine learning models. Let’s see which features we should impute.

fights.count()

	count
"Sp_Atk_diff"	50000.0
"Speed_diff"	50000.0
"Sp_Def_diff"	50000.0
"Defense_diff"	50000.0
"HP_diff"	50000.0
"Attack_diff"	50000.0
"Type_1_1"	50000.0
"Type_1_2"	50000.0
"Type_2_1"	25969.0
"Type_2_2"	26015.0
"Winner"	50000.0

In terms of missing values, our only concern is the Pokemon’s second type (Type_2_1 and Type_2_2). Since some Pokemon only have one type, these features are MNAR (missing values not at random). We can impute the missing values by creating another category.

fights["Type_2_1"].fillna("No")
fights["Type_2_2"].fillna("No")

	123 Sp_Atk_diff Integer 100%	...	Abc Type_2_2 Varchar(20) 100%	123 Winner Integer 100%
1	-25	...	Flying	0
2	30	...	Flying	0
3	-22	...	Flying	1
4	-20	...	Flying	1
5	-5	...	Flying	0
6	-25	...	Flying	0
7	30	...	Flying	0
8	0	...	Flying	0
9	44	...	Flying	1
10	-30	...	Flying	0
11	50	...	Flying	0
12	-25	...	Flying	0
13	15	...	Flying	0
14	60	...	Flying	0
15	0	...	Flying	0
16	25	...	Flying	1
17	-55	...	Flying	0
18	15	...	Flying	0
19	-5	...	Flying	0
20	60	...	Flying	0

Let’s use the current_relation method to see how our data preparation so far on the vDataFrame generates SQL code.

print(fights.current_relation())
(
   SELECT
     "Sp_Atk_diff",
     "Speed_diff",
     "Sp_Def_diff",
     "Defense_diff",
     "HP_diff",
     "Attack_diff",
     "Type_1_1",
     "Type_1_2",
     COALESCE("Type_2_1", 'No') AS "Type_2_1",
     COALESCE("Type_2_2", 'No') AS "Type_2_2",
     "Winner" 
   FROM
 (
   SELECT
     "Sp_Atk_diff",
     "Speed_diff",
     "Sp_Def_diff",
     "Defense_diff",
     "HP_diff",
     "Attack_diff",
     "Type_1_1",
     "Type_1_2",
     "Type_2_1",
     "Type_2_2",
     "Winner" 
   FROM
 (
   SELECT
     "Sp_Atk_1",
     "Speed_1",
     "Sp_Def_1",
     "Defense_1",
     "Type_1_1",
     "Type_2_1",
     "HP_1",
     "Attack_1",
     CASE WHEN ("Winner") = ("Second_pokemon") THEN 0 ELSE 1 END AS "Winner",
     "Second_pokemon",
     "Sp_Atk_2",
     "Speed_2",
     "Sp_Def_2",
     "Defense_2",
     "Type_1_2",
     "Type_2_2",
     "HP_2",
     "Attack_2",
     ("Sp_Atk_1") - ("Sp_Atk_2") AS "Sp_Atk_diff",
     ("Speed_1") - ("Speed_2") AS "Speed_diff",
     ("Sp_Def_1") - ("Sp_Def_2") AS "Sp_Def_diff",
     ("Defense_1") - ("Defense_2") AS "Defense_diff",
     ("HP_1") - ("HP_2") AS "HP_diff",
     ("Attack_1") - ("Attack_2") AS "Attack_diff" 
   FROM
 ( 
   SELECT
     "Sp_Atk_1",
     "Speed_1",
     "Sp_Def_1",
     "Defense_1",
     "Type_1_1",
     "Type_2_1",
     "HP_1",
     "Attack_1",
     "Winner",
     "Second_pokemon",
     "Sp_Atk_2",
     "Speed_2",
     "Sp_Def_2",
     "Defense_2",
     "Type_1_2",
     "Type_2_2",
     "HP_2",
     "Attack_2" 
   FROM
 (
   SELECT
     x.Sp_Atk_1,
     x.Speed_1,
     x.Sp_Def_1,
     x.Defense_1,
     x.Type_1_1,
     x.Type_2_1,
     x.HP_1,
     x.Attack_1,
     x.Winner,
     x.Second_pokemon,
     y.Sp_Atk AS Sp_Atk_2,
     y.Speed AS Speed_2,
     y.Sp_Def AS Sp_Def_2,
     y.Defense AS Defense_2,
     y.Type_1 AS Type_1_2,
     y.Type_2 AS Type_2_2,
     y.HP AS HP_2,
     y.Attack AS Attack_2 
   FROM
 (
   SELECT
     x.Sp_Atk AS Sp_Atk_1,
     x.Speed AS Speed_1,
     x.Sp_Def AS Sp_Def_1,
     x.Defense AS Defense_1,
     x.Type_1 AS Type_1_1,
     x.Type_2 AS Type_2_1,
     x.HP AS HP_1,
     x.Attack AS Attack_1,
     y.First_Pokemon,
     y.Second_Pokemon,
     y.Winner 
   FROM
 (
   SELECT
     "ID",
     "Type_1",
     "Type_2",
     "HP",
     "Attack",
     "Defense",
     "Sp_Atk",
     "Sp_Def",
     "Speed" 
   FROM
 "v_temp_schema"."_verticapy_tmp_pokemons_v_mldb_144abc1c97b811efa8720242ac120002_") AS "x" INNER JOIN "v_temp_schema"."_verticapy_tmp_fights_v_mldb_11665b2897b811efa8720242ac120002_" AS "y" ON x."ID" = y."First_Pokemon") AS "x" INNER JOIN (
   SELECT
     "ID",
     "Type_1",
     "Type_2",
     "HP",
     "Attack",
     "Defense",
     "Sp_Atk",
     "Sp_Def",
     "Speed" 
   FROM
 "v_temp_schema"."_verticapy_tmp_pokemons_v_mldb_144abc1c97b811efa8720242ac120002_") AS "y" ON x."Second_Pokemon" = y."ID") 
VERTICAPY_SUBTABLE) 
VERTICAPY_SUBTABLE) 
VERTICAPY_SUBTABLE) 
VERTICAPY_SUBTABLE) 
VERTICAPY_SUBTABLE

VerticaPy will remember your modifications and always generate an up-to-date SQL query.

Let’s look at the correlations between all the variables.

fights.corr(method = "spearman")

Many variables are correlated to the response column. We have enough information to create our predictive model.

Machine Learning¶

Some really important features are categorical. Random Forest can handle them. Besides, we need trees deep enough to compare all the different types.

from verticapy.machine_learning.vertica import RandomForestClassifier
from verticapy.machine_learning.model_selection import cross_validate

predictors = fights.get_columns(exclude_columns = ["Winner"])
model = RandomForestClassifier(
    n_estimators = 50,
    max_depth = 100,
    max_leaf_nodes = 400,
    nbins = 100,
)
cross_validate(model, fights, predictors, "Winner")

	...	csi	time
1-fold	...	0.9243189942068285	41.20065116882324
2-fold	...	0.9212951734174145	40.695603370666504
3-fold	...	0.9231133191388692	40.95596504211426
avg	...	0.9229091622543707	40.95073986053467
std	...	0.0012428818841008571	0.20621800195852144

We have an excellent model with an average AUC of more than 99%. Let’s create a model with the entire dataset and look at the importance of each feature.

model.fit(
    fights,
    predictors,
    "Winner",
)
model.features_importance()

Based on our model, it seems that a Pokemon’s speed and attack stats are the strongest predictors for the winner of a battle.

Conclusion¶

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