Feedback

×

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

Health Insurance Costs

In this example, we use a dataset of personal medical costs to create a model to estimate treatment costs.

You can download the Jupyter notebook here.

The columns provided include:

• age: age of the primary beneficiary.

• sex: insurance contractor’s gender.

• bmi: body mass index.

• children: number of dependent children covered by health insurance.

• smoker: smoker on non-smoker.

• region: the beneficiary’s residential area in the US: northeast, southeast, southwest, northwest.

• charges: individual medical costs billed by health insurance.

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 new schema and assign the data to a vDataFrame object.

vp.drop("insurance", method="schema")
vp.create_schema("insurance")
data = vp.read_csv("insurance.csv", schema = "insurance")

Let’s take a look at the first few entries in the dataset.

data.head(5)

	123 age Int 100%	...	Abc region Varchar(20) 100%	123 charges Float 100%
1	18	...	northeast	2196.4732
2	18	...	northeast	2203.47185
3	18	...	northeast	4561.1885
4	18	...	northeast	2205.9808
5	18	...	southeast	2801.2588

Data Exploration

Let’s check our dataset for missing values. If we find any, we’ll have to impute them before we create any models.

data.count_percent()

	...	count	percent
"age"	...	1338.0	100.0
"sex"	...	1338.0	100.0
"bmi"	...	1338.0	100.0
"children"	...	1338.0	100.0
"smoker"	...	1338.0	100.0
"region"	...	1338.0	100.0
"charges"	...	1338.0	100.0

There aren’t missing any values, so let’s get a summary of the features.

data.describe(method = "all")

	...	Abc "sex" Varchar(20) 100%	Abc "region" Varchar(20) 100%
dtype	...	varchar(20)	varchar(20)
percent	...	100	100
count	...	1338	1338
top	...	male	southeast
top_percent	...	50.523	27.205
avg	...	4.98953662182362	9.0
stddev	...	1.00031913856875	0.0
min	...	4	9
approx_25%	...	4	9
approx_50%	...	4	9
approx_75%	...	6	9
max	...	6	9
range	...	2	0
empty	...	0	0

The dataset covers 1338 individuals up to age 64 from four different regions, each with up to six dependent children.

We might find some interesting patterns if we check age distribution, so let’s create a histogram.

data["age"].hist(method = "count", h = 1)

We have a pretty obvious trend here: the 18 and 19 year old age groups are significantly more frequent than any other, older age group. The other ages range from 20 to 30 people.

Before we do anything else, let’s discretize the age column using equal-width binning with a width of 5. Our goal is to see if there are any obvious patterns among the different age groups.

data["age"].discretize(method = "same_width", h = 5)

	Abc age Varchar 100%	...	Abc region Varchar(20) 100%	123 charges Float 100%
1	[15;19]	...	northeast	2196.4732
2	[15;19]	...	northeast	2203.47185
3	[15;19]	...	northeast	4561.1885
4	[15;19]	...	northeast	2205.9808
5	[15;19]	...	southeast	2801.2588
6	[15;19]	...	northeast	2207.69745
7	[15;19]	...	southeast	11482.63485
8	[15;19]	...	northeast	2211.13075
9	[15;19]	...	southeast	38792.6856
10	[15;19]	...	northeast	1694.7964
11	[15;19]	...	southeast	1121.8739
12	[15;19]	...	northeast	15518.18025
13	[15;19]	...	northeast	1708.0014
14	[15;19]	...	northeast	1708.92575
15	[15;19]	...	southeast	36307.7983
16	[15;19]	...	northeast	12890.05765
17	[15;19]	...	southeast	1163.4627
18	[15;19]	...	southwest	1727.785
19	[15;19]	...	southwest	13844.506
20	[15;19]	...	northwest	2709.1119

Age probably influences one’s body mass index (BMI), so let’s compare the average of body mass indexes of each age group and look for patterns there. We’ll use a bar graph this time.

data.bar(
    ["age"],
    method = "mean",
    of = "bmi",
)

There’s a pretty clear trend here, and we can say that, in general, older individuals tend to have a greater BMIs.

Let’s check the average number of smokers for each age-group. Before we do, we’ll convert the ‘yes’ and ‘no’ ‘smoker’ values to more convenient boolean values.

import verticapy.sql.functions as fun

# Applying the decode function
data["smoker_int"] = fun.decode(data["smoker"], True, 1, 0)

Now we can plot the average number of smokers for each age group.

data.bar(
    ["age"],
    method = "mean",
    of = "smoker_int",
)

Unfortunately, there’s no obvious relationship between age and smoking habits - none that we can find from this graph, anyway.

Let’s see if we can relate an individual’s smoking habits with their sex.

data.bar(
    ["sex"],
    method = "mean",
    of = "smoker_int",
)

Now we’re getting somewhere! Looks like we have noticeably more male smokers than female ones.

Let’s see how an individual’s BMI relates to their sex.

data.bar(
    ["sex"],
    method = "mean",
    of = "bmi",
)

Males seem to have a slightly higher BMI, but it’d be hard to draw any conclusions from such a small difference.

Going back to our earlier patterns, let’s check the distribution of sexes among age groups and see if the patterns we identified earlier skews toward one of the sexes.

data.pivot_table(["age", "sex"])

It seems that sex is pretty evenly distributed in each age group.

Let’s move onto costs: how much do people tend to spend on medical treatments?

data["charges"].hist(method = "count")

Based on this graph, the majority of insurance holders tend to spend less than 1500 and only a handful of people spend more than 5000.

