Spam¶

This example uses the 'Spam' dataset to detect SMS spam. You can download the Jupyter Notebook of the study here.

v1: the SMS type (spam or ham)
v2: SMS content

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:

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 Virtual DataFrame of the dataset. The dataset is available here.

In [3]:

spam = vp.read_csv("data/spam.csv")
display(spam)

	Abc type Varchar(20)	Abc Varchar(1820)
1	ham
2	ham
3	ham
4	ham
5	ham
6	ham
7	ham
8	ham
9	ham
10	ham
11	ham
12	ham
13	ham
14	ham
15	ham
16	ham
17	ham
18	ham
19	ham
20	ham
21	ham
22	ham
23	ham
24	ham
25	ham
26	ham
27	ham
28	ham
29	ham
30	ham
31	ham
32	ham
33	ham
34	ham
35	ham
36	ham
37	ham
38	ham
39	ham
40	ham
41	ham
42	ham
43	ham
44	ham
45	ham
46	ham
47	ham
48	ham
49	ham
50	ham
51	ham
52	ham
53	ham
54	ham
55	ham
56	ham
57	ham
58	ham
59	ham
60	ham
61	ham
62	ham
63	ham
64	ham
65	ham
66	ham
67	ham
68	ham
69	ham
70	ham
71	ham
72	ham
73	ham
74	ham
75	ham
76	ham
77	ham
78	ham
79	ham
80	ham
81	ham
82	ham
83	ham
84	ham
85	ham
86	ham
87	ham
88	ham
89	ham
90	ham
91	ham
92	ham
93	ham
94	ham
95	ham
96	ham
97	ham
98	ham
99	ham
100	ham

Rows: 1-100 | Columns: 2

Data Exploration and Preparation¶

Our dataset relies on text analysis. First, we should create some features. For example, we can use the SMS length and label encoding on the 'type' to get a dummy (1 if the message is a SPAM, 0 otherwise). We should also convert the message content to lowercase to simplify our analysis.

In [4]:

import verticapy.stats as st
spam["length"] = st.length(spam["content"])
spam["content"].apply("LOWER({})")
spam["type"].decode('spam', 1, 0)

Out[4]:

	123 type Integer	Abc Varchar(3640)	123 length Integer
1	0		103
2	0		133
3	0		200
4	0		45
5	0		85
6	0		35
7	0		461
8	0		50
9	0		37
10	0		327
11	0		48
12	0		45
13	0		131
14	0		155
15	0		66
16	0		132
17	0		60
18	0		43
19	0		28
20	0		56
21	0		14
22	0		27
23	0		140
24	0		69
25	0		92
26	0		28
27	0		67
28	0		37
29	0		129
30	0		169
31	0		38
32	0		146
33	0		55
34	0		23
35	0		42
36	0		52
37	0		121
38	0		23
39	0		27
40	0		23
41	0		141
42	0		141
43	0		141
44	0		158
45	0		79
46	0		17
47	0		154
48	0		44
49	0		30
50	0		58
51	0		12
52	0		42
53	0		46
54	0		22
55	0		47
56	0		33
57	0		51
58	0		3
59	0		69
60	0		46
61	0		99
62	0		24
63	0		3
64	0		19
65	0		26
66	0		7
67	0		62
68	0		22
69	0		32
70	0		152
71	0		152
72	0		41
73	0		90
74	0		169
75	0		66
76	0		124
77	0		50
78	0		114
79	0		84
80	0		198
81	0		6
82	0		68
83	0		35
84	0		37
85	0		50
86	0		26
87	0		87
88	0		28
89	0		88
90	0		87
91	0		60
92	0		106
93	0		42
94	0		80
95	0		60
96	0		61
97	0		134
98	0		88
99	0		59
100	0		107

Rows: 1-100 | Columns: 3

Let's compute some statistics using the length of the message.

In [5]:

spam['type'].describe(method = 'cat_stats', 
                      numcol = 'length')

Out[5]:

	count	percent	mean	std	min	approx_10%	approx_25%	approx_50%	approx_75%	approx_90%	max
0	4723	86.53352876511542	69.5968663984755	56.2618267177762	2	24.0	33.0	51.0	89.0	143.0	910
1	735	13.466471234884573	139.239455782313	29.1254417417428	13	101.0	133.5	149.0	158.0	161.0	224

Rows: 1-2 | Columns: 12

Notice: spam tends to be longer than a normal message. First, let's create a view with just spam. Then, we'll use the CountVectorizer to create a dictionary and identify keywords.

