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

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

In [3]:

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

The table "insurance"."insurance" has been successfully created.

	123 age Int	Abc sex Varchar(20)	123 bmi Numeric(8,4)	123 children Int	010 smoker Boolean	Abc region Varchar(20)	123 charges Float
1	18	female	20.79	0	❌	southeast	1607.5101
2	18	female	21.66	0	✅	northeast	14283.4594
3	18	female	24.09	1	❌	southeast	2201.0971
4	18	female	25.08	0	❌	northeast	2196.4732
5	18	female	26.315	0	❌	northeast	2198.18985
6	18	female	26.73	0	❌	southeast	1615.7667
7	18	female	27.28	3	✅	southeast	18223.4512
8	18	female	28.215	0	❌	northeast	2200.83085
9	18	female	29.165	0	❌	northeast	7323.734819
10	18	female	30.115	0	❌	northeast	2203.47185
11	18	female	30.115	0	❌	northeast	21344.8467
12	18	female	30.305	0	❌	northeast	2203.73595
13	18	female	31.13	0	❌	southeast	1621.8827
14	18	female	31.35	0	❌	southeast	1622.1885
15	18	female	31.35	4	❌	northeast	4561.1885
16	18	female	31.92	0	❌	northeast	2205.9808
17	18	female	32.12	2	❌	southeast	2801.2588
18	18	female	33.155	0	❌	northeast	2207.69745
19	18	female	33.88	0	❌	southeast	11482.63485
20	18	female	35.625	0	❌	northeast	2211.13075
21	18	female	36.85	0	❌	southeast	1629.8335
22	18	female	36.85	0	✅	southeast	36149.4835
23	18	female	37.29	1	❌	southeast	2219.4451
24	18	female	38.17	0	❌	southeast	1631.6683
25	18	female	38.28	0	❌	southeast	1631.8212
26	18	female	38.28	0	❌	southeast	14133.03775
27	18	female	38.665	2	❌	northeast	3393.35635
28	18	female	39.16	0	❌	southeast	1633.0444
29	18	female	39.82	0	❌	southeast	1633.9618
30	18	female	40.185	0	❌	northeast	2217.46915
31	18	female	40.26	0	❌	southeast	1634.5734
32	18	female	40.28	0	❌	northeast	2217.6012
33	18	female	42.24	0	✅	southeast	38792.6856
34	18	male	15.96	0	❌	northeast	1694.7964
35	18	male	17.29	2	✅	northeast	12829.4551
36	18	male	21.47	0	❌	northeast	1702.4553
37	18	male	21.565	0	✅	northeast	13747.87235
38	18	male	21.78	2	❌	southeast	11884.04858
39	18	male	22.99	0	❌	northeast	1704.5681
40	18	male	23.085	0	❌	northeast	1704.70015
41	18	male	23.21	0	❌	southeast	1121.8739
42	18	male	23.32	1	❌	southeast	1711.0268
43	18	male	23.75	0	❌	northeast	1705.6245
44	18	male	25.175	0	✅	northeast	15518.18025
45	18	male	25.46	0	❌	northeast	1708.0014
46	18	male	26.125	0	❌	northeast	1708.92575
47	18	male	26.18	2	❌	southeast	2304.0022
48	18	male	27.36	1	✅	northeast	17178.6824
49	18	male	28.31	1	❌	northeast	11272.33139
50	18	male	28.5	0	❌	northeast	1712.227
51	18	male	29.37	1	❌	southeast	1719.4363
52	18	male	30.03	1	❌	southeast	1720.3537
53	18	male	30.14	0	❌	southeast	1131.5066
54	18	male	30.4	3	❌	northeast	3481.868
55	18	male	31.68	2	✅	southeast	34303.1672
56	18	male	31.73	0	✅	northeast	33732.6867
57	18	male	33.33	0	❌	southeast	1135.9407
58	18	male	33.535	0	✅	northeast	34617.84065
59	18	male	33.66	0	❌	southeast	1136.3994
60	18	male	33.77	1	❌	southeast	1725.5523
61	18	male	34.1	0	❌	southeast	1137.011
62	18	male	34.43	0	❌	southeast	1137.4697
63	18	male	35.2	1	❌	southeast	1727.54
64	18	male	37.29	0	❌	southeast	1141.4451
65	18	male	38.17	0	✅	southeast	36307.7983
66	18	male	39.14	0	❌	northeast	12890.05765
67	18	male	41.14	0	❌	southeast	1146.7966
68	18	male	43.01	0	❌	southeast	1149.3959
69	18	male	53.13	0	❌	southeast	1163.4627
70	19	female	17.8	0	❌	southwest	1727.785
71	19	female	18.6	0	❌	southwest	1728.897
72	19	female	20.6	0	❌	southwest	1731.677
73	19	female	21.7	0	✅	southwest	13844.506
74	19	female	22.515	0	❌	northwest	2117.33885
75	19	female	23.4	2	❌	southwest	2913.569
76	19	female	24.51	1	❌	northwest	2709.1119
77	19	female	24.605	1	❌	northwest	2709.24395
78	19	female	24.7	0	❌	southwest	1737.376
79	19	female	25.745	1	❌	northwest	2710.82855
80	19	female	27.9	0	✅	southwest	16884.924
81	19	female	27.93	3	❌	northwest	18838.70366
82	19	female	28.3	0	✅	southwest	17081.08
83	19	female	28.31	0	✅	northwest	17468.9839
84	19	female	28.4	1	❌	southwest	2331.519
85	19	female	28.6	5	❌	southwest	4687.797
86	19	female	28.88	0	✅	northwest	17748.5062
87	19	female	28.9	0	❌	southwest	1743.214
88	19	female	29.8	0	❌	southwest	1744.465
89	19	female	30.02	0	✅	northwest	33307.5508
90	19	female	30.495	0	❌	northwest	2128.43105
91	19	female	30.59	2	❌	northwest	24059.68019
92	19	female	31.825	1	❌	northwest	2719.27975
93	19	female	32.11	0	❌	northwest	2130.6759
94	19	female	32.49	0	✅	northwest	36898.73308
95	19	female	32.9	0	❌	southwest	1748.774
96	19	female	33.11	0	✅	southeast	34439.8559
97	19	female	34.7	2	✅	southwest	36397.576
98	19	female	35.15	0	❌	northwest	2134.9015
99	19	female	36.575	0	❌	northwest	2136.88225
100	19	female	37.43	0	❌	northwest	2138.0707

