How do I run an Agglomerative Hierarchical Clustering with XLSTAT?

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.

Once XLSTAT-Pro is activated, select the XLSTAT/Analyzing data/Agglomerative Hierarchical Clustering command, or click on the corresponding button of the "Analyzing data" toolbar (see below).

The Hierarchical Clustering dialog box will appear. Then select the data on the Excel sheet. (Note: There are several ways of selecting data with XLSTAT - for further information, please check the section on selecting data in the XLSTAT tutorial.)

In this example, the data start from the first row, so it is quicker and easier to use columns selection. This explains why the letters corresponding to the columns are displayed in the selection boxes. 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.

In the Options tab, the Center/Reduce options were selected to avoid having group creation influenced by scaling effects.

We selected the automatic truncation option, so that the results show the groups to which each observation belongs. The automatic truncation is based on the entropy and tries to create homogeneous groups. However it should not prevent you from using a different number of groups either because of operational constraints, or because of your prior knowledge.

The computations begin once you have clicked on "OK".

Interpreting the results of an Agglomerative Hierarchical Clustering

The first result to look at is the levels bar chart. The shape reveals a great deal about the structure of the data. When the increase in dissimilarity level is strong, we have reached a level where we are grouping groups that are already homogenous. Automatic truncation uses this criterion to decide when to stop aggregating observations (or groups of observations).

The chart below is the dendrogram. It represents how the algorithm works to group the observations, then the sub groups of observations. As you can see, the algorithm has successfully grouped all the observations. The dotted line represents the automatic truncation, leading to three group

Two groups are approxilmately the same size, and the third one has only two States. The first group (displayed in blue color) is more homogeneous than the third one (it is flatter on the dendrogram). This is confirmed when looking at the Within-class variable. It is a lot higher for the third group than for the first one.

The following table shows the states that have been classified into each cluster.

A table with the group ID for each state is displayed on the results sheet. A sample is shown below. This table is useful as it can be merged (or joined) with the initial table for further analyses.

This video shows how to do this tutorial.

Click here for other tutorials.