Clustering big datasets with XLSTAT - Using k-means clustering followed by an AHC

Dataset to cluster

An Excel sheet containing both the data and the results for use in this tutorial can be downloaded by clicking here.

The data are from the US Census Bureau and describe the changes in the population of 51 states between 2000 and 2001. The initial dataset has been transformed to rates per 1000 inhabitants, with the data for 2001 serving as the focus for the analysis. Our aim is to create homogeneous clusters of states based on the demographic data we have available. This dataset is not very big but it will illustrate how to deal with much bigger dataset.

Note: if you try to re-run the same analysis as described below on the same data, as the k-means method starts from randomly selected clusters, you may obtain different results from those listed hereunder. To fix the seed, go to the XLSTAT Options, Advanced tab, then check the "fix the seed" option.

Setting up the k-means clustering

Once XLSTAT is activated, select the XLSTAT / Analyzing data / k-means clustering command, or click on the corresponding button of the Analyzing data toolbar (see below).

Once you've clicked the button, the k-means clustering dialog box appears.

Select the data on the Excel sheet with the mouse. (Note: There are several ways of selecting data with XLSTAT - for further information, please check the tutorial on selecting data.) In this example, the data start from the first row, so it is quicker and easier to use the "column selection" mode. This explains why the letters corresponding to the columns are displayed in the selection boxes (C to H).

The Total population variable was not selected, as we are interested mainly in the demographic dynamics. The last column was not selected as it is fully correlated with the column preceding it. The observations labels were selected as they are available.

We set the number of groups to be created to 25. In the case of much bigger dataset you may use a bigger number.

The selected criterion is "Determinant(W)" as it allows to remove the scale effects of the variables.

In the options tab we increased the number of repetitions to 50 in order to increase the quality and the stability of the results.

In the Outputs tab we select only the Centroids which we will use in the AHC, the results by class as it will give us the samples within each class and the results by object to get the table of the sample with an attribution variable.

Once you clicked on OK the results of the k-means clustering will appear in a new sheet.

Agglomerative Hierarchical Clustering on the results of the k-means clustering

We are going to work on the table Class centroids.

Another important table is the table containing the information about which states are clustered together.

Select now the XLSTAT / Analyzing data / Agglomerative Hierarchical Clustering command, or click on the corresponding button of the "Analyzing data" toolbar (see below).

In the General tab you need to select the data to cluster. Select the original variables describing the 25 classes in the Class centroids.

We will use the Proximity type: Dissimilarities and the Euclidian distance, as well as the Ward’s method as the agglomeration method.

We have the name of the variables included in the selection so we tick the option Column labels and select the Row labels that are the cluster number (1-25).

We will use the Row weights option and select the column Sum of weights of the same table Class centroids.

In the Options tab we confirm we want to cluster the rows as the classes are in rows but in this type of clustering (AHC after k-means) you need to include the Within-class variances. You will find this information in the same table as before: Class centroids, in the last column Within class variance.

We can select all the Outputs for this analysis.

Finally in the tab Charts, select all the charts. Pay special attention the the dendrogram type and select the option Vertical.

Results of the Agglomerative Hierarchical Clustering

In the results of the AHC, look at the two dendrogams that give you the composition of the 3 clusters. You can see how the 25 clusters are clustered in three final clusters.

Also you can see the decomposition of the variance.

You can finally use the table obtained in the AHC to recode the table obtained in the k-means clustering so as to have the final results. Go to XLSTAT / Preparing data / Coding.

You need to select the column Class from the classification table obtained in the k-means clustering as the variable to recode. Select the table Result by object from the AHC including the name of the columns as the coding table. You then select the option Column labels.

To append the new column to the first table select the option Range and the first cell next to the table. Also untick the option Display report header so as to not have anything else displayed.

Finally you obtained the results of the classification for the all the states.

The following video shows how to do this tutorial. (This video does not have sound.)

Where to go from here? Back to the top of the page