Rows: 1-100 | Columns: 7

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

In [4]:

# returns the first five rows
data.head(5)

Out[4]:

	123 age Int	Abc sex Varchar(20)	123 bmi Numeric(8,4)	123 children Int	010 smoker Boolean	Abc region Varchar(20)	123 charges Float
1	18	female	20.79	0	❌	southeast	1607.5101
2	18	female	21.66	0	✅	northeast	14283.4594
3	18	female	24.09	1	❌	southeast	2201.0971
4	18	female	25.08	0	❌	northeast	2196.4732
5	18	female	26.315	0	❌	northeast	2198.18985

Rows: 1-5 | Columns: 7

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.

In [5]:

# count the number of non-null entries per column 
data.count_percent()

Out[5]:

	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

Rows: 1-7 | Columns: 3

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

In [6]:

# returns summary data of each feature
data.describe(method='all')

Out[6]:

	123 "age" Int 100%	123 "bmi" Numeric(8,4) 100%	123 "children" Int 100%	010 "smoker" Boolean 100%	123 "charges" Float 100%	Abc "sex" Varchar(20) 100%	Abc "region" Varchar(20) 100%
dtype	int	numeric(8,4)	int	boolean	float	varchar(20)	varchar(20)
percent	100	100	100	100	100	100	100
count	1338	1338	1338	1338	1338	1338	1338
top	18	32.3	0	❌	1639.5631	male	southeast
top_percent	5.157	0.972	42.9	79.522	0.149	50.523	27.205
avg	39.2070254110613	30.6633968609865	1.0949177877429	0.204783258594918	13270.4222651413	4.98953662182362	9.0
stddev	14.0499603792162	6.09818691167901	1.20549273978191	0.403694037545617	12110.011236694	1.00031913856875	0.0
min	18	15.96	0	0	1121.8739	4	9
approx_25%	27.0	26.29625	0.0	0.0	4740.28715	4	9
approx_50%	39.0	30.4	1.0	0.0	9379.1847	4	9
approx_75%	51.0	34.69375	2.0	0.0	16639.912515	6	9
max	64	53.13	5	1	63770.42801	6	9
range	46	37.17	5	1	62648.55411	2	0
empty	[null]	[null]	[null]	[null]	[null]	0	0

