On the XLMiner ribbon, from the Applying Your Model tab, select Help - Examples, then select Forecasting/Data Mining Examples, and open the example file Utilities.xlsx. This example data set provides data on 22 public utilities in the U.S.
Select a cell within the data set, then on the XLMiner ribbon, from the Data Analysis tab, select Transform - Principal Components to open the Principal Components Analysis - Step1 of 3 dialog.
From the Variables In Input Data list, select variables x1 to x8, then click the > button to move them to the Selected Variables list, and click Next to open the Principal Components Analysis - Step 2 of 3 dialog.
XLMiner provides two routines for specifying the number of principal components: Fixed #components and Smallest # components explaining. Use the Fixed # components method to specify a fixed number of components or variables to be included in the reduced model. The Smallest #components explaining method allows the user to specify a percentage of the variance. When this method is selected, XLMiner calculates the minimum number of principal components required to account for that percentage of the variance.
XLMiner provides two methods for calculating the principal components: using the covariance, or the correlation matrix. When using the correlation matrix method, the data is normalized first before the method is applied (i.e., the data set is normalized by dividing each variable by its standard deviation). Normalizing gives all variables equal importance in terms of variability. If the covariance method is selected, the data set should first be normalized.
Select Use Correlation Matrix (Use Standardized Variables), then click Next to open the Principal Components - Step 3 of 3 dialog.
Confirm Show principal components score is selected, then click Finish. This option displays an output matrix where the columns are the principal components, the rows are the individual data records, and the value in each cell is the calculated score for that record on the relevant principal component.
For a description of Show Q-Statistics and Show Hotteling's T-Squared Statistics options, please see the Principal Components Options section.
Three worksheets are inserted after the Description worksheet: PCA_Summary, PCA_Scores, and PCA_Components. The output from PCA_Summary is displayed below.
The top section of the PCA_Output worksheet displays the number of principal components created (eight as selected in the Step 2 of 3 dialog), the number of records in the data set (No. of Patterns: 22), the method chosen (Matrix Used: Correlation selected in the Step 2 of 3 dialog), and the Component chosen (Component: Fixed Number as selected in the Step 2 of 3 dialog).
PCA_Components displays the principal component table. The maximum magnitude element for Component 1 corresponds to x2 (|-0.5712|). This signifies that the first principal component is measuring the effect of x2 on the utility companies. The second component appears to be measuring the effect of x4 on the utility companies (maximum magnitude = |-0.4091|). The first component accounts for 27.16% of the variance, while the second component accounts for 23.75%. Together, these two components account for more than 50% of the total variation. The maximum magnitude element for component 1 corresponds to x2.
The output from the PCA_Scores worksheet is displayed below. This table holds the weighted averages of the normalized variables (after each variable's mean is subtracted). This matrix is described in the secoond step of the PCA algorithm. We are looking for the magnitude or absolute value of each figure in the table.
Click back to the Data worksheet, select any cell in the data set, then on the XLMiner ribbon, from the Data Analysis tab, select Transform - Principal Components. Select cells x1 through x8, then click Next to advance to the Step 2 of 3 dialog.
Select Smallest # of components explaining, next to at least, enter 50 for % of variance, select Use Correlation Matrix (Use Standardized Variables), then click Finish.
From the output worksheet PCA_Components1, only the first two components are included in the output file, since these two components account for over 50% of the variation.
The output from the PCA_Scores1 worksheet is below. This table holds the weighted averages of the normalized variables (after each variable's mean is subtracted). This matrix is described in the second step of the PCA algorithm. We are looking for the magnitude or absolute value of each figure in the table.
After applying the PCA algorithm, proceed to analyze the data set by applying additional data mining algorithms featured in XLMiner.
____________________________________________________________________________________________________________
1. Shmueli, Galit, Nitin R. Patel, and Peter C. Bruce. Data Mining for Business Intelligence. 2nd ed. New Jersey: Wiley, 2010.