XLSTAT: Missing Data Imputation Using NIPALS in Excel

 

Background

The NIPALS method was presented by H. Wold (1973) to allow principal component analysis with missing values. The NIPALS algorithm is applied on the dataset and the obtained PCA model is used to predict the missing values.

 

Dataset for Example

The dataset we are using here contains six variables and six observations with six missing values. See the attached file.

 

Setting Up

• Open XLSTAT.

• Click on the Preparing data menu and select the Missing data feature.

• The Missing data dialog box appears.

In the General tab:

• Select the data you want to complete in the Quantitative data field (in our case the table with missing values).

• Select the NIPALS missing data method.

• Activate the option for observation labels and select the name of the cars.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

Descriptive statistics tables are displayed (one table before and a second one after imputation).

Then the completed data is displayed.

We can see in bold the completed values. We can compare these values to the real value available in this dataset:

We can see that imputed missing values are very close to the real values. For example for the displacement of Honda Civic, the real value is 1396 and the imputed value is 1390.725. If we had used a mean imputation method, the imputed value would have been 1781.4 which is very far from the value obtained with NIPALS.