Encoding

Since our features vary in type, let’s start by encoding our categorical features. Remember, we label-encoded smoker from boolean. Let’s label-encode some other features: sex, region, and age groups.

# encoding sex
data["sex"].label_encode()

# encoding region
data["region"].label_encode()

# encoding age
data["age"].label_encode()

	123 age Int 100%	...	123 sex Int 100%	123 smoker_int Integer 100%
1	0	...	0	0
2	0	...	0	0
3	0	...	0	0
4	0	...	0	0
5	0	...	0	0
6	0	...	0	0
7	0	...	0	0
8	0	...	0	0
9	0	...	0	1
10	0	...	1	0
11	0	...	1	0
12	0	...	1	1
13	0	...	1	0
14	0	...	1	0
15	0	...	1	1
16	0	...	1	0
17	0	...	1	0
18	0	...	0	0
19	0	...	0	1
20	0	...	0	0

Before going further, let’s check the correlation of the variables with the predictor charges.

data.corr(focus = "charges")

data.to_db("insurance.final_ins_data", relation_type = "table")

---

Predicting insurance charges

Since our response variable is continuous, we can use regression to predict it. For this example, let’s use a Random Forest model.

from verticapy.machine_learning.vertica.ensemble import RandomForestRegressor

# define the random forest model
rf_model = RandomForestRegressor(
    n_estimators = 20,
    max_features = "auto",
    max_leaf_nodes = 32,
    sample = 0.7,
    max_depth = 3,
    min_samples_leaf = 5,
    min_info_gain = 0.0,
    nbins = 32,
)


# train the model
rf_model.fit(
    data,
    X = ["age", "sex", "bmi", "children", "smoker", "region"],
    y = "charges",
)



===========
call_string
===========
SELECT rf_regressor('"public"."_verticapy_tmp_randomforestregressor_v_mldb_efc40d5497b111efa8720242ac120002_"', '"public"."_verticapy_tmp_view_v_mldb_f01cf4b497b111efa8720242ac120002_"', 'charges', '"age", "sex", "bmi", "children", "smoker", "region"' USING PARAMETERS exclude_columns='', ntree=20, mtry=3, sampling_size=0.7, max_depth=3, max_breadth=32, min_leaf_size=5, min_info_gain=0, nbins=32);

=======
details
=======
predictor|      type      
---------+----------------
   age   |      int       
   sex   |      int       
   bmi   |float or numeric
children |      int       
 smoker  |      bool      
 region  |      int       


===============
Additional Info
===============
       Name       |Value
------------------+-----
    tree_count    | 20  
rejected_row_count|  0  
accepted_row_count|1338 

We can create a regression report to check our model’s performance.

rf_model.report()

	value
explained_variance	0.753975281650272
max_error	28515.3971097498
median_absolute_error	3142.81188656875
mean_absolute_error	4298.74999590123
mean_squared_error	36056945.1688865
root_mean_squared_error	6004.74355563054
r2	0.753949334848445
r2_adj	0.752840165809444
aic	23296.1741509
bic	23332.408774848

The results seem to be quite good! We have an explained variance around 0.8. Let’s plot the predicted values and compare them to the real ones.

# plot the predicted values and real ones
result = rf_model.predict(
    data,
    name = "pred_charges",
)

# add an index
result["id"] = "ROW_NUMBER() OVER()"

# plot them along the id
result.plot(
    ts = "id",
    columns = ['charges', 'pred_charges'],
)

data.to_db("insurance.final_ins_data", relation_type = "table")

Now, let’s examine the importance of each feature for this model. Ours is a random forest model, so we can use the built-in Vertica function RF_PREDICTOR_IMPORTANCE() to calculate the importance of each predictor with Mean Decrease in Impurity (MDI).

# feature importance for our random forest model
rf_model.features_importance()

data.to_db("insurance.final_ins_data", relation_type = "table")

rf_model.features_importance(show = False)

	...	importance	sign
smoker	...	81.09	1.0
age	...	11.8	1.0
bmi	...	6.66	1.0
children	...	0.46	1.0
sex	...	0.0	0.0
region	...	0.0	0.0

We can examine how our model works by visualizing one of the trees in our Random Forest.

# plot one of the trees comprising the forest
rf_model.plot_tree(tree_id = 3)

What affects medical costs?

We have a couple ways to approach this question. First, let’s see what features are linearly correlated with the cost.

It seems that smoking habits have a significant effect on medical costs. Next in line comes BMI, the number of dependents, and sex.

As one might expect, the correlation between charges and region is almost 0.

Now, let’s see what we can learn from a stepwise model with forward elimination using Bayesian information criterion (BIC) as a selection criteria.

from verticapy.machine_learning.vertica.linear_model import LinearRegression

model = LinearRegression()

# backward
from verticapy.machine_learning.model_selection import stepwise

stepwise(
    model,
    input_relation = data,
    direction = "forward",
    X = ["age","sex", "bmi", "children", "smoker", "region"],
    y = "charges",
)

	...	variable	importance
0	...	[null]	0.0
1	...	"smoker"	70.8384024957294
2	...	"age"	22.142591346125677
3	...	"bmi"	6.80855415292928
4	...	"children"	0.2104520052156456
5	...	"sex"	0.0
6	...	"region"	0.0

From here we see that, again, the same features have similarly significant effects on medical costs.

Conclusion

In this example, we used several methods to identify the primary factors that affect one’s insurance costs.