XLSTAT: Elastic Net Regression in Excel

 

Dataset for Example

The (attached) original data are from Osborne and al. (1984). The dataset contains, for 30 cookies, the discretizations of near-infrared spectra. Each spectrum is observed at wavelengths ranging from 1100 to 2460 nanometers and spaced by 40 nanometers, which leads to 35 explanatory variables in the dataset. The data table also contains the flour composition of each cookie. 

The goal is to predict the flour composition of the different cookies.

 

Setting Up

• Open XLSTAT.

• Click on the Modeling data menu and select the Elastic net regression feature.

In the General tab:

• Select the flour composition of the cookies as the Dependent variable  (or variable to model).

• Select all columns numbered L1 to L35 as the Quantitative explanatory variables. Here we want to explain the variability of the flour content in the cookies as a function of the spectrum.

• Select the first 20 cookies from the dataset to be included in the modeling, the others will be used for prediction.

• Check the Variable labels checkbox to display the name of each variable at the top of the table.

• Check the Observation labels box and select the name of the different cookies.

In the Options tab:

• Use Cross-validation to find the optimal Alpha and Lambda parameters.

• Choose to form 5 folds for the cross-validation and to test 100 values of each parameter.

• In the Prediction tab, select the data on which you want to get a prediction. Here we select the last 10 cookies in the dataset.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

The table “Cross-validation results” gives the values of the parameters retained by the cross-validation: the value of the pair (Lambda; Alpha) selected by XLSTAT is the one that minimizes the MSE, here the pair (0.012; 0.616)

The table “Model parameters” gives details on the model. This table is helpful when predictions are needed or to analyze the importance of variables in the model. We see that only 15 of the 35 variables were kept in the modeling, the others have a zero coefficient.

The last table gives, following the estimation of the model, the predictions of the flour composition of the 10 cookies used in the prediction dataset.

 

Conclusion

Elastic Net regression modeled the flour composition of the different cookies using only 15 of the 35 available variables. The remaining variables were either not sufficiently explanatory or contained information highly similar to that of the selected variables.

Using the same dataset, LASSO regression would have retained 11 variables, while Ridge regression would have included all 35. This demonstrates that Elastic Net acts as a compromise between Ridge and LASSO regressions, balancing variable selection and coefficient shrinkage.