# Calculating optimal 'phase shift' when comparing two linear datasets to maximise correlation?

Let's say I have two columns of data, when graphed they look like this:

This does not have a particularly wonderful correlation.

On the other hand if I shift one graph 10 datapoints relative to the other:

Clearly the two graphs are virtually identical, but one 'lags' the other.

What are:

1. the proper technical terms where I have said 'lag' and 'phase shift', to describe this situation?
2. tools to identify this i.e. "what 'shift' will give the best correlation score'?

I'm currently just hacking this in Excel, is it the sort of thing I might find available without specialist tools or writing my own code?

• May be start with some lagged autocorrelation. If you need something fancier look at dynamic time warping. Commented Jun 21, 2021 at 21:13
• Autocorrelation is how a time series is related to itself. Dynamic time warping models changes in speed between two process. Neither a directly relevant to this problem. Commented Jun 26, 2021 at 18:52

The concept of cross-correlation is used in signal processing to find delay in signal and also in image processing to match-images(known as template matching)

The general approach is to go on shifting the signal by 1 and compute correlation and find where the maximum values, here's a solution using matplotlib, if someone wants a ready to use implementation

x=np.random.randint(0,500,50).reshape(1,-1)
k=4
y=np.concatenate([np.random.randint(0,500,k),x[0,:-k]]).reshape(1,-1)
plt.plot(x[0])
plt.plot(y[0])
plt.show()


plt.xcorr(y[0].astype(float),x[0].astype(float), maxlags=20)
plt.show()


Here matplotlib is under the hood calculating all the correlations by shifting the values (using np.correlate). In this chart as you can see the max value comes at 4 which is what we expected

[EDIT]

How to implement cross correlation using excel: https://exceluser.com/1069/use-automated-cross-correlations-in-excel-to-find-leading-indicators-part-1/

• I'm not familiar with that tool but the principle makes sense Commented Jun 28, 2021 at 8:31
• In excel you can check this: exceluser.com/1069/… Commented Jun 28, 2021 at 9:41
• Can you mark the answer as correct if you need no further clarification ? Commented Jun 28, 2021 at 12:33
• I that that link is even better than your answer; can you edit it in?! For my purposes this looks more practical than FFT if only because I don't really understand FFT :) Commented Jun 28, 2021 at 12:40

You can use Fast Fourier Transform to detect similarities and get the time shift easily.

https://stackoverflow.com/questions/4688715/find-time-shift-between-two-similar-waveforms

• I wouldn't have considered it a SO sort of question - not a language/tech I know but still useful. I would love a more high-end answer if anyone can go into it at a more math/data angle Commented Jun 23, 2021 at 15:51
• Yes I know it is unexpected, but interesting. I wonder why someone has set it as "not useful"? An interesting data approach is to use 2D CNNs, because they detect very well shapes, and curves could be considered as rows of values very well adapted to 2D CNNs. Commented Jun 23, 2021 at 17:16
• If you consider the answers somewhat usefull, don't hesitate to upvote them as acknowledgment :) Commented Jul 22, 2021 at 9:15

It is called "lag", a delay in one process compared to another process.

In Microsoft Excel, lag can be modeled with the correlation / CORREL function.