<jupyter_start><jupyter_text>hana_ml Clustering AlgorithmsAuthor: SAP TI HA DB ML Chinahana_ml provides the following clustering functions:      - hana_ml.algorithms.pal.clustering.AffinityPropagation      - hana_ml.algorithms.pal.clustering.AgglomerateHierarchicalClustering      - hana_ml.algorithms.pal.clustering.DBSCAN      - hana_ml.algorithms.pal.clustering.KMeans      - hana_ml.algorithms.pal.clustering.KMedians      - hana_ml.algorithms.pal.clustering.KMedoids      - hana_ml.algorithms.pal.mixture.GaussianMixture      - hana_ml.algorithms.pal.som.SOMWe also provide a unified clustering function to invoke the following function:          - 'AgglomerateHierarchicalClustering'      - 'DBSCAN'      - 'gaussianmixture'      - 'AcceleratedKMeans'      - 'KMeans'      - 'KMedians'      - 'KMedoids'      - 'SOM'      In the following sections, we will use a 2D random data to call clustering algorithms and unified clustering function.   HANA ConnectionFirst, create a connetion to SAP HANA. To create a such connection, a config file, config/e2edata.ini is used to control the connection parameters.A sample section in the config file is shown below which includes HANA url, port, user and password information.[hana]url=host-urluser=usernamepasswd=userpasswordport=3xx15<jupyter_code>from hana_ml.dataframe import ConnectionContext
from hana_ml.algorithms.pal.utility import DataSets, Settings
url, port, user, pwd = Settings.load_config("../../config/e2edata.ini")
connection_context = ConnectionContext(url, port, user, pwd)
connection_context.get_current_schema()<jupyter_output>ModuleNotFoundError: No module named 'shapely'<jupyter_text>Dataset - Random 2D Samples<jupyter_code>import pandas as pd
import numpy as np
from hana_ml.dataframe import create_dataframe_from_pandas
import matplotlib.pyplot as plt

n_samples = 1000
df = pd.DataFrame(np.random.randn(n_samples,2))
df.columns = ['X1', 'X2']

df_clustering = create_dataframe_from_pandas(connection_context=connection_context, pandas_df=df, table_name='TBL', force=True, replace=True)
df_clustering = df_clustering.add_id('ID')

# Draw the dataset
plt.figure(figsize=(6,6))
X1 = df_clustering.collect()[["X1"]]
X2 = df_clustering.collect()[["X2"]]
plt.scatter(X1, X2)
plt.xlim(-3, 3)
plt.ylim(-3, 3)
plt.xlabel('X1')
plt.ylabel('X2')
plt.title('Random 2D Samples')
plt.show()<jupyter_output>100%|████████████████████████████████████████████████████████████████████████████████████| 1/1 [00:00<00:00,  1.15it/s]<jupyter_text>Invoke the Clustering Algorithms and Visualize the Results<jupyter_code>import time 
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from hana_ml.algorithms.pal.clustering import AffinityPropagation, AgglomerateHierarchicalClustering, DBSCAN, KMeans, KMedians, KMedoids
from hana_ml.algorithms.pal.clustering import SlightSilhouette
import matplotlib.pyplot as plt

df_clustering = connection_context.table('TBL')
df_clustering = df_clustering.add_id('ID')

k_means = KMeans(n_clusters = 3)
acc_k_means = KMeans(n_clusters = 3, accelerated=True)
k_medians = KMedians(n_clusters = 3)
k_medoids = KMedoids(n_clusters = 3)
ahc = AgglomerateHierarchicalClustering(n_clusters = 3)
dbscan = DBSCAN()
agg = AgglomerateHierarchicalClustering(n_clusters = 3)


clustering_algorithms = ( 
    ('Accelerated K-Means', acc_k_means),
    ('K-Means', k_means),
    ('K-Medians', k_medians),
    ('K-Medoids', k_medoids),
    ('AHC', ahc),
    ('DBSCAN', dbscan))

X1 = df_clustering.collect()[["X1"]]
X2 = df_clustering.collect()[["X2"]]
ss_array = list()

