Time-Correlations between two data sets moving one step forward and backward
sometime call "time/frequency signal shift", "Time Delay" or "Horizontal Shifting"

history, inside the problem, the software, how to use, examples, donations

Fig.1, results after moving the 2nd data set 2 steps forward, the correlation on left, the residues on right

History

  Copying from Wikipedia: Early work in time-frequency analysis can be seen in the Haar wavelets (1909) of Alfréd Haar, though these were not significantly applied to signal processing. More substantial work was undertaken by Dennis Gabor, such as Gabor atoms (1947), an early form of wavelets, and the Gabor transform, a modified short-time Fourier transform. The Wigner-Ville distribution (Ville 1948, in a signal processing context) was another foundational step.

  With this small piece of software we do not like to solve all problems of correlation between 2 distributions but only make a tentative to produce a Lag-Plot (or lag correlation) of your data. Please do not confuse with Correlogram and with Scaled Correlation.

  In Fig.1 you can see the scatterlplot between 2 distributions after 2 steps forward; on the right the residues of regression, very useful to check "the quality" of obtained values from correlation's formulae.

  Accounting all I said previously, I select Excel for Horizontal Shifting. It is not a new idea and you can be inspired reading the following:

1. from David Meko, University of Arizona; a description of 3 different Lagged Correlation with examples in climate analysis,
2. from Prof. John McDonald, University of Delaware; a very, very interesting web page on regression/correlation, and an Excel spreadsheet regression up to 1000 pairs of observations,
3. a citation of Frank Anscombe; for their study around 1961 on the formal properties of residuals in linear regression, see Wikipedia web page,
4. also from Wikipedia; a web page on Exploratory Data Analysis (EDA) a starting point for any future analysis,
5. from Jeffrey Sukharev et al.; a paper on "Correlation Study of Time-Varying Multivariate Climate Data Sets", this is the link, to the .pdf version,
6. from Mehmet Sayal, Intelligent Enterprise Technologies Laboratory, HP Laboratories, Palo Alto; a paper on "Detecting Time Correlations in Time-Series Data Streams", this is the link, to the .pdf version,
7. from WaveMetrics software web site; a page on Convolution, please see the zero-delay point definition.

Inside the Problem

  The idea of this software comes from many of us work in the spectrophotometric UV-Vis field. Several times the replicate of the same solution produce a slight different spectra.

  Accepting a little difference in absorbance, we aspect the same value in nanometre, for the centre of absorbance band. Plotting 3 replicate we can find a difference of one step (0.5nm) in the centre values! And sometime not.

  So I have started the design of a simple spreadsheet to move the second distribution 4 steps forward and backward and every time calculates the Pearson correlation.

  It is better to spent some time on the procedure:

1. first we have 2 different distributions with the same number of data points, the distribution comes from a replicate in an instrument, or from a day windowing of a microclimate monitoring,
2. if it is coming from a replicate or from a windowing we can hypothesized a "linear correlation" between the 2 distributions,
3. if there is a little error of the reproducibility of the instrument, or a little delay between 2 consecutive days, we need a few steps forward or backward for looking for the best correlation,
4. the simple coefficient of determination (r²) is not sufficient, (but this is another story), it is better to calculate some parameters errors and the residues of correlation. Plot always is mandatory.

  Avoiding confusion we are doing a procedure as shown in Fig.2. In column Y1 the 1^st distribution, is fixed, in column Y2 and following the 2^nd distribution moved one cell up and down (one lambda step of spectrometer, as example).

Fig.2, the second distribution is moved forward and backward before calculation

The Software

Fig.3, the calculated parameters for regression

  This free software was distributed under Creative Commons Public License, CCPL 3.0, see my Copyright web page. Please read accurately the Liability Waiver in red.

  There are a 2 different versions, all work on MS-Excel 2000, up to MS-Excel 2003, and was tested on MS-Excel 2007 and 2010 version. All files are in native .XLS format, available:

