Pokemon¶

This example uses the pokemons and combats datasets to predict the winner of a 1-on-1 Pokemon battle. You can download the two datasets:

pokemons

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 vastorbit:

import vastorbit as vo

vo.__version__

Connect to VAST. This example uses an existing connection called VASTDSN . 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.

vo.connect("VASTDSN")

Let’s ingest the datasets.

combats = vo.read_csv("fights.csv")
combats

	123 first_pokemon Integer 100%	123 second_pokemon Integer 100%	123 winner Integer 100%
1	310	153	153
2	349	663	349
3	516	130	516
4	783	681	783
5	657	355	355
6	549	100	549
7	462	690	690
8	112	465	465
9	117	370	370
10	549	54	549
11	478	593	478
12	497	280	280
13	766	591	766
14	677	17	677
15	251	259	251
16	53	494	494
17	746	494	494
18	475	723	475
19	305	238	305
20	288	524	288

pokemons = vo.read_csv("pokemons.csv")
pokemons

	123 id Integer 100%	Abc name Varchar(50) 99%	Abc type_1 Varchar(50) 100%	Abc type_2 Varchar(50) 51%	123 hp Integer 100%	123 attack Integer 100%	123 defense Integer 100%	123 sp_atk Integer 100%	123 sp_def Integer 100%	123 speed Integer 100%	123 generation Integer 100%	0\|1 legendary Boolean 100%
1	1	Bulbasaur	Grass	Poison	45	49	49	65	65	45	1	✗
2	2	Ivysaur	Grass	Poison	60	62	63	80	80	60	1	✗
3	3	Venusaur	Grass	Poison	80	82	83	100	100	80	1	✗
4	4	Mega Venusaur	Grass	Poison	80	100	123	122	120	80	1	✗
5	5	Charmander	Fire	[null]	39	52	43	60	50	65	1	✗
6	6	Charmeleon	Fire	[null]	58	64	58	80	65	80	1	✗
7	7	Charizard	Fire	Flying	78	84	78	109	85	100	1	✗
8	8	Mega Charizard X	Fire	Dragon	78	130	111	130	85	100	1	✗
9	9	Mega Charizard Y	Fire	Flying	78	104	78	159	115	100	1	✗
10	10	Squirtle	Water	[null]	44	48	65	50	64	43	1	✗
11	11	Wartortle	Water	[null]	59	63	80	65	80	58	1	✗
12	12	Blastoise	Water	[null]	79	83	100	85	105	78	1	✗
13	13	Mega Blastoise	Water	[null]	79	103	120	135	115	78	1	✗
14	14	Caterpie	Bug	[null]	45	30	35	20	20	45	1	✗
15	15	Metapod	Bug	[null]	50	20	55	25	25	30	1	✗
16	16	Butterfree	Bug	Flying	60	45	50	90	80	70	1	✗
17	17	Weedle	Bug	Poison	40	35	30	20	20	50	1	✗
18	18	Kakuna	Bug	Poison	45	25	50	25	25	35	1	✗
19	19	Beedrill	Bug	Poison	65	90	40	45	80	75	1	✗
20	20	Mega Beedrill	Bug	Poison	65	150	40	15	80	145	1	✗

Data Exploration and Preparation¶

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

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

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

	dtype	count	top	top_percent	unique
"id"	integer	800	13	0.125	800.0
"name"	varchar(50)	799	Spearow	0.125	799.0
"type_1"	varchar(50)	800	Water	14.0	18.0
"type_2"	varchar(50)	414	[null]	48.25	18.0
"hp"	integer	800	60	8.375	94.0
"attack"	integer	800	100	5.0	111.0
"defense"	integer	800	70	6.75	103.0
"sp_atk"	integer	800	60	6.375	105.0
"sp_def"	integer	800	80	6.5	92.0
"speed"	integer	800	50	5.75	108.0
"generation"	integer	800	1	20.75	6.0
"legendary"	boolean	800	✗	91.875	2.0

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

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

	123 id Integer 100%	Abc type_1 Varchar(50) 100%	Abc type_2 Varchar(50) 51%	123 hp Integer 100%	123 attack Integer 100%	123 defense Integer 100%	123 sp_atk Integer 100%	123 sp_def Integer 100%	123 speed Integer 100%
