Duplicates¶

When merging different data sources, we're likely to end up with duplicates that can add a lot of bias to and skew our data. Just imagine running a Telco marketing campaign and not removing your duplicates: you'll end up targeting the same person multiple times!

Let's use the Iris dataset to understand the tools VerticaPy gives you for handling duplicate values.

In [28]:

from verticapy.datasets import load_iris
vdf = load_iris()
vdf = vdf.append(load_iris().sample(3)) # adding some duplicates
display(vdf)

	123 Id Integer	123 PetalLengthCm Numeric(6,3)	123 PetalWidthCm Numeric(6,3)	123 SepalLengthCm Numeric(6,3)	123 SepalWidthCm Numeric(6,3)	Abc Species Varchar(30)
1	1	1.4	0.2	5.1	3.5	Iris-setosa
2	2	1.4	0.2	4.9	3.0	Iris-setosa
3	3	1.3	0.2	4.7	3.2	Iris-setosa
4	4	1.5	0.2	4.6	3.1	Iris-setosa
5	5	1.4	0.2	5.0	3.6	Iris-setosa
6	6	1.7	0.4	5.4	3.9	Iris-setosa
7	7	1.4	0.3	4.6	3.4	Iris-setosa
8	8	1.5	0.2	5.0	3.4	Iris-setosa
9	9	1.4	0.2	4.4	2.9	Iris-setosa
10	10	1.5	0.1	4.9	3.1	Iris-setosa
11	11	1.5	0.2	5.4	3.7	Iris-setosa
12	12	1.6	0.2	4.8	3.4	Iris-setosa
13	13	1.4	0.1	4.8	3.0	Iris-setosa
14	14	1.1	0.1	4.3	3.0	Iris-setosa
15	15	1.2	0.2	5.8	4.0	Iris-setosa
16	16	1.5	0.4	5.7	4.4	Iris-setosa
17	17	1.3	0.4	5.4	3.9	Iris-setosa
18	18	1.4	0.3	5.1	3.5	Iris-setosa
19	19	1.7	0.3	5.7	3.8	Iris-setosa
20	20	1.5	0.3	5.1	3.8	Iris-setosa
21	21	1.7	0.2	5.4	3.4	Iris-setosa
22	22	1.5	0.4	5.1	3.7	Iris-setosa
23	23	1.0	0.2	4.6	3.6	Iris-setosa
24	24	1.7	0.5	5.1	3.3	Iris-setosa
25	25	1.9	0.2	4.8	3.4	Iris-setosa
26	26	1.6	0.2	5.0	3.0	Iris-setosa
27	27	1.6	0.4	5.0	3.4	Iris-setosa
28	28	1.5	0.2	5.2	3.5	Iris-setosa
29	29	1.4	0.2	5.2	3.4	Iris-setosa
30	30	1.6	0.2	4.7	3.2	Iris-setosa
31	31	1.6	0.2	4.8	3.1	Iris-setosa
32	32	1.5	0.4	5.4	3.4	Iris-setosa
33	33	1.5	0.1	5.2	4.1	Iris-setosa
34	34	1.4	0.2	5.5	4.2	Iris-setosa
35	35	1.5	0.1	4.9	3.1	Iris-setosa
36	36	1.2	0.2	5.0	3.2	Iris-setosa
37	37	1.3	0.2	5.5	3.5	Iris-setosa
38	38	1.5	0.1	4.9	3.1	Iris-setosa
39	39	1.3	0.2	4.4	3.0	Iris-setosa
40	40	1.5	0.2	5.1	3.4	Iris-setosa
41	41	1.3	0.3	5.0	3.5	Iris-setosa
42	42	1.3	0.3	4.5	2.3	Iris-setosa
43	43	1.3	0.2	4.4	3.2	Iris-setosa
44	44	1.6	0.6	5.0	3.5	Iris-setosa
45	45	1.9	0.4	5.1	3.8	Iris-setosa
46	46	1.4	0.3	4.8	3.0	Iris-setosa
47	47	1.6	0.2	5.1	3.8	Iris-setosa
48	48	1.4	0.2	4.6	3.2	Iris-setosa
49	49	1.5	0.2	5.3	3.7	Iris-setosa
50	50	1.4	0.2	5.0	3.3	Iris-setosa
51	51	4.7	1.4	7.0	3.2	Iris-versicolor
52	52	4.5	1.5	6.4	3.2	Iris-versicolor
53	53	4.9	1.5	6.9	3.1	Iris-versicolor
54	54	4.0	1.3	5.5	2.3	Iris-versicolor
55	55	4.6	1.5	6.5	2.8	Iris-versicolor
56	56	4.5	1.3	5.7	2.8	Iris-versicolor
57	57	4.7	1.6	6.3	3.3	Iris-versicolor
58	58	3.3	1.0	4.9	2.4	Iris-versicolor
59	59	4.6	1.3	6.6	2.9	Iris-versicolor
60	60	3.9	1.4	5.2	2.7	Iris-versicolor
61	61	3.5	1.0	5.0	2.0	Iris-versicolor
62	62	4.2	1.5	5.9	3.0	Iris-versicolor
63	63	4.0	1.0	6.0	2.2	Iris-versicolor
64	64	4.7	1.4	6.1	2.9	Iris-versicolor
65	65	3.6	1.3	5.6	2.9	Iris-versicolor
66	66	4.4	1.4	6.7	3.1	Iris-versicolor
67	67	4.5	1.5	5.6	3.0	Iris-versicolor
68	68	4.1	1.0	5.8	2.7	Iris-versicolor
69	69	4.5	1.5	6.2	2.2	Iris-versicolor
70	70	3.9	1.1	5.6	2.5	Iris-versicolor
71	71	4.8	1.8	5.9	3.2	Iris-versicolor
72	72	4.0	1.3	6.1	2.8	Iris-versicolor
73	73	4.9	1.5	6.3	2.5	Iris-versicolor
74	74	4.7	1.2	6.1	2.8	Iris-versicolor
75	75	4.3	1.3	6.4	2.9	Iris-versicolor
76	76	4.4	1.4	6.6	3.0	Iris-versicolor
77	77	4.8	1.4	6.8	2.8	Iris-versicolor
78	78	5.0	1.7	6.7	3.0	Iris-versicolor
79	79	4.5	1.5	6.0	2.9	Iris-versicolor
80	80	3.5	1.0	5.7	2.6	Iris-versicolor
81	81	3.8	1.1	5.5	2.4	Iris-versicolor
82	82	3.7	1.0	5.5	2.4	Iris-versicolor
83	83	3.9	1.2	5.8	2.7	Iris-versicolor
84	84	5.1	1.6	6.0	2.7	Iris-versicolor
85	85	4.5	1.5	5.4	3.0	Iris-versicolor
86	86	4.5	1.6	6.0	3.4	Iris-versicolor
87	87	4.7	1.5	6.7	3.1	Iris-versicolor
88	88	4.4	1.3	6.3	2.3	Iris-versicolor
89	89	4.1	1.3	5.6	3.0	Iris-versicolor
90	90	4.0	1.3	5.5	2.5	Iris-versicolor
91	91	4.4	1.2	5.5	2.6	Iris-versicolor
92	92	4.6	1.4	6.1	3.0	Iris-versicolor
93	93	4.0	1.2	5.8	2.6	Iris-versicolor
94	94	3.3	1.0	5.0	2.3	Iris-versicolor
95	95	4.2	1.3	5.6	2.7	Iris-versicolor
96	96	4.2	1.2	5.7	3.0	Iris-versicolor
97	97	4.2	1.3	5.7	2.9	Iris-versicolor
98	98	4.3	1.3	6.2	2.9	Iris-versicolor
99	99	3.0	1.1	5.1	2.5	Iris-versicolor
100	100	4.1	1.3	5.7	2.8	Iris-versicolor

