In this video, we will learn how to create a correlation matrix, which gives us the correlation between
different variables of our dataset.
To create a correlation matrix, we will use the data analysis adn which contains the correlation matrix
option, so we'll click on data analysis.
Here you can see the analysis tool, which is correlation, click on OK and the input range, we will
select the whole table.
To select the whole level, you can use the shortcut of control shift plus Iraqis.
So first is the input range, which is the whole table, second is grouped by which is in columns,
since columns contains are variables, next option is labeled in first row, which is true since our
first row of the table contains the name of the variables.
And lastly, it is asking us where do we want our output?
We want our output in a new sheet.
So that is selected and we can click on again on.
So here is the correlation matrix for all the variables in our dataset.
You can see that all the variables are listed in rows and columns, both so in the first column and
in the first row we have the variable names.
This fossil is giving me the correlation between the first column, which is price, and the first row,
which is, again, price.
So since it has the same variable on both sides, the correlation of that very well with itself is 100
percent, which is one.
So you can see the whole diagonal is intersection of the same variable with itself.
That is why it is having the value of one in this second value.
It is the correlation between crime rate and the price variable.
And the value of this is minus point 46.
That is, if crime rate is increasing, price values are in general decreasing.
And if crime rate values are decreasing, prices increasing.
Similarly, we get decorrelation values of all the combinations of variables.
The corresponding second value on the row is empty.
Because it is, again, the correlation between price and crime rate, which is the same as this value.
So that is why the upper half of this diagonal is empty and the lower half is filled.
Not only did he reelected, we can see that which all variables have very high correlation with the
dependent variable.
This will help us identify which all variables are important and are expected to have some impact on
the response variable.
So since this first column is the price column, which is are dependent variable, we can callachor
this using conditional formatting in home menu under these conditions, formatting, we can use color
skills.
So if I use this particular color scale, a dark blue color would mean a very high positive correlation,
a dark red color would mean a very high negative correlation, and no color or white would mean that
there is very less correlation between these two particular variables.
So from this, we can say that we expect ruminant teachers, poor Propp, all these variables to highly
impact the price.
Whereas in boardrooms, waterboarding, like waterboarding were waterboarding and they were all they
were able to have a very little impact on the price variable.
So this is how we interpret the relationship between independent and dependent variables versus their
correlation matrix.
We also need to see the correlation values for all the pairs of independent variables.
We need to establish that are independent variables are not correlated amongst themselves.
This is because if the independent variables are highly correlated, we face a problem named multipolarity
in our analysis that may impact that final result.
So we need to identify those variable pairs which have high correlation and delete one of the variables
of that bid so that we do not encounter multiple linearity.
So to identify a pair of rebels which have high correlation, again, use conditional formatting.
We will select this and go to.
The conditions for rating options and will give a neutral.
And this we will select the role, that format only cells that contain cell value, not between minus
point eight and plus point eight.
Wherever this is the scenario, we want that to be filled with green color.
So whenever the sale value is not between minus pointed and pointed, which means that the sale value
is very high, there is very high, either positive or negative correlation between that pair of variables.
The DNA should be color green.
So we'll click on talk.
You can see that only despair of box and air quality is colored green, that is the value of parks and
air quality is is highly, positively correlated to prevent multicellularity.
We need to delete either of these two variables now, which we're able to delete will depend on the
business scenario.
You have to go back and take the business knowledge on which of these two variables is more important
to our analysis and which can be dropped.
Alternatively, you can also consider the fact that collecting data for each of these variables is easier
if there is no difference between these two variables in decent.
You can opt for that variable, which is higher correlation with the dependent variable, for example,
here.
Air quality has minus zero point for two coalition and parties, minus zero point three nine.
Coalition air quality has a higher value of correlation with the price.
Variable will keep air quality and will the lead box.
So we'll go to our house pricing data and we will delete the column which contains the value of box.
So this is how we draw the correlation matrix and analyze it using conditional formatting in Microsoft
Excel.