Reading tip. Intermediate Spark
.show()/ cell outputs are folded in the bordered blocks below. Click Click to expand to reveal a horizontally scrollable monospace dump; the Python steps stay visible in the main flow.
K-Means is known as a common unsupervised learning clustering method. But in fact, the K-Means algorithm can be applied to more scenarios. In this post, I will use a K-Means-based approach to complete anomaly detection for text-based email content.
All the data manipulation and modeling processes involved in this approach will be fully implemented based on PySpark-3.1.1. Considering the need to work with large amounts of text data and the high time complexity of K-Means-class algorithms, Spark-based practice can effectively improve processing power and running speed. To make reproduction easier and reduce problems caused by setting up a Spark environment, everything can be done in a notebook in Google Colab.
The data I used is the Insider Threat Test Dataset from Carnegie Mellon University, provided by the CERT Division. It contains synthetic insider threat test data with both background and malicious actor activity. It includes 1000 users and spans 17 months. Please download the dataset from CMU KiltHub and unzip it. Then put the CSV files into the folder ./data/ under the same folder as the notebook.
For more background on this data, please see the paper Bridging the Gap: A Pragmatic Approach to Generating Insider Threat Data.
Method overview
- Split text into word lists
- Remove stop words
- Generate word count vectors
- Reduce dimensionality by MinHash
- Find the appropriate centroid for each observation by K-Means
- Calculate the distance
- Sort by distance
K-Means partitions points ${x_i}$ into $k$ clusters by minimizing within-cluster sum of squares:
\[\min_{C_1,\ldots,C_k} \sum_{j=1}^{k}\sum_{x\in C_j} \lVert x - \mu_j\rVert_2^2, \qquad \mu_j=\frac{1}{|C_j|}\sum_{x\in C_j}x.\]MinHash is used so that collision rates approximate Jaccard similarity of the sets of tokens with nonzero counts,
\[P\bigl(h_{\min}(A)=h_{\min}(B)\bigr) = J(A,B) = \frac{|A\cap B|}{|A\cup B|}.\]Anomaly scores are Euclidean distances to the assigned centroid,
\[d(x,\mu_{\hat{c}(x)})=\lVert x-\mu_{\hat{c}(x)}\rVert_2,\]with larger $d$ treated as more anomalous. Cluster count $k$ is chosen using the Silhouette score.
If you want to accelerate the manipulation process, you can skip all of the dataframe.show() calls.
Build environment
Since Colab does not have the PySpark module installed, we need to install PySpark and configure the related environment first.
!pip install pyspark
!pip install -U -q PyDrive
!apt update
!apt install openjdk-8-jdk-headless -qq
import os
os.environ["JAVA_HOME"] = "/usr/lib/jvm/java-8-openjdk-amd64"
from google.colab import drive
drive.mount('/content/drive')
Please navigate to the location where this notebook is saved.
import os
cur_path = "/content/drive/MyDrive/Insider-Risk-in-PySpark/"
os.chdir(cur_path)
!pwd
Start a Spark session, and then we can check the configuration details.
from pyspark.sql import SparkSession
spark = SparkSession.builder.appName('proj').getOrCreate()
spark.sparkContext.getConf().getAll()
Output
[('spark.sql.warehouse.dir',
'file:/content/drive/MyDrive/insider-risk-in-spark/spark-warehouse'),
('spark.driver.port', '36571'),
('spark.rdd.compress', 'True'),
('spark.driver.host', '5a29464ac89d'),
('spark.app.id', 'local-1619919014564'),
('spark.serializer.objectStreamReset', '100'),
('spark.master', 'local[*]'),
('spark.submit.pyFiles', ''),
('spark.app.startTime', '1619919013556'),
('spark.executor.id', 'driver'),
('spark.submit.deployMode', 'client'),
('spark.ui.showConsoleProgress', 'true'),
('spark.app.name', 'proj')]
Import necessary modules.
import matplotlib.pyplot as plt
import numpy as np
from pyspark.ml.feature import Tokenizer, StopWordsRemover, CountVectorizer, StandardScaler, MinHashLSH, VectorAssembler
from pyspark.sql.functions import udf, col
from pyspark.sql.types import *
from pyspark.ml.functions import vector_to_array
from pyspark.ml.linalg import Vectors
from pyspark.ml.clustering import KMeans
from pyspark.ml.evaluation import ClusteringEvaluator
from pyspark.mllib.stat import KernelDensity
Load data
The email.csv file is about 1GB and contains 2.6 million emails. Since we are only working on the email content this time, we can just read the content of that file.
email = spark.read.csv( './data/email.csv',inferSchema=True,header=True)
email.printSchema()
email.show(5)
Output
root
|-- id: string (nullable = true)
|-- date: string (nullable = true)
|-- user: string (nullable = true)
|-- pc: string (nullable = true)
|-- to: string (nullable = true)
|-- cc: string (nullable = true)
|-- bcc: string (nullable = true)
|-- from: string (nullable = true)
|-- size: integer (nullable = true)
|-- attachments: integer (nullable = true)
|-- content: string (nullable = true)
+--------------------+-------------------+-------+-------+--------------------+--------------------+--------------------+--------------------+-----+-----------+--------------------+
| id| date| user| pc| to| cc| bcc| from| size|attachments| content|
+--------------------+-------------------+-------+-------+--------------------+--------------------+--------------------+--------------------+-----+-----------+--------------------+
|{R3I7-S4TX96FG-82...|01/02/2010 07:11:45|LAP0338|PC-5758|Dean.Flynn.Hines@...|Nathaniel.Hunter....| null|Lynn.Adena.Pratt@...|25830| 0|middle f2 systems...|
|{R0R9-E4GL59IK-29...|01/02/2010 07:12:16|MOH0273|PC-6699|Odonnell-Gage@bel...| null| null| MOH68@optonline.net|29942| 0|the breaking call...|
|{G2B2-A8XY58CP-28...|01/02/2010 07:13:00|LAP0338|PC-5758|Penelope_Colon@ne...| null| null|Lynn_A_Pratt@eart...|28780| 0|slowly this uncin...|
|{A3A9-F4TH89AA-83...|01/02/2010 07:13:17|LAP0338|PC-5758|Judith_Hayden@com...| null| null|Lynn_A_Pratt@eart...|21907| 0|400 other difficu...|
|{E8B7-C8FZ88UF-29...|01/02/2010 07:13:28|MOH0273|PC-6699|Bond-Raymond@veri...| null|Odonnell-Gage@bel...| MOH68@optonline.net|17319| 0|this kmh october ...|
+--------------------+-------------------+-------+-------+--------------------+--------------------+--------------------+--------------------+-----+-----------+--------------------+
only showing top 5 rows
Extract word-vector
First, we need to split the email content into lists of words, and then remove common meaningless words, also known as “stop words”. The list of stop words is provided by PySpark. Sometimes, we can also use n-grams to get a more representative list of phrases.
tokenizer = Tokenizer(inputCol="content", outputCol="words")
wordsData = tokenizer.transform(email)
remover = StopWordsRemover(inputCol="words", outputCol="clean_words")
wordsData = remover.transform(wordsData)
wordsData.show()
Output
+--------------------+-------------------+-------+-------+--------------------+--------------------+--------------------+--------------------+-----+-----------+--------------------+--------------------+--------------------+
| id| date| user| pc| to| cc| bcc| from| size|attachments| content| words| clean_words|
+--------------------+-------------------+-------+-------+--------------------+--------------------+--------------------+--------------------+-----+-----------+--------------------+--------------------+--------------------+
|{R3I7-S4TX96FG-82...|01/02/2010 07:11:45|LAP0338|PC-5758|Dean.Flynn.Hines@...|Nathaniel.Hunter....| null|Lynn.Adena.Pratt@...|25830| 0|middle f2 systems...|[middle, f2, syst...|[middle, f2, syst...|
|{R0R9-E4GL59IK-29...|01/02/2010 07:12:16|MOH0273|PC-6699|Odonnell-Gage@bel...| null| null| MOH68@optonline.net|29942| 0|the breaking call...|[the, breaking, c...|[breaking, called...|
|{G2B2-A8XY58CP-28...|01/02/2010 07:13:00|LAP0338|PC-5758|Penelope_Colon@ne...| null| null|Lynn_A_Pratt@eart...|28780| 0|slowly this uncin...|[slowly, this, un...|[slowly, uncinus,...|
|{A3A9-F4TH89AA-83...|01/02/2010 07:13:17|LAP0338|PC-5758|Judith_Hayden@com...| null| null|Lynn_A_Pratt@eart...|21907| 0|400 other difficu...|[400, other, diff...|[400, difficult, ...|
|{E8B7-C8FZ88UF-29...|01/02/2010 07:13:28|MOH0273|PC-6699|Bond-Raymond@veri...| null|Odonnell-Gage@bel...| MOH68@optonline.net|17319| 0|this kmh october ...|[this, kmh, octob...|[kmh, october, ho...|
|{X8T7-A6BT54FP-72...|01/02/2010 07:36:03|HVB0037|PC-7979|Gaines-Joseph@msn...|Hollee_Becker@hot...| null|Hollee_Becker@hot...|44345| 0|little equal k is...|[little, equal, k...|[little, equal, k...|
|{H5J6-G2RS59KI-83...|01/02/2010 07:52:20|NWK0215|PC-8370|Heidi_Wilson@msn....|Noelani.W.Kennedy...| null|Noelani.W.Kennedy...|35328| 0|stroke menacing 1...|[stroke, menacing...|[stroke, menacing...|
|{D9T8-M1HJ89XP-63...|01/02/2010 07:54:12|LRR0148|PC-4275|Eve.Isadora.Mcken...| null| null|Libby.Rosalyn.Ric...|25255| 1|leading companys ...|[leading, company...|[leading, company...|
|{V3L7-L2RB92RV-91...|01/02/2010 07:54:49|LRR0148|PC-4275|Cedric.Herrod.Gil...| null| null|Libby.Rosalyn.Ric...|33967| 0|reception website...|[reception, websi...|[reception, websi...|
|{D5K9-P0IJ71WK-63...|01/02/2010 07:54:58|LRR0148|PC-4275|Gay.Ria.Cantu@dta...|Zenia.Freya.Macia...| null|Libby.Rosalyn.Ric...|19456| 1|smaller weather r...|[smaller, weather...|[smaller, weather...|
|{R0A5-U4YQ17EA-34...|01/02/2010 07:55:16|LRR0148|PC-4275|August_Holt@boein...| null| null|Libby.Rosalyn.Ric...|23687| 0|do potentially 2 ...|[do, potentially,...|[potentially, 2, ...|
|{Y8Z6-X5HU72BM-73...|01/02/2010 07:55:51|NWK0215|PC-8370|Tasha_Sanchez@opt...|Ulric-Knapp@earth...|Noelani.W.Kennedy...|Noelani.W.Kennedy...|28960| 0|villepin five sha...|[villepin, five, ...|[villepin, five, ...|
|{K3B8-S0RJ27BU-68...|01/02/2010 07:56:09|AJR0319|PC-4736|Ulric.Ferdinand.K...|Meghan.Brianna.Je...|Arthur.Jacob.Raym...|Arthur.Jacob.Raym...|23116| 1|he instill prehis...|[he, instill, pre...|[instill, prehist...|
|{J7Y1-G7KD78BQ-41...|01/02/2010 07:56:49|LRR0148|PC-4275|Gay.Ria.Cantu@dta...|Sasha.Rina.Huffma...| null|Libby.Rosalyn.Ric...|53349| 1|went could jackso...|[went, could, jac...|[went, jackson, a...|
|{D7P4-Z0PP26KM-17...|01/02/2010 07:57:10|AJR0319|PC-4736|Meredith.Ainsley....|Arthur.Jacob.Raym...| null|Arthur.Jacob.Raym...|26284| 0|connections progr...|[connections, pro...|[connections, pro...|
|{P6J4-Y0XJ63II-57...|01/02/2010 07:58:04|AJR0319|PC-4736|Connor.Phelan.Gue...|Arthur.Jacob.Raym...| null|Arthur.Jacob.Raym...|25317| 0|rested considered...|[rested, consider...|[rested, consider...|
|{K7Y5-V5IP47OA-83...|01/02/2010 07:58:07|LRR0148|PC-4275|Bevis.Brady.Shepp...|Gay.Ria.Cantu@dta...| null|Libby.Rosalyn.Ric...|16168| 2|additional funera...|[additional, fune...|[additional, fune...|
|{R9V2-W5OA43XS-14...|01/02/2010 07:58:13|LRR0148|PC-4275|Thomas.Vladimir.S...| null| null|Libby.Rosalyn.Ric...|52290| 0|need there did co...|[need, there, did...|[need, comes, nam...|
|{X4R4-F1BP75UA-02...|01/02/2010 07:58:15|LRR0148|PC-4275|Sasha.Rina.Huffma...| null| null|Libby.Rosalyn.Ric...|18333| 0|since data englis...|[since, data, eng...|[since, data, eng...|
|{N4L7-S2MN81EJ-50...|01/02/2010 07:58:25|LRR0148|PC-4275|Nissim.Gil.French...| null| null|Libby.Rosalyn.Ric...|35956| 3|orphan treatment ...|[orphan, treatmen...|[orphan, treatmen...|
+--------------------+-------------------+-------+-------+--------------------+--------------------+--------------------+--------------------+-----+-----------+--------------------+--------------------+--------------------+
only showing top 20 rows
The function CountVectorizer can convert a collection of text documents to vectors of token counts. It can produce sparse representations for the documents over the vocabulary.
We choose 1000 as the vocabulary size under consideration. Of course, if the device allows, we can choose a larger dimension to obtain stronger representational power.
cv = CountVectorizer(inputCol="clean_words", outputCol="features", vocabSize=1000, minDF=2.0)
model = cv.fit(wordsData)
wordsCV = model.transform(wordsData)
Since the MinHash algorithm used in the later steps cannot handle an all-0 vector, we need to remove it in this step. Of course, if the content of an email generates an all-0 vector, it means that the content of that email is also anomalous. Therefore, the emails removed in this step also need to be treated as anomalous emails.
all0vector = Vectors.dense([0]*1000)
# Filter the empty Sparse Vector
def no_empty_vector(value):
if value != all0vector:
return True
else:
return False
no_empty_vector_udf = udf(no_empty_vector, BooleanType())
wordsCV = wordsCV.filter(no_empty_vector_udf('features'))
wordsCV.show()
Output
+--------------------+-------------------+-------+-------+--------------------+--------------------+--------------------+--------------------+-----+-----------+--------------------+--------------------+--------------------+--------------------+
| id| date| user| pc| to| cc| bcc| from| size|attachments| content| words| clean_words| features|
+--------------------+-------------------+-------+-------+--------------------+--------------------+--------------------+--------------------+-----+-----------+--------------------+--------------------+--------------------+--------------------+
|{R3I7-S4TX96FG-82...|01/02/2010 07:11:45|LAP0338|PC-5758|Dean.Flynn.Hines@...|Nathaniel.Hunter....| null|Lynn.Adena.Pratt@...|25830| 0|middle f2 systems...|[middle, f2, syst...|[middle, f2, syst...|(1000,[28,59,105,...|
|{R0R9-E4GL59IK-29...|01/02/2010 07:12:16|MOH0273|PC-6699|Odonnell-Gage@bel...| null| null| MOH68@optonline.net|29942| 0|the breaking call...|[the, breaking, c...|[breaking, called...|(1000,[5,10,22,29...|
|{G2B2-A8XY58CP-28...|01/02/2010 07:13:00|LAP0338|PC-5758|Penelope_Colon@ne...| null| null|Lynn_A_Pratt@eart...|28780| 0|slowly this uncin...|[slowly, this, un...|[slowly, uncinus,...|(1000,[5,16,63,14...|
|{A3A9-F4TH89AA-83...|01/02/2010 07:13:17|LAP0338|PC-5758|Judith_Hayden@com...| null| null|Lynn_A_Pratt@eart...|21907| 0|400 other difficu...|[400, other, diff...|[400, difficult, ...|(1000,[5,36,38,80...|
|{E8B7-C8FZ88UF-29...|01/02/2010 07:13:28|MOH0273|PC-6699|Bond-Raymond@veri...| null|Odonnell-Gage@bel...| MOH68@optonline.net|17319| 0|this kmh october ...|[this, kmh, octob...|[kmh, october, ho...|(1000,[9,14,56,72...|
|{X8T7-A6BT54FP-72...|01/02/2010 07:36:03|HVB0037|PC-7979|Gaines-Joseph@msn...|Hollee_Becker@hot...| null|Hollee_Becker@hot...|44345| 0|little equal k is...|[little, equal, k...|[little, equal, k...|(1000,[28,40,54,5...|
|{H5J6-G2RS59KI-83...|01/02/2010 07:52:20|NWK0215|PC-8370|Heidi_Wilson@msn....|Noelani.W.Kennedy...| null|Noelani.W.Kennedy...|35328| 0|stroke menacing 1...|[stroke, menacing...|[stroke, menacing...|(1000,[12,38,71,7...|
|{D9T8-M1HJ89XP-63...|01/02/2010 07:54:12|LRR0148|PC-4275|Eve.Isadora.Mcken...| null| null|Libby.Rosalyn.Ric...|25255| 1|leading companys ...|[leading, company...|[leading, company...|(1000,[9,10,24,11...|
|{V3L7-L2RB92RV-91...|01/02/2010 07:54:49|LRR0148|PC-4275|Cedric.Herrod.Gil...| null| null|Libby.Rosalyn.Ric...|33967| 0|reception website...|[reception, websi...|[reception, websi...|(1000,[28,40,41,4...|
|{D5K9-P0IJ71WK-63...|01/02/2010 07:54:58|LRR0148|PC-4275|Gay.Ria.Cantu@dta...|Zenia.Freya.Macia...| null|Libby.Rosalyn.Ric...|19456| 1|smaller weather r...|[smaller, weather...|[smaller, weather...|(1000,[16,25,38,9...|
|{R0A5-U4YQ17EA-34...|01/02/2010 07:55:16|LRR0148|PC-4275|August_Holt@boein...| null| null|Libby.Rosalyn.Ric...|23687| 0|do potentially 2 ...|[do, potentially,...|[potentially, 2, ...|(1000,[7,29,122,1...|
|{Y8Z6-X5HU72BM-73...|01/02/2010 07:55:51|NWK0215|PC-8370|Tasha_Sanchez@opt...|Ulric-Knapp@earth...|Noelani.W.Kennedy...|Noelani.W.Kennedy...|28960| 0|villepin five sha...|[villepin, five, ...|[villepin, five, ...|(1000,[10,29,78,1...|
|{K3B8-S0RJ27BU-68...|01/02/2010 07:56:09|AJR0319|PC-4736|Ulric.Ferdinand.K...|Meghan.Brianna.Je...|Arthur.Jacob.Raym...|Arthur.Jacob.Raym...|23116| 1|he instill prehis...|[he, instill, pre...|[instill, prehist...|(1000,[3,116,126,...|
|{J7Y1-G7KD78BQ-41...|01/02/2010 07:56:49|LRR0148|PC-4275|Gay.Ria.Cantu@dta...|Sasha.Rina.Huffma...| null|Libby.Rosalyn.Ric...|53349| 1|went could jackso...|[went, could, jac...|[went, jackson, a...|(1000,[33,43,45,4...|
|{D7P4-Z0PP26KM-17...|01/02/2010 07:57:10|AJR0319|PC-4736|Meredith.Ainsley....|Arthur.Jacob.Raym...| null|Arthur.Jacob.Raym...|26284| 0|connections progr...|[connections, pro...|[connections, pro...|(1000,[35,52,128,...|
|{P6J4-Y0XJ63II-57...|01/02/2010 07:58:04|AJR0319|PC-4736|Connor.Phelan.Gue...|Arthur.Jacob.Raym...| null|Arthur.Jacob.Raym...|25317| 0|rested considered...|[rested, consider...|[rested, consider...|(1000,[4,10,38,10...|
|{K7Y5-V5IP47OA-83...|01/02/2010 07:58:07|LRR0148|PC-4275|Bevis.Brady.Shepp...|Gay.Ria.Cantu@dta...| null|Libby.Rosalyn.Ric...|16168| 2|additional funera...|[additional, fune...|[additional, fune...|(1000,[23,65,74,1...|
|{R9V2-W5OA43XS-14...|01/02/2010 07:58:13|LRR0148|PC-4275|Thomas.Vladimir.S...| null| null|Libby.Rosalyn.Ric...|52290| 0|need there did co...|[need, there, did...|[need, comes, nam...|(1000,[9,65,80,11...|
|{X4R4-F1BP75UA-02...|01/02/2010 07:58:15|LRR0148|PC-4275|Sasha.Rina.Huffma...| null| null|Libby.Rosalyn.Ric...|18333| 0|since data englis...|[since, data, eng...|[since, data, eng...|(1000,[4,23,24,33...|
|{N4L7-S2MN81EJ-50...|01/02/2010 07:58:25|LRR0148|PC-4275|Nissim.Gil.French...| null| null|Libby.Rosalyn.Ric...|35956| 3|orphan treatment ...|[orphan, treatmen...|[orphan, treatmen...|(1000,[47,77,96,1...|
+--------------------+-------------------+-------+-------+--------------------+--------------------+--------------------+--------------------+-----+-----------+--------------------+--------------------+--------------------+--------------------+
only showing top 20 rows
Dimension reduction by MinHash
In this step, we reduce the dimensionality of the features by using the MinHash algorithm while ensuring that the similarity between emails is maintained. It also converts sparse features into dense features. With $m$ independent MinHash functions, each email is mapped to an $m$-dimensional signature whose pairwise agreement rate estimates Jaccard similarity.
For more details about the MinHash algorithm, there is a good explanation from the University of Utah.
mh = MinHashLSH(inputCol="features", outputCol="hashes", numHashTables=20)
model = mh.fit(wordsCV)
wordsHash = model.transform(wordsCV)
wordsHash.show()
Output
+--------------------+-------------------+-------+-------+--------------------+--------------------+--------------------+--------------------+-----+-----------+--------------------+--------------------+--------------------+--------------------+--------------------+
| id| date| user| pc| to| cc| bcc| from| size|attachments| content| words| clean_words| features| hashes|
+--------------------+-------------------+-------+-------+--------------------+--------------------+--------------------+--------------------+-----+-----------+--------------------+--------------------+--------------------+--------------------+--------------------+
|{R3I7-S4TX96FG-82...|01/02/2010 07:11:45|LAP0338|PC-5758|Dean.Flynn.Hines@...|Nathaniel.Hunter....| null|Lynn.Adena.Pratt@...|25830| 0|middle f2 systems...|[middle, f2, syst...|[middle, f2, syst...|(1000,[28,59,105,...|[[8.776332E7], [1...|
|{R0R9-E4GL59IK-29...|01/02/2010 07:12:16|MOH0273|PC-6699|Odonnell-Gage@bel...| null| null| MOH68@optonline.net|29942| 0|the breaking call...|[the, breaking, c...|[breaking, called...|(1000,[5,10,22,29...|[[195285.0], [1.2...|
|{G2B2-A8XY58CP-28...|01/02/2010 07:13:00|LAP0338|PC-5758|Penelope_Colon@ne...| null| null|Lynn_A_Pratt@eart...|28780| 0|slowly this uncin...|[slowly, this, un...|[slowly, uncinus,...|(1000,[5,16,63,14...|[[2.90624351E8], ...|
|{A3A9-F4TH89AA-83...|01/02/2010 07:13:17|LAP0338|PC-5758|Judith_Hayden@com...| null| null|Lynn_A_Pratt@eart...|21907| 0|400 other difficu...|[400, other, diff...|[400, difficult, ...|(1000,[5,36,38,80...|[[8.2692039E7], [...|
|{E8B7-C8FZ88UF-29...|01/02/2010 07:13:28|MOH0273|PC-6699|Bond-Raymond@veri...| null|Odonnell-Gage@bel...| MOH68@optonline.net|17319| 0|this kmh october ...|[this, kmh, octob...|[kmh, october, ho...|(1000,[9,14,56,72...|[[2.62393241E8], ...|
|{X8T7-A6BT54FP-72...|01/02/2010 07:36:03|HVB0037|PC-7979|Gaines-Joseph@msn...|Hollee_Becker@hot...| null|Hollee_Becker@hot...|44345| 0|little equal k is...|[little, equal, k...|[little, equal, k...|(1000,[28,40,54,5...|[[3.66359397E8], ...|
|{H5J6-G2RS59KI-83...|01/02/2010 07:52:20|NWK0215|PC-8370|Heidi_Wilson@msn....|Noelani.W.Kennedy...| null|Noelani.W.Kennedy...|35328| 0|stroke menacing 1...|[stroke, menacing...|[stroke, menacing...|(1000,[12,38,71,7...|[[1.3212552E7], [...|
|{D9T8-M1HJ89XP-63...|01/02/2010 07:54:12|LRR0148|PC-4275|Eve.Isadora.Mcken...| null| null|Libby.Rosalyn.Ric...|25255| 1|leading companys ...|[leading, company...|[leading, company...|(1000,[9,10,24,11...|[[1.88348622E8], ...|
|{V3L7-L2RB92RV-91...|01/02/2010 07:54:49|LRR0148|PC-4275|Cedric.Herrod.Gil...| null| null|Libby.Rosalyn.Ric...|33967| 0|reception website...|[reception, websi...|[reception, websi...|(1000,[28,40,41,4...|[[4.6514943E7], [...|
|{D5K9-P0IJ71WK-63...|01/02/2010 07:54:58|LRR0148|PC-4275|Gay.Ria.Cantu@dta...|Zenia.Freya.Macia...| null|Libby.Rosalyn.Ric...|19456| 1|smaller weather r...|[smaller, weather...|[smaller, weather...|(1000,[16,25,38,9...|[[5266566.0], [2....|
|{R0A5-U4YQ17EA-34...|01/02/2010 07:55:16|LRR0148|PC-4275|August_Holt@boein...| null| null|Libby.Rosalyn.Ric...|23687| 0|do potentially 2 ...|[do, potentially,...|[potentially, 2, ...|(1000,[7,29,122,1...|[[1.05851868E8], ...|
|{Y8Z6-X5HU72BM-73...|01/02/2010 07:55:51|NWK0215|PC-8370|Tasha_Sanchez@opt...|Ulric-Knapp@earth...|Noelani.W.Kennedy...|Noelani.W.Kennedy...|28960| 0|villepin five sha...|[villepin, five, ...|[villepin, five, ...|(1000,[10,29,78,1...|[[1.3212552E7], [...|
|{K3B8-S0RJ27BU-68...|01/02/2010 07:56:09|AJR0319|PC-4736|Ulric.Ferdinand.K...|Meghan.Brianna.Je...|Arthur.Jacob.Raym...|Arthur.Jacob.Raym...|23116| 1|he instill prehis...|[he, instill, pre...|[instill, prehist...|(1000,[3,116,126,...|[[1.3864811E8], [...|
|{J7Y1-G7KD78BQ-41...|01/02/2010 07:56:49|LRR0148|PC-4275|Gay.Ria.Cantu@dta...|Sasha.Rina.Huffma...| null|Libby.Rosalyn.Ric...|53349| 1|went could jackso...|[went, could, jac...|[went, jackson, a...|(1000,[33,43,45,4...|[[4.6514943E7], [...|
|{D7P4-Z0PP26KM-17...|01/02/2010 07:57:10|AJR0319|PC-4736|Meredith.Ainsley....|Arthur.Jacob.Raym...| null|Arthur.Jacob.Raym...|26284| 0|connections progr...|[connections, pro...|[connections, pro...|(1000,[35,52,128,...|[[2.8426395E7], [...|
|{P6J4-Y0XJ63II-57...|01/02/2010 07:58:04|AJR0319|PC-4736|Connor.Phelan.Gue...|Arthur.Jacob.Raym...| null|Arthur.Jacob.Raym...|25317| 0|rested considered...|[rested, consider...|[rested, consider...|(1000,[4,10,38,10...|[[9.5709306E7], [...|
|{K7Y5-V5IP47OA-83...|01/02/2010 07:58:07|LRR0148|PC-4275|Bevis.Brady.Shepp...|Gay.Ria.Cantu@dta...| null|Libby.Rosalyn.Ric...|16168| 2|additional funera...|[additional, fune...|[additional, fune...|(1000,[23,65,74,1...|[[1.30702124E8], ...|
|{R9V2-W5OA43XS-14...|01/02/2010 07:58:13|LRR0148|PC-4275|Thomas.Vladimir.S...| null| null|Libby.Rosalyn.Ric...|52290| 0|need there did co...|[need, there, did...|[need, comes, nam...|(1000,[9,65,80,11...|[[5.953221E7], [3...|
|{X4R4-F1BP75UA-02...|01/02/2010 07:58:15|LRR0148|PC-4275|Sasha.Rina.Huffma...| null| null|Libby.Rosalyn.Ric...|18333| 0|since data englis...|[since, data, eng...|[since, data, eng...|(1000,[4,23,24,33...|[[3.15474607E8], ...|
|{N4L7-S2MN81EJ-50...|01/02/2010 07:58:25|LRR0148|PC-4275|Nissim.Gil.French...| null| null|Libby.Rosalyn.Ric...|35956| 3|orphan treatment ...|[orphan, treatmen...|[orphan, treatmen...|(1000,[47,77,96,1...|[[1.10923149E8], ...|
+--------------------+-------------------+-------+-------+--------------------+--------------------+--------------------+--------------------+-----+-----------+--------------------+--------------------+--------------------+--------------------+--------------------+
only showing top 20 rows
id_hash = wordsHash.select('id', 'hashes')
Since the features generated by the MinHashLSH function are a 20-dimensional list composed of 20 DenseVectors, we need to convert it to a flat 20-dimensional DenseVector.
Therefore, we first split the list into 20 columns, then convert the DenseVector in each column to a pure value, and finally merge the 20 columns.
sc = spark.sparkContext
numAttrs = 20
attrs = sc.parallelize(["hash_" + str(i) for i in range(numAttrs)]).zipWithIndex().collect()
for name, index in attrs:
id_hash = id_hash.withColumn(name, id_hash['hashes'].getItem(index))
id_hash.show()
Output
+--------------------+--------------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+
| id| hashes| hash_0| hash_1| hash_2| hash_3| hash_4| hash_5| hash_6| hash_7| hash_8| hash_9| hash_10| hash_11| hash_12| hash_13| hash_14| hash_15| hash_16| hash_17| hash_18| hash_19|
+--------------------+--------------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+
|{R3I7-S4TX96FG-82...|[[8.776332E7], [1...| [8.776332E7]| [1.7940101E7]| [6.4377752E7]| [4.5060227E7]| [9.4146089E7]| [5.4730737E7]| [2045183.0]| [3.5318959E7]| [9.7305009E7]|[1.60568104E8]|[1.26579267E8]| [4.1193117E7]| [5.1198766E7]| [4.7158998E7]| [7507190.0]| [8.2002584E7]| [5.0971487E7]| [1.9229588E7]| [2.8138237E7]| [5.5599848E7]|
|{R0R9-E4GL59IK-29...|[[195285.0], [1.2...| [195285.0]|[1.26315806E8]|[2.26939755E8]| [4.3066557E7]| [2.9401395E7]|[1.49026164E8]|[4.65667429E8]|[1.36802781E8]| [6.212708E7]| [7.625038E7]|[1.48161613E8]| [5.1362335E7]| [7.259244E7]|[2.81864322E8]| [3.2096901E8]|[1.63367351E8]| [2.3015655E7]| [7.5967366E7]|[1.23447019E8]| [2.548758E7]|
|{G2B2-A8XY58CP-28...|[[2.90624351E8], ...|[2.90624351E8]|[1.04640665E8]| [1.2043641E8]| [4.8549236E7]|[1.15042474E8]|[1.14372217E8]| [1.7074233E8]| [3.854587E8]|[5.72021968E8]| [4.425368E7]|[3.20208273E8]| [4.1193117E7]| [8.5952566E7]| [2.4460579E7]|[1.45858361E8]|[5.25808011E8]|[1.32294306E8]| [2.6179662E7]|[1.05112325E8]|[2.40326464E8]|
|{A3A9-F4TH89AA-83...|[[8.2692039E7], [...| [8.2692039E7]|[3.39218494E8]|[2.10928785E8]|[1.98576277E8]| [3.1456934E7]| [3.9409618E7]|[2.73620356E8]| [2.1076498E7]|[1.17151187E8]|[1.40896553E8]| [2.9764764E7]| [9.0192079E7]| [4627505.0]|[2.00334998E8]| [9798842.0]| [50832.0]|[1.48256912E8]|[1.71309613E8]|[1.52766716E8]| [2.8672437E7]|
|{E8B7-C8FZ88UF-29...|[[2.62393241E8], ...|[2.62393241E8]| [3.9615242E7]|[1.05215857E8]| [1.1665476E7]|[1.56835244E8]|[1.24038631E8]|[2.29117145E8]|[2.73739681E8]|[2.59651373E8]|[1.80239655E8]|[1.57012455E8]| [1.2203462E7]|[3.78507397E8]|[2.73371579E8]| [7.467758E7]| [1.7048611E7]| [8562838.0]|[1.07621761E8]|[1.21234205E8]|[4.88462473E8]|
|{X8T7-A6BT54FP-72...|[[3.66359397E8], ...|[3.66359397E8]| [1.7940101E7]| [8319094.0]| [4.1571218E7]|[1.47414821E8]| [3.3919616E8]| [6.5667663E7]| [8.2053606E7]|[1.27968511E8]|[1.03921106E8]| [5.134711E7]| [1.7288071E7]| [6.0774979E7]| [1.7357741E7]| [9.706771E7]| [8.3227663E7]| [5.0971487E7]| [9.3342551E7]| [7.2868565E7]|[1.97474768E8]|
|{H5J6-G2RS59KI-83...|[[1.3212552E7], [...| [1.3212552E7]| [5.501013E7]|[1.05215857E8]| [1.4923182E8]| [8.0614588E7]| [6.4397151E7]| [2.5395109E7]| [5.3568684E7]| [8.1973258E7]| [3.1928531E7]| [6.4623373E7]| [5929983.0]|[1.49900727E8]|[1.98945093E8]|[4.34638399E8]| [2.3122897E7]| [1.7536486E7]| [4.7408946E7]| [4.3548868E7]| [1.680116E7]|
|{D9T8-M1HJ89XP-63...|[[1.88348622E8], ...|[1.88348622E8]|[1.45559416E8]| [5116900.0]| [2.9110521E7]|[2.62515436E8]| [7.0841427E7]| [4.5531386E7]| [5.3568684E7]| [3.5978005E7]|[1.08899853E8]|[2.44976116E8]| [6.3909293E7]| [1.0419805E7]|[2.07437836E8]| [1.0371755E7]|[1.63367351E8]| [8562838.0]|[1.99488971E8]|[1.68888596E8]| [3.0989143E7]|
|{V3L7-L2RB92RV-91...|[[4.6514943E7], [...| [4.6514943E7]| [1.7940101E7]| [4.8366782E7]| [9.6398354E7]| [4.4988435E7]| [4755671.0]| [4.1300729E7]| [4.8080974E7]| [9.8809818E7]| [4.0580479E7]| [6.0197952E7]| [3.6108508E7]| [6.0774979E7]| [6.9857417E7]| [2.989732E7]| [8.807687E7]| [7.5907741E7]| [1475341.0]| [1.8722329E8]| [3.5042151E7]|
|{D5K9-P0IJ71WK-63...|[[5266566.0], [2....| [5266566.0]| [2.1788823E7]| [7.078214E7]| [3.259953E7]| [3.1456934E7]| [5.229817E7]| [1.3720146E7]| [6.7811145E7]|[2.48834049E8]| [8.0576354E7]| [3.4734944E7]| [3.6108508E7]| [4627505.0]| [9.2555836E7]| [3.1043146E7]|[1.43332429E8]|[1.97654519E8]| [2.6179662E7]|[2.40014558E8]| [8.0210553E7]|
|{R0A5-U4YQ17EA-34...|[[1.05851868E8], ...|[1.05851868E8]| [2.1788823E7]| [2.0337453E7]|[1.95087268E8]| [1140126.0]| [3.618748E7]| [4.1300729E7]|[1.42290491E8]|[1.11131951E8]|[2.16562329E8]| [6.9593553E7]| [9.5276688E7]|[1.69286014E8]| [2.4460579E7]| [3.0470233E7]|[4.14709908E8]|[2.03133688E8]| [6.2067218E7]| [4666666.0]|[1.15824384E8]|
|{Y8Z6-X5HU72BM-73...|[[1.3212552E7], [...| [1.3212552E7]|[2.48669208E8]|[1.89303844E8]|[2.79820146E8]| [8.6781205E7]| [2.0866361E7]|[3.17106559E8]| [2.1076498E7]| [5.2814565E7]|[1.08899853E8]|[2.49401537E8]| [5929983.0]| [8.417704E7]|[1.91842255E8]|[1.45858361E8]| [50832.0]|[3.39333283E8]|[3.65469144E8]|[4.31343371E8]| [2.8672437E7]|
|{K3B8-S0RJ27BU-68...|[[1.3864811E8], [...| [1.3864811E8]|[1.16186831E8]| [9.2407081E7]|[3.61064015E8]|[3.36680553E8]|[1.08717512E8]|[1.43161747E8]|[1.13805569E8]| [8.3478067E7]|[1.00900678E8]|[3.72768566E8]| [5.8824684E7]| [6.4791753E7]|[1.27298417E8]| [3.2761885E7]| [4.4263118E8]|[1.21810869E8]| [6.863823E7]|[4.04236488E8]| [4061732.0]|
|{J7Y1-G7KD78BQ-41...|[[4.6514943E7], [...| [4.6514943E7]| [6.5139105E7]| [8.9204887E7]| [3.6088539E7]| [3.3512473E7]|[1.52248302E8]| [1.5483671E8]| [1346327.0]| [9.8809818E7]| [3.9927706E7]|[3.72768566E8]| [7.2560512E7]| [7.4367966E7]| [5.920316E7]| [3.2188972E7]|[1.39070207E8]| [4.7477008E7]| [1475341.0]| [1.8722329E8]| [3.0989143E7]|
|{D7P4-Z0PP26KM-17...|[[2.8426395E7], [...| [2.8426395E7]|[1.47990947E8]| [5.7973364E7]| [7.4965969E7]| [3195665.0]| [3.8620047E7]| [6275840.0]| [6093814.0]|[1.35492556E8]|[1.56894903E8]| [4.6921689E7]|[1.69369566E8]| [4.3398079E7]| [4.005616E7]| [1.642381E8]| [4.0120676E7]|[1.71208476E8]| [1.2279514E7]| [3.4338428E8]| [1.3616303E7]|
|{P6J4-Y0XJ63II-57...|[[9.5709306E7], [...| [9.5709306E7]| [5.1161408E7]| [4.8366782E7]|[1.56209838E8]| [4.7043974E7]| [4755671.0]| [8.8000581E7]| [2.5400951E8]| [3809694.0]| [2.3929356E7]| [6.9048794E7]| [7.0182772E7]| [5.8999453E7]| [5.920316E7]| [5.7443667E7]|[1.63367351E8]| [7.0428572E7]|[1.14950897E8]| [3.912324E7]| [1.0076825E8]|
|{K7Y5-V5IP47OA-83...|[[1.30702124E8], ...|[1.30702124E8]|[1.31581719E8]| [1914706.0]|[1.01382702E8]|[7.65567938E8]|[1.20816493E8]|[3.12875902E8]| [1.3353574E8]| [3.5978005E7]|[1.75913681E8]| [2.5884102E7]| [2.7457289E7]| [2.8029566E7]|[2.19481998E8]| [3.5053537E7]|[1.57293065E8]|[1.07832953E8]| [3.0033761E7]| [1.7864483E7]| [1.5933009E7]|
|{R9V2-W5OA43XS-14...|[[5.953221E7], [3...| [5.953221E7]| [3.3334989E7]| [2.0337453E7]| [2693788.0]| [6.1773742E7]| [3.2965342E7]| [8.0556275E7]| [1346327.0]| [7.896364E7]|[1.60568104E8]| [6.9593553E7]|[1.02739037E8]| [1.0419805E7]|[1.42893998E8]| [3.1616059E7]| [7937182.0]| [6.3439614E7]| [6.863823E7]| [1.7864483E7]|[1.42751795E8]|
|{X4R4-F1BP75UA-02...|[[3.15474607E8], ...|[3.15474607E8]| [113682.0]| [4.8366782E7]| [7.347063E7]|[2.76046937E8]| [4755671.0]| [9.7478903E7]|[1.47037978E8]|[1.78478191E8]| [7931006.0]|[1.04452162E8]| [7.0182772E7]| [1.3051544E8]|[3.18768417E8]| [2.0901836E8]| [1.8036513E8]|[2.78977338E8]|[1.92538897E8]| [3.912324E7]|[1.15824384E8]|
|{N4L7-S2MN81EJ-50...|[[1.10923149E8], ...|[1.10923149E8]|[2.09167648E8]| [3.9550617E7]| [8.0448648E7]| [7.3525113E8]|[1.52248302E8]| [6.0419998E7]| [7.2558632E7]| [1.4627018E7]| [7.2577179E7]|[6.67637496E8]|[2.17179658E8]| [4.9190379E7]| [7475093.0]|[1.87774056E8]|[1.37258143E8]| [1.2057317E7]| [8.9867514E7]| [4.4260117E7]| [3.0989143E7]|
+--------------------+--------------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+
only showing top 20 rows
udf_getNumber = udf(lambda x: int(x[0]), LongType())
for col_num in range(20):
id_hash = id_hash.withColumn('hash_'+str(col_num), udf_getNumber('hash_'+str(col_num)))
id_hash.show()
Output
+--------------------+--------------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
| id| hashes| hash_0| hash_1| hash_2| hash_3| hash_4| hash_5| hash_6| hash_7| hash_8| hash_9| hash_10| hash_11| hash_12| hash_13| hash_14| hash_15| hash_16| hash_17| hash_18| hash_19|
+--------------------+--------------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
|{R3I7-S4TX96FG-82...|[[8.776332E7], [1...| 87763320| 17940101| 64377752| 45060227| 94146089| 54730737| 2045183| 35318959| 97305009|160568104|126579267| 41193117| 51198766| 47158998| 7507190| 82002584| 50971487| 19229588| 28138237| 55599848|
|{R0R9-E4GL59IK-29...|[[195285.0], [1.2...| 195285|126315806|226939755| 43066557| 29401395|149026164|465667429|136802781| 62127080| 76250380|148161613| 51362335| 72592440|281864322|320969010|163367351| 23015655| 75967366|123447019| 25487580|
|{G2B2-A8XY58CP-28...|[[2.90624351E8], ...|290624351|104640665|120436410| 48549236|115042474|114372217|170742330|385458700|572021968| 44253680|320208273| 41193117| 85952566| 24460579|145858361|525808011|132294306| 26179662|105112325|240326464|
|{A3A9-F4TH89AA-83...|[[8.2692039E7], [...| 82692039|339218494|210928785|198576277| 31456934| 39409618|273620356| 21076498|117151187|140896553| 29764764| 90192079| 4627505|200334998| 9798842| 50832|148256912|171309613|152766716| 28672437|
|{E8B7-C8FZ88UF-29...|[[2.62393241E8], ...|262393241| 39615242|105215857| 11665476|156835244|124038631|229117145|273739681|259651373|180239655|157012455| 12203462|378507397|273371579| 74677580| 17048611| 8562838|107621761|121234205|488462473|
|{X8T7-A6BT54FP-72...|[[3.66359397E8], ...|366359397| 17940101| 8319094| 41571218|147414821|339196160| 65667663| 82053606|127968511|103921106| 51347110| 17288071| 60774979| 17357741| 97067710| 83227663| 50971487| 93342551| 72868565|197474768|
|{H5J6-G2RS59KI-83...|[[1.3212552E7], [...| 13212552| 55010130|105215857|149231820| 80614588| 64397151| 25395109| 53568684| 81973258| 31928531| 64623373| 5929983|149900727|198945093|434638399| 23122897| 17536486| 47408946| 43548868| 16801160|
|{D9T8-M1HJ89XP-63...|[[1.88348622E8], ...|188348622|145559416| 5116900| 29110521|262515436| 70841427| 45531386| 53568684| 35978005|108899853|244976116| 63909293| 10419805|207437836| 10371755|163367351| 8562838|199488971|168888596| 30989143|
|{V3L7-L2RB92RV-91...|[[4.6514943E7], [...| 46514943| 17940101| 48366782| 96398354| 44988435| 4755671| 41300729| 48080974| 98809818| 40580479| 60197952| 36108508| 60774979| 69857417| 29897320| 88076870| 75907741| 1475341|187223290| 35042151|
|{D5K9-P0IJ71WK-63...|[[5266566.0], [2....| 5266566| 21788823| 70782140| 32599530| 31456934| 52298170| 13720146| 67811145|248834049| 80576354| 34734944| 36108508| 4627505| 92555836| 31043146|143332429|197654519| 26179662|240014558| 80210553|
|{R0A5-U4YQ17EA-34...|[[1.05851868E8], ...|105851868| 21788823| 20337453|195087268| 1140126| 36187480| 41300729|142290491|111131951|216562329| 69593553| 95276688|169286014| 24460579| 30470233|414709908|203133688| 62067218| 4666666|115824384|
|{Y8Z6-X5HU72BM-73...|[[1.3212552E7], [...| 13212552|248669208|189303844|279820146| 86781205| 20866361|317106559| 21076498| 52814565|108899853|249401537| 5929983| 84177040|191842255|145858361| 50832|339333283|365469144|431343371| 28672437|
|{K3B8-S0RJ27BU-68...|[[1.3864811E8], [...|138648110|116186831| 92407081|361064015|336680553|108717512|143161747|113805569| 83478067|100900678|372768566| 58824684| 64791753|127298417| 32761885|442631180|121810869| 68638230|404236488| 4061732|
|{J7Y1-G7KD78BQ-41...|[[4.6514943E7], [...| 46514943| 65139105| 89204887| 36088539| 33512473|152248302|154836710| 1346327| 98809818| 39927706|372768566| 72560512| 74367966| 59203160| 32188972|139070207| 47477008| 1475341|187223290| 30989143|
|{D7P4-Z0PP26KM-17...|[[2.8426395E7], [...| 28426395|147990947| 57973364| 74965969| 3195665| 38620047| 6275840| 6093814|135492556|156894903| 46921689|169369566| 43398079| 40056160|164238100| 40120676|171208476| 12279514|343384280| 13616303|
|{P6J4-Y0XJ63II-57...|[[9.5709306E7], [...| 95709306| 51161408| 48366782|156209838| 47043974| 4755671| 88000581|254009510| 3809694| 23929356| 69048794| 70182772| 58999453| 59203160| 57443667|163367351| 70428572|114950897| 39123240|100768250|
|{K7Y5-V5IP47OA-83...|[[1.30702124E8], ...|130702124|131581719| 1914706|101382702|765567938|120816493|312875902|133535740| 35978005|175913681| 25884102| 27457289| 28029566|219481998| 35053537|157293065|107832953| 30033761| 17864483| 15933009|
|{R9V2-W5OA43XS-14...|[[5.953221E7], [3...| 59532210| 33334989| 20337453| 2693788| 61773742| 32965342| 80556275| 1346327| 78963640|160568104| 69593553|102739037| 10419805|142893998| 31616059| 7937182| 63439614| 68638230| 17864483|142751795|
|{X4R4-F1BP75UA-02...|[[3.15474607E8], ...|315474607| 113682| 48366782| 73470630|276046937| 4755671| 97478903|147037978|178478191| 7931006|104452162| 70182772|130515440|318768417|209018360|180365130|278977338|192538897| 39123240|115824384|
|{N4L7-S2MN81EJ-50...|[[1.10923149E8], ...|110923149|209167648| 39550617| 80448648|735251130|152248302| 60419998| 72558632| 14627018| 72577179|667637496|217179658| 49190379| 7475093|187774056|137258143| 12057317| 89867514| 44260117| 30989143|
+--------------------+--------------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
only showing top 20 rows
hash_cols = ['hash_'+str(i) for i in range(20)]
assembler = VectorAssembler(inputCols=hash_cols, outputCol="features")
id_hash = assembler.transform(id_hash)
id_hash.show()
Output
+--------------------+--------------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------------------+
| id| hashes| hash_0| hash_1| hash_2| hash_3| hash_4| hash_5| hash_6| hash_7| hash_8| hash_9| hash_10| hash_11| hash_12| hash_13| hash_14| hash_15| hash_16| hash_17| hash_18| hash_19| features|
+--------------------+--------------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------------------+
|{R3I7-S4TX96FG-82...|[[8.776332E7], [1...| 87763320| 17940101| 64377752| 45060227| 94146089| 54730737| 2045183| 35318959| 97305009|160568104|126579267| 41193117| 51198766| 47158998| 7507190| 82002584| 50971487| 19229588| 28138237| 55599848|[8.776332E7,1.794...|
|{R0R9-E4GL59IK-29...|[[195285.0], [1.2...| 195285|126315806|226939755| 43066557| 29401395|149026164|465667429|136802781| 62127080| 76250380|148161613| 51362335| 72592440|281864322|320969010|163367351| 23015655| 75967366|123447019| 25487580|[195285.0,1.26315...|
|{G2B2-A8XY58CP-28...|[[2.90624351E8], ...|290624351|104640665|120436410| 48549236|115042474|114372217|170742330|385458700|572021968| 44253680|320208273| 41193117| 85952566| 24460579|145858361|525808011|132294306| 26179662|105112325|240326464|[2.90624351E8,1.0...|
|{A3A9-F4TH89AA-83...|[[8.2692039E7], [...| 82692039|339218494|210928785|198576277| 31456934| 39409618|273620356| 21076498|117151187|140896553| 29764764| 90192079| 4627505|200334998| 9798842| 50832|148256912|171309613|152766716| 28672437|[8.2692039E7,3.39...|
|{E8B7-C8FZ88UF-29...|[[2.62393241E8], ...|262393241| 39615242|105215857| 11665476|156835244|124038631|229117145|273739681|259651373|180239655|157012455| 12203462|378507397|273371579| 74677580| 17048611| 8562838|107621761|121234205|488462473|[2.62393241E8,3.9...|
|{X8T7-A6BT54FP-72...|[[3.66359397E8], ...|366359397| 17940101| 8319094| 41571218|147414821|339196160| 65667663| 82053606|127968511|103921106| 51347110| 17288071| 60774979| 17357741| 97067710| 83227663| 50971487| 93342551| 72868565|197474768|[3.66359397E8,1.7...|
|{H5J6-G2RS59KI-83...|[[1.3212552E7], [...| 13212552| 55010130|105215857|149231820| 80614588| 64397151| 25395109| 53568684| 81973258| 31928531| 64623373| 5929983|149900727|198945093|434638399| 23122897| 17536486| 47408946| 43548868| 16801160|[1.3212552E7,5.50...|
|{D9T8-M1HJ89XP-63...|[[1.88348622E8], ...|188348622|145559416| 5116900| 29110521|262515436| 70841427| 45531386| 53568684| 35978005|108899853|244976116| 63909293| 10419805|207437836| 10371755|163367351| 8562838|199488971|168888596| 30989143|[1.88348622E8,1.4...|
|{V3L7-L2RB92RV-91...|[[4.6514943E7], [...| 46514943| 17940101| 48366782| 96398354| 44988435| 4755671| 41300729| 48080974| 98809818| 40580479| 60197952| 36108508| 60774979| 69857417| 29897320| 88076870| 75907741| 1475341|187223290| 35042151|[4.6514943E7,1.79...|
|{D5K9-P0IJ71WK-63...|[[5266566.0], [2....| 5266566| 21788823| 70782140| 32599530| 31456934| 52298170| 13720146| 67811145|248834049| 80576354| 34734944| 36108508| 4627505| 92555836| 31043146|143332429|197654519| 26179662|240014558| 80210553|[5266566.0,2.1788...|
|{R0A5-U4YQ17EA-34...|[[1.05851868E8], ...|105851868| 21788823| 20337453|195087268| 1140126| 36187480| 41300729|142290491|111131951|216562329| 69593553| 95276688|169286014| 24460579| 30470233|414709908|203133688| 62067218| 4666666|115824384|[1.05851868E8,2.1...|
|{Y8Z6-X5HU72BM-73...|[[1.3212552E7], [...| 13212552|248669208|189303844|279820146| 86781205| 20866361|317106559| 21076498| 52814565|108899853|249401537| 5929983| 84177040|191842255|145858361| 50832|339333283|365469144|431343371| 28672437|[1.3212552E7,2.48...|
|{K3B8-S0RJ27BU-68...|[[1.3864811E8], [...|138648110|116186831| 92407081|361064015|336680553|108717512|143161747|113805569| 83478067|100900678|372768566| 58824684| 64791753|127298417| 32761885|442631180|121810869| 68638230|404236488| 4061732|[1.3864811E8,1.16...|
|{J7Y1-G7KD78BQ-41...|[[4.6514943E7], [...| 46514943| 65139105| 89204887| 36088539| 33512473|152248302|154836710| 1346327| 98809818| 39927706|372768566| 72560512| 74367966| 59203160| 32188972|139070207| 47477008| 1475341|187223290| 30989143|[4.6514943E7,6.51...|
|{D7P4-Z0PP26KM-17...|[[2.8426395E7], [...| 28426395|147990947| 57973364| 74965969| 3195665| 38620047| 6275840| 6093814|135492556|156894903| 46921689|169369566| 43398079| 40056160|164238100| 40120676|171208476| 12279514|343384280| 13616303|[2.8426395E7,1.47...|
|{P6J4-Y0XJ63II-57...|[[9.5709306E7], [...| 95709306| 51161408| 48366782|156209838| 47043974| 4755671| 88000581|254009510| 3809694| 23929356| 69048794| 70182772| 58999453| 59203160| 57443667|163367351| 70428572|114950897| 39123240|100768250|[9.5709306E7,5.11...|
|{K7Y5-V5IP47OA-83...|[[1.30702124E8], ...|130702124|131581719| 1914706|101382702|765567938|120816493|312875902|133535740| 35978005|175913681| 25884102| 27457289| 28029566|219481998| 35053537|157293065|107832953| 30033761| 17864483| 15933009|[1.30702124E8,1.3...|
|{R9V2-W5OA43XS-14...|[[5.953221E7], [3...| 59532210| 33334989| 20337453| 2693788| 61773742| 32965342| 80556275| 1346327| 78963640|160568104| 69593553|102739037| 10419805|142893998| 31616059| 7937182| 63439614| 68638230| 17864483|142751795|[5.953221E7,3.333...|
|{X4R4-F1BP75UA-02...|[[3.15474607E8], ...|315474607| 113682| 48366782| 73470630|276046937| 4755671| 97478903|147037978|178478191| 7931006|104452162| 70182772|130515440|318768417|209018360|180365130|278977338|192538897| 39123240|115824384|[3.15474607E8,113...|
|{N4L7-S2MN81EJ-50...|[[1.10923149E8], ...|110923149|209167648| 39550617| 80448648|735251130|152248302| 60419998| 72558632| 14627018| 72577179|667637496|217179658| 49190379| 7475093|187774056|137258143| 12057317| 89867514| 44260117| 30989143|[1.10923149E8,2.0...|
+--------------------+--------------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------------------+
only showing top 20 rows
Now the column features has the DenseVector format we want.
Rescale data
At the same time, we can find that the values in the column features are very large, which is not suitable for subsequent steps such as model training. Therefore, we need to use Scaler to scale them down to a suitable size.
scaler = StandardScaler(inputCol="features", outputCol="scaledFeatures",
withStd=True, withMean=False)
# Compute summary statistics by fitting the StandardScaler
scalerModel = scaler.fit(id_hash)
# Normalize each feature to have unit standard deviation.
id_hash_scaled = scalerModel.transform(id_hash)
id_hash_scaled.show()
Output
+--------------------+--------------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------------------+--------------------+
| id| hashes| hash_0| hash_1| hash_2| hash_3| hash_4| hash_5| hash_6| hash_7| hash_8| hash_9| hash_10| hash_11| hash_12| hash_13| hash_14| hash_15| hash_16| hash_17| hash_18| hash_19| features| scaledFeatures|
+--------------------+--------------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------------------+--------------------+
|{R3I7-S4TX96FG-82...|[[8.776332E7], [1...| 87763320| 17940101| 64377752| 45060227| 94146089| 54730737| 2045183| 35318959| 97305009|160568104|126579267| 41193117| 51198766| 47158998| 7507190| 82002584| 50971487| 19229588| 28138237| 55599848|[8.776332E7,1.794...|[0.31075800760838...|
|{R0R9-E4GL59IK-29...|[[195285.0], [1.2...| 195285|126315806|226939755| 43066557| 29401395|149026164|465667429|136802781| 62127080| 76250380|148161613| 51362335| 72592440|281864322|320969010|163367351| 23015655| 75967366|123447019| 25487580|[195285.0,1.26315...|[6.91477686985905...|
|{G2B2-A8XY58CP-28...|[[2.90624351E8], ...|290624351|104640665|120436410| 48549236|115042474|114372217|170742330|385458700|572021968| 44253680|320208273| 41193117| 85952566| 24460579|145858361|525808011|132294306| 26179662|105112325|240326464|[2.90624351E8,1.0...|[1.02906139238169...|
|{A3A9-F4TH89AA-83...|[[8.2692039E7], [...| 82692039|339218494|210928785|198576277| 31456934| 39409618|273620356| 21076498|117151187|140896553| 29764764| 90192079| 4627505|200334998| 9798842| 50832|148256912|171309613|152766716| 28672437|[8.2692039E7,3.39...|[0.29280128970411...|
|{E8B7-C8FZ88UF-29...|[[2.62393241E8], ...|262393241| 39615242|105215857| 11665476|156835244|124038631|229117145|273739681|259651373|180239655|157012455| 12203462|378507397|273371579| 74677580| 17048611| 8562838|107621761|121234205|488462473|[2.62393241E8,3.9...|[0.92909886252100...|
|{X8T7-A6BT54FP-72...|[[3.66359397E8], ...|366359397| 17940101| 8319094| 41571218|147414821|339196160| 65667663| 82053606|127968511|103921106| 51347110| 17288071| 60774979| 17357741| 97067710| 83227663| 50971487| 93342551| 72868565|197474768|[3.66359397E8,1.7...|[1.2972289138598,...|
|{H5J6-G2RS59KI-83...|[[1.3212552E7], [...| 13212552| 55010130|105215857|149231820| 80614588| 64397151| 25395109| 53568684| 81973258| 31928531| 64623373| 5929983|149900727|198945093|434638399| 23122897| 17536486| 47408946| 43548868| 16801160|[1.3212552E7,5.50...|[0.04678385383486...|
|{D9T8-M1HJ89XP-63...|[[1.88348622E8], ...|188348622|145559416| 5116900| 29110521|262515436| 70841427| 45531386| 53568684| 35978005|108899853|244976116| 63909293| 10419805|207437836| 10371755|163367351| 8562838|199488971|168888596| 30989143|[1.88348622E8,1.4...|[0.66691691367766...|
|{V3L7-L2RB92RV-91...|[[4.6514943E7], [...| 46514943| 17940101| 48366782| 96398354| 44988435| 4755671| 41300729| 48080974| 98809818| 40580479| 60197952| 36108508| 60774979| 69857417| 29897320| 88076870| 75907741| 1475341|187223290| 35042151|[4.6514943E7,1.79...|[0.16470310159982...|
|{D5K9-P0IJ71WK-63...|[[5266566.0], [2....| 5266566| 21788823| 70782140| 32599530| 31456934| 52298170| 13720146| 67811145|248834049| 80576354| 34734944| 36108508| 4627505| 92555836| 31043146|143332429|197654519| 26179662|240014558| 80210553|[5266566.0,2.1788...|[0.01864819559125...|
|{R0A5-U4YQ17EA-34...|[[1.05851868E8], ...|105851868| 21788823| 20337453|195087268| 1140126| 36187480| 41300729|142290491|111131951|216562329| 69593553| 95276688|169286014| 24460579| 30470233|414709908|203133688| 62067218| 4666666|115824384|[1.05851868E8,2.1...|[0.37480710166053...|
|{Y8Z6-X5HU72BM-73...|[[1.3212552E7], [...| 13212552|248669208|189303844|279820146| 86781205| 20866361|317106559| 21076498| 52814565|108899853|249401537| 5929983| 84177040|191842255|145858361| 50832|339333283|365469144|431343371| 28672437|[1.3212552E7,2.48...|[0.04678385383486...|
|{K3B8-S0RJ27BU-68...|[[1.3864811E8], [...|138648110|116186831| 92407081|361064015|336680553|108717512|143161747|113805569| 83478067|100900678|372768566| 58824684| 64791753|127298417| 32761885|442631180|121810869| 68638230|404236488| 4061732|[1.3864811E8,1.16...|[0.49093414449531...|
|{J7Y1-G7KD78BQ-41...|[[4.6514943E7], [...| 46514943| 65139105| 89204887| 36088539| 33512473|152248302|154836710| 1346327| 98809818| 39927706|372768566| 72560512| 74367966| 59203160| 32188972|139070207| 47477008| 1475341|187223290| 30989143|[4.6514943E7,6.51...|[0.16470310159982...|
|{D7P4-Z0PP26KM-17...|[[2.8426395E7], [...| 28426395|147990947| 57973364| 74965969| 3195665| 38620047| 6275840| 6093814|135492556|156894903| 46921689|169369566| 43398079| 40056160|164238100| 40120676|171208476| 12279514|343384280| 13616303|[2.8426395E7,1.47...|[0.10065400754767...|
|{P6J4-Y0XJ63II-57...|[[9.5709306E7], [...| 95709306| 51161408| 48366782|156209838| 47043974| 4755671| 88000581|254009510| 3809694| 23929356| 69048794| 70182772| 58999453| 59203160| 57443667|163367351| 70428572|114950897| 39123240|100768250|[9.5709306E7,5.11...|[0.33889366585199...|
|{K7Y5-V5IP47OA-83...|[[1.30702124E8], ...|130702124|131581719| 1914706|101382702|765567938|120816493|312875902|133535740| 35978005|175913681| 25884102| 27457289| 28029566|219481998| 35053537|157293065|107832953| 30033761| 17864483| 15933009|[1.30702124E8,1.3...|[0.46279848625170...|
|{R9V2-W5OA43XS-14...|[[5.953221E7], [3...| 59532210| 33334989| 20337453| 2693788| 61773742| 32965342| 80556275| 1346327| 78963640|160568104| 69593553|102739037| 10419805|142893998| 31616059| 7937182| 63439614| 68638230| 17864483|142751795|[5.953221E7,3.333...|[0.21079547774769...|
|{X4R4-F1BP75UA-02...|[[3.15474607E8], ...|315474607| 113682| 48366782| 73470630|276046937| 4755671| 97478903|147037978|178478191| 7931006|104452162| 70182772|130515440|318768417|209018360|180365130|278977338|192538897| 39123240|115824384|[3.15474607E8,113...|[1.11705277697287...|
|{N4L7-S2MN81EJ-50...|[[1.10923149E8], ...|110923149|209167648| 39550617| 80448648|735251130|152248302| 60419998| 72558632| 14627018| 72577179|667637496|217179658| 49190379| 7475093|187774056|137258143| 12057317| 89867514| 44260117| 30989143|[1.10923149E8,2.0...|[0.39276381956480...|
+--------------------+--------------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------------------+--------------------+
only showing top 20 rows
This step may take several minutes.
id_hash_scaled = id_hash_scaled.select('id','scaledFeatures')
id_hash_scaled.show()
Output
+--------------------+--------------------+
| id| scaledFeatures|
+--------------------+--------------------+
|{R3I7-S4TX96FG-82...|[0.31075800760838...|
|{R0R9-E4GL59IK-29...|[6.91477686985905...|
|{G2B2-A8XY58CP-28...|[1.02906139238169...|
|{A3A9-F4TH89AA-83...|[0.29280128970411...|
|{E8B7-C8FZ88UF-29...|[0.92909886252100...|
|{X8T7-A6BT54FP-72...|[1.2972289138598,...|
|{H5J6-G2RS59KI-83...|[0.04678385383486...|
|{D9T8-M1HJ89XP-63...|[0.66691691367766...|
|{V3L7-L2RB92RV-91...|[0.16470310159982...|
|{D5K9-P0IJ71WK-63...|[0.01864819559125...|
|{R0A5-U4YQ17EA-34...|[0.37480710166053...|
|{Y8Z6-X5HU72BM-73...|[0.04678385383486...|
|{K3B8-S0RJ27BU-68...|[0.49093414449531...|
|{J7Y1-G7KD78BQ-41...|[0.16470310159982...|
|{D7P4-Z0PP26KM-17...|[0.10065400754767...|
|{P6J4-Y0XJ63II-57...|[0.33889366585199...|
|{K7Y5-V5IP47OA-83...|[0.46279848625170...|
|{R9V2-W5OA43XS-14...|[0.21079547774769...|
|{X4R4-F1BP75UA-02...|[1.11705277697287...|
|{N4L7-S2MN81EJ-50...|[0.39276381956480...|
+--------------------+--------------------+
only showing top 20 rows
Save & Retrieve data
The amount of data was simply too large to be handled by Colab during the modeling process and caused a disconnection from its Java backend server. Therefore, we only extract a portion of the data for demonstration. Here, I randomly sampled only 0.1% of the original data.
id_hash_sub = id_hash_scaled.sample(withReplacement=False, fraction=0.001, seed=42)
id_hash_sub_split = id_hash_sub.withColumn("scaledHash", vector_to_array("scaledFeatures")).select(['id'] + [col("scaledHash")[i] for i in range(20)])
id_hash_sub_split.show()
Output
+--------------------+--------------------+-------------------+--------------------+-------------------+--------------------+--------------------+-------------------+-------------------+--------------------+--------------------+-------------------+--------------------+--------------------+--------------------+-------------------+--------------------+-------------------+--------------------+--------------------+--------------------+
| id| scaledHash[0]| scaledHash[1]| scaledHash[2]| scaledHash[3]| scaledHash[4]| scaledHash[5]| scaledHash[6]| scaledHash[7]| scaledHash[8]| scaledHash[9]| scaledHash[10]| scaledHash[11]| scaledHash[12]| scaledHash[13]| scaledHash[14]| scaledHash[15]| scaledHash[16]| scaledHash[17]| scaledHash[18]| scaledHash[19]|
+--------------------+--------------------+-------------------+--------------------+-------------------+--------------------+--------------------+-------------------+-------------------+--------------------+--------------------+-------------------+--------------------+--------------------+--------------------+-------------------+--------------------+-------------------+--------------------+--------------------+--------------------+
|{K2S4-F1DY33JO-15...| 0.29280128970411595| 0.3091033263397524| 2.476281640149856|0.12861855363131602| 0.2518061180151361| 0.5796193263919759| 3.0776898705378564| 2.177209668935742| 0.6748656539993456| 0.2110169547910796| 0.673424066914295| 5.619513628670911| 1.177556880743654| 2.331892220940217| 0.6603510093508081| 1.7811379014403648| 2.232125442478518| 1.0194858268210645| 1.2534815642644868|0.036371409483113765|
|{D1I9-G5HX22BQ-41...|0.024633768226013954| 0.4878114669475884| 2.2940673130343248| 8.753337779706603| 2.143880850303592| 0.16856546777515793| 2.3061401268552855| 1.506277442688671| 2.813599251203017| 1.716103268825347|0.28752009454781946| 4.639510658817771| 3.3263900582637285| 1.1344919733439336| 3.161960857414897| 0.14624060997894936| 3.2281803139849057| 2.1853046676413554| 0.7301844611439626| 2.3284683037114586|
|{I9E9-Q3OT41AO-31...| 0.368821529025777| 2.0278705582652594| 1.740669600417486| 1.2875654000968493| 1.3308415278261754| 0.02247731958342665| 0.8790143676268818|0.44944703745559733| 0.30799466065031955| 0.5543026192616594| 1.9935958416189692| 0.8092609117203796| 0.3960105278308719| 0.39284644224423737| 0.2514173549141416| 0.24072594895414837| 0.786553328360483| 0.10594780803748093| 0.12258914456450333| 0.32261815583704795|
|{S7Z6-V6GC84AJ-34...| 6.375029393281213| 5.300359716339008| 0.4256292299089153| 3.0779641787054346| 2.376968625846961| 5.457482574221892|0.04448809445873216| 1.9842605794427406| 6.0733492705399925| 6.01806834603609| 4.028590989786734| 0.9145188180748663| 4.414433387405006| 2.0352340085003386| 5.976271936486997| 5.947038568675222| 2.543187373343792| 5.714168897515404| 0.6037371409555599| 4.00748258766664|
|{T5S5-V5TC63CJ-15...| 0.3388936658519919|0.36601829572652156| 0.9009350880476696|0.09011486551094054| 0.2257600876666489| 1.0819782776286258| 0.8518278896657661|0.05733830764840028| 0.5022728966838492| 0.3048177593351857| 1.4817236690544147| 0.08242913749621385| 0.5356322755638796| 0.42035878392087134| 0.8874882439177393|0.028886254236132933|0.26545000731622687| 0.23942811693198968| 0.03829093110556815| 0.5041896108235284|
|{O0Q0-G0QO68BH-68...| 1.4031770163552462| 0.5959418033380846| 4.129720046730127| 0.9063834315029069| 0.2518061180151361| 0.8855818575822001| 1.9196773763784638| 0.2502873971414017| 0.7998503797803153|0.002787595423446...| 0.3120126942966307| 0.17771347359345907| 1.8612002930473002|0.002883806937687...| 0.3503168850896909| 2.1533017452257455| 1.2360705709721302| 0.525164762290429| 1.055056672798426| 0.6387669533362803|
|{T6J3-O6HK21DK-63...| 1.129023922242387| 2.894085591643298|0.015564634988835327| 0.7446695450906622| 0.5109399239069924| 0.6526634004878414| 0.9234079927146928| 0.4892789655184364| 0.7305568695277551| 0.1728741031832374| 0.9293601531965723| 1.400914068818334| 0.6190698898116233| 0.1512129131576268| 0.2540844616610355| 0.21207942007111574| 0.6688154793457498| 0.201876061793146| 0.7723335678734302| 0.12395463952213706|
|{W9R3-C0TQ20IV-04...| 0.19283875984342688|0.06551698389761866| 1.3890915374458344|0.14401949431502212|0.058488935703786944| 0.11825719592046345| 1.3098707431915144|0.05112775682720604|0.003616030191778...| 0.1141035012959747| 0.2185364656196012| 0.5361425755769211| 0.27085410645925634| 0.45863852343427475|0.13918229100412294| 0.6759603626210096|0.22329839113730143| 0.9839818875907766| 0.321014036030564| 0.9955048684008074|
|{Q0P2-K8RK31CY-66...| 0.22875219565196894|0.14405802056248707| 0.5753876432353795| 0.4982491485069237| 0.1763976199812685| 0.6847106538185695| 1.8838873248540005| 0.7681024619583103| 0.3976916129891054| 0.3079304082861845| 1.1243119823228696| 0.5560897160914042| 0.1729511658501205| 1.7828330815769586|0.04294986757546754| 0.6759603626210096| 1.1437670298078815| 0.12472383560690277|0.014298407346348845| 0.5447787283888231|
|{N1I1-L9QL88UM-07...| 1.303214486494557| 1.0535251884967243| 0.11968710762628161| 0.9217843721866129| 0.6946652317649812|0.008691084776664991| 2.9789232895278137| 3.433199714483011| 1.8094843708568| 0.3223328606636074| 0.6694243810281758| 3.32102572749565| 1.667071583789333| 0.17872525483426077| 0.6656852228445959| 2.3937879688267936| 1.1310498758826393| 1.899568022282916| 1.8370843570847268| 1.655581591147699|
|{P1U7-G4IM71WT-84...| 0.19283875984342688| 0.44455933239139| 0.14571772578564318|0.19792412311910368| 0.30116858570051647| 1.4702963340042725| 2.0728169133107377| 1.9045967233170622| 0.6680645066372703| 0.3635883612224484| 0.4744725519018785| 0.6513740521886495| 1.177556880743654| 2.359404562616851|0.13918229100412294| 0.10904832876981561|0.10156038771936927| 1.5554551783076553| 0.321014036030564| 0.16454375708743177|
|{X6E3-Z7LK32OI-96...| 0.07072614437388991|0.10080588600628863| 0.9073603836773748| 1.0834982585988577|0.058488935703786944| 0.15030444925119152| 0.4567615656255967| 0.2564979479625959|0.030820619640079327| 0.2491598063989218|0.34450466581768024| 0.6087186692686476| 0.34150356630591244| 1.0136752088006284|0.24608314142035387| 0.00878547767920146| 2.022084052299536|0.007971438373260023| 0.2490364647529061| 0.6387669533362803|
|{T9L8-P4FF61BO-55...| 1.245150965077167| 0.6528567727248538| 0.7251460565618438| 0.1709718087170629| 1.8422468581681217| 0.15030444925119152| 2.736996502419918| 0.1307916129528843| 0.37728817090287964| 0.4811295649969738| 0.2835204086617003| 2.3709434684142336| 0.5500976019252717| 0.3653341005676034| 1.0906215177626255| 2.1962715385502944|0.10156038771936927| 0.963157744112799| 0.321014036030564| 0.6387669533362803|
|{G7Q1-O9SX68CM-70...| 0.2048099051129409|0.26585119178355393| 1.3890915374458344| 2.4503618135277536| 0.4355314258731248| 0.5796193263919759| 1.1123375811714298|0.29632987602543504| 0.5022728966838492| 0.4605018147175533| 1.1528042679577999| 0.5560897160914042| 0.6480005425344075| 0.13446796931776228| 0.2514173549141416| 0.12337159321133193| 0.9798774102110238| 0.5231166463818732| 0.03829093110556815|0.036371409483113765|
|{V4Y4-D2OI92EQ-25...| 0.59688224699076|0.05185414906704792| 0.11968710762628161|0.09011486551094054| 1.4322960562085303| 1.2921609324818133| 0.8160378381413028|0.29632987602543504| 0.40449276035118065| 0.519272416604816| 4.682430106093844| 0.1023762780106969| 0.6190698898116233| 2.0902586918536064| 0.8688184966894822| 0.18343289118808312|0.42933962691308447| 2.8547543280224548| 1.2294890405052674| 1.4804151310696525|
|{K8U6-M2IB80FI-83...| 0.49691971713007094| 1.648828209771488| 1.5844858914613165|0.14401949431502212| 0.9469367562150711| 0.09999617739649704| 0.6456911540823338| 0.6087747497069538| 0.14220305069689843| 0.5543026192616594| 0.3525040375899186|0.029800184318970472| 0.10397887796376892| 1.6177590315171548| 0.8954895641584208| 0.2035336677450146| 1.5638498101658456| 0.46883667958216346| 1.2956306709939545| 2.503634763789505|
|{B5X0-S1HT16TF-47...| 6.375029393281213| 5.300359716339008| 0.4256292299089153| 3.0779641787054346| 2.376968625846961| 5.457482574221892|0.04448809445873216| 1.9842605794427406| 6.0733492705399925| 6.01806834603609| 4.028590989786734| 0.9145188180748663| 4.414433387405006| 2.0352340085003386| 5.976271936486997| 5.947038568675222| 2.543187373343792| 5.714168897515404| 0.6037371409555599| 4.00748258766664|
|{W3F3-H9CT13FV-87...| 0.560968811182218| 0.1087691184538171| 0.7772072928805669|0.15557086803335682| 0.10098912194740091| 0.451792196724211| 1.2468942137059356| 0.8017238391999552|0.017218324915928816| 0.7862723778597114|0.15755220846362125| 0.6940294351086513|0.062260070839897076| 0.9036258420940925|0.03494854733478592| 0.9307698506635739| 1.2193532626436892| 1.3260466156574815| 0.7120278781737144| 0.14745169575900136|
|{V1G2-M6NB39HJ-20...| 0.368821529025777| 2.801881720147859| 0.9529963243663927| 1.071946884880523| 1.2979332160359218| 0.04073833810739306|0.08027814598319546| 2.091335261988869| 0.15580534542104893|0.002787595423446...| 1.2827721540419983| 1.9072564600762847| 1.8612002930473002| 1.0244426066373977| 2.0180567837990835| 1.7668146369988484| 1.9632151277921694| 0.5231166463818732| 0.03829093110556815| 1.463323069741222|
|{I0L5-D2RB95MK-64...| 0.22276662301721195| 0.587978570890556| 0.4452345524385717| 1.4107755983887185| 0.1763976199812685| 0.31465361596688923|0.32220493309109105| 0.2564979479625959| 0.48058741796581594| 0.8213025805165548| 0.9293601531965723| 0.3455739033824307| 0.5645629282866638|0.013651204774456999|0.24608314142035387| 0.16056387442046569| 0.3116017778983513| 0.25820414450141155| 0.321014036030564| 0.12395463952213706|
+--------------------+--------------------+-------------------+--------------------+-------------------+--------------------+--------------------+-------------------+-------------------+--------------------+--------------------+-------------------+--------------------+--------------------+--------------------+-------------------+--------------------+-------------------+--------------------+--------------------+--------------------+
only showing top 20 rows
Also, the data was stored and then retrieved to speed up the subsequent modeling process.
id_hash_sub_split.write.csv('./data/id_hash_sub_split.csv', header = True, mode = 'error')
Now, we need to retrieve the previously saved data and perform the modeling operation. If the runtime is interrupted after the previous step, you need to run the Build environment section at the beginning of the notebook.
id_hash_sub_split = spark.read.csv( './data/id_hash_sub_split.csv',inferSchema=True,header=True)
id_hash_sub_split.show()
Output
+--------------------+--------------------+--------------------+-------------------+-------------------+-------------------+-------------------+--------------------+-------------------+--------------------+--------------------+-------------------+-------------------+-------------------+--------------------+--------------------+--------------------+--------------------+-------------------+--------------------+-------------------+
| id| scaledHash[0]| scaledHash[1]| scaledHash[2]| scaledHash[3]| scaledHash[4]| scaledHash[5]| scaledHash[6]| scaledHash[7]| scaledHash[8]| scaledHash[9]| scaledHash[10]| scaledHash[11]| scaledHash[12]| scaledHash[13]| scaledHash[14]| scaledHash[15]| scaledHash[16]| scaledHash[17]| scaledHash[18]| scaledHash[19]|
+--------------------+--------------------+--------------------+-------------------+-------------------+-------------------+-------------------+--------------------+-------------------+--------------------+--------------------+-------------------+-------------------+-------------------+--------------------+--------------------+--------------------+--------------------+-------------------+--------------------+-------------------+
|{R0A8-D0KA23YU-26...| 0.05276942646961887| 1.3756525676037266| 2.6324653491060253| 2.0268265898480644| 0.7536195739037219| 0.5796193263919759| 0.25062483004216446| 3.001259056612975| 1.117831304963007| 0.6718438230361848| 0.9293601531965723| 0.5034607629141609| 1.652606257427941| 0.5028958089507732| 0.9730522403588192| 0.4440198913460626| 1.3999601905689878|0.10389969212892508| 0.7963260916326496| 1.527409243543381|
|{C4W6-P6ZE51MH-58...| 0.4909341444953139| 1.2618226288301881| 0.7251460565618438| 0.5521537773110052|0.41907726997799805| 0.2736567952017516| 2.5838569654876444| 0.722059983074277| 0.847458411314842| 0.5749303695410799| 0.7344083240702749| 0.1876870438507006| 0.5500976019252717| 0.5411755484641766| 0.7752531800077206| 0.509858701438229| 0.17714662055517702| 2.126928469024534| 0.5136029867076537| 0.9485107559270788|
|{Q7V6-N6NE83EZ-03...| 1.00511910184267| 0.853190980610789| 0.0415952531481969| 0.1825231824353976| 0.6946652317649812| 1.0637172591046593| 1.8394936997661895|0.29632987602543504| 0.1762087875072747| 0.709986674644027|0.32001206606886906| 0.3029185204624289| 0.466659987677528| 1.0686998921538964| 0.06161961480372464|0.028886254236132933| 1.4715462690015966|0.06839575289863725| 0.14074572753475162|0.02996641457467989|
|{R1W9-Z1SB61VN-25...| 0.4909341444953139|6.387820633209871E-4|0.14571772578564318| 1.7265015644602446| 0.6192567337311136| 0.6847106538185695| 0.8790143676268818| 1.432824137384187| 0.6191744384709361| 0.2316447050705001| 1.3802480686051468|0.29294495020518735| 0.3960105278308719| 1.6452713731937887| 0.368986632317948| 0.6616370981794932| 0.9210084857036572|0.33330825477909887| 0.2970215122713447|0.07696052704840847|
|{T8V6-A0YZ92MM-43...| 0.06474057173913289| 0.44455933239139| 0.3411120798011254|0.09011486551094054| 1.766838360134254|0.09552139367929229| 0.08027814598319546| 1.1203792637026682| 0.7998503797803153| 0.6893589243646066| 0.3120126942966307| 0.1023762780106969| 1.9173844265325641| 0.7828090775507872| 0.6656852228445959|0.037432006562234076| 0.9671602562857817|0.31453222720967705| 2.2760832467463157| 0.1175496446137032|
|{J5S7-D3EN76YN-47...| 6.375029393281213| 5.300359716339008| 0.4256292299089153| 3.0779641787054346| 2.376968625846961| 5.457482574221892| 0.04448809445873216| 1.9842605794427406| 6.0733492705399925| 6.01806834603609| 4.028590989786734| 0.9145188180748663| 4.414433387405006| 2.0352340085003386| 5.976271936486997| 5.947038568675222| 2.543187373343792| 5.714168897515404| 0.6037371409555599| 4.00748258766664|
|{V6X5-A6JS37ZC-78...| 0.12280409315652288| 0.10080588600628863| 0.9269657062070312|0.07471392482723446| 1.0717077219343192| 1.488557352528239| 1.2740806916670513|0.29011932520424083| 1.0349354999862965| 1.1264453933792924|0.18604449409855162| 1.1676900137038417| 1.1503033999811743| 1.9586745294735317| 0.14184939775101682| 0.9164465862220575| 0.23601554506254357| 0.6190449001375382| 0.6458862476850274|0.12395463952213706|
|{K9Q3-C6MB20EN-05...| 0.05276942646961887| 0.1656840878405863| 0.2694455209527459|0.09011486551094054|0.13389743373765453|0.02247731958342665| 0.5111345215478281| 1.1079581620602796| 0.3011935132882443| 0.07907329863913132|0.38499600911096815| 0.1023762780106969| 1.8339468122848206|0.013651204774456999| 0.7725860732608267| 0.2980190067202136| 0.786553328360483| 0.4480125361041858| 0.08044003783503574| 0.0940525883768389|
|{E2N0-S3EQ10XZ-59...| 0.05276942646961887| 0.44455933239139| 0.8032379110399285| 0.7831732332110376| 1.4981126797890374| 0.9038428761061664| 0.4481579920622491|0.21666601989975678| 0.07290954044433837| 0.11721615024697352| 1.798644012492672|0.23034242677070246| 1.0106816522481665| 0.3103094172143355| 0.15251782473859232| 0.12337159321133193| 0.9082913317784151| 1.0986861689158636|0.014298407346348845| 0.5853678459541178|
|{X2V0-G5QC95QV-97...| 0.374807101660534| 1.1400294576091212| 0.5233264069166564| 0.3480866358130137| 0.8125739160424628|0.18682648629912435| 1.4444273757260202| 1.6257732268771883|0.059307245720187855| 0.519272416604816| 0.4419805803808289| 0.3029185204624289|0.20188181857290474| 0.18949265267103022| 0.24341603467345999| 0.17488713886198198| 0.08884323379412713|0.31453222720967705| 1.0790491965576454|0.14745169575900136|
|{W8Y0-Q6VB44AY-48...| 0.24670891355624| 0.22259905722735548| 0.0612005756778533|0.09011486551094054| 0.2093059317715221| 1.0957645124353874| 1.1123375811714298|0.28390877438304657| 0.4329793864312892| 0.900700932683238|0.12106055105645251| 0.1023762780106969| 0.814875771029895| 1.3486131607539102| 0.34764977834279703| 1.3087112065643698| 0.072125925465686| 0.6002688725681163| 0.40531224948949923| 0.615269897099416|
|{Z3O3-C5WT32II-33...| 0.748922725634082| 0.9829473842793844| 0.4452345524385717| 1.672596935656163|0.36012292783925726| 0.3832229063455501| 0.32220493309109105| 0.4096151093927582| 0.21829770831153375| 0.4223589631097111|0.12106055105645251|0.25028956728518553| 1.2754598213527897| 0.4478711255975053| 1.1921881546850688| 0.16056387442046569| 0.6226637087636253| 0.0663476369900814| 0.20688735802343852| 0.2991210996001837|
|{J0V1-Q3VT17LQ-96...| 0.08269728964340393| 1.6408649773239594| 1.005057560685116| 0.6484116612008337| 2.586739964053437|0.09552139367929229| 0.4567615656255967| 5.626524381074837| 0.4397805337933644| 0.6893589243646066| 3.5452111028571096| 1.3156033029783303|0.21466997297399235| 0.661992313007482| 0.3583182053303725| 2.6257284401017404| 2.471601294911183| 1.2488943894712383| 0.12258914456450333| 0.9250136996902145|
|{I3C6-O0BG43IS-00...| 0.801000674416715| 0.674482840002953| 0.7772072928805669| 2.434960872844048| 1.599567208171392| 0.5293110545372813| 0.8518278896657661| 0.2502873971414017| 0.02401947227800407| 0.4223589631097111| 0.380996323224849|0.08242913749621385| 2.113190307750836| 0.42035878392087134| 0.8874882439177393| 0.12337159321133193|5.398470330772673E-4| 0.5231166463818732| 0.03829093110556815| 1.661986586056133|
|{Z1D7-Y9ON91MY-18...| 0.19283875984342688| 0.6881456748335237| 0.3995986117495537| 1.2490617119764738| 0.8619363837278432| 1.8675639578143288| 1.318474316754862| 1.5461093707515101| 1.511906887760334|0.002787595423446...| 1.8961199270558207| 0.4934871926569193| 1.8612002930473002| 0.9861628671239944| 0.13918229100412294| 0.6845061149471107| 0.7738361744352408| 1.5366791507382336| 0.315178095241593| 0.6387669533362803|
|{E8R7-M4NQ76XB-96...| 0.3388936658519919| 1.0751512557748235| 1.56488056893166| 0.0323606697414876| 2.645694306192178|0.16856546777515793| 2.2431635973697066| 1.3531602812585088|0.030820619640079327| 0.6718438230361848|0.06007629390047258| 2.1704012259625016|0.20188181857290474| 1.1344919733439336|0.045616974322361406| 0.14624060997894936| 0.38718801073415904| 0.7357972973711809| 0.2490364647529061| 1.433421018595924|
|{M2B5-U6QV73PC-27...| 0.11681852052176586| 0.5526896687818861|0.17174834394500474| 0.5675547179947114| 0.6946652317649812| 0.8445850368170625|0.017301616497616433| 0.2564979479625959| 1.0417366473483716| 0.5749303695410799|0.40948860885977933| 0.7566319585431363| 0.2563887800978642| 1.1620043150205677| 0.24608314142035387| 0.2035336677450146| 0.05940877154044385| 1.1174621964852853| 2.99196930054393| 0.9485107559270788|
|{X1H9-X8SF98ZC-71...| 1.2972289138598| 0.44455933239139| 0.5689623476056743| 0.9602880603069884|0.23535196212000928| 0.3832229063455501| 0.6099011025578706| 0.2564979479625959| 0.5022728966838492| 1.2202461979233985| 0.3769966373387298| 0.7466583882858947|0.45219466131613584| 3.2218668582598524| 0.24608314142035387|0.028886254236132933| 0.19386392888361814| 0.0663476369900814| 0.03829093110556815| 0.2991210996001837|
|{K3G8-R5SI91WD-94...| 0.21678105038245493| 0.3091033263397524| 0.6534794977134643|0.19792412311910368| 0.7700737297988488| 0.543097289344043| 0.9777809486369242| 2.177209668935742| 0.522676338770075| 0.2110169547910796| 1.1203122964367505|0.13505809067345723|0.07504822524098467| 1.5244546086504835| 0.4598848422528157| 0.080401799886783| 1.1437670298078815|0.10389969212892508| 0.3573272019710606|0.07696052704840847|
|{J9N9-W9ZA59WS-07...|0.024633768226013954| 0.939695249723186| 0.2954761391121074| 0.1709718087170629|0.04203477980866014|0.22334852334705715| 0.9777809486369242| 0.6025641988857596| 0.6123732911088609| 0.2316447050705001|0.28752009454781946| 0.5361425755769211| 2.6844654530839542| 0.3103094172143355| 0.13918229100412294| 0.6329905692964606| 0.0426914632120027|0.10389969212892508| 1.7949352503552594| 1.0531860472945325|
+--------------------+--------------------+--------------------+-------------------+-------------------+-------------------+-------------------+--------------------+-------------------+--------------------+--------------------+-------------------+-------------------+-------------------+--------------------+--------------------+--------------------+--------------------+-------------------+--------------------+-------------------+
only showing top 20 rows
hash_cols = ['scaledHash['+str(i)+']' for i in range(20)]
assembler = VectorAssembler(inputCols=hash_cols, outputCol="scaledFeatures")
id_hash_sub = assembler.transform(id_hash_sub_split).select('id','scaledFeatures')
id_hash_sub.show()
Output
+--------------------+--------------------+
| id| scaledFeatures|
+--------------------+--------------------+
|{R0A8-D0KA23YU-26...|[0.05276942646961...|
|{C4W6-P6ZE51MH-58...|[0.49093414449531...|
|{Q7V6-N6NE83EZ-03...|[1.00511910184267...|
|{R1W9-Z1SB61VN-25...|[0.49093414449531...|
|{T8V6-A0YZ92MM-43...|[0.06474057173913...|
|{J5S7-D3EN76YN-47...|[6.37502939328121...|
|{V6X5-A6JS37ZC-78...|[0.12280409315652...|
|{K9Q3-C6MB20EN-05...|[0.05276942646961...|
|{E2N0-S3EQ10XZ-59...|[0.05276942646961...|
|{X2V0-G5QC95QV-97...|[0.37480710166053...|
|{W8Y0-Q6VB44AY-48...|[0.24670891355624...|
|{Z3O3-C5WT32II-33...|[0.74892272563408...|
|{J0V1-Q3VT17LQ-96...|[0.08269728964340...|
|{I3C6-O0BG43IS-00...|[0.80100067441671...|
|{Z1D7-Y9ON91MY-18...|[0.19283875984342...|
|{E8R7-M4NQ76XB-96...|[0.33889366585199...|
|{M2B5-U6QV73PC-27...|[0.11681852052176...|
|{X1H9-X8SF98ZC-71...|[1.2972289138598,...|
|{K3G8-R5SI91WD-94...|[0.21678105038245...|
|{J9N9-W9ZA59WS-07...|[0.02463376822601...|
+--------------------+--------------------+
only showing top 20 rows
As you can see, the data we have now is exactly the same as the data before the Save & Retrieve data step. Therefore, if your device (assigned Colab resource) is powerful enough, you can skip this step.
K-Means Modeling
Now we need to train the K-Means model and determine the most suitable number of clusters.
errors = []
results = []
for k in range(2,10):
kmeansmodel = KMeans().setK(k).setMaxIter(10).setFeaturesCol('scaledFeatures').setPredictionCol('prediction').fit(id_hash_sub)
print("With K={}".format(k))
kmeans_results = kmeansmodel.transform(id_hash_sub)
results.append(kmeans_results)
# Evaluate clustering by computing Silhouette score
evaluator = ClusteringEvaluator()
evaluator.setFeaturesCol('scaledFeatures').setPredictionCol("prediction")
silhouette = evaluator.evaluate(kmeans_results)
errors.append(silhouette)
print("Silhouette with squared euclidean distance = " + str(silhouette))
print('--'*30)
Output
With K=2 Silhouette with squared euclidean distance = 0.9054606158887029 ------------------------------------------------------------ With K=3 Silhouette with squared euclidean distance = 0.378622885181141 ------------------------------------------------------------ With K=4 Silhouette with squared euclidean distance = 0.27556189292394295 ------------------------------------------------------------ With K=5 Silhouette with squared euclidean distance = 0.17562579597023814 ------------------------------------------------------------ With K=6 Silhouette with squared euclidean distance = 0.22579471704625662 ------------------------------------------------------------ With K=7 Silhouette with squared euclidean distance = 0.18564839132066216 ------------------------------------------------------------ With K=8 Silhouette with squared euclidean distance = 0.16016978372894128 ------------------------------------------------------------ With K=9 Silhouette with squared euclidean distance = 0.11530445939779198 ------------------------------------------------------------
plt.figure()
k_number = range(2,10)
plt.plot(k_number,errors)
plt.xlabel('Value of K')
plt.ylabel('Silhouette')
plt.title('K - Silhouette')
plt.show()
Based on the Silhouette scores (squared Euclidean distance) across $k$ in the figure above, $k=5$ is a reasonable choice: after $k=4\to 5$ the silhouette drop flattens relative to the large drop from $k=2\to 3$, so five clusters buy most of the structure without further clear gains. (This is a silhouette-based selection, not the classical within-SS elbow rule.)
k = 5
kmeansmodel = KMeans().setK(k).setMaxIter(10).setFeaturesCol('scaledFeatures').setPredictionCol('prediction').fit(id_hash_sub)
kmeans_results = kmeansmodel.transform(id_hash_sub)
clusterCenters = kmeansmodel.clusterCenters()
kmeans_results.show()
Output
+--------------------+--------------------+----------+
| id| scaledFeatures|prediction|
+--------------------+--------------------+----------+
|{R0A8-D0KA23YU-26...|[0.05276942646961...| 0|
|{C4W6-P6ZE51MH-58...|[0.49093414449531...| 3|
|{Q7V6-N6NE83EZ-03...|[1.00511910184267...| 3|
|{R1W9-Z1SB61VN-25...|[0.49093414449531...| 3|
|{T8V6-A0YZ92MM-43...|[0.06474057173913...| 3|
|{J5S7-D3EN76YN-47...|[6.37502939328121...| 1|
|{V6X5-A6JS37ZC-78...|[0.12280409315652...| 3|
|{K9Q3-C6MB20EN-05...|[0.05276942646961...| 3|
|{E2N0-S3EQ10XZ-59...|[0.05276942646961...| 3|
|{X2V0-G5QC95QV-97...|[0.37480710166053...| 3|
|{W8Y0-Q6VB44AY-48...|[0.24670891355624...| 3|
|{Z3O3-C5WT32II-33...|[0.74892272563408...| 3|
|{J0V1-Q3VT17LQ-96...|[0.08269728964340...| 4|
|{I3C6-O0BG43IS-00...|[0.80100067441671...| 0|
|{Z1D7-Y9ON91MY-18...|[0.19283875984342...| 0|
|{E8R7-M4NQ76XB-96...|[0.33889366585199...| 0|
|{M2B5-U6QV73PC-27...|[0.11681852052176...| 3|
|{X1H9-X8SF98ZC-71...|[1.2972289138598,...| 3|
|{K3G8-R5SI91WD-94...|[0.21678105038245...| 3|
|{J9N9-W9ZA59WS-07...|[0.02463376822601...| 3|
+--------------------+--------------------+----------+
only showing top 20 rows
Based on the clustering results obtained from the final model, we can calculate the Euclidean distance between each data point and its corresponding clustering center,
\[d_i=\bigl\lVert x_i-\mu_{\hat{c}(x_i)}\bigr\rVert_2,\]and rank emails by $d_i$ descending.
df_list = []
for row in kmeans_results.collect():
id = row['id']
distance = np.linalg.norm(row['scaledFeatures'] - clusterCenters[row['prediction']])
item = (id, row['scaledFeatures'],row['prediction'], str(distance))
df_list.append(item)
rdd = sc.parallelize(df_list)
results = spark.createDataFrame(rdd,['id', 'scaledFeatures','prediction', 'distance'])
results = results.withColumn('distance', col('distance').cast(DoubleType()))
results = results.orderBy('distance', ascending=False)
results.show()
Output
+--------------------+--------------------+----------+------------------+
| id| scaledFeatures|prediction| distance|
+--------------------+--------------------+----------+------------------+
|{D4D6-O3CF39OC-21...|[4.76243985455224...| 4|14.589378928119531|
|{I0N3-A1QK61ZK-75...|[0.78902952914720...| 2|14.079435575560426|
|{E0Y8-O4HG80PC-27...|[1.84134173438094...| 2|12.950850280350657|
|{F6X4-S4PK22AK-22...|[0.04678385383486...| 2|11.292647843883817|
|{E7R0-U5JS84PD-21...|[6.37502939328121...| 1|11.188457733982629|
|{K0W8-S1FN43PK-21...|[1.05898925555548...| 2|10.659549142628387|
|{A7O1-Z0XQ34YC-09...|[2.33756997382402...| 2|10.403478656841392|
|{P8K2-Q4DT05VF-43...|[2.48961045246734...| 2| 9.751568445106406|
|{N8G3-T8ZQ91IA-15...|[2.95171746103207...| 2| 9.697390803040353|
|{K1R6-N7JJ70ZO-34...|[0.85307862319934...| 2| 9.66275103075823|
|{D1I9-G5HX22BQ-41...|[0.02463376822601...| 2| 9.400571104136892|
|{Q8D1-I2CB87LP-11...|[1.75335034978976...| 2| 9.315246083895909|
|{A3D9-T0QN45UG-36...|[0.26885899916508...| 2| 8.90252992949646|
|{K5V2-Q0UP20CL-70...|[0.80698624705147...| 2| 8.835153657957248|
|{Q4C5-H9UB97OL-06...|[1.35109906757261...| 4| 8.667696292388|
|{M7U9-M3KZ03UJ-30...|[0.04678385383486...| 4| 8.34628083353364|
|{X2F5-V0KB19QO-49...|[0.81895739232098...| 2| 8.305671532451226|
|{V6U4-Z0RL57LU-20...|[0.44484176834743...| 4| 8.159222661283914|
|{N8D3-U7OT51OU-46...|[0.24072334092148...| 4| 8.139282456385539|
|{Z2H0-J1RJ27TB-54...|[0.57293995645173...| 0| 7.938517702943826|
+--------------------+--------------------+----------+------------------+
only showing top 20 rows
The data points farthest from their corresponding clustering centers are the anomalous emails we are looking for.
In addition, we can also get a better view of the distance distribution by drawing the KDE probability distribution for each distance. Based on this, we can determine the appropriate distance threshold as the criterion for classifying anomalies.
distance = results.select('distance')
kd = KernelDensity()
kd.setSample(distance.rdd.map(lambda x: x[0]))
all_distance = list(np.arange(0,20,0.1))
prob_all_distance = kd.estimate(all_distance)
prob_max = max(prob_all_distance)
prob_min = min(prob_all_distance)
plt.plot(all_distance,prob_all_distance)
plt.xlim(0, 20)
plt.title("KDE Curve")
plt.show()
Example of an anomalous email
Based on the above steps, we obtain the list of emails sorted by anomaly score.
For the obtained list of anomalous emails, we can take out the content of an email and review it.
targetId = results.take(1)[0]['id']
targetId
Output
'{D4D6-O3CF39OC-2139MWTY}'
targetEmail = email.where(col('id') == targetId)
targetEmail.show()
Output
+--------------------+-------------------+-------+-------+--------------------+--------------------+------------------+--------------------+-----+-----------+--------------------+
| id| date| user| pc| to| cc| bcc| from| size|attachments| content|
+--------------------+-------------------+-------+-------+--------------------+--------------------+------------------+--------------------+-----+-----------+--------------------+
|{D4D6-O3CF39OC-21...|10/14/2010 14:07:39|NIH0969|PC-3978|FRR127@earthlink....|Lilah.R.Wagner@be...|SBD478@verizon.net|Naida.I.Hughes@ju...|44147| 1|eruption unknown ...|
+--------------------+-------------------+-------+-------+--------------------+--------------------+------------------+--------------------+-----+-----------+--------------------+
targetEmail.collect()[0]['content']
'eruption unknown 1956 advocated 1860s between common proclaimed blizzards rich 25 lifted blocks used clinton mistake sharp visible continues say'
We can see that the first anomalous email in the list is an email containing nonsense content. This shows that our algorithm is effective in finding anomalous emails in a huge volume of emails.
So why do we need to cluster before calculating the distance to the centroid? In other words, why can’t we just use the full email-generated vector as one cluster to find outliers?
Because emails often have multiple types, such as official notifications, work schedules, personal matters, and so on. If all emails are treated as one class, then in the high-dimensional Euclidean space formed by the features, an anomaly cannot be successfully distinguished if it does not belong to any class but is between multiple classes.
P.S. In addition to the classical K-Means algorithm used in the previous section, the Bisecting KMeans algorithm described below can also be used as an alternative when we have strict requirements for running time.
from pyspark.ml.clustering import BisectingKMeans
k = 2
bkm = BisectingKMeans().setK(k).setMaxIter(1).setFeaturesCol('scaledFeatures').setPredictionCol('prediction')
model = bkm.fit(id_hash_sub)
results = model.transform(id_hash_sub)
results.show()
Output
+--------------------+--------------------+----------+
| id| scaledFeatures|prediction|
+--------------------+--------------------+----------+
|{R0A8-D0KA23YU-26...|[0.33982462203890...| 0|
|{C4W6-P6ZE51MH-58...|[1.02642189827338...| 1|
|{Q7V6-N6NE83EZ-03...|[0.73497742184762...| 0|
|{R1W9-Z1SB61VN-25...|[0.23160965393239...| 0|
|{T8V6-A0YZ92MM-43...|[0.10576771195358...| 0|
|{J5S7-D3EN76YN-47...|[2.31353510123168...| 1|
|{V6X5-A6JS37ZC-78...|[0.26030343710261...| 0|
|{K9Q3-C6MB20EN-05...|[0.52305413035815...| 0|
|{E2N0-S3EQ10XZ-59...|[0.18528889688987...| 0|
|{X2V0-G5QC95QV-97...|[0.48329353789000...| 0|
|{W8Y0-Q6VB44AY-48...|[0.00861955314499...| 0|
|{Z3O3-C5WT32II-33...|[0.38614537908142...| 0|
|{J0V1-Q3VT17LQ-96...|[2.15899937608265...| 0|
|{I3C6-O0BG43IS-00...|[0.07707392878336...| 0|
|{Z1D7-Y9ON91MY-18...|[1.30679956540122...| 0|
|{E8R7-M4NQ76XB-96...|[1.76384657627393...| 1|
|{M2B5-U6QV73PC-27...|[0.13446149512380...| 0|
|{X1H9-X8SF98ZC-71...|[0.26030343710261...| 0|
|{K3G8-R5SI91WD-94...|[0.26030343710261...| 0|
|{J9N9-W9ZA59WS-07...|[0.32875781274097...| 0|
+--------------------+--------------------+----------+
only showing top 20 rows
-
Previous
WearMask - Face Mask Detection Project Details -
Next
WearMask: Fast in-browser face mask detection with serverless edge computing for COVID-19