Rows: 1-100 | Columns: 6

To find all the duplicates, you can use the 'duplicated' method.

In [29]:

vdf.duplicated()

Out[29]:

	123 Id Integer	123 PetalLengthCm Numeric(6,3)	123 PetalWidthCm Numeric(6,3)	123 SepalLengthCm Numeric(6,3)	123 SepalWidthCm Numeric(6,3)	Abc Species Varchar(30)	123 occurrence Integer
1	33	1.5	0.1	5.2	4.1	Iris-setosa	2
2	70	3.9	1.1	5.6	2.5	Iris-versicolor	2
3	149	5.4	2.3	6.2	3.4	Iris-virginica	2

Rows: 3 | Columns: 7

As you might expect, some flowers might share the exact same characteristics. But we have to be careful; this doesn't mean that they are real duplicates. In this case, we don't have to drop them.

That said, if we did want to drop these duplicates, we can do so with the 'drop_duplicates' method.

In [30]:

vdf.drop_duplicates()

3 elements were filtered.

Out[30]:

	123 Id Integer	123 PetalLengthCm Numeric(6,3)	123 PetalWidthCm Numeric(6,3)	123 SepalLengthCm Numeric(6,3)	123 SepalWidthCm Numeric(6,3)	Abc Species Varchar(30)