for name, algorithm in clustering_algorithms:
    
    # Invoke the algorithm and record the caculation time
    t0 = time.time()  
    algorithm.fit(data=df_clustering, key = "ID")
    t1 = time.time()
    y = algorithm.labels_.collect()[["CLUSTER_ID"]].astype(np.int)  
    
    # obtain the SlightSilhouette of each algorithm 
    result = algorithm.labels_.select("ID", "CLUSTER_ID").rename_columns(["ID_R", "CLUSTER_ID"])    
    data_ss = df_clustering.join(result, condition="ID = ID_R")
    ss = SlightSilhouette(data_ss, label = "CLUSTER_ID", features = ["X1", "X2"]) 
    ss_array.append(ss.collect()['VALIDATE_VALUE'][0].astype(np.float))
    ss = None
    
    # plot the scatter figure and each cluster is colour with different colors
    plt.figure(figsize=(5,5))
    plt.title(name, size=18)
    m = {-1: '#999999', 0:'#377eb8',1:'#ff7f00',2:'#4daf4a', 3: '#f781bf', 4: '#a65628', 5:'#984ea3', 6: '#e41a1c'}

    if name == 'HierarchicalClustering':
        colors = list(map(lambda x:m[x-1], y["CLUSTER_ID"]))
    else:
        colors = list(map(lambda x:m[x], y["CLUSTER_ID"]))
        
    plot_num =0     
    plt.scatter(X1, X2, s=10, color=colors)
    plt.xlim(-3, 3)
    plt.ylim(-3, 3)
    plt.xlabel('X1')
    plt.ylabel('X2')
    plt.text(.99, .01, ('%.2fs' % (t1 - t0)).lstrip('0'),
            transform=plt.gca().transAxes, size=15,
            horizontalalignment='right')
    plot_num += 1
    colors = None
    plt.show()

# plot the Slight Silhouette figure of algorithms
names = ('Accelerated K-Means', 'K-Means',  'K-Medians', 'K-Medoids', 'AHC', 'DBSCAN')
y_pos = np.arange(len(names))
plt.bar(y_pos, ss_array, align='center', alpha=0.5)
plt.xticks(y_pos, names , rotation=40)
plt.xlabel('Clustering Algorithms', size =12)
plt.ylabel('Slight Silhouette')
plt.title('Slight Silhouette of Clustering Algorithms')
plt.show()<jupyter_output><ipython-input-4-fc0ce5693745>:39: DeprecationWarning: `np.int` is a deprecated alias for the builtin `int`. To silence this warning, use `int` by itself. Doing this will not modify any behavior and is safe. When replacing `np.int`, you may wish to use e.g. `np.int64` or `np.int32` to specify the precision. If you wish to review your current use, check the release note link for additional information.
Deprecated in NumPy 1.20; for more details and guidance: https://numpy.org/devdocs/release/1.20.0-notes.html#deprecations
  y = algorithm.labels_.collect()[["CLUSTER_ID"]].astype(np.int)
<ipython-input-4-fc0ce5693745>:45: DeprecationWarning: `np.float` is a deprecated alias for the builtin `float`. To silence this warning, use `float` by itself. Doing this will not modify any behavior and is safe. If you specifically wanted the numpy scalar type, use `np.float64` here.
Deprecated in NumPy 1.20; for more details and guidance: https://numpy.org/devdocs/release/1.20.0-notes.html#deprecations[...]<jupyter_text>Invoke the Unified Clustering and Visualize the Results<jupyter_code>import time 
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.pyplot as plt
from hana_ml.algorithms.pal.unified_clustering import UnifiedClustering

df_clustering = connection_context.table('TBL')
df_clustering = df_clustering.add_id('ID')

param_simple =dict(n_clusters =3)
clustering_algorithms =  {'acceleratedkmeans' : param_simple,
                          'kmeans' : param_simple,
                          'kmedians' : param_simple,
                          'kmedoids' : param_simple,       
                          'agglomeratehierarchicalclustering' : param_simple, 
                          'dbscan' : dict()                             
                         }

X1 = df_clustering.collect()[["X1"]]
X2 = df_clustering.collect()[["X2"]]
ss_array = list()

