XLSTAT: C&RT Classification Tree in Excel

 

Dataset for Example

This dataset, from the National Institute of Diabetes and Digestive and Kidney Diseases, aims to predict diabetes diagnosis in Pima Indian female patients aged 21 and older. It includes medical predictor variables like pregnancies, BMI, insulin level, age, and the target variable, Outcome.

 

Setting Up

• Open XLSTAT.

• Click on the Machine Learning menu and select the Classification and regression trees feature.

• After clicking the button, the Regression Trees dialog appears.

In the General tab:

• Select the qualitative dependent variable: Diabetes (Yes = diabetic, No = non-diabetic).

• Select all other variables as quantitative explanatory variables.

• Choose the C&RT algorithm to build the tree.

• Check Variable labels to use variable names from the first row.

In the Options tab:

• Set maximum tree depth to 3 to limit complexity.

• Fix complexity parameter (CP) at 0.001, so the tree grows only if impurity reduces by at least this factor.

• Additional technical options are available to control tree construction.

• In Charts tab, select the Bar charts  option to display the distribution of the species at each node.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

Summary statistics and the correlation matrix are displayed first, followed by the confusion matrix showing reclassification results. The percentage of well-classified observations is 77.3%.

For each node, the tree structure shows: number of objects, percentage of total, improvement (node size × impurity reduction), purity (% of dominant class), parent and child nodes, split variable with its value(s), and the predicted class.

The table lists rules from the algorithm in natural language. Each node shows the rule for the predicted class and the percentage of observations belonging to that class at that node.

For example, the rule "If Glucose ≤ 127.5 then Diabetes = No" applies to 485 observations (63% of the dataset) with a node purity of 80.6%, as shown in the tree structure. The next result displays part of the classification tree.

This diagram shows how the C&RT algorithm successively identifies the best variables to split the dependent variable's categories. The process stops when no further splits are possible or when user-defined limits are reached (e.g., minimum node size, maximum tree depth, or threshold CP). XLSTAT also offers an alternative pie chart visualization, which is clearer when the tree has many nodes or categories.