XLSTAT: Create an Intelligent Pivot Table in Excel

 

Dataset for Example

The attached data, from the 1994 U.S. Census (http://www.census.gov), include 8 descriptors per individual (e.g., age, occupation, education, sex). The dataset has 8,000 records; the population weight variable is not used.

We'll create a pivot table and a contribution chart to identify which factors—or combinations—most influence whether an individual earns over $50K (column J). XLSTAT makes this analysis quick and easy.

 

Setting Up

• Open XLSTAT.

• Click on the Describing data menu and select the Intelligent Pivot Tables feature.

• Click the button to open the Pivot dialog box.

In the General tab:

• Select the data directly by clicking the column letters (first row contains labels).

• Check Labels included, as the first row holds variable names.

• Both explanatory and response variables can be qualitative or quantitative.

• Since the response variable is qualitative, choose Qualitative and select the target modality (e.g., “>50K”).

Note: The pivot algorithm uses classification trees and the CHAID method.

• Click OK to launch the computations.In the next dialog box, select the variables for the pivot table. Each variable's contribution to the model is shown—higher values indicate greater explanatory power.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

A new sheet is displayed with a histogram of the contributions of the variables, and a dynamic pivot table.

The chart confirms that the variables that have the highest effect on the revenue are the Position family followed by the age.

The dynamic pivot table can show up to four values per category combination:

• Target average: % of cases in the target category (qualitative) or average value (quantitative)

• Target size: Count of target category occurrences (qualitative)

• Population size %: % of total population represented

• Population size: Number of individuals in the combination

The pivot table is as follows:

Now analyze the dynamic pivot table to identify category combinations that most influence earning over $50K.

For deeper insights, consider a correspondence analysis to explore relationships between explanatory variable categories. Use only the "Target size" values to build the input table.