for name in clustering_algorithms:

    param=clustering_algorithms[name]
    uc_algorithm =  UnifiedClustering(func=name, **param)
    
    # Invoke the algorithm and record the caculation time
    t0 = time.time()  
    uc_algorithm.fit(data=df_clustering, key = "ID")
    t1 = time.time()
    y = uc_algorithm.labels_.collect()[["CLUSTER_ID"]].astype(np.int)    
        
    # obtain the SlightSilhouette of each algorithm 
    result = uc_algorithm.labels_.select("ID", "CLUSTER_ID").rename_columns(["ID_R", "CLUSTER_ID"])    
    
    data_ss = df_clustering.join(result, condition="ID = ID_R")
    ss = SlightSilhouette(data_ss, label = "CLUSTER_ID", features = ["X1", "X2"]) 
    ss_array.append(ss.collect()['VALIDATE_VALUE'][0].astype(np.float))
    ss = None
    
    # plot the scatter figure and each cluster is colour with different colors
    plt.figure(figsize=(5,5))
    plt.title(name, size=18)
    m = {-1: '#999999', 0:'#377eb8',1:'#ff7f00',2:'#4daf4a', 3: '#f781bf', 4: '#a65628', 5:'#984ea3', 6: '#e41a1c'}

    colors = list(map(lambda x:m[x], y["CLUSTER_ID"]))
    
    plot_num =0  
    plt.scatter(X1, X2, s=10, color=colors)
    plt.xlim(-3, 3)
    plt.ylim(-3, 3)
    plt.xlabel('X1')
    plt.ylabel('X2')
    plt.text(.99, .01, ('%.2fs' % (t1 - t0)).lstrip('0'),
            transform=plt.gca().transAxes, size=15,
            horizontalalignment='right')
    plot_num += 1
    colors = None
    plt.show()

# plot the Slight Silhouette figure of algorithms
names=[]
for name in clustering_algorithms:
    names.append(name)
names[0]='AHC'

y_pos = np.arange(len(names))
names[4]='AHC'
plt.bar(y_pos, ss_array, align='center', alpha=0.5)
plt.xticks(y_pos, names , rotation=40)
plt.xlabel('Clustering Algorithms', size =12)
plt.ylabel('Slight Silhouette')
plt.title('Slight Silhouette of Clustering Algorithms')
plt.show()<jupyter_output><ipython-input-6-964bece53506>:33: DeprecationWarning: `np.int` is a deprecated alias for the builtin `int`. To silence this warning, use `int` by itself. Doing this will not modify any behavior and is safe. When replacing `np.int`, you may wish to use e.g. `np.int64` or `np.int32` to specify the precision. If you wish to review your current use, check the release note link for additional information.
Deprecated in NumPy 1.20; for more details and guidance: https://numpy.org/devdocs/release/1.20.0-notes.html#deprecations
  y = uc_algorithm.labels_.collect()[["CLUSTER_ID"]].astype(np.int)
<ipython-input-6-964bece53506>:40: DeprecationWarning: `np.float` is a deprecated alias for the builtin `float`. To silence this warning, use `float` by itself. Doing this will not modify any behavior and is safe. If you specifically wanted the numpy scalar type, use `np.float64` here.
Deprecated in NumPy 1.20; for more details and guidance: https://numpy.org/devdocs/release/1.20.0-notes.html#deprecati[...]<jupyter_text>Close the Connection<jupyter_code>cursor = connection_context.connection.cursor()
try:
    drop_sql = "DROP TABLE TBL"
    cursor.execute(drop_sql)
finally:
    cursor.close()
connection_context.connection.commit()
connection_context.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1330188679245283, 'fraction_numerical': 0.0280188679245283, 'mean_word_length': 4.014664143803216, 'pattern_counts': {'":': 0, '<': 22, '<?xml version=': 0, '>': 21, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '128965', 'license': 'Apache-2.0', 'n_tokens_mistral': 3977, 'n_tokens_neox': 3686, 'n_words': 859, 'path': 'Python-API/pal/notebooks/ClusteringComparision.ipynb', 'repo_name': 'pbaumann76/hana-ml-samples', 'size': '10600'}