1	1	1.4	0.2	5.1	3.5	Iris-setosa
2	2	1.4	0.2	4.9	3.0	Iris-setosa
3	3	1.3	0.2	4.7	3.2	Iris-setosa
4	4	1.5	0.2	4.6	3.1	Iris-setosa
5	5	1.4	0.2	5.0	3.6	Iris-setosa
6	6	1.7	0.4	5.4	3.9	Iris-setosa
7	7	1.4	0.3	4.6	3.4	Iris-setosa
8	8	1.5	0.2	5.0	3.4	Iris-setosa
9	9	1.4	0.2	4.4	2.9	Iris-setosa
10	10	1.5	0.1	4.9	3.1	Iris-setosa
11	11	1.5	0.2	5.4	3.7	Iris-setosa
12	12	1.6	0.2	4.8	3.4	Iris-setosa
13	13	1.4	0.1	4.8	3.0	Iris-setosa
14	14	1.1	0.1	4.3	3.0	Iris-setosa
15	15	1.2	0.2	5.8	4.0	Iris-setosa
16	16	1.5	0.4	5.7	4.4	Iris-setosa
17	17	1.3	0.4	5.4	3.9	Iris-setosa
18	18	1.4	0.3	5.1	3.5	Iris-setosa
19	19	1.7	0.3	5.7	3.8	Iris-setosa
20	20	1.5	0.3	5.1	3.8	Iris-setosa
21	21	1.7	0.2	5.4	3.4	Iris-setosa
22	22	1.5	0.4	5.1	3.7	Iris-setosa
23	23	1.0	0.2	4.6	3.6	Iris-setosa
24	24	1.7	0.5	5.1	3.3	Iris-setosa
25	25	1.9	0.2	4.8	3.4	Iris-setosa
26	26	1.6	0.2	5.0	3.0	Iris-setosa
27	27	1.6	0.4	5.0	3.4	Iris-setosa
28	28	1.5	0.2	5.2	3.5	Iris-setosa
29	29	1.4	0.2	5.2	3.4	Iris-setosa
30	30	1.6	0.2	4.7	3.2	Iris-setosa
31	31	1.6	0.2	4.8	3.1	Iris-setosa
32	32	1.5	0.4	5.4	3.4	Iris-setosa
33	33	1.5	0.1	5.2	4.1	Iris-setosa
34	34	1.4	0.2	5.5	4.2	Iris-setosa
35	35	1.5	0.1	4.9	3.1	Iris-setosa
36	36	1.2	0.2	5.0	3.2	Iris-setosa
37	37	1.3	0.2	5.5	3.5	Iris-setosa
38	38	1.5	0.1	4.9	3.1	Iris-setosa
39	39	1.3	0.2	4.4	3.0	Iris-setosa
40	40	1.5	0.2	5.1	3.4	Iris-setosa
41	41	1.3	0.3	5.0	3.5	Iris-setosa
42	42	1.3	0.3	4.5	2.3	Iris-setosa
43	43	1.3	0.2	4.4	3.2	Iris-setosa
44	44	1.6	0.6	5.0	3.5	Iris-setosa
45	45	1.9	0.4	5.1	3.8	Iris-setosa
46	46	1.4	0.3	4.8	3.0	Iris-setosa
47	47	1.6	0.2	5.1	3.8	Iris-setosa
48	48	1.4	0.2	4.6	3.2	Iris-setosa
49	49	1.5	0.2	5.3	3.7	Iris-setosa
50	50	1.4	0.2	5.0	3.3	Iris-setosa
51	51	4.7	1.4	7.0	3.2	Iris-versicolor
52	52	4.5	1.5	6.4	3.2	Iris-versicolor
53	53	4.9	1.5	6.9	3.1	Iris-versicolor
54	54	4.0	1.3	5.5	2.3	Iris-versicolor
55	55	4.6	1.5	6.5	2.8	Iris-versicolor
56	56	4.5	1.3	5.7	2.8	Iris-versicolor
57	57	4.7	1.6	6.3	3.3	Iris-versicolor
58	58	3.3	1.0	4.9	2.4	Iris-versicolor
59	59	4.6	1.3	6.6	2.9	Iris-versicolor
60	60	3.9	1.4	5.2	2.7	Iris-versicolor
61	61	3.5	1.0	5.0	2.0	Iris-versicolor
62	62	4.2	1.5	5.9	3.0	Iris-versicolor
63	63	4.0	1.0	6.0	2.2	Iris-versicolor
64	64	4.7	1.4	6.1	2.9	Iris-versicolor
65	65	3.6	1.3	5.6	2.9	Iris-versicolor
66	66	4.4	1.4	6.7	3.1	Iris-versicolor
67	67	4.5	1.5	5.6	3.0	Iris-versicolor
68	68	4.1	1.0	5.8	2.7	Iris-versicolor
69	69	4.5	1.5	6.2	2.2	Iris-versicolor
70	70	3.9	1.1	5.6	2.5	Iris-versicolor
71	71	4.8	1.8	5.9	3.2	Iris-versicolor
72	72	4.0	1.3	6.1	2.8	Iris-versicolor
73	73	4.9	1.5	6.3	2.5	Iris-versicolor
74	74	4.7	1.2	6.1	2.8	Iris-versicolor
75	75	4.3	1.3	6.4	2.9	Iris-versicolor
76	76	4.4	1.4	6.6	3.0	Iris-versicolor
77	77	4.8	1.4	6.8	2.8	Iris-versicolor
78	78	5.0	1.7	6.7	3.0	Iris-versicolor
79	79	4.5	1.5	6.0	2.9	Iris-versicolor
80	80	3.5	1.0	5.7	2.6	Iris-versicolor
81	81	3.8	1.1	5.5	2.4	Iris-versicolor
82	82	3.7	1.0	5.5	2.4	Iris-versicolor
83	83	3.9	1.2	5.8	2.7	Iris-versicolor
84	84	5.1	1.6	6.0	2.7	Iris-versicolor
85	85	4.5	1.5	5.4	3.0	Iris-versicolor
86	86	4.5	1.6	6.0	3.4	Iris-versicolor
87	87	4.7	1.5	6.7	3.1	Iris-versicolor
88	88	4.4	1.3	6.3	2.3	Iris-versicolor
89	89	4.1	1.3	5.6	3.0	Iris-versicolor
90	90	4.0	1.3	5.5	2.5	Iris-versicolor
91	91	4.4	1.2	5.5	2.6	Iris-versicolor
92	92	4.6	1.4	6.1	3.0	Iris-versicolor
93	93	4.0	1.2	5.8	2.6	Iris-versicolor
94	94	3.3	1.0	5.0	2.3	Iris-versicolor
95	95	4.2	1.3	5.6	2.7	Iris-versicolor
96	96	4.2	1.2	5.7	3.0	Iris-versicolor
97	97	4.2	1.3	5.7	2.9	Iris-versicolor
98	98	4.3	1.3	6.2	2.9	Iris-versicolor
99	99	3.0	1.1	5.1	2.5	Iris-versicolor
100	100	4.1	1.3	5.7	2.8	Iris-versicolor

Rows: 1-100 of 150 | Columns: 6

Using this method will add an advanced analytical function to the SQL code generation which is quite expensive. You should only use this method after aggregating the data to avoid stacking heavy computations on top of each other.