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. All other options were left at default value.

The computations begin once you have clicked on "OK". 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 two groups which are approximately the same size. The first group (displayed in blue color) is more homogeneous than the second (it is flatter on the dendrogram). This is confirmed when looking at the Within-class variable. It is a lot higher for the second group (599.88 compared to 139.11).

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.

Click here for other tutorials.