Loading...

Titanic

This example uses the titanic dataset to predict the survival of passengers on the Titanic. You can download the Jupyter Notebook of the study here.

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

Initialization

This example uses the following version of VerticaPy:

import verticapy as vp

vp.__version__
Out[2]: '1.1.0'

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

You can skip the below cell if you already have an established connection.

vp.connect("VerticaDSN")

Let’s create a Virtual DataFrame of the dataset.

from verticapy.datasets import load_titanic

titanic = load_titanic()
titanic.head(5)
123
pclass
Int
100%
...
123
body
Int
9%
Abc
home.dest
Varchar(100)
57%
11...22Montevideo, Uruguay
21...[null]Trenton, NJ
31...[null][null]
41...[null]Montevideo, Uruguay
51...[null]Los Angeles, CA

Data Exploration and Preparation

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

titanic.describe(method = "categorical", unique = True)
...
top_percent
unique
"pclass"...53.7283.0
"survived"...63.5332.0
"name"...0.1621232.0
"sex"...65.9642.0
"age"...19.20696.0
"sibsp"...67.7477.0
"parch"...76.9048.0
"ticket"...0.81887.0
"fare"...4.7277.0
"cabin"...76.823182.0
"embarked"...70.7463.0
"boat"...64.42526.0
"body"...90.438118.0
"home.dest"...42.788359.0

The columns body (passenger ID), home.dest (passenger origin/destination), embarked (origin port) and ticket (ticket ID) shouldn’t influence survival, so we can ignore these.

Let’s focus our analysis on the columns name and cabin. We’ll begin with the passengers’ names.

from verticapy.machine_learning.vertica import CountVectorizer

model = CountVectorizer()
model.fit(titanic, ["Name"])
model.transform()
Abc
token
Varchar(128)
100%
...
123
df
Numeric(38)
100%
123
rnk
Integer
100%
1mr...0.148163100524828421
2miss...0.0460234154218813062
3mrs...0.037343560758982643
4william...0.0161485668146951964
5john...0.0137262817924909175
6master...0.0113039967702866376
7henry...0.0088817117480823597
8james...0.0074687121517965288
9charles...0.0074687121517965288
10thomas...0.00686314089624545810
11joseph...0.00625756964069438811
12george...0.00625756964069438811
13edward...0.00545014129995962913
14mary...0.00524828421477593914
15elizabeth...0.00444085587404117915
16johan...0.00444085587404117915
17frederick...0.00403714170367379917
18samuel...0.00383528461849010918
19robert...0.00363342753330641919
20alexander...0.00363342753330641919

Passengers’ titles might come in handy. We can extract these from their names.

Let’s move on to the cabins.

model = CountVectorizer()
model.fit("titanic", ["cabin"])
model.transform()
Abc
token
Varchar(128)
99%
...
123
df
Numeric(38)
100%
123
rnk
Integer
100%
1[null]...0.73374613003095981
2f...0.0061919504643962852
3c27...0.0046439628482972143
4c25...0.0046439628482972143
5c23...0.0046439628482972143
6g6...0.0038699690402476786
7c78...0.0030959752321981427
8b96...0.0030959752321981427
9b98...0.0030959752321981427
10f4...0.0030959752321981427
11b57...0.0030959752321981427
12d...0.0030959752321981427
13c26...0.0030959752321981427
14c22...0.0030959752321981427
15b66...0.0030959752321981427
16b59...0.0030959752321981427
17b63...0.0030959752321981427
18b60...0.00232198142414860718
19b58...0.00232198142414860718
20f33...0.00232198142414860718

Here, we have the cabin IDs, the letter of which represents a certain position on the boat. Let’s see how often each cabin occurs in the dataset.

model = CountVectorizer()
model.fit("titanic", ["cabin"])
model.transform()["token"].str_slice(1, 1).groupby(
    columns = ["token"], expr = ["SUM(cnt)"]
).head(30)
Abc
token
Varchar(4)
88%
123
SUM
Integer
100%
1d47
2c113
3a20
4e43
5b92
6f19
7g9
8[null]948
9t1