Rows: 1-14 | Columns: 8

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.

In [7]:

# histogram of age
data["age"].hist(method = "count", color = "#0073E7", h = 1)

Out[7]:

<AxesSubplot:xlabel='"age"', ylabel='Frequency'>

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.

In [8]:

# discretize the age using a bin of 5
data["age"].discretize(method = "same_width", h = 5)

Out[8]:

	Abc age Varchar 100%	Abc sex Varchar(20) 100%	123 bmi Numeric(8,4) 100%	123 children Int 100%	010 smoker Boolean 100%	Abc region Varchar(20) 100%	123 charges Float 100%
1	[15;19]	female	20.79	0	❌	southeast	1607.5101
2	[15;19]	female	21.66	0	✅	northeast	14283.4594
3	[15;19]	female	24.09	1	❌	southeast	2201.0971
4	[15;19]	female	25.08	0	❌	northeast	2196.4732
5	[15;19]	female	26.315	0	❌	northeast	2198.18985
6	[15;19]	female	26.73	0	❌	southeast	1615.7667
7	[15;19]	female	27.28	3	✅	southeast	18223.4512
8	[15;19]	female	28.215	0	❌	northeast	2200.83085
9	[15;19]	female	29.165	0	❌	northeast	7323.734819
10	[15;19]	female	30.115	0	❌	northeast	2203.47185
11	[15;19]	female	30.115	0	❌	northeast	21344.8467
12	[15;19]	female	30.305	0	❌	northeast	2203.73595
13	[15;19]	female	31.13	0	❌	southeast	1621.8827
14	[15;19]	female	31.35	0	❌	southeast	1622.1885
15	[15;19]	female	31.35	4	❌	northeast	4561.1885
16	[15;19]	female	31.92	0	❌	northeast	2205.9808
17	[15;19]	female	32.12	2	❌	southeast	2801.2588
18	[15;19]	female	33.155	0	❌	northeast	2207.69745
19	[15;19]	female	33.88	0	❌	southeast	11482.63485
20	[15;19]	female	35.625	0	❌	northeast	2211.13075
21	[15;19]	female	36.85	0	❌	southeast	1629.8335
22	[15;19]	female	36.85	0	✅	southeast	36149.4835
23	[15;19]	female	37.29	1	❌	southeast	2219.4451
24	[15;19]	female	38.17	0	❌	southeast	1631.6683
25	[15;19]	female	38.28	0	❌	southeast	1631.8212
26	[15;19]	female	38.28	0	❌	southeast	14133.03775
27	[15;19]	female	38.665	2	❌	northeast	3393.35635
28	[15;19]	female	39.16	0	❌	southeast	1633.0444
29	[15;19]	female	39.82	0	❌	southeast	1633.9618
30	[15;19]	female	40.185	0	❌	northeast	2217.46915
31	[15;19]	female	40.26	0	❌	southeast	1634.5734
32	[15;19]	female	40.28	0	❌	northeast	2217.6012
33	[15;19]	female	42.24	0	✅	southeast	38792.6856
34	[15;19]	male	15.96	0	❌	northeast	1694.7964
35	[15;19]	male	17.29	2	✅	northeast	12829.4551
36	[15;19]	male	21.47	0	❌	northeast	1702.4553
37	[15;19]	male	21.565	0	✅	northeast	13747.87235
38	[15;19]	male	21.78	2	❌	southeast	11884.04858
39	[15;19]	male	22.99	0	❌	northeast	1704.5681
40	[15;19]	male	23.085	0	❌	northeast	1704.70015
41	[15;19]	male	23.21	0	❌	southeast	1121.8739
42	[15;19]	male	23.32	1	❌	southeast	1711.0268
43	[15;19]	male	23.75	0	❌	northeast	1705.6245
44	[15;19]	male	25.175	0	✅	northeast	15518.18025
45	[15;19]	male	25.46	0	❌	northeast	1708.0014
46	[15;19]	male	26.125	0	❌	northeast	1708.92575
47	[15;19]	male	26.18	2	❌	southeast	2304.0022
48	[15;19]	male	27.36	1	✅	northeast	17178.6824
49	[15;19]	male	28.31	1	❌	northeast	11272.33139
50	[15;19]	male	28.5	0	❌	northeast	1712.227
51	[15;19]	male	29.37	1	❌	southeast	1719.4363
52	[15;19]	male	30.03	1	❌	southeast	1720.3537
53	[15;19]	male	30.14	0	❌	southeast	1131.5066
54	[15;19]	male	30.4	3	❌	northeast	3481.868
55	[15;19]	male	31.68	2	✅	southeast	34303.1672
56	[15;19]	male	31.73	0	✅	northeast	33732.6867
57	[15;19]	male	33.33	0	❌	southeast	1135.9407
58	[15;19]	male	33.535	0	✅	northeast	34617.84065
59	[15;19]	male	33.66	0	❌	southeast	1136.3994
60	[15;19]	male	33.77	1	❌	southeast	1725.5523
61	[15;19]	male	34.1	0	❌	southeast	1137.011
62	[15;19]	male	34.43	0	❌	southeast	1137.4697
63	[15;19]	male	35.2	1	❌	southeast	1727.54
64	[15;19]	male	37.29	0	❌	southeast	1141.4451
65	[15;19]	male	38.17	0	✅	southeast	36307.7983
66	[15;19]	male	39.14	0	❌	northeast	12890.05765
67	[15;19]	male	41.14	0	❌	southeast	1146.7966
68	[15;19]	male	43.01	0	❌	southeast	1149.3959
69	[15;19]	male	53.13	0	❌	southeast	1163.4627
70	[15;19]	female	17.8	0	❌	southwest	1727.785
71	[15;19]	female	18.6	0	❌	southwest	1728.897
72	[15;19]	female	20.6	0	❌	southwest	1731.677
73	[15;19]	female	21.7	0	✅	southwest	13844.506
74	[15;19]	female	22.515	0	❌	northwest	2117.33885
75	[15;19]	female	23.4	2	❌	southwest	2913.569
76	[15;19]	female	24.51	1	❌	northwest	2709.1119
77	[15;19]	female	24.605	1	❌	northwest	2709.24395
78	[15;19]	female	24.7	0	❌	southwest	1737.376
79	[15;19]	female	25.745	1	❌	northwest	2710.82855
80	[15;19]	female	27.9	0	✅	southwest	16884.924
81	[15;19]	female	27.93	3	❌	northwest	18838.70366
82	[15;19]	female	28.3	0	✅	southwest	17081.08
83	[15;19]	female	28.31	0	✅	northwest	17468.9839
84	[15;19]	female	28.4	1	❌	southwest	2331.519
85	[15;19]	female	28.6	5	❌	southwest	4687.797
86	[15;19]	female	28.88	0	✅	northwest	17748.5062
87	[15;19]	female	28.9	0	❌	southwest	1743.214
88	[15;19]	female	29.8	0	❌	southwest	1744.465
89	[15;19]	female	30.02	0	✅	northwest	33307.5508
90	[15;19]	female	30.495	0	❌	northwest	2128.43105
91	[15;19]	female	30.59	2	❌	northwest	24059.68019
92	[15;19]	female	31.825	1	❌	northwest	2719.27975
93	[15;19]	female	32.11	0	❌	northwest	2130.6759
94	[15;19]	female	32.49	0	✅	northwest	36898.73308
95	[15;19]	female	32.9	0	❌	southwest	1748.774
96	[15;19]	female	33.11	0	✅	southeast	34439.8559
97	[15;19]	female	34.7	2	✅	southwest	36397.576
98	[15;19]	female	35.15	0	❌	northwest	2134.9015
99	[15;19]	female	36.575	0	❌	northwest	2136.88225
100	[15;19]	female	37.43	0	❌	northwest	2138.0707

