Your data analysis solution

Clustering big datasets using k-means then AHC

2017-12-27

This tutorial will help you segmenting big datasets using k-means Clustering followed by an Agglomerative Hierarchical Clustering (AHC) in Excel using the XLSTAT software. 

Dataset to cluster

An Excel sheet with both the data and the results can be downloaded by clicking on the button below:
Download the data
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).

XLSTAT Analyzing data menu k-means

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.

XLSTAT k-means dialog box, General tab

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

XLSTAT k-means dialog box Options tab

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.

XLSTAT k-means dialog box Outputs tab

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 Class centroids table.

k-means clustering Class centroids

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

k-means clustering in Excel results by class

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

XLSTAT Toolbar

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.

XLSTAT AHC in Excel, dialog box general tab

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.

XLSTAT AHC in Excel, dialog box options tab

We can select all the Outputs for this analysis.

XLSTAT AHC in Excel, dialog box Outputs tab

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

XLSTAT AHC in Excel, dialog box charts tab

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.

AHC in Excel, dendrogram

Also you can see the decomposition of the variance.

XLSTAT variance decomposition in AHC

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.

Clustering big datasets: final manipulations

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

Clustering big datasets with k-means and AHC: final result

 

1c26995d494fb3061dd0ae8571ffc0a4@xlstat.desk-mail.com
https://cdn.desk.com/
false
desk
Loading
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
about
false
Invalid characters found
/customer/portal/articles/autocomplete
9283