# 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. Jun 21 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. Jun 26 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 Jun 28 at 8:31
• In excel you can check this: exceluser.com/1069/… Jun 28 at 9:41
• Can you mark the answer as correct if you need no further clarification ? Jun 28 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 :) Jun 28 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 Jun 23 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. Jun 23 at 17:16
• If you consider the answers somewhat usefull, don't hesitate to upvote them as acknowledgment :) Jul 22 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.