Comparison of Data that looks at correlation and absolute values

Question

I am looking for a way to compare two sets of data in order to find out how similar they are to each other.

My application: I try to compare multiple Measurement methods that both measure the sound absorption parameter of a material (Foam, wall-absorbers etc) over frequency (X Axis). The values can be between 0 and 1, so the absorption can also be described as a percentage.

One of these methods is my "reference", that produces the most "trustable" data. So if I compare another method to that reference, in order to call it "similar", the absorption coefficients of each frequency band are going to be as close to each other as possible (ideally zero difference).

The method should take into account not only the strength of the linear relationship (correlation) but also the absolute values of the data points. The problem with correlation in Excel is that the result can be 100% correlation even if the curves run parallel to each other. I only want the result to be 100% if they are exactly the same, value by value.

For example if my reference values of absorption over the frequency are A: 0.1, 0.2, 0.3, 0.4, 0.5, 0.6 compared to B: 0.1, 0.2, 0.3, 0.4, 0.5, 0.6 should result in a high similarity, whereas A compared to C: 0.2, 0.3, 0.4, 0.5, 0.6, 0.7 should also indicate similarity but now 100% (because these absorption values run parallel and are not entirely similar).

A compared to D: 0.3, 0.4, 0.5, 0.6, 0.7, 0.8 should also result in a value that indicates similarity but less than A compared to C because A and D are more "far" away from each other.

Does such a method exist or can be built in any way in excel or python? So far, I found the RMSE to be close to what I need but it does not take into account the shape of my data.

Answer 1

Depending on the way you define the calculation, $R^2$ might make sense for you.

However, I would not recommend just squaring the Pearson correlation between your vectors. That retains most of the issues you see from just Pearson correlation on its own. I would calculate according to the equation I give here.

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

The $x_i$ are your reference values. The $y_i$ are the corresponding values from other measurement systems. The $\bar x$ is the mean of the $x_i$. $N$ is the sample size.

Your comments mention that root mean squared error/deviation might make sense for your purposes. Notice how related to RMSE the numerator of that fraction is (square RMSE and multiply by the sample size).

Advantages of this calculation are:

1. It is available in Python through sklearn.metrics.r2_score.

2. Because of how related this calculation is to the squared Pearson correlation of true and predicted values in OLS linear regression, this calculation arguably retains a flavor of Pearson correlation.

3. This calculation flags terrible $y_i$ values with $R^2<0$, meaning that you would have a smaller RMSE if you just predicted $\bar x$ every time instead of doing whatever you do to get your $y_i$ values (which is, presumably, harder than running AVERAGE(X:X) in Excel).

An extension of this might use absolute deviations instead of squared deviations. I discuss this in my answers here. Such a calculation is available in Python through sklearn.metrics.d2_pinball_score.

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

This has the property of giving less penalty for large misses than the $R^2$ equation given earlier. This need not be a desirable property, but it might be. For instance, one terrible measurement might wreck all downstream work, no matter how good the others are. On the other hand, you might be willing to sacrifice one point if the others can be made closer to their reference values.

EDIT

The $D^2$ score I wrote above uses $\bar x$ in the denominator. For reasons related to what quantile regression estimates, it is reasonable to subtract the median of the $x$-values instead of the mean. I suspect this is how sklearn does the function, though what I wrote above does have a reasonable interpretation as relating to a comparison of the absolute loss incurred by your model and the absolute loss incurred by a naïve model that always predicts $\bar x$.