Quick Answer: Cross Correlation Result

The cross-correlation analysis used G1 as the X series and G3 as the Y series. The analysis used the row order as the ordered sequence. The lag convention was: positive lag means X_t is compared with Y_t+lag. In simple words, a positive lag asks whether G1 is associated with later G3 values, while a negative lag asks whether G3 is earlier relative to G1.

The main result showed that the strongest absolute cross-correlation occurred at lag 0, with r = 0.826387 and p = 1.617571e-163. This means the strongest relationship is the same-row relationship between G1 and G3, not a delayed relationship. The 95% confidence interval for lag 0 was approximately 0.800326 to 0.849331, and the result was outside the approximate 95% band.

What Is Cross Correlation?

Cross correlation is a lag-based correlation method used to compare two ordered series. A normal Pearson correlation compares X and Y in the same row. Cross-correlation repeats the comparison after shifting one series forward or backward by different lag values.

This is useful when the researcher wants to know whether one series leads or follows another series. For example, in a time-series setting, a researcher may ask whether advertising today is related to sales next week. In this student-grade example, the ordered rows are used to demonstrate the mechanics of lagged cross-correlation with G1 and G3.

Cross-correlation is connected to Durbin-Watson Test, p-value, confidence interval, descriptive statistics and standard deviation because the final interpretation depends on sequence order, association strength, uncertainty and data quality.

Lag Direction and Interpretation

The lag convention used in this report is:

With X = G1 and Y = G3, a positive lag means the current G1 value is compared with a later G3 value. A negative lag means G3 is earlier relative to G1. Lag 0 means no shifting: G1 and G3 are compared in the same row.

Cross Correlation Formula

For each lag, the analysis aligns the two series and calculates the Pearson correlation between the aligned values. A simplified lagged cross-correlation formula is:

Here, k is the lag. If k = 0, the formula becomes a same-row Pearson correlation. If k is positive, X is compared with later Y. If k is negative, Y is earlier relative to X.

The report also calculates a t statistic, two-tailed p-value, Fisher 95% confidence interval, approximate 95% band and an outside-band decision for each lag. This makes the output more informative than a simple lag plot alone.

Dataset and Variables Used

The analysis used G1 as the X series and G3 as the Y series. G1 represents the first-period grade, while G3 represents the final grade. Both series had 649 valid observations.

The same-row SPSS context also shows strong relationships among grade variables: G1 with G2 is about .865, G1 with G3 is about .826, and G2 with G3 is about .919. These same-row correlations help explain why the lag 0 cross-correlation is the strongest result.

Verified Cross Correlation Results

The top lag table ranks the largest absolute cross-correlations. Lag 0 is clearly strongest, with r = 0.826387. The next lag values are much smaller, even when statistically significant.

The final decision is straightforward: the strongest relationship is the same-row association. Lagged values exist and some are statistically significant, but they do not exceed lag 0. Therefore, this report should emphasize the strong same-row G1-G3 association rather than claiming a delayed lead-lag effect.

Python Chart-by-Chart Interpretation

The Python charts show the ordered series profile, cross-correlation function, lag direction strength, same-row scatterplot, strongest-lag scatterplot and ranked lag results.

Python Chart 1: Ordered Series Profile

This chart standardizes G1 and G3 so their patterns can be compared on the same scale. Standardization removes the original unit difference and makes co-movement easier to see. When the two lines move in similar directions, it suggests that the two series share an ordered pattern.

The chart supports the later cross-correlation result because G1 and G3 often move together in the same row order. This is why lag 0 becomes the strongest association in the cross-correlation table.

Python Chart 2: Cross Correlation Function

The cross-correlation function chart plots r values across the tested lags. The largest spike occurs at lag 0, where r = 0.8264. The surrounding lag values are much smaller, so the chart visually confirms that the same-row association dominates the lagged pattern.

This chart is the main diagnostic plot for cross-correlation. If a positive or negative lag were stronger than lag 0, the tallest spike would appear away from zero. Here, the strongest spike is at zero, so the best interpretation is no meaningful row delay beyond the same-row relationship.

Python Chart 3: Lag Direction Strength

This chart focuses on absolute strength, so both positive and negative correlations can be compared by size. Lag 0 has the largest absolute cross-correlation, making it the strongest lag overall. The next strongest lags are smaller and do not change the main conclusion.

The direction labels help prevent a common mistake. A positive lag is not automatically “better” than a negative lag. The researcher must read the lag convention and then interpret whether X leads Y or Y leads X.

Python Chart 4: Lag 0 Aligned Scatterplot

The lag 0 scatterplot shows the same-row relationship between G1 and G3. The points form a clear upward pattern. This matches the strong Pearson value of r = 0.8264. Students with higher G1 values generally have higher G3 values.

