# Why does the same data get different $R^2$ using three methods (r2_score & fit trendline in Excel & linear regression in SPSS)?

For the same set of data

x1=1, y1=3

x1=2, y1=2

x1=3, y1=1

1. calculated by r2_score:

from sklearn.metrics import r2_score

r2_score([1,2,3], [3,2,1])

the result is -3.

1. plot graph in Excel -- add a trendline -- right-click on your trendline -- Format Trendline --

(1) if select the checkbox next to "Set Intercept = 0.0", the r2=-2.429

(2) if deselect the checkbox next to "Set Intercept = 0.0", the r2=1

1. linear regression in SPSS:

the $$R^2$$ and adjusted $$R^2$$ is 1.

Why do the same set of data get different $$R^2$$ calculated by three methods?

These methods are not even calculating $$R^2$$ of the same object. Python is calculating the $$R^2$$ of your exact entries; Excel (when you don’t specify an intercept) and SPSS calculate the $$R^2$$ of a simple linear regression that uses one of your variables to predict the other; and Excel (when you specify the intercept as zero) is calculating the $$R^2$$ of a linear regression that sets an intercept instead of calculating it from the data.

When you stick the numbers into the sklearn function, it is calculating its output value according to the following formula.

$$R^2 = 1-\left(\dfrac{ \overset{n}{\underset{i=1}{\sum}}\left( x_i - y_i \right)^2 }{ \overset{n}{\underset{i=1}{\sum}}\left( x_i - \bar x \right)^2 }\right)$$

This formula gives negative values when the numerator of that fraction exceeds the denominator, indicating, in some sense, that the predictions are bad. As you can see from your values, your $$y$$ makes for poor predictions of your $$x$$, and you would have a smaller sum of squared deviations between $$x$$ and $$y$$ (that's what is in the fraction) if your $$y$$ were just $$(2,2,2)$$, that is, the average value of $$x$$ in every place.

I get into my thoughts on the sklearn $$R^2$$ definition here.

The reason for the negative value from the Python function is because $$x$$ and $$y$$ make for terrible predictions of each other. The interpretation of $$R^2$$ taken by that Python function is a comparison of your predictions (numerator) to the predictions of a baseline, “must beat” model (denominator), and the negative value indicates predictions worse than those made by the “must beat” model.

When you go to Excel, the $$R^2$$ refers to a regression fit to your input points. This is a subtle but important distinction. $$R^2$$ is supposed to measure the quality of your predictions, and if you have true values like $$y=(1,2,3,4)$$ and predictions like $$\hat y=(105, 205, 305, 405)$$, the predictions are dreadful, agreed? However, when you run a regression using those values, you correctly get that either is a perfect predictor of the other, explaining why you get $$R^2=1$$ when Excel fits its own intercept. Calculating $$R^2$$ this way is equivalent to calculating the Pearson correlation between your two sets of numbers (which will be $$-1$$ for yours) and then squaring that Pearson correlation (giving $$1$$).

When you fix your own intercept in Excel, you force the regression line to go through the origin, which causes all kinds of havoc and can give the dreadful $$R^2<0$$ that you get. There are some good explanations here about why that is the case and what the intercept has to do with anything.

Excel fitting its own intercept means that Excel fits a simple linear regression and using ordinary least squares to calculate the intercept of that regression. Most software does this by default (so SPSS probably does this by default, too). Excel determines this intercept to be $$4$$, so when you click the box to set your own intercept that is zero, you tell Excel to fit a model that is wrong, hence the poor performance.

Then when you go into SPSS, you again are calculating the $$R^2$$ of the regression using those $$x$$ and $$y$$ values, not the sklearn-style $$R^2$$, and you have an intercept, so you get a perfect $$R^2$$ since your $$x$$ and $$y$$ do perfectly predict each other. This is the same point about squared Pearson correlation.

Therefore, each value you get has some kind of meaning. The value you get from Python tells you that $$x$$ and $$y$$ make for terrible predictions of one another. The value you get from SPSS and Excel (where Excel fits its own intercept) tells you that $$x$$ and $$y$$ are strong predictors of each other, even if the numbers themselves do not match. The value you get from Excel when you force the intercept to be zero is probably the least important, but there could be a time where you have reason for set a particular intercept, and it is important to be able to evaluate mode performance in such a situation.

(For an example of how numbers can be terrible predictions of each other but contain enough information to make good, even perfect, predictions of each other, consider height in meters vs centimeters. If I’m am $$182$$ centimeters tall, $$1.82$$ is a terrible prediction of my height in centimeters, but if you know I am $$1.82$$ meters tall, you know how many centimeters tall I am. That’s what’s happening with your $$x$$ and $$y$$: $$3$$ is a terrible prediction of $$1$$, but if you know $$x=1$$, then you know $$y=3$$.)

Depending on what you want to know, any of these methods could be appropriate.

• Since any of these methods could be appropriate, you might consider posting a new question where you ask about which $R^2$ you want to calculate, given what you want to learn from calculating $R^2$.
– Dave
Commented Feb 27, 2023 at 3:27
• Really appreciate your thoughtful response, but i still have a few questions: 1. after reading your second link, I wonder if I can interpret in this way: the reason for negative value in r2_score is because the intercept is not specified, while in spss and excel ( deselect "Set Intercept") the interpret is specified; 2. what do you mean by "Excel fits its own intercept"? Does the intercept fit by default in excel and spss? @Dave Commented Feb 27, 2023 at 9:20
• @ZhangNaiChi I have edited in my responses. Please do, however, think about what you want to learn by calculating $R^2$, as the three distinct values you get all have reasonable interpretations.
– Dave
Commented Feb 27, 2023 at 12:21