In [6]:

spams = spam.search(spam["type"] == 1)

from verticapy.learn.preprocessing import CountVectorizer
dict_spams = CountVectorizer("spams_voc")
dict_spams.fit(spams, ["content"])
dict_spams = dict_spams.transform()
display(dict_spams)

	Abc token Varchar(128)	123 df Numeric(36,18)	123 cnt Integer	123 rnk Integer
1	to	0.028203859475507177	456	1
2	call	0.01966848095002474	318	2
3	a	0.01793666501731816	290	3
4	you	0.014287481444829293	231	4
5	your	0.013854527461652647	224	5
6	now	0.011071251855517071	179	6
7	or	0.010885700148441367	176	7
8	for	0.010761999010390896	174	8
9	free	0.009896091044037604	160	9
10	the	0.00983424047501237	159	10
11	txt	0.008782780801583375	142	11
12	is	0.008535378525482433	138	12
13	2	0.008287976249381494	134	13
14	have	0.007731321128154379	125	14
15	from	0.007607619990103909	123	15
16	on	0.007422068283028204	120	16
17	u	0.007298367144977734	118	17
18	ur	0.007050964868876794	114	18
19	4	0.006803562592775854	110	19
20	mobile	0.006741712023750619	109	20
21	claim	0.006679861454725383	108	21
22	and	0.006556160316674913	106	22
23	text	0.006246907471548738	101	23
24	with	0.006123206333498268	99	24
25	stop	0.005690252350321623	92	25
26	of	0.005628401781296388	91	26
27	reply	0.005504700643245918	89	27
28	prize	0.005195447798119743	84	28
29	our	0.005133597229094508	83	29
30	this	0.005009896091044038	81	30
31	only	0.004824344383968333	78	31
32	get	0.004824344383968333	78	31
33	just	0.004638792676892627	75	33
34	are	0.004638792676892627	75	33
35	won	0.004453240969816922	72	35
36	send	0.004082137555665512	66	36
37	new	0.004082137555665512	66	36
38	in	0.003896585848589807	63	38
39	urgent	0.003772884710539337	61	39
40	cash	0.003772884710539337	61	39
41	win	0.003649183572488867	59	41
42	no	0.003649183572488867	59	41
43	out	0.003463631865413162	56	43
44	contact	0.003401781296387927	55	44
45	service	0.003339930727362692	54	45
46	please	0.003216229589312222	52	46
47	guaranteed	0.003092528451261752	50	47
48	nokia	0.003030677882236517	49	48
49	customer	0.003030677882236517	49	48
50	we	0.002968827313211282	48	50
51	be	0.002968827313211282	48	50
52	16	0.002968827313211282	48	50
53	phone	0.002783275606135576	45	53
54	per	0.002721425037110341	44	54
55	500	0.002721425037110341	44	54
56	18	0.002659574468085106	43	56
57	will	0.002597723899059871	42	57
58	week	0.002535873330034636	41	58
59	been	0.002535873330034636	41	58
60	1000	0.002535873330034636	41	58
61	tone	0.002474022761009401	40	61
62	150	0.002412172191984166	39	62
63	draw	0.002350321622958931	38	63
64	awarded	0.002350321622958931	38	63
65	i	0.002288471053933696	37	65
66	chat	0.002288471053933696	37	65
67	100	0.002288471053933696	37	65
68	line	0.002164769915883226	35	68
69	1	0.002164769915883226	35	68
70	mins	0.002102919346857991	34	70
71	latest	0.002102919346857991	34	70
72	2000	0.002102919346857991	34	70
73	150ppm	0.002102919346857991	34	70
74	landline	0.001979218208807521	32	74
75	holiday	0.001979218208807521	32	74
76	every	0.001979218208807521	32	74
77	who	0.001917367639782286	31	77
78	shows	0.001917367639782286	31	77
79	by	0.001917367639782286	31	77
80	as	0.001917367639782286	31	77
81	number	0.001855517070757051	30	81
82	if	0.001855517070757051	30	81
83	has	0.001855517070757051	30	81
84	box	0.001855517070757051	30	81
85	po	0.001793666501731816	29	85
86	go	0.001793666501731816	29	85
87	code	0.001793666501731816	29	85
88	camera	0.001793666501731816	29	85
89	tcs	0.001731815932706581	28	89
90	receive	0.001731815932706581	28	89
91	message	0.001731815932706581	28	89
92	apply	0.001731815932706581	28	89
93	want	0.001669965363681346	27	93
94	more	0.001669965363681346	27	93
95	me	0.001669965363681346	27	93
96	it	0.001669965363681346	27	93
97	can	0.001669965363681346	27	93
98	all	0.001669965363681346	27	93
99	video	0.001608114794656111	26	99
100	that	0.001608114794656111	26	99