Rows: 1-100 of 1338 | Columns: 7

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.

In [9]:

# average of BMI for each age group
data.hchart(x = "age",
            y = "AVG(bmi)", 
            aggregate = True,
            kind = "bar")

Out[9]:

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.

In [10]:

# Importing the stats module
import verticapy.stats as st

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

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

In [11]:

# average of number of smokers per age group
data.hchart(x = "age",
            y = "AVG(smoker_int)", 
            aggregate = True,
            kind = "bar")

Out[11]:

Unfortuantely, 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.

In [12]:

# average of number of smokers per sex
data.hchart(x = "sex",
            y = "AVG(smoker_int)", 
            aggregate = True,
            kind = "bar")

Out[12]:

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.

In [13]:

# average bmi per sex
data.hchart(x = "sex",
            y = "AVG(bmi)", 
            aggregate = True,
            kind = "bar")

Out[13]:

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.

In [14]:

# pivot table with number of each sex per age group
data.pivot_table(['age','sex'])

Out[14]:

	"age"/"sex"	female	male
1	[15;19]	66	71
2	[20;24]	68	73
3	[25;29]	67	72
4	[30;34]	65	67
5	[35;39]	62	63
6	[40;44]	67	68
7	[45;49]	72	72
8	[50;54]	72	71
9	[55;59]	65	63
10	[60;64]	58	56