• a single spreadsheet with space for 1500 data values, having only the 2 columns Y1 and Y2 to be filled with your values. This GVisco_OLS-time-shift_v1.19.XLS
• a 2 spreadsheets version, one with your data and the other with calculation. You must download the file: the GVisco_OLS-time-shift_v1.19_autoload.XLS and the sources.XLS.

  In the Fig.3, on the right, are shown the regression parameters calculated by software, the "errors" are useful to estimate the quality of correlation.

How To Use

  Dear students, this is not a course on Excel so you should already know how to use formulae and functions. I remember you to pass every lesson for Excel 2003 in this Course, and after that, pass all lessons of 2010 version (and do ALL projects).

  Very often the writer of a software is not the best writer of "instructions", he/she already knows how to use it! But I have made here a tentative to describe how to proceed:

• there are 2 versions of software, all with space for 1500 data values. Following the first one,
• download the single sheet file GVisco_OLS-time-shift_v1.19.XLS, with space for 1500 values, and open it with Excel,
• download the file with examples, sources.XLS and open it with Excel,
• copy the cells B1-C1501 from sources.XLS and paste in the other file, sheet "raw", pointing to cell A6,
• do not touch any other cells in the "raw" sheet and in the "calc" sheet, pass to "chart" sheet
• in the "chart" sheet you can see the scatterplot of the two distributions on the left and the consequent residues on the right,
• looking the scatterplot and the values in the "calc" sheet you can find the best lag for obtaining correlation, after that you can copy the value of Fig.3 for your lag.
•  
• alternatively you can download the autoload sheet file GVisco_OLS-time-shift_v1.19_autoload.XLS, and save in your HD,
• download the file sources.XLS and save in the same place of HD (same directory, same folder, same ....),
• start Excel and File/Open the file sources.XLS,
• in the same Excel running use File/Open the file GVisco_OLS-time-shift_v1.19_autoload.XLS,
• the software "autoload" 2 columns from the previous ones, see the cells G1 and G2 in the "chart" sheet,
• make some tentative to change the cell G2 from C to I and see the obtained scatterplots, it is interesting the couple H and I with a complete uncorrelation,
• do not touch any other cells in the "raw" sheet and in the "calc" sheet, fill only the G1 and G2 in "chart" sheet
• next you can fill the sources.XLS with your data from B2 to IV2 and point to those columns in the working spreadsheet,
• in the "chart" sheet you can see the scatterplot of the two distribution on the left and the consequent residues on the right,
• looking the scatterplot and the values in the "calc" sheet you can find the best lag for obtaining correlation, after you can copy the value of Fig.3 for your best lag.

Examples

  The best example, up to today, is the file sources.XLS with the columns B and C correlated with a lag, and with the other column with practically no correlation.

  We are working to fill the file sources.XLS with some other examples coming from UV-VIS or NIR spectra, wait please.

Tips

  If you know Excel you can expand or reduce the 1500 rows, and you can probably design a better "autoload" solution. But remember the Copyright on these files.

Donations if you have a Bitcoin wallet

  If you have a nice fat wallet you can send a few cents to this code 1ETv1mJTZTB7EiXwZkucF2BTA512Rkaw6 maybe with a message (be careful to the transaction cost, send 0.1 and pay 0.01 is not a good idea).

1ETv1mJTZTB7EiXwZkucF2BTA512Rkaw6    <== my code , Thank you.

Donations if you have not a Bitcoin wallet

  If you like this work and measures you can make a small donation of Bitcoin. You do not need a wallet to make a donation, indeed even those who have Bitcoin wallet is better to pass for a web site that "gift" currency since this does not have a transaction cost.

  To make a donation you must use some of your web-time. Go to www.GreenBitCo.in, enter my code 1ETv1mJTZTB7EiXwZkucF2BTA512Rkaw6 enter the Captcha code that you see, press "Claim Now" button. Wait 15 minutes on the publicity page, when the 15 minutes pass fill the new Captcha and press "Claim Now" again. To get some value are necessary 10 pages or more. Be careful, do not use roulette, dices, casino and other stealing web site.

1ETv1mJTZTB7EiXwZkucF2BTA512Rkaw6    <== my code , Thank you.

     

Back Page   read help   C^© Copyright   we did it