This chart is important because it proves that the strongest cross-correlation is not just a table value. The aligned data visually show a strong positive relationship without shifting the series.

Python Chart 5: Strongest Lag Aligned Scatterplot

Because the strongest lag is lag 0, this chart confirms the same aligned relationship shown in the lag 0 scatterplot. The strongest-lag view is useful because in another dataset the strongest lag might not be zero. Here, it reinforces the conclusion that the same-row alignment is the best alignment.

The practical interpretation is that shifting G1 or G3 does not improve the relationship. The strongest association occurs when the two series are compared directly in the same ordered position.

Python Chart 6: Top Lag Rankings

The top lag ranking chart places the strongest lags in order. Lag 0 is clearly first. Lag -2, lag +2, lag -3, lag +1 and lag -1 follow with much smaller absolute correlations. This ranking chart is useful for quickly summarizing the lag evidence.

The correct reporting decision is to highlight lag 0 as the main finding and describe other lags as secondary. The lagged correlations are not strong enough to replace the same-row interpretation.

R Chart-by-Chart Interpretation

The R charts validate the same cross-correlation result with colorful output. They repeat the ordered profile, cross-correlation function, lag strength, lag 0 scatterplot, strongest-lag scatterplot and top lag ranking.

R Chart 1: Colorful Ordered Series Profile

The R ordered profile confirms the same pattern as the Python chart. G1 and G3 are standardized and compared across row order. The visual co-movement supports a strong same-row relationship.

R Chart 2: Colorful Cross Correlation Function

The R cross-correlation function again shows the tallest spike at lag 0. This confirms that the strongest relationship is not a lead-lag shift but the same-row association between G1 and G3.

R Chart 3: Colorful Lag Direction Strength

The lag direction strength chart ranks lags by magnitude. Lag 0 is the strongest absolute result. The next lags are smaller, so the same-row interpretation remains the primary finding.

R Chart 4: Colorful Lag 0 Aligned Scatterplot

The R lag 0 scatterplot shows the strong upward relationship between G1 and G3. The reported aligned-pair Pearson value is approximately r = 0.8264. This confirms the same-row association visually.

R Chart 5: Colorful Strongest Lag Scatterplot

The strongest lag scatterplot is also based on lag 0. This is an important validation point: both the lag 0 chart and strongest-lag chart point to the same relationship, not to a delayed lead-lag effect.

R Chart 6: Colorful Top Lag Rankings

The R top lag ranking chart confirms the Python ranking. Lag 0 is the strongest, followed by smaller lagged correlations. The chart provides a clean summary for the final report section.

SPSS Output Interpretation

The SPSS output gives a formal same-row Pearson correlation context before the lagged cross-correlation interpretation. It confirms that the grade variables are strongly related in the original row alignment.

Open the SPSS Cross Correlation Output PDF

The SPSS output should be treated as a formal correlation context for the cross-correlation post. It helps readers understand why lag 0 is strong: G1 and G3 already have a strong same-row association.

Excel Worked File Explanation

The Excel workbook is a fully worked cross-correlation file. It is designed so students can replace the sample series with their own X and Y columns and watch the lag formulas update automatically.

Download the Cross Correlation Fully Worked Excel File

The Excel workbook is especially useful for teaching because it does not only give the final r value. It shows how aligned pairs change by lag, how each lag correlation is calculated, and how the strongest lag is identified.

Python, R, SPSS and Excel Workflows

Code Blocks and Excel Formulas

Python Code for Cross Correlation

import pandas as pd
import numpy as np
from scipy import stats

df = pd.read_csv("dataset.csv")

x_name = "G1"
y_name = "G3"
max_lag = 20

work = df[[x_name, y_name]].dropna().copy()
x = work[x_name].astype(float).to_numpy()
y = work[y_name].astype(float).to_numpy()

rows = []

for lag in range(-max_lag, max_lag + 1):
    if lag < 0:
        x_aligned = x[-lag:]
        y_aligned = y[:len(y) + lag]
        direction = f"{y_name} leads {x_name} by {abs(lag)} row(s)"
    elif lag > 0:
        x_aligned = x[:len(x) - lag]
        y_aligned = y[lag:]
        direction = f"{x_name} leads {y_name} by {lag} row(s)"
    else:
        x_aligned = x
        y_aligned = y
        direction = "Same-row / no lag correlation"

    n = len(x_aligned)
    r, p = stats.pearsonr(x_aligned, y_aligned)
    t_stat = r * np.sqrt((n - 2) / (1 - r**2))

    z = np.arctanh(r)
    se = 1 / np.sqrt(n - 3)
    ci_low, ci_high = np.tanh([z - 1.96 * se, z + 1.96 * se])
    approx_band = 1.96 / np.sqrt(n)

    rows.append({
        "lag": lag,
        "pair_n": n,
        "cross_correlation_r": r,
        "t_statistic": t_stat,
        "p_value_two_tailed": p,
        "ci95_lower": ci_low,
        "ci95_upper": ci_high,
        "approx_95_band": approx_band,
        "outside_approx_95_band": abs(r) > approx_band,
        "lag_direction_interpretation": direction,
        "absolute_correlation": abs(r)
    })