Rows: 1-10 | Columns: 3

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?

In [15]:

data["charges"].hist(method = "count", color = "#0073E7")

Out[15]:

<AxesSubplot:xlabel='"charges"', ylabel='Frequency'>

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.

In [16]:

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

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

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

Out[16]:

	123 age Int	123 sex Int	123 bmi Numeric(8,4)	123 children Int	010 smoker Boolean	123 region Int	123 charges Float	123 smoker_int Integer
1	0	0	20.79	0	❌	2	1607.5101	0
2	0	0	21.66	0	✅	0	14283.4594	1
3	0	0	24.09	1	❌	2	2201.0971	0
4	0	0	25.08	0	❌	0	2196.4732	0
5	0	0	26.315	0	❌	0	2198.18985	0
6	0	0	26.73	0	❌	2	1615.7667	0
7	0	0	27.28	3	✅	2	18223.4512	1
8	0	0	28.215	0	❌	0	2200.83085	0
9	0	0	29.165	0	❌	0	7323.734819	0
10	0	0	30.115	0	❌	0	2203.47185	0
11	0	0	30.115	0	❌	0	21344.8467	0
12	0	0	30.305	0	❌	0	2203.73595	0
13	0	0	31.13	0	❌	2	1621.8827	0
14	0	0	31.35	0	❌	2	1622.1885	0
15	0	0	31.35	4	❌	0	4561.1885	0
16	0	0	31.92	0	❌	0	2205.9808	0
17	0	0	32.12	2	❌	2	2801.2588	0
18	0	0	33.155	0	❌	0	2207.69745	0
19	0	0	33.88	0	❌	2	11482.63485	0
20	0	0	35.625	0	❌	0	2211.13075	0
21	0	0	36.85	0	❌	2	1629.8335	0
22	0	0	36.85	0	✅	2	36149.4835	1
23	0	0	37.29	1	❌	2	2219.4451	0
24	0	0	38.17	0	❌	2	1631.6683	0
25	0	0	38.28	0	❌	2	1631.8212	0
26	0	0	38.28	0	❌	2	14133.03775	0
27	0	0	38.665	2	❌	0	3393.35635	0
28	0	0	39.16	0	❌	2	1633.0444	0
29	0	0	39.82	0	❌	2	1633.9618	0
30	0	0	40.185	0	❌	0	2217.46915	0
31	0	0	40.26	0	❌	2	1634.5734	0
32	0	0	40.28	0	❌	0	2217.6012	0
33	0	0	42.24	0	✅	2	38792.6856	1
34	0	1	15.96	0	❌	0	1694.7964	0
35	0	1	17.29	2	✅	0	12829.4551	1
36	0	1	21.47	0	❌	0	1702.4553	0
37	0	1	21.565	0	✅	0	13747.87235	1
38	0	1	21.78	2	❌	2	11884.04858	0
39	0	1	22.99	0	❌	0	1704.5681	0
40	0	1	23.085	0	❌	0	1704.70015	0
41	0	1	23.21	0	❌	2	1121.8739	0
42	0	1	23.32	1	❌	2	1711.0268	0
43	0	1	23.75	0	❌	0	1705.6245	0
44	0	1	25.175	0	✅	0	15518.18025	1
45	0	1	25.46	0	❌	0	1708.0014	0
46	0	1	26.125	0	❌	0	1708.92575	0
47	0	1	26.18	2	❌	2	2304.0022	0
48	0	1	27.36	1	✅	0	17178.6824	1
49	0	1	28.31	1	❌	0	11272.33139	0
50	0	1	28.5	0	❌	0	1712.227	0
51	0	1	29.37	1	❌	2	1719.4363	0
52	0	1	30.03	1	❌	2	1720.3537	0
53	0	1	30.14	0	❌	2	1131.5066	0
54	0	1	30.4	3	❌	0	3481.868	0
55	0	1	31.68	2	✅	2	34303.1672	1
56	0	1	31.73	0	✅	0	33732.6867	1
57	0	1	33.33	0	❌	2	1135.9407	0
58	0	1	33.535	0	✅	0	34617.84065	1
59	0	1	33.66	0	❌	2	1136.3994	0
60	0	1	33.77	1	❌	2	1725.5523	0
61	0	1	34.1	0	❌	2	1137.011	0
62	0	1	34.43	0	❌	2	1137.4697	0
63	0	1	35.2	1	❌	2	1727.54	0
64	0	1	37.29	0	❌	2	1141.4451	0
65	0	1	38.17	0	✅	2	36307.7983	1
66	0	1	39.14	0	❌	0	12890.05765	0
67	0	1	41.14	0	❌	2	1146.7966	0
68	0	1	43.01	0	❌	2	1149.3959	0
69	0	1	53.13	0	❌	2	1163.4627	0
70	0	0	17.8	0	❌	3	1727.785	0
71	0	0	18.6	0	❌	3	1728.897	0
72	0	0	20.6	0	❌	3	1731.677	0
73	0	0	21.7	0	✅	3	13844.506	1
74	0	0	22.515	0	❌	1	2117.33885	0
75	0	0	23.4	2	❌	3	2913.569	0
76	0	0	24.51	1	❌	1	2709.1119	0
77	0	0	24.605	1	❌	1	2709.24395	0
78	0	0	24.7	0	❌	3	1737.376	0
79	0	0	25.745	1	❌	1	2710.82855	0
80	0	0	27.9	0	✅	3	16884.924	1
81	0	0	27.93	3	❌	1	18838.70366	0
82	0	0	28.3	0	✅	3	17081.08	1
83	0	0	28.31	0	✅	1	17468.9839	1
84	0	0	28.4	1	❌	3	2331.519	0
85	0	0	28.6	5	❌	3	4687.797	0
86	0	0	28.88	0	✅	1	17748.5062	1
87	0	0	28.9	0	❌	3	1743.214	0
88	0	0	29.8	0	❌	3	1744.465	0
89	0	0	30.02	0	✅	1	33307.5508	1
90	0	0	30.495	0	❌	1	2128.43105	0
91	0	0	30.59	2	❌	1	24059.68019	0
92	0	0	31.825	1	❌	1	2719.27975	0
93	0	0	32.11	0	❌	1	2130.6759	0
94	0	0	32.49	0	✅	1	36898.73308	1
95	0	0	32.9	0	❌	3	1748.774	0
96	0	0	33.11	0	✅	2	34439.8559	1
97	0	0	34.7	2	✅	3	36397.576	1
98	0	0	35.15	0	❌	1	2134.9015	0
99	0	0	36.575	0	❌	1	2136.88225	0
100	0	0	37.43	0	❌	1	2138.0707	0