1	1	Grass	Poison	45	49	49	65	65	45
2	2	Grass	Poison	60	62	63	80	80	60
3	3	Grass	Poison	80	82	83	100	100	80
4	4	Grass	Poison	80	100	123	122	120	80
5	5	Fire	[null]	39	52	43	60	50	65
6	6	Fire	[null]	58	64	58	80	65	80
7	7	Fire	Flying	78	84	78	109	85	100
8	8	Fire	Dragon	78	130	111	130	85	100
9	9	Fire	Flying	78	104	78	159	115	100
10	10	Water	[null]	44	48	65	50	64	43
11	11	Water	[null]	59	63	80	65	80	58
12	12	Water	[null]	79	83	100	85	105	78
13	13	Water	[null]	79	103	120	135	115	78
14	14	Bug	[null]	45	30	35	20	20	45
15	15	Bug	[null]	50	20	55	25	25	30
16	16	Bug	Flying	60	45	50	90	80	70
17	17	Bug	Poison	40	35	30	20	20	50
18	18	Bug	Poison	45	25	50	25	25	35
19	19	Bug	Poison	65	90	40	45	80	75
20	20	Bug	Poison	65	150	40	15	80	145

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

fights = pokemons.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(pokemons,
    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 pokemons won and 0 otherwise.

import vastorbit.sql.functions as fun

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%	123 Speed_diff Integer 100%	123 Sp_Def_diff Integer 100%	123 Defense_diff Integer 100%	123 HP_diff Integer 100%	123 Attack_diff Integer 100%	Abc Type_1_1 Varchar(50) 100%	Abc Type_1_2 Varchar(50) 100%	Abc Type_2_1 Varchar(50) 100%	Abc Type_2_2 Varchar(50) 100%	123 Winner Integer 100%	123 Type_1_1_Bug Bool 100%	123 Type_1_1_Dragon Bool 100%	123 Type_1_1_Fighting Bool 100%	123 Type_1_1_Fire Bool 100%	123 Type_1_1_Ghost Bool 100%	123 Type_1_1_Grass Bool 100%	123 Type_1_1_Ground Bool 100%	123 Type_1_1_Normal Bool 100%	123 Type_1_1_Poison Bool 100%	...	123 Type_2_1_No Bool 100%	123 Type_2_1_Poison Bool 100%	123 Type_2_1_Psychic Bool 100%	123 Type_2_2_Fairy Bool 100%	123 Type_2_2_Flying Bool 100%	123 Type_2_2_Grass Bool 100%	123 Type_2_2_No Bool 100%	123 Type_2_2_Poison Bool 100%	123 Type_2_2_Psychic Bool 100%
1	-45	-59	-15	5	-15	-21	Grass	Fire	No	No	0	0	0	0	0	0	1	0	0	0	...	1	0	0	0	0	0	1	0	0
2	65	49	30	8	29	18	Fire	Ghost	Flying	Grass	1	0	0	0	1	0	0	0	0	0	...	0	0	0	0	0	1	0	0	0
3	-50	-35	-58	-38	-38	-23	Normal	Steel	Flying	Psychic	0	0	0	0	0	0	0	0	1	0	...	0	0	0	0	0	0	0	0	1
4	-61	-14	-44	-54	-26	-61	Water	Dragon	No	Fairy	0	0	0	0	0	0	0	0	0	0	...	1	0	0	1	0	0	0	0	0
5	-15	-20	50	75	-10	0	Steel	Water	No	No	0	0	0	0	0	0	0	0	0	0	...	1	0	0	0	0	0	1	0	0
6	-30	35	10	5	5	-25	Normal	Grass	No	Poison	1	0	0	0	0	0	0	0	1	0	...	1	0	0	0	0	0	0	1	0
7	-78	-45	-18	-70	36	-160	Normal	Ghost	No	No	0	0	0	0	0	0	0	0	1	0	...	1	0	0	0	0	0	1	0	0
8	15	-50	25	35	-17	-45	Ghost	Normal	No	Flying	0	0	0	0	0	1	0	0	0	0	...	1	0	0	0	1	0	0	0	0
9	0	43	30	35	-70	0	Grass	Normal	No	Fairy	1	0	0	0	0	0	1	0	0	0	...	1	0	0	1	0	0	0	0	0
10	-5	28	45	-21	-2	-10	Water	Fairy	Poison	No	1	0	0	0	0	0	0	0	0	0	...	0	1	0	0	0	0	1	0	0
11	-10	-5	10	-30	15	5	Fighting	Dragon	No	No	0	0	0	1	0	0	0	0	0	0	...	1	0	0	0	0	0	1	0	0
12	42	42	17	1	33	41	Poison	Water	Fighting	No	1	0	0	0	0	0	0	0	0	1	...	0	0	0	0	0	0	1	0	0
13	-20	70	-5	30	5	25	Normal	Normal	No	No	1	0	0	0	0	0	0	0	1	0	...	1	0	0	0	0	0	1	0	0
14	18	-11	38	22	29	4	Bug	Water	Flying	No	0	1	0	0	0	0	0	0	0	0	...	0	0	0	0	0	0	1	0	0
15	10	62	35	25	33	67	Dragon	Water	No	No	1	0	1	0	0	0	0	0	0	0	...	1	0	0	0	0	0	1	0	0
16	22	1	10	-37	5	15	Fighting	Ghost	Psychic	Grass	0	0	0	1	0	0	0	0	0	0	...	0	0	1	0	0	1	0	0	0
17	85	-30	-5	55	50	-5	Fire	Bug	Psychic	Flying	0	0	0	0	1	0	0	0	0	0	...	0	0	1	0	1	0	0	0	0
18	30	30	-30	-30	80	35	Water	Normal	No	No	1	0	0	0	0	0	0	0	0	0	...	1	0	0	0	0	0	1	0	0
19	-6	-39	28	55	-2	7	Ground	Fire	No	Flying	0	0	0	0	0	0	0	1	0	0	...	1	0	0	0	1	0	0	0	0
20	-55	-32	-8	55	-125	-10	Steel	Water	Ghost	No	0	0	0	0	0	0	0	0	0	0	...	0	0	0	0	0	0	1	0	0

Let’s use a one hot encoder to get numerical dummies out of the different types.

fights["Type_1_1"].one_hot_encode()
fights["Type_1_2"].one_hot_encode()
fights["Type_2_1"].one_hot_encode()
fights["Type_2_2"].one_hot_encode()

	123 Sp_Atk_diff Integer 100%	123 Speed_diff Integer 100%	123 Sp_Def_diff Integer 100%	123 Defense_diff Integer 100%	123 HP_diff Integer 100%	123 Attack_diff Integer 100%	Abc Type_1_1 Varchar(50) 100%	Abc Type_1_2 Varchar(50) 100%	Abc Type_2_1 Varchar(50) 100%	Abc Type_2_2 Varchar(50) 100%	123 Winner Integer 100%	123 Type_1_1_Bug Bool 100%	123 Type_1_1_Dragon Bool 100%	123 Type_1_1_Fighting Bool 100%	123 Type_1_1_Fire Bool 100%	123 Type_1_1_Ghost Bool 100%	123 Type_1_1_Grass Bool 100%	123 Type_1_1_Ground Bool 100%	123 Type_1_1_Normal Bool 100%	123 Type_1_1_Poison Bool 100%	...	123 Type_2_1_No Bool 100%	123 Type_2_1_Poison Bool 100%	123 Type_2_1_Psychic Bool 100%	123 Type_2_2_Fairy Bool 100%	123 Type_2_2_Flying Bool 100%	123 Type_2_2_Grass Bool 100%	123 Type_2_2_No Bool 100%	123 Type_2_2_Poison Bool 100%	123 Type_2_2_Psychic Bool 100%
1	-45	-59	-15	5	-15	-21	Grass	Fire	No	No	0	0	0	0	0	0	1	0	0	0	...	1	0	0	0	0	0	1	0	0
2	65	49	30	8	29	18	Fire	Ghost	Flying	Grass	1	0	0	0	1	0	0	0	0	0	...	0	0	0	0	0	1	0	0	0
3	-50	-35	-58	-38	-38	-23	Normal	Steel	Flying	Psychic	0	0	0	0	0	0	0	0	1	0	...	0	0	0	0	0	0	0	0	1
4	-61	-14	-44	-54	-26	-61	Water	Dragon	No	Fairy	0	0	0	0	0	0	0	0	0	0	...	1	0	0	1	0	0	0	0	0
5	-15	-20	50	75	-10	0	Steel	Water	No	No	0	0	0	0	0	0	0	0	0	0	...	1	0	0	0	0	0	1	0	0
6	-30	35	10	5	5	-25	Normal	Grass	No	Poison	1	0	0	0	0	0	0	0	1	0	...	1	0	0	0	0	0	0	1	0
7	-78	-45	-18	-70	36	-160	Normal	Ghost	No	No	0	0	0	0	0	0	0	0	1	0	...	1	0	0	0	0	0	1	0	0
8	15	-50	25	35	-17	-45	Ghost	Normal	No	Flying	0	0	0	0	0	1	0	0	0	0	...	1	0	0	0	1	0	0	0	0
9	0	43	30	35	-70	0	Grass	Normal	No	Fairy	1	0	0	0	0	0	1	0	0	0	...	1	0	0	1	0	0	0	0	0
10	-5	28	45	-21	-2	-10	Water	Fairy	Poison	No	1	0	0	0	0	0	0	0	0	0	...	0	1	0	0	0	0	1	0	0
11	-10	-5	10	-30	15	5	Fighting	Dragon	No	No	0	0	0	1	0	0	0	0	0	0	...	1	0	0	0	0	0	1	0	0
12	42	42	17	1	33	41	Poison	Water	Fighting	No	1	0	0	0	0	0	0	0	0	1	...	0	0	0	0	0	0	1	0	0
13	-20	70	-5	30	5	25	Normal	Normal	No	No	1	0	0	0	0	0	0	0	1	0	...	1	0	0	0	0	0	1	0	0
14	18	-11	38	22	29	4	Bug	Water	Flying	No	0	1	0	0	0	0	0	0	0	0	...	0	0	0	0	0	0	1	0	0
15	10	62	35	25	33	67	Dragon	Water	No	No	1	0	1	0	0	0	0	0	0	0	...	1	0	0	0	0	0	1	0	0
16	22	1	10	-37	5	15	Fighting	Ghost	Psychic	Grass	0	0	0	1	0	0	0	0	0	0	...	0	0	1	0	0	1	0	0	0
17	85	-30	-5	55	50	-5	Fire	Bug	Psychic	Flying	0	0	0	0	1	0	0	0	0	0	...	0	0	1	0	1	0	0	0	0
18	30	30	-30	-30	80	35	Water	Normal	No	No	1	0	0	0	0	0	0	0	0	0	...	1	0	0	0	0	0	1	0	0
19	-6	-39	28	55	-2	7	Ground	Fire	No	Flying	0	0	0	0	0	0	0	1	0	0	...	1	0	0	0	1	0	0	0	0
20	-55	-32	-8	55	-125	-10	Steel	Water	Ghost	No	0	0	0	0	0	0	0	0	0	0	...	0	0	0	0	0	0	1	0	0

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

print(fights.current_relation())

vastorbit 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¶

Let’s create a LogisticRegression to see the importance of the features in the final result.

from vastorbit.machine_learning.vast import LogisticRegression
from vastorbit.machine_learning.model_selection import cross_validate

predictors = fights.get_columns(exclude_columns = ["Winner", "Type_1_1", "Type_1_2", "Type_2_1", "Type_2_2"])
model = LogisticRegression(max_iter=1000)
cross_validate(model, fights, predictors, "Winner")

	auc	prc_auc	accuracy	log_loss	precision	recall	f1_score	mcc	informedness	markedness	csi	time
1-fold	0.9326232687451464	0.3246845337450549	0.8915548726434608	0.3494433560244252	0.8887360920115014	0.8826669977650857	0.8856911480720114	0.7825564401081079	0.7822948313051783	0.7828181363961684	0.7948345259391771	1.2829837799072266
2-fold	0.9322633124958158	0.32804535268559065	0.8895770660060166	0.3518932909996929	0.8857918105574741	0.8836122047244095	0.8847006651884702	0.7787608740961296	0.7786830262541544	0.7788387297208432	0.7932405566600398	1.3019630908966064
3-fold	0.9326499366983012	0.32364078986370615	0.8907020641782866	0.3532699971282389	0.8904234527687297	0.8775038520801233	0.8839164457091121	0.780739486388497	0.7801095266603202	0.7813699548263429	0.7919805307683393	1.3493530750274658
avg	0.9325121726464212	0.32545689209811723	0.8906113342759214	0.35153554805078563	0.8883171184459018	0.8812610181898729	0.8847694196565312	0.7806856001975783	0.780362461406551	0.7810089403144514	0.7933518711225188	1.3114333152770996
std	0.00017630716727886074	0.0018792634163665768	0.0008099809504619923	0.0015825676494163443	0.0019139281647568935	0.0026845951812984755	0.0007261485164339727	0.0015500017481710532	0.0014853205986225144	0.0016445197840611327	0.0011677943000828997	0.02791038869969411

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!

	123 first_pokemon Integer 100%	123 second_pokemon Integer 100%	123 winner Integer 100%
1	310	153	153
2	349	663	349
3	516	130	516
4	783	681	783
5	657	355	355
6	549	100	549
7	462	690	690
8	112	465	465
9	117	370	370
10	549	54	549
11	478	593	478
12	497	280	280
13	766	591	766
14	677	17	677
15	251	259	251
16	53	494	494
17	746	494	494
18	475	723	475
19	305	238	305
20	288	524	288

	123 first_pokemon Integer 100%	123 second_pokemon Integer 100%	123 winner Integer 100%
1	310	153	153
2	349	663	349
3	516	130	516
4	783	681	783
5	657	355	355
6	549	100	549
7	462	690	690
8	112	465	465
9	117	370	370
10	549	54	549
11	478	593	478
12	497	280	280
13	766	591	766
14	677	17	677
15	251	259	251
16	53	494	494
17	746	494	494
18	475	723	475
19	305	238	305
20	288	524	288

	123 first_pokemon Integer 100%	123 second_pokemon Integer 100%	123 winner Integer 100%
1	310	153	153
2	349	663	349
3	516	130	516
4	783	681	783
5	657	355	355
6	549	100	549
7	462	690	690
8	112	465	465
9	117	370	370
10	549	54	549
11	478	593	478
12	497	280	280
13	766	591	766
14	677	17	677
15	251	259	251
16	53	494	494
17	746	494	494
18	475	723	475
19	305	238	305
20	288	524	288