XLSTAT: Calculating the Pearson Correlation Coefficient

 

Data Example

The data represents a sample of customers from an online shoe shop. See the attached file.

Rows correspond to customers and columns to the money they spent and other characteristics (e.g. shoe size, weight…).

The data is used here to compute the correlations between the money spent on the online shoe store and the different attributes. A correlation coefficient depicts the strength of the link between two quantitative variables, whether positive or negative. Our data consist of continuous variables so we will use the  Pearson correlation coefficient . We will also test the significance of the correlations.

We will generate two types of graphs:

• A correlation map  to visually explore correlations

• A matrix of scatter plots  to visualize the relationships among pairs of variables.

Setting up a Pearson Correlation Coefficient

• Open XLSTAT

• Select the Correlation /Association tests / Correlation tests command.

• In the General tab, select columns A-E in the Observations/Quantitative variables field.

•  Choose the Pearson correlation coefficient from the drop-down list.

• In the Outputs tab, activate the display of the p-values, the coefficients of determination (R2)

• Define the filtering and sorting of the variables depending on their R2.

• Click on OK

Interpreting the Results

The first results in XLSTAT are the descriptive statistics for all variables (mean, std.deviation, etc).

The correlation matrix is then displayed followed by the 95% lower and upper confidence bounds for the correlation coefficients.

One table will display the upper bounds and another the lower bounds.

We can also display both bounds in a single table.

The correlations between the Invoice amount and the attributes Height and Weight are positive and strong (close to 1).

On the other hand, we observe a negative correlation between the Time spent and the Invoice amount suggesting that the more time customers spend on the website the less money they spend.

All the coefficients appear to be significant at a 0.05 significance level (values in bold). The p-values will be computed for each coefficient in order to test the null hypothesis that the correlation coefficients are equal to 0. (i.e. the risk of rejecting the null hypothesis while this is true (coefficient =0) is less than 5%.)

This is confirmed by the table of the p-values below (p-values < 0.0001).
Note that the shoe size is not displayed in the correlation matrix. This variable was excluded because it has the lowest sum of R2 among all the variables. 
 

The coefficients of determination correspond to the squared correlation coefficients. They measure the strength of the correlation, whether it is negative or positive.

Here, using the filter variables option, we have chosen to display only the 4 variables for which the sum of R2 with other variables is the highest.

We have sorted the variables using the BEA (Bond Energy Algorithm). This method applies a permutation on rows and columns of a square matrix in a way that columns having similar values on rows are close to each other.

The FPC (First Principal Component) is also available.

The next graph is a correlation map that uses a blue-red (cold-hot) scale to display the correlations. The blue color corresponds to a correlation close to -1 (e.g. Time spent on site vs Invoice amount) and the red color corresponds to a correlation close to 1 (e.g. Height vs Invoice amount).  

The last graph is a matrix of plots. A histogram is displayed for each variable (diagonal) and a scatter plot for all combinations of variables.
The color of the data points in the scatter plots reveals whether there is a positive (red) or negative correlation (blue). 

The patterns found in the scatter plots indicate the type but also the strength of the relationship between the two variables.

For example, shoe size looks like it is poorly linked to all other attributes (last column or last row of the matrix) implying correlations close to zero.
 

Exploring Quantitative Variables with Principal Component Analysis

The Principal Coordinate Analysis is a multivariate analysis method that will allow us to further explore the relationships between the variables and relate customers to variables and to one another.

Use this Statistical Test Guide to find other calculation options.