result = pd.DataFrame(rows)
result = result.sort_values("absolute_correlation", ascending=False)

print(result.head(10))
result.to_csv("cross_correlation_results.csv", index=False)

R Code for Cross Correlation

df <- read.csv("dataset.csv", stringsAsFactors = FALSE)

x_name <- "G1"
y_name <- "G3"
max_lag <- 20

work <- na.omit(df[, c(x_name, y_name)])
x <- as.numeric(work[[x_name]])
y <- as.numeric(work[[y_name]])

rows <- data.frame()

for(lag in -max_lag:max_lag){
  if(lag < 0){
    x_aligned <- x[(abs(lag) + 1):length(x)]
    y_aligned <- y[1:(length(y) + lag)]
    direction <- paste(y_name, "leads", x_name, "by", abs(lag), "row(s)")
  } else if(lag > 0){
    x_aligned <- x[1:(length(x) - lag)]
    y_aligned <- y[(lag + 1):length(y)]
    direction <- paste(x_name, "leads", y_name, "by", lag, "row(s)")
  } else {
    x_aligned <- x
    y_aligned <- y
    direction <- "Same-row / no lag correlation"
  }

  n <- length(x_aligned)
  test <- cor.test(x_aligned, y_aligned, method = "pearson")
  r <- unname(test$estimate)
  t_stat <- unname(test$statistic)
  p_value <- test$p.value

  z <- atanh(r)
  se <- 1 / sqrt(n - 3)
  ci <- tanh(c(z - 1.96 * se, z + 1.96 * se))
  approx_band <- 1.96 / sqrt(n)

  rows <- rbind(rows, data.frame(
    lag = lag,
    pair_n = n,
    cross_correlation_r = r,
    t_statistic = t_stat,
    p_value_two_tailed = p_value,
    ci95_lower = ci[1],
    ci95_upper = ci[2],
    approx_95_band = approx_band,
    outside_approx_95_band = abs(r) > approx_band,
    lag_direction_interpretation = direction,
    absolute_correlation = abs(r)
  ))
}

rows <- rows[order(-rows$absolute_correlation), ]
print(head(rows, 10))
write.csv(rows, "cross_correlation_results_r.csv", row.names = FALSE)

SPSS Syntax for Same-Row Context and Lagged Checks

* Cross Correlation context in SPSS.
OUTPUT CLOSE ALL.
OUTPUT NEW NAME=Cross_Correlation_Output.

* Same-row Pearson correlations before lagged checks.
CORRELATIONS
  /VARIABLES=G1 G2 G3 absences studytime failures
  /PRINT=TWOTAIL
  /MISSING=PAIRWISE.

* Example selected lag variables.
* Positive lag example: G1_t compared with later G3.
COMPUTE G3_lead1 = LAG(G3, -1).
COMPUTE G3_lead2 = LAG(G3, -2).

* Negative lag example: earlier G3 relative to G1.
COMPUTE G3_lag1 = LAG(G3, 1).
COMPUTE G3_lag2 = LAG(G3, 2).
EXECUTE.

CORRELATIONS
  /VARIABLES=G1 G3 G3_lead1 G3_lead2 G3_lag1 G3_lag2
  /PRINT=TWOTAIL
  /MISSING=PAIRWISE.

OUTPUT EXPORT
  /CONTENTS EXPORT=VISIBLE
  /PDF DOCUMENTFILE='Cross-Correlation-SPSS-Output.pdf'.

Excel Formula Patterns for Cross Correlation

Lagged cross-correlation:
=CORREL(aligned_X_range, aligned_Y_range)

t statistic:
=r*SQRT((n-2)/(1-r^2))

Two-tailed p-value:
=T.DIST.2T(ABS(t), n-2)

Fisher z:
=ATANH(r)

Fisher 95% CI lower:
=TANH(z - 1.96/SQRT(n-3))

Fisher 95% CI upper:
=TANH(z + 1.96/SQRT(n-3))

Approximate 95% band:
=1.96/SQRT(n)

Decision:
=IF(ABS(r)>approx_band,"Outside 95% band","Inside 95% band")

How to Report Cross Correlation

A good cross-correlation report should state the two series, lag convention, lag range, strongest lag, coefficient, p-value, confidence interval and whether the strongest result is same-row or lagged.

Common Mistakes in Cross Correlation

Downloads and Resources

External References

For software documentation, see the R documentation for acf/ccf, the statsmodels documentation for ccf, and the SciPy documentation for signal.correlate.

Related Statistical Guides