Rows: 1-100 of 1338 | Columns: 8

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

In [17]:

# find correlations with charges
data.corr(focus = 'charges')

Out[17]:

	"charges"
"charges"	1.0
"smoker"	0.787
"smoker_int"	0.787
"age"	0.295
"bmi"	0.198
"children"	0.068
"sex"	0.057
"region"	-0.006

Rows: 1-8 | Columns: 2

In [18]:

# save our modified dataset to the database
data.to_db("insurance.final_ins_data", relation_type = "table")

Out[18]:

	123 age Int	123 sex Int	123 bmi Numeric(8,4)	123 children Int	010 smoker Boolean	123 region Int	123 charges Float	123 smoker_int Integer
1	0	0	20.79	0	❌	2	1607.5101	0
2	0	0	21.66	0	✅	0	14283.4594	1
3	0	0	24.09	1	❌	2	2201.0971	0
4	0	0	25.08	0	❌	0	2196.4732	0
5	0	0	26.315	0	❌	0	2198.18985	0
6	0	0	26.73	0	❌	2	1615.7667	0
7	0	0	27.28	3	✅	2	18223.4512	1
8	0	0	28.215	0	❌	0	2200.83085	0
9	0	0	29.165	0	❌	0	7323.734819	0
10	0	0	30.115	0	❌	0	2203.47185	0
11	0	0	30.115	0	❌	0	21344.8467	0
12	0	0	30.305	0	❌	0	2203.73595	0
13	0	0	31.13	0	❌	2	1621.8827	0
14	0	0	31.35	0	❌	2	1622.1885	0
15	0	0	31.35	4	❌	0	4561.1885	0
16	0	0	31.92	0	❌	0	2205.9808	0
17	0	0	32.12	2	❌	2	2801.2588	0
18	0	0	33.155	0	❌	0	2207.69745	0
19	0	0	33.88	0	❌	2	11482.63485	0
20	0	0	35.625	0	❌	0	2211.13075	0
21	0	0	36.85	0	❌	2	1629.8335	0
22	0	0	36.85	0	✅	2	36149.4835	1
23	0	0	37.29	1	❌	2	2219.4451	0
24	0	0	38.17	0	❌	2	1631.6683	0
25	0	0	38.28	0	❌	2	1631.8212	0
26	0	0	38.28	0	❌	2	14133.03775	0
27	0	0	38.665	2	❌	0	3393.35635	0
28	0	0	39.16	0	❌	2	1633.0444	0
29	0	0	39.82	0	❌	2	1633.9618	0
30	0	0	40.185	0	❌	0	2217.46915	0
31	0	0	40.26	0	❌	2	1634.5734	0
32	0	0	40.28	0	❌	0	2217.6012	0
33	0	0	42.24	0	✅	2	38792.6856	1
34	0	1	15.96	0	❌	0	1694.7964	0
35	0	1	17.29	2	✅	0	12829.4551	1
36	0	1	21.47	0	❌	0	1702.4553	0
37	0	1	21.565	0	✅	0	13747.87235	1
38	0	1	21.78	2	❌	2	11884.04858	0
39	0	1	22.99	0	❌	0	1704.5681	0
40	0	1	23.085	0	❌	0	1704.70015	0
41	0	1	23.21	0	❌	2	1121.8739	0
42	0	1	23.32	1	❌	2	1711.0268	0
43	0	1	23.75	0	❌	0	1705.6245	0
44	0	1	25.175	0	✅	0	15518.18025	1
45	0	1	25.46	0	❌	0	1708.0014	0
46	0	1	26.125	0	❌	0	1708.92575	0
47	0	1	26.18	2	❌	2	2304.0022	0
48	0	1	27.36	1	✅	0	17178.6824	1
49	0	1	28.31	1	❌	0	11272.33139	0
50	0	1	28.5	0	❌	0	1712.227	0
51	0	1	29.37	1	❌	2	1719.4363	0
52	0	1	30.03	1	❌	2	1720.3537	0
53	0	1	30.14	0	❌	2	1131.5066	0
54	0	1	30.4	3	❌	0	3481.868	0
55	0	1	31.68	2	✅	2	34303.1672	1
56	0	1	31.73	0	✅	0	33732.6867	1
57	0	1	33.33	0	❌	2	1135.9407	0
58	0	1	33.535	0	✅	0	34617.84065	1
59	0	1	33.66	0	❌	2	1136.3994	0
60	0	1	33.77	1	❌	2	1725.5523	0
61	0	1	34.1	0	❌	2	1137.011	0
62	0	1	34.43	0	❌	2	1137.4697	0
63	0	1	35.2	1	❌	2	1727.54	0
64	0	1	37.29	0	❌	2	1141.4451	0
65	0	1	38.17	0	✅	2	36307.7983	1
66	0	1	39.14	0	❌	0	12890.05765	0
67	0	1	41.14	0	❌	2	1146.7966	0
68	0	1	43.01	0	❌	2	1149.3959	0
69	0	1	53.13	0	❌	2	1163.4627	0
70	0	0	17.8	0	❌	3	1727.785	0
71	0	0	18.6	0	❌	3	1728.897	0
72	0	0	20.6	0	❌	3	1731.677	0
73	0	0	21.7	0	✅	3	13844.506	1
74	0	0	22.515	0	❌	1	2117.33885	0
75	0	0	23.4	2	❌	3	2913.569	0
76	0	0	24.51	1	❌	1	2709.1119	0
77	0	0	24.605	1	❌	1	2709.24395	0
78	0	0	24.7	0	❌	3	1737.376	0
79	0	0	25.745	1	❌	1	2710.82855	0
80	0	0	27.9	0	✅	3	16884.924	1
81	0	0	27.93	3	❌	1	18838.70366	0
82	0	0	28.3	0	✅	3	17081.08	1
83	0	0	28.31	0	✅	1	17468.9839	1
84	0	0	28.4	1	❌	3	2331.519	0
85	0	0	28.6	5	❌	3	4687.797	0
86	0	0	28.88	0	✅	1	17748.5062	1
87	0	0	28.9	0	❌	3	1743.214	0
88	0	0	29.8	0	❌	3	1744.465	0
89	0	0	30.02	0	✅	1	33307.5508	1
90	0	0	30.495	0	❌	1	2128.43105	0
91	0	0	30.59	2	❌	1	24059.68019	0
92	0	0	31.825	1	❌	1	2719.27975	0
93	0	0	32.11	0	❌	1	2130.6759	0
94	0	0	32.49	0	✅	1	36898.73308	1
95	0	0	32.9	0	❌	3	1748.774	0
96	0	0	33.11	0	✅	2	34439.8559	1
97	0	0	34.7	2	✅	3	36397.576	1
98	0	0	35.15	0	❌	1	2134.9015	0
99	0	0	36.575	0	❌	1	2136.88225	0
100	0	0	37.43	0	❌	1	2138.0707	0

