In a previous series of postings, I described a model that I developed to predict monthly electricity usage and expenditure for a condo association. I based my model on the average monthly temperature at a nearby NOAA weather station at Ronald Reagan Airport (DCA), because the results are reasonable and more importantly because I can actually obtain forecasts from NOAA up to a year out.

The small complication is that the NOAA forecasts cover three-month periods rather than single month: JFM (Jan-Feb-Mar), FMA (Feb-Mar-Apr), MAM (Mar-Apr-May), etc. So, in this posting, we’ll briefly describe how to turn a series of these overlapping three-month forecasts into a series of monthly approximations.

The first assumption we’ll make is that each three-month forecast is the average of three monthly forecasts. (My guess would be that NOAA is actually forecasting three-month periods.) If we had some random three-month average temperatures, we wouldn’t be able to figure out the numbers behind the averages, but in this case we have overlapping three-month forecasts, which corresponds to a three-month running average. Since most of the months’ values are thus reflected in three averages, we have enough information to pull the underlying values out in a principled manner.

Let’s work forwards from monthly values to a running average, to see how we would reverse the process. Say we had seven months of values and wanted to create a three-month running average. If we want each resulting value to reflect three months — no averages of only one or two months — we would multiply our monthly (column) vector with this matrix:

0.333 0.333 0.333 0.000 0.000 0.000 0.000
0.000 0.333 0.333 0.333 0.000 0.000 0.000
0.000 0.000 0.333 0.333 0.333 0.000 0.000
0.000 0.000 0.000 0.333 0.333 0.333 0.000
0.000 0.000 0.000 0.000 0.333 0.333 0.333

Which you can see will result in a five-value column vector where each value is the (rolling) average of three months. In order to reverse this process, we simply need to find the inverse of this matrix and multiply the running average vector. Of course, since the matrix is not square, we’ll have to use the pseudo-inverse.

Let’s run an example! Say that I had 13 three-month forecasts: SON (Sep-Oct-Nov), OND, through SON. These forecasts would actually cover a 15-month period from September of this year through November of next year. From NOAA, I retrieved the following forecast for DCA:

SON 2013        59.44
OND             49.92
NDJ             42.97
DJF             40.05
JFM 2014        42.42
FMA             48.90
MAM             56.79
AMJ             65.45
MJJ             72.26
JJA             75.99
JAS             74.56
ASO             68.17
SON             59.44

which in R would be:

> temps3 <- c(59.44, 49.92, 42.97, 40.05, 42.42, 48.90, 56.79,
65.45, 72.26, 75.99, 74.56, 68.17, 59.44)

In order to create and invert our averaging matrix, I’ll use the Matrix and matrixcalc libraries:

> library (Matrix)
> library (matrixcalc)

> avg <- as.matrix (band (matrix (1, nrow=15, ncol=15), -1, 1)[-c(1, 15),]) / 3

> round (avg, 2)
      [,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9] [,10] [,11] [,12] [,13] [,14] [,15]
 [1,] 0.33 0.33 0.33 0.00 0.00 0.00 0.00 0.00 0.00  0.00  0.00  0.00  0.00  0.00  0.00
 [2,] 0.00 0.33 0.33 0.33 0.00 0.00 0.00 0.00 0.00  0.00  0.00  0.00  0.00  0.00  0.00
 [3,] 0.00 0.00 0.33 0.33 0.33 0.00 0.00 0.00 0.00  0.00  0.00  0.00  0.00  0.00  0.00
 [4,] 0.00 0.00 0.00 0.33 0.33 0.33 0.00 0.00 0.00  0.00  0.00  0.00  0.00  0.00  0.00
 [5,] 0.00 0.00 0.00 0.00 0.33 0.33 0.33 0.00 0.00  0.00  0.00  0.00  0.00  0.00  0.00
 [6,] 0.00 0.00 0.00 0.00 0.00 0.33 0.33 0.33 0.00  0.00  0.00  0.00  0.00  0.00  0.00
 [7,] 0.00 0.00 0.00 0.00 0.00 0.00 0.33 0.33 0.33  0.00  0.00  0.00  0.00  0.00  0.00
 [8,] 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.33 0.33  0.33  0.00  0.00  0.00  0.00  0.00
 [9,] 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.33  0.33  0.33  0.00  0.00  0.00  0.00
[10,] 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00  0.33  0.33  0.33  0.00  0.00  0.00
[11,] 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00  0.00  0.33  0.33  0.33  0.00  0.00
[12,] 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00  0.00  0.00  0.33  0.33  0.33  0.00
[13,] 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00  0.00  0.00  0.00  0.33  0.33  0.33

> iavg <- svd.inverse (avg)

Now we’re ready to multiply our three-month rolling average by this inverse:

> temps1 <- c(t(temps3) %*% t(iavg)) 
> temps1
 [1] 67.39 59.02 51.91 38.83 38.17 43.15 45.94 57.61
     66.82 71.92 78.04 78.01 67.63 58.87 51.82
> mean (temps1[1:3])
[1] 59.44

Yep, looks like we reversed the process. A graph of the NOAA three-month averages (in pink) and the results of this process (in blue) are shown at the top of the posting. They’re pretty similar, of course, though the highs go higher and the lows go lower which is exactly what we’d expect since an average will pull extremes towards the mean.

Hope you find this helpful!