Rows: 1-100 | Columns: 4

Let's add the most occurent words in our vDataFrame and compute the correlation vector.

In [15]:

%matplotlib inline
for elem in dict_spams.head(200).values["token"]:
    spam.regexp(name = elem,
                pattern = elem,
                method = "count",
                column = "content")
x = spam.corr(focus = "type")

Let's just keep the first 100-most correlated features and merge the numbers together.

In [16]:

spam.drop(columns = x["index"][101:])
for elem in x["index"][1:101]:
    if any(char.isdigit() for char in elem):
        spam[elem].drop()
spam.regexp(column = "content",
            pattern = "([0-9])+",
            method = "count",
            name = "nb_numbers")

Out[16]:

	123 type Integer	Abc Varchar(3640)	123 length Integer	123 to Integer	123 call Integer	123 a Integer	123 your Integer	123 now Integer	123 or Integer	123 free Integer	123 txt Integer	123 from Integer	123 on Integer	123 u Integer	123 ur Integer	123 mobile Integer	123 claim Integer	123 text Integer	123 stop Integer	123 reply Integer	123 prize Integer	123 our Integer	123 won Integer	123 new Integer	123 urgent Integer	...	123 mob Integer	123 live Integer	123 valid Integer	123 ringtone Integer	123 collect Integer	123 sae Integer	123 r Integer	123 entry Integer	123 cost Integer	123 bonus Integer	123 vouchers Integer	123 rate Integer	123 tones Integer	123 await Integer	123 land Integer	123 award Integer	123 c Integer	123 winner Integer	123 top Integer	123 pobox Integer	123 wk Integer	123 voucher Integer	123 poly Integer	123 ltd Integer	123 nb_numbers Integer
1	0		103	2	0	8	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	...	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	2	0	0	0	0	0	0	0	3
2	0		133	1	0	6	0	0	1	0	0	0	1	2	0	0	0	0	0	0	0	0	0	0	0	...	0	0	0	0	0	0	3	0	0	0	0	0	0	0	0	0	2	0	0	0	0	0	0	0	0
3	0		200	4	0	18	0	0	0	0	0	0	3	8	0	0	0	0	0	0	0	0	0	0	0	...	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	4	0	0	0	0	0	0	0	1
4	0		45	0	0	3	0	0	0	0	0	0	1	2	0	0	0	0	0	0	0	0	0	0	0	...	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	0		85	1	0	8	0	0	0	0	0	0	0	3	1	0	0	0	0	0	0	0	0	0	0	...	0	0	0	0	0	0	5	0	0	0	0	0	0	0	0	0	2	0	0	0	0	0	0	0	0
6	0		35	0	0	1	0	0	1	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	0	...	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	2	0	0	0	0	0	0	0	0
7	0		461	1	0	47	1	0	1	0	0	0	1	6	1	0	0	0	0	0	0	1	0	1	0	...	0	0	0	0	0	0	22	0	0	0	0	0	0	0	0	0	6	0	0	0	0	0	0	0	1
8	0		50	2	0	1	0	0	0	0	0	0	0	1	0	0	0	0	1	0	0	0	0	0	0	...	0	0	0	0	0	0	2	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0
9	0		37	1	0	1	0	0	1	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	0	...	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10	0		327	2	0	11	1	0	1	0	0	0	3	11	1	0	0	0	0	0	0	1	0	1	0	...	0	0	0	0	0	0	16	0	0	0	0	0	0	0	0	0	2	0	0	0	0	0	0	0	1
11	0		48	0	0	4	0	0	0	0	0	0	0	2	0	0	0	0	0	0	0	0	0	0	0	...	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
12	0		45	0	0	2	0	0	0	0	0	0	0	2	0	0	0	0	0	0	0	0	0	0	0	...	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
13	0		131	1	0	7	0	0	2	0	0	1	1	2	0	0	0	0	0	0	0	0	0	0	0	...	0	0	0	0	0	0	6	0	0	0	0	0	0	0	0	0	4	0	0	0	0	0	0	0	0
14	0		155	0	0	13	0	0	2	0	0	0	0	5	0	0	0	0	0	0	0	0