While NULL values for “boat” clearly represent passengers who have a dedicated “lifeboat”, we can’t be so sure about NULL values for “cabin”. We can guess that these might represent passengers without a cabin. If this is the case, then these are missing values not at random (MNAR).

We’ll revisit this problem later. For now, let’s drop the columns that don’t affect survival and then encode the rest.

titanic.drop(["body", "home.dest", "embarked", "ticket"])
titanic["cabin"].str_slice(1, 1)["name"].str_extract(
        ' ([A-Za-z]+)\.')["boat"].fillna(
        method = "0ifnull"
)["cabin"].fillna("No Cabin")
123
pclass
Int
100%
...
123
survived
Int
100%
123
boat
Bool
100%
11...00
21...00
31...00
41...00
51...00
61...00
71...00
81...00
91...00
101...00
111...00
121...00
131...00
141...00
151...00
161...00
171...01
181...00
191...00
201...00

Looking at our data now, we can see that some first class passengers have a NULL value for their cabin, so we can safely say that our assumption about the meaning of a NULL value of “cabin” turned out to be incorrect. This means that the “cabin” column has far too many missing values at random (MAR). We’ll have to drop it.

titanic["cabin"].drop()
123
pclass
Int
100%
...
123
fare
Numeric(10,5)
99%
123
boat
Bool
100%
11...49.50420
21...35.50
31...31.00
41...47.10
51...136.77920
61...83.15830
71...52.00
81...106.4250
91...31.67920
101...53.10
111...79.20
121...83.4750
131...0.00
141...42.50
151...51.86250
161...52.00
171...30.69581
181...30.00
191...75.24170
201...51.86250

Let’s look at descriptive statistics of the entire Virtual Dataframe.

titanic.describe(method = "all")
...
Abc
"name"
Varchar(164)
100%
Abc
"sex"
Varchar(20)
100%
dtype...varchar(164)varchar(20)
percent...100.0100
count...12341234
top... Mr.male
top_percent...59.48165.964
avg...4.731766612641814.6807131280389
stddev...1.083764559049660.948042321667023
min...44
approx_25%...44
approx_50%...44
approx_75%...56
max...106
range...62
empty...00

Descriptive statistics can give us valuable insights into our data. Notice, for example, that the column “fare” has many outliers (The maximum of 512.33 is much greater than the 9th decile of 79.13). Most passengers traveled in 3rd class (median of pclass = 3).

The “sibsp” column represents the number of siblings for each passenger, while the “parch” column represents the number of parents and children. We can use these to create a new feature: “family_size”.

titanic["family_size"] = titanic["parch"] + titanic["sibsp"] + 1

Let’s move on to outliers. We have several tools for locating outliers (LocalOutlierFactor, DBSCAN, KMeans…), but we’ll just use winsorization in this example. Again, “fare” has many outliers, so we’ll start there.

titanic["fare"].fill_outliers(
    method = "winsorize",
    alpha = 0.03,
)
123
pclass
Int
100%
...
123
survived
Int
100%
123
family_size
Integer
100%
11...01
21...01
31...01
41...01
51...02
61...03
71...02
81...02
91...01
101...02
111...01
121...02
131...01
141...01
151...01
161...02
171...01
181...01
191...01
201...01

Let’s encode the column sex so we can use it with numerical methods.

titanic["sex"].label_encode()
123
pclass
Int
100%
...
123
survived
Int
100%
123
family_size
Integer
100%
11...01
21...01
31...01
41...01
51...02
61...03
71...02
81...02
91...01
101...02
111...01
121...02
131...01
141...01
151...01
161...02
171...01
181...01
191...01
201...01

The column age has too many missing values and since most machine learning algorithms can’t handle missing values, we need to impute our data. Let’s fill the missing values using the average age of the passengers that have the same “pclass” and “sex”.

titanic["age"].fillna(method = "mean", by = ["pclass", "sex"])
123
pclass
Int
100%
...
123
survived
Int
100%
123
family_size
Integer
100%
11...01
21...01
31...01
41...01
51...02
61...03
71...02
81...02
91...01
101...02
111...01
121...02
131...01
141...01
151...01
161...02
171...01
181...01
191...01
201...01

Let’s draw the correlation matrix to see the links between variables.

titanic.corr(method = "spearman")

