I have data from Google Trends and Center for Disease control that I want to compare. I used regression analysis from Excel to find a curve of best fit. The original data is in this form: enter image description here

It was impossible to entirely model this using excel so I broke it down into two parts and found a curve of best fit for those two. enter image description here

The goal is to compare this data with another graph I made with Google Trends (same process, break it down and regression) and assess to what extent is it accurate to use search engine queries to predict flu seasons. If I want to find the graph of the derivative of the original data to look at rates of change, can I take the derivatives of the two smaller functions and compile them together?


Make two columns in Excel: A is CDC data, B is Google Trends data. Then you will make two new columns. In cell C2 put "=A2-A1" and copy/paste this equation down to one row past the data in column A. Similarly put "=B2-B1" in cell D2 and copy/paste it. Columns C and D are your rates of change. Next plot column C vs column D and fit a line to perform linear regression.

  • $\begingroup$ IN column E put =A1-B1. Plot column E as a line graph. $\endgroup$ – power Apr 16 '14 at 6:05
  • 1
    $\begingroup$ Variations of this theme are the application of a Savitzky-Golay filter which is used for smoothening (stats.stackexchange.com/questions/284114 ), although it can also determine a derivative. The filter is a polynomial fit analogue of a rolling/moving average, and you can use the information of the fit to determine a (smoothened) derivative. It is similar as the original poster suggestion to take the derivative of the two fitted curves, but now there are more fits (which solves the problem that, in OP's suggested solution, at the point where the two fits meet no derivative is defined). $\endgroup$ – Sextus Empiricus Oct 18 '17 at 11:53

I would suggest you store both graphs, as columns in excel. Make sure that the timestamps (rows) are properly aligned. Then, calculate the crosscorrelation between the two timeseries for plus/minus several lags - maybe one month or so for example.

If you see the crosscorrelation function peaking at a non-zero interval. Then, you can use a linear model to predict one of the timeseries with the help of the other. Thus, you might have predictive capabilities, as you say.

In order to establish if this is the case, you have to test if the Google search timeseries is leading the disease outbreak timeseries. If this is the case you can use the former to predict the latter. However, it might be that the opposite happens: the outbreaks lead the searches. Is that case, you can predict the searches by looking at the outbreaks, which is not very useful.

The advantage of this method is that you can see who leads who AND at what lag. Meaning. This is in contrast with merely fitting an first order autoregressive model on the differences of the two timeseries. The difference is that in the latter case, you will get statistical significance only if there is a leading/lagging effect with a lag of one. On the other hand, visually inspecting the crosscorrelation function will reveal effects across any lag interval.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.