Rows: 1-100 of 1338 | Columns: 8

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.

In [19]:

from verticapy.learn.ensemble import RandomForestRegressor
# define the random forest model
rf_model = RandomForestRegressor(name = "insurance.randf_insurance",
                                 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("insurance.final_ins_data", 
             ["age", "sex", "bmi", "children", "smoker", "region"], 
             "charges")

Out[19]:


===========
call_string
===========
SELECT rf_regressor('insurance.randf_insurance', 'insurance.final_ins_data', '"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.

In [20]:

# regression report for the model
rf_model.report()

Out[20]:

	value
explained_variance	0.791747824330153
max_error	30324.5370613481
median_absolute_error	2904.8161480505
mean_absolute_error	3895.99020116082
mean_squared_error	30521340.1955543
root_mean_squared_error	5524.612221283436
r2	0.791724007087732
r2_adj	0.7907851220708473
aic	23073.09148229111
bic	23109.399790449614

Rows: 1-10 | Columns: 2

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.

In [21]:

# 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'])

Out[21]:

<AxesSubplot:xlabel='"id"'>

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

In [22]:

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

Out[22]:

	importance	sign
smoker	78.58	1
bmi	10.83	1
age	9.68	1
region	0.43	1
children	0.36	1
sex	0.12	1

Rows: 1-6 | Columns: 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.

In [24]:

from verticapy.learn.linear_model import LinearRegression
model = LinearRegression(name = "step_lr_insurance_f")

# backward
from verticapy.learn.model_selection import stepwise
stepwise(model,
         input_relation = "insurance.final_ins_data", 
         direction = "forward",
         X = ["age","sex", "bmi", "children", "smoker", "region"], 
         y = "charges",)

Starting Stepwise

[Model 0] bic: 25165.382614983027; Variables: []
[Model 1] bic: 23878.781962322682; (+) Variable: "smoker"
[Model 2] bic: 23476.617719852235; (+) Variable: "age"
[Model 3] bic: 23352.957531194494; (+) Variable: "bmi"
[Model 4] bic: 23349.135202094396; (+) Variable: "children"

Selected Model

[Model 4] bic: 23349.135202094396; Variables: ['"smoker"', '"age"', '"bmi"', '"children"']

Out[24]:

	bic	change	variable	importance
0	25165.382614983027	[null]	[null]	0.0
1	23878.781962322682	+	"smoker"	70.83840249573072
2	23476.617719852235	+	"age"	22.142591346122273
3	23352.957531194494	+	"bmi"	6.808554152931561
4	23349.135202094396	+	"children"	0.2104520052154415
5	23356.15090424339	-	"sex"	0.0
6	23351.09070406615	-	"region"	0.0

Rows: 1-7 | Columns: 6

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.

VerticaPy

Python API for Vertica Data Science at Scale

About the Author

Jehona Kryeziu
Data Scientist

Jehona is a Junior Data Scientist working for Vertica. She works on data projects identifying use cases and implementing solutions that can deliver value and insights to businesses having in focus to provide machine learning, analytics and architecture solutions. She follows modern trends in data science and is curious in current needs that come as a result of the recent developments of technology and increase of data volume. Her other interests include mathematics and Natural Language Processing.