Fare correlates strongly with family size. This is about what you would expect: a larger family means more tickets, and more tickets means a greater fare.

Survival correlates strongly with whether or not a passenger has a lifeboat (the boat variable). Still, to increase the generality of our model, we should avoid predictions based on just one variable. Let’s split the study into two use cases:

  • Passengers with a lifeboat

  • Passengers without a lifeboat

Before we move on: we did a lot of work to clean up this data, but we haven’t saved anything to our Vertica database! Let’s look at the modifications we’ve made to the vDataFrame.

print(titanic.current_relation())
(
   SELECT
     "pclass",
     "survived",
     "name",
     "sex",
     
                    COALESCE("age", AVG("age")  OVER (PARTITION BY "pclass", "sex")) AS "age",
     "sibsp",
     "parch",
     "fare",
     "boat",
     "family_size" 
   FROM
 (
   SELECT
     "pclass",
     "survived",
     REGEXP_SUBSTR("name", ' ([A-Za-z]+)\.') AS "name",
     DECODE("sex", 'female', 0, 'male', 1, 2) AS "sex",
     "age",
     "sibsp",
     "parch",
     (CASE WHEN "fare" < -176.6204982585513 THEN -176.6204982585513 WHEN "fare" > 244.5480856064831 THEN 244.5480856064831 ELSE "fare" END) AS "fare",
     DECODE("boat", NULL, 0, 1) AS "boat",
     (("parch") + ("sibsp")) + (1) AS "family_size" 
   FROM
 (
                
   SELECT
     
                    "pclass",
     "survived",
     "name",
     "sex",
     "age",
     "sibsp",
     "parch",
     "fare",
     "boat" 
                
   FROM
 "public"."titanic") 
VERTICAPY_SUBTABLE) 
VERTICAPY_SUBTABLE) 
VERTICAPY_SUBTABLE

Let see what’s happening when we aggregate and turn on SQL generation.

vp.set_option("sql_on", True)

titanic_avg = titanic.avg()
<IPython.core.display.HTML object>
<IPython.core.display.HTML object>

VerticaPy dynamically generates SQL code whenever you make modifications to your data. To avoid recomputation, it also stores previous aggregations. If we filter anything in our data, it will update the catalog with our modifications.

vp.set_option("sql_on", False)

print(titanic.info())
The vDataFrame was modified many times: 
 * {Thu Oct 31 18:48:17 2024} [Drop]: vDataColumn "body" was deleted from the vDataFrame.
 * {Thu Oct 31 18:48:17 2024} [Drop]: vDataColumn "home.dest" was deleted from the vDataFrame.
 * {Thu Oct 31 18:48:17 2024} [Drop]: vDataColumn "embarked" was deleted from the vDataFrame.
 * {Thu Oct 31 18:48:18 2024} [Drop]: vDataColumn "ticket" was deleted from the vDataFrame.
 * {Thu Oct 31 18:48:19 2024} [Apply]: The vDataColumn 'cabin' was transformed with the func 'x -> SUBSTR("cabin", 1, 1)'.
 * {Thu Oct 31 18:48:20 2024} [Apply]: The vDataColumn 'name' was transformed with the func 'x -> REGEXP_SUBSTR("name", ' ([A-Za-z]+)\.')'.
 * {Thu Oct 31 18:48:22 2024} [Fillna]: 795 "boat" missing values were  filled.
 * {Thu Oct 31 18:48:25 2024} [Fillna]: 948 "cabin" missing values were  filled.
 * {Thu Oct 31 18:48:26 2024} [Drop]: vDataColumn "cabin" was deleted from the vDataFrame.
 * {Thu Oct 31 18:48:30 2024} [Eval]: A new vDataColumn "family_size" was added to the vDataFrame.
 * {Thu Oct 31 18:48:31 2024} [Apply]: The vDataColumn 'fare' was transformed with the func 'x -> (CASE WHEN "fare" < -176.6204982585513 THEN -176.6204982585513 WHEN "fare" > 244.5480856064831 THEN 244.5480856064831 ELSE "fare" END)'.
 * {Thu Oct 31 18:48:33 2024} [Label Encoding]: Label Encoding was applied to the vDataColumn "sex" using the following mapping:
	female => 0	male => 1
 * {Thu Oct 31 18:48:36 2024} [Fillna]: 237 "age" missing values were  filled.

