XLSTAT: k-Means Clustering in Excel

 

Dataset for Example

The dataset from the US Census Bureau shows population changes across 51 states from 2000 to 2001, expressed as rates per 1,000 inhabitants, focusing on 2001 data.

The goal is to use this demographic data to create homogeneous clusters of states. This dataset is also featured in PCA and HAC tutorials.

Note: K-means results may vary due to random initialization. To replicate results, fix the random seed to 4414218 in XLSTAT (Options > Advanced tab > check "fix the seed").

 

Setting Up

• Open XLSTAT.

• Click on the Clustering menu and select the k-means clustering feature.

• In the K-means clustering dialog box, under the General tab:

  • Column selection mode is used since data starts from the first row, making column references quicker.

  • TOTAL POPULATION and >65 POP. EST. columns are excluded— the former due to irrelevance to dynamics, the latter due to full correlation with a preceding column.

  • Variable labels checkbox is selected as column headers contain variable names.

  • Determinant(W) is selected as the criterion to minimize scale effects.

  • Euclidean distance is chosen for dissimilarity.

  • Number of clusters is set to 4.

  • Row labels (STATE column) are selected to identify observations by state.

• In the Options tab, set the number of repetitions to 10 to improve result quality and stability.

  In the Outputs tab, select the desired output tables to display.

  Click OK to run the analysis—results will appear on a new Excel sheet.

Interpreting the Results

After the descriptive statistics and optimization summary, the inertia decomposition table is shown for the best clustering solution.
It breaks down total inertia into:
Total inertia = Between-class inertia + Within-class inertia.

 

After a series of tables that include the class centroids, the distance between the class centroids, the central objects (here, the state that is the closest to the class centroid), a table shows the states that have been classified into each cluster.

 

Next, a table displays the group ID (cluster assignment) for each state. These IDs can be merged with the original dataset for further analysis, such as discriminant analysis.

If the Correlations with centroids and Silhouette scores options are selected, their respective columns appear alongside the group IDs in the same table.

 

A graph representing silhouette scores then allows to visually study the goodness of the clustering. If the score is close to 1, the observation lies well in its class. On the contrary, if the score is close to -1, the observation is assigned to the wrong class.

Mean silhouette scores by class also allow to compare classes and tell which one is the most uniform according to this score.

Class 1 has the highest silhouette scores. Meanwhile, Class 2 has a score close to 0, which means 4 is not the best number of classes for this data. In the tutorial on Agglomerative Hierarchical Clustering (AHC), we see that the States could be clustered differently with another method.

The following video shows how to group samples with the k-means clustering: