The goal is to expand the functionality of the “DamianCalin/SQLOperators” package to include certain machine learning capabilities. https://resources.wolframcloud.com/PacletRepository/resources/DamianCalin/SQLOperators/This post introduces “findClustersSQL,” which serves as a wrapper for the Wolfram “FindClusters” function when applied to SQL tables.The syntax for the new function is as follows:findClustersSQL [Input table , {column list } , OptionsPattern ]findClustersSQL [Input table , { column list } , Association ] findClustersSQL [Input table , {cluster column list } , { Association list} ]The function is overloaded, and all its usages will be demonstrated in the following section.
Demo
Demo
Install SQL Paclet
Install SQL Paclet
Install the last version of “DamianCalin/SQLOperators” paclet.
https://resources.wolframcloud.com/PacletRepository/resources/DamianCalin/SQLOperators/
https://resources.wolframcloud.com/PacletRepository/resources/DamianCalin/SQLOperators/
In[]:=
pclt=PacletInstall["DamianCalin/SQLOperators"];Needs["DamianCalin`SQLOperators`"]
Get sample data
Get sample data
We’ll use the Fisher’s Irises dataset, which is a record of 150 specimens of irises and some of their characteristics:
In[]:=
tbIrises=ResourceData["Sample Data: Fisher's Irises"]//datasetAsTableSQL[#]&;tbIrises//tableSQLAsDataset
Out[]=
Obtaining clusters based on specific columns selected from a table
Obtaining clusters based on specific columns selected from a table
We want to cluster data based on petal length and sepal width
The basic use of function use default clustering options and returns a list of tables with same structure as input table
The basic use of function use default clustering options and returns a list of tables with same structure as input table
In[]:=
clusters=findClustersSQL[tbIrises,{PetalLength,SepalWidth}];
Display tables
In[]:=
tableSQLAsDataset/@clusters
Out[]=
,
The automatic function FindClusters identified two clusters, one with 49 examples and one with 101 examples.
Various options are available to modify the clustering behavior. You can explore the complete list and details of these options in the official Wolfram Language documentation for FindClusters.
Various options are available to modify the clustering behavior. You can explore the complete list and details of these options in the official Wolfram Language documentation for FindClusters.
clusters=findClustersSQL[tbIrises,{PetalLength,SepalWidth},3,Method->"KMeans",FeatureExtractor->"Minimal"];tableSQLAsDataset/@clusters
Out[]=
,
,
Incorporate clusters to table as column
Incorporate clusters to table as column
To integrate clustering into the table structure, you need to provide the following association as an additional parameter:
<|”ClusterName” -> “New Column Name” ,
“ClusterOptions”-> {Method->”KMeans” ...} |>
”ClusterName” key is mandatory .
The result will be a table containing new column containing cluster group values.
<|”ClusterName” -> “New Column Name” ,
“ClusterOptions”-> {Method->”KMeans” ...} |>
”ClusterName” key is mandatory .
The result will be a table containing new column containing cluster group values.
In[]:=
tbCluster=tbIrises//findClustersSQL#,{PetalLength,SepalWidth}, <|"ClusterName"->"ClusterKMeans", "ClusterOptions"->{3,Method->"KMeans",FeatureExtractor->"Minimal"} |>&;tbCluster//tableSQLAsDataset
Out[]=
Now that tbCluster we can use groupBy to explore cluster aggregates
Plot clusters
Define multiples clusters models on table
Define multiples clusters models on table
Multiple cluster models on the same table can be defined by specifying a list of associations containing cluster names and model options.
The outcome will be a table with as many new columns as there are cluster models.
The outcome will be a table with as many new columns as there are cluster models.
In order to use groupBy and comparing the models we have to transpose or pivot on cluster columns.
Plot clusters multiple cluster models
Comments/Suggestions/Donations
Comments/Suggestions/Donations
CITE THIS NOTEBOOK
CITE THIS NOTEBOOK
SQLOperators package: Introducing new function findClustersSQL
by Damian Calin
Wolfram Community, STAFF PICKS, November 29, 2023
https://community.wolfram.com/groups/-/m/t/3073895
by Damian Calin
Wolfram Community, STAFF PICKS, November 29, 2023
https://community.wolfram.com/groups/-/m/t/3073895