Let’s move on to modeling our data. Save the vDataFrame to your Vertica database.

from verticapy.sql import drop

# Titanic Boat
drop("titanic_boat", method = "view")
Out[10]: True

titanic_boat = titanic.search(titanic["boat"] == 1).to_db("titanic_boat", relation_type = "view")

# Titanic No Boat
drop("titanic_no_boat", method = "view")
Out[12]: True

titanic_no_boat = titanic.search(titanic["boat"] == 0).to_db("titanic_no_boat", relation_type = "view")

Machine Learning

Passengers with a lifeboat

First, let’s look at the number of survivors.

titanic_boat["survived"].describe()
value
name"survived"
dtypeinteger
unique2.0
count439.0
1430
09

We have nine deaths. Let’s try to understand why these passengers died.

titanic_boat.search(titanic_boat["survived"] == 0).head(10)
123
pclass
Integer
100%
...
123
boat
Integer
100%
123
family_size
Integer
100%
11...11
21...11
32...12
43...12
53...11
63...12
73...12
83...11
93...12

Apart from a decent amount of these passengers being third-class passengers, it doesn’t seem like there are any clear predictors here for their deaths. Making a model from this would be unhelpful.

Passengers without a lifeboat

Let’s move on to passengers without a lifeboat.

titanic_no_boat["survived"].describe()
value
name"survived"
dtypeinteger
unique2.0
count795.0
0775
120

Only 20 survived. Let’s find out why.

titanic_no_boat.search(titanic_boat["survived"] == 1).head(20)
123
pclass
Integer
100%
...
123
boat
Integer
100%
123
family_size
Integer
100%
11...01
22...01
32...01
42...02
52...02
62...01
72...02
82...04
92...02
103...02
113...02
123...01
133...01
143...01
153...01
163...04
173...01
183...01
193...01
203...01

Most survivors seem to be women. Let’s build a model with this in mind.

One of our predictors is categorical: the passenger title. Some of these predictors are corrleated, so it’d be best to work with a non-linear classifier that can handle that. In this case, a random forest classifier seems to be perfect. Let’s evaluate it with a cross-validation.

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

predictors = titanic.get_columns(exclude_columns = ["survived"])
response = "survived"
model = RandomForestClassifier(
    n_estimators = 40,
    max_depth = 4,
)
cross_validate(model, titanic_no_boat, predictors, response)
...
csi
time
1-fold...0.217.374155044555664
2-fold...0.2142857142857142717.296217679977417
3-fold...0.1666666666666666617.17010188102722
avg...0.1936507936507936317.280158201853435
std...0.0199520715713913250.08407478224653067

This dataset is pretty unbalanced so we’ll use an AUC to evaluate it. Looking at our table, our model has an average AUC of more than 0.9, so our model is quite good.

We can now build a model with the entire dataset.

model.fit(titanic_no_boat, predictors, response)


===========
call_string
===========
SELECT rf_classifier('"public"."_verticapy_tmp_randomforestclassifier_v_mldb_c4bd13b097b811efa8720242ac120002_"', '"public"."_verticapy_tmp_view_v_mldb_efbce32497b811efa8720242ac120002_"', 'survived', '"pclass", "name", "sex", "age", "sibsp", "parch", "fare", "boat", "family_size"' USING PARAMETERS exclude_columns='', ntree=40, mtry=4, sampling_size=0.632, max_depth=4, max_breadth=1000000000, min_leaf_size=1, min_info_gain=0, nbins=32);

=======
details
=======
 predictor |      type      
-----------+----------------
  pclass   |      int       
   name    |char or varchar 
    sex    |      int       
    age    |float or numeric
   sibsp   |      int       
   parch   |      int       
   fare    |float or numeric
   boat    |      int       
family_size|      int       


===============
Additional Info
===============
       Name       |Value
------------------+-----
    tree_count    | 40  
rejected_row_count|  1  
accepted_row_count| 794

Let’s look at the importance of each feature.

model.features_importance()

As expected, the passenger’s title is the most important predictors of survival.

Conclusion

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