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 vastorbit gives you for handling duplicate values.
from vastorbit.datasets import load_iris
iris = load_iris()
iris = iris.append(load_iris().sample(3)) # adding some duplicates
iris.head(100)
123 sepallengthcmDecimal(5,2) | 123 sepalwidthcmDecimal(5,2) | 123 petallengthcmDecimal(5,2) | 123 petalwidthcmDecimal(5,2) | Abc speciesVarchar(30) | |
|---|---|---|---|---|---|
| 1 | 5.1 | 3.5 | 1.4 | 0.2 | Iris-setosa |
| 2 | 4.9 | 3.0 | 1.4 | 0.2 | Iris-setosa |
| 3 | 4.7 | 3.2 | 1.3 | 0.2 | Iris-setosa |
| 4 | 4.6 | 3.1 | 1.5 | 0.2 | Iris-setosa |
| 5 | 5.0 | 3.6 | 1.4 | 0.2 | Iris-setosa |
| 6 | 5.4 | 3.9 | 1.7 | 0.4 | Iris-setosa |
| 7 | 4.6 | 3.4 | 1.4 | 0.3 | Iris-setosa |
| 8 | 5.0 | 3.4 | 1.5 | 0.2 | Iris-setosa |
| 9 | 4.4 | 2.9 | 1.4 | 0.2 | Iris-setosa |
| 10 | 4.9 | 3.1 | 1.5 | 0.1 | Iris-setosa |
| 11 | 5.4 | 3.7 | 1.5 | 0.2 | Iris-setosa |
| 12 | 4.8 | 3.4 | 1.6 | 0.2 | Iris-setosa |
| 13 | 4.8 | 3.0 | 1.4 | 0.1 | Iris-setosa |
| 14 | 4.3 | 3.0 | 1.1 | 0.1 | Iris-setosa |
| 15 | 5.8 | 4.0 | 1.2 | 0.2 | Iris-setosa |
| 16 | 5.7 | 4.4 | 1.5 | 0.4 | Iris-setosa |
| 17 | 5.4 | 3.9 | 1.3 | 0.4 | Iris-setosa |
| 18 | 5.1 | 3.5 | 1.4 | 0.3 | Iris-setosa |
| 19 | 5.7 | 3.8 | 1.7 | 0.3 | Iris-setosa |
| 20 | 5.1 | 3.8 | 1.5 | 0.3 | Iris-setosa |
| 21 | 5.4 | 3.4 | 1.7 | 0.2 | Iris-setosa |
| 22 | 5.1 | 3.7 | 1.5 | 0.4 | Iris-setosa |
| 23 | 4.6 | 3.6 | 1.0 | 0.2 | Iris-setosa |
| 24 | 5.1 | 3.3 | 1.7 | 0.5 | Iris-setosa |
| 25 | 4.8 | 3.4 | 1.9 | 0.2 | Iris-setosa |
| 26 | 5.0 | 3.0 | 1.6 | 0.2 | Iris-setosa |
| 27 | 5.0 | 3.4 | 1.6 | 0.4 | Iris-setosa |
| 28 | 5.2 | 3.5 | 1.5 | 0.2 | Iris-setosa |
| 29 | 5.2 | 3.4 | 1.4 | 0.2 | Iris-setosa |
| 30 | 4.7 | 3.2 | 1.6 | 0.2 | Iris-setosa |
| 31 | 4.8 | 3.1 | 1.6 | 0.2 | Iris-setosa |
| 32 | 5.4 | 3.4 | 1.5 | 0.4 | Iris-setosa |
| 33 | 5.2 | 4.1 | 1.5 | 0.1 | Iris-setosa |
| 34 | 5.5 | 4.2 | 1.4 | 0.2 | Iris-setosa |
| 35 | 4.9 | 3.1 | 1.5 | 0.1 | Iris-setosa |
| 36 | 5.0 | 3.2 | 1.2 | 0.2 | Iris-setosa |
| 37 | 5.5 | 3.5 | 1.3 | 0.2 | Iris-setosa |
| 38 | 4.9 | 3.1 | 1.5 | 0.1 | Iris-setosa |
| 39 | 4.4 | 3.0 | 1.3 | 0.2 | Iris-setosa |
| 40 | 5.1 | 3.4 | 1.5 | 0.2 | Iris-setosa |
| 41 | 5.0 | 3.5 | 1.3 | 0.3 | Iris-setosa |
| 42 | 4.5 | 2.3 | 1.3 | 0.3 | Iris-setosa |
| 43 | 4.4 | 3.2 | 1.3 | 0.2 | Iris-setosa |
| 44 | 5.0 | 3.5 | 1.6 | 0.6 | Iris-setosa |
| 45 | 5.1 | 3.8 | 1.9 | 0.4 | Iris-setosa |
| 46 | 4.8 | 3.0 | 1.4 | 0.3 | Iris-setosa |
| 47 | 5.1 | 3.8 | 1.6 | 0.2 | Iris-setosa |
| 48 | 4.6 | 3.2 | 1.4 | 0.2 | Iris-setosa |
| 49 | 5.3 | 3.7 | 1.5 | 0.2 | Iris-setosa |
| 50 | 5.0 | 3.3 | 1.4 | 0.2 | Iris-setosa |
| 51 | 7.0 | 3.2 | 4.7 | 1.4 | Iris-versicolor |
| 52 | 6.4 | 3.2 | 4.5 | 1.5 | Iris-versicolor |
| 53 | 6.9 | 3.1 | 4.9 | 1.5 | Iris-versicolor |
| 54 | 5.5 | 2.3 | 4.0 | 1.3 | Iris-versicolor |
| 55 | 6.5 | 2.8 | 4.6 | 1.5 | Iris-versicolor |
| 56 | 5.7 | 2.8 | 4.5 | 1.3 | Iris-versicolor |
| 57 | 6.3 | 3.3 | 4.7 | 1.6 | Iris-versicolor |
| 58 | 4.9 | 2.4 | 3.3 | 1.0 | Iris-versicolor |
| 59 | 6.6 | 2.9 | 4.6 | 1.3 | Iris-versicolor |
| 60 | 5.2 | 2.7 | 3.9 | 1.4 | Iris-versicolor |
| 61 | 5.0 | 2.0 | 3.5 | 1.0 | Iris-versicolor |
| 62 | 5.9 | 3.0 | 4.2 | 1.5 | Iris-versicolor |
| 63 | 6.0 | 2.2 | 4.0 | 1.0 | Iris-versicolor |
| 64 | 6.1 | 2.9 | 4.7 | 1.4 | Iris-versicolor |
| 65 | 5.6 | 2.9 | 3.6 | 1.3 | Iris-versicolor |
| 66 | 6.7 | 3.1 | 4.4 | 1.4 | Iris-versicolor |
| 67 | 5.6 | 3.0 | 4.5 | 1.5 | Iris-versicolor |
| 68 | 5.8 | 2.7 | 4.1 | 1.0 | Iris-versicolor |
| 69 | 6.2 | 2.2 | 4.5 | 1.5 | Iris-versicolor |
| 70 | 5.6 | 2.5 | 3.9 | 1.1 | Iris-versicolor |
| 71 | 5.9 | 3.2 | 4.8 | 1.8 | Iris-versicolor |
| 72 | 6.1 | 2.8 | 4.0 | 1.3 | Iris-versicolor |
| 73 | 6.3 | 2.5 | 4.9 | 1.5 | Iris-versicolor |
| 74 | 6.1 | 2.8 | 4.7 | 1.2 | Iris-versicolor |
| 75 | 6.4 | 2.9 | 4.3 | 1.3 | Iris-versicolor |
| 76 | 6.6 | 3.0 | 4.4 | 1.4 | Iris-versicolor |
| 77 | 6.8 | 2.8 | 4.8 | 1.4 | Iris-versicolor |
| 78 | 6.7 | 3.0 | 5.0 | 1.7 | Iris-versicolor |
| 79 | 6.0 | 2.9 | 4.5 | 1.5 | Iris-versicolor |
| 80 | 5.7 | 2.6 | 3.5 | 1.0 | Iris-versicolor |
| 81 | 5.5 | 2.4 | 3.8 | 1.1 | Iris-versicolor |
| 82 | 5.5 | 2.4 | 3.7 | 1.0 | Iris-versicolor |
| 83 | 5.8 | 2.7 | 3.9 | 1.2 | Iris-versicolor |
| 84 | 6.0 | 2.7 | 5.1 | 1.6 | Iris-versicolor |
| 85 | 5.4 | 3.0 | 4.5 | 1.5 | Iris-versicolor |
| 86 | 6.0 | 3.4 | 4.5 | 1.6 | Iris-versicolor |
| 87 | 6.7 | 3.1 | 4.7 | 1.5 | Iris-versicolor |
| 88 | 6.3 | 2.3 | 4.4 | 1.3 | Iris-versicolor |
| 89 | 5.6 | 3.0 | 4.1 | 1.3 | Iris-versicolor |
| 90 | 5.5 | 2.5 | 4.0 | 1.3 | Iris-versicolor |
| 91 | 5.5 | 2.6 | 4.4 | 1.2 | Iris-versicolor |
| 92 | 6.1 | 3.0 | 4.6 | 1.4 | Iris-versicolor |
| 93 | 5.8 | 2.6 | 4.0 | 1.2 | Iris-versicolor |
| 94 | 5.0 | 2.3 | 3.3 | 1.0 | Iris-versicolor |
| 95 | 5.6 | 2.7 | 4.2 | 1.3 | Iris-versicolor |
| 96 | 5.7 | 3.0 | 4.2 | 1.2 | Iris-versicolor |
| 97 | 5.7 | 2.9 | 4.2 | 1.3 | Iris-versicolor |
| 98 | 6.2 | 2.9 | 4.3 | 1.3 | Iris-versicolor |
| 99 | 5.1 | 2.5 | 3.0 | 1.1 | Iris-versicolor |
| 100 | 5.7 | 2.8 | 4.1 | 1.3 | Iris-versicolor |
To find all the duplicates, you can use the duplicated() method.
iris.duplicated()
123 sepallengthcmDecimal(5, 2) | 123 sepalwidthcmDecimal(5, 2) | 123 petallengthcmDecimal(5, 2) | 123 petalwidthcmDecimal(5, 2) | Abc speciesVarchar(30) | 123 occurrenceBigint | |
|---|---|---|---|---|---|---|
| 1 | 5.8 | 2.7 | 5.1 | 1.9 | Iris-virginica | 3 |
| 2 | 4.9 | 3.1 | 1.5 | 0.1 | Iris-setosa | 3 |
| 3 | 5.8 | 2.8 | 5.1 | 2.4 | Iris-virginica | 2 |
| 4 | 4.5 | 2.3 | 1.3 | 0.3 | Iris-setosa | 2 |
| 5 | 5.1 | 3.7 | 1.5 | 0.4 | Iris-setosa | 2 |
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.
iris.drop_duplicates()
123 sepallengthcmDecimal(5,2) | 123 sepalwidthcmDecimal(5,2) | 123 petallengthcmDecimal(5,2) | 123 petalwidthcmDecimal(5,2) | Abc speciesVarchar(30) | |
|---|---|---|---|---|---|
| 1 | 4.8 | 3.4 | 1.9 | 0.2 | Iris-setosa |
| 2 | 5.2 | 3.4 | 1.4 | 0.2 | Iris-setosa |
| 3 | 6.6 | 3.0 | 4.4 | 1.4 | Iris-versicolor |
| 4 | 5.7 | 2.6 | 3.5 | 1.0 | Iris-versicolor |
| 5 | 6.0 | 3.4 | 4.5 | 1.6 | Iris-versicolor |
| 6 | 6.3 | 2.3 | 4.4 | 1.3 | Iris-versicolor |
| 7 | 5.6 | 3.0 | 4.1 | 1.3 | Iris-versicolor |
| 8 | 5.6 | 2.7 | 4.2 | 1.3 | Iris-versicolor |
| 9 | 6.7 | 3.3 | 5.7 | 2.1 | Iris-virginica |
| 10 | 6.9 | 3.1 | 5.4 | 2.1 | Iris-virginica |
| 11 | 6.7 | 3.1 | 5.6 | 2.4 | Iris-virginica |
| 12 | 6.7 | 3.3 | 5.7 | 2.5 | Iris-virginica |
| 13 | 5.1 | 3.5 | 1.4 | 0.2 | Iris-setosa |
| 14 | 5.7 | 4.4 | 1.5 | 0.4 | Iris-setosa |
| 15 | 5.4 | 3.4 | 1.5 | 0.4 | Iris-setosa |
| 16 | 5.0 | 3.5 | 1.6 | 0.6 | Iris-setosa |
| 17 | 5.6 | 2.5 | 3.9 | 1.1 | Iris-versicolor |
| 18 | 7.2 | 3.6 | 6.1 | 2.5 | Iris-virginica |
| 19 | 5.8 | 2.8 | 5.1 | 2.4 | Iris-virginica |
| 20 | 5.7 | 3.8 | 1.7 | 0.3 | Iris-setosa |
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.