Obtaining the monthly averages across years

[palaremzi]

This sounds pretty simple, I know, but I could not figure out. The data looks like this
1991M01 100.00
1991M02 100.49
1991M03 100.73
......
2011M12 182.52

I want to get this:
Av_Jan = Average of all Jans 1991 till 2011
Av_Feb = Average of all Febs 1991 till 2011
...and so on

This can be done manually in Excel, but the data is much larger than just this column, so I need a better way of doing this.

Thanks in advance

[EViews Gareth]

You can create a series full of monthly averages with:

Code: Select all

Series monthavg = @ meansby(X, @month)

[TimGrun]

It would be nice if meansby could work with matrix columns or vectors.

Tim

[EViews Glenn]

How would you like that to work? Would you provide a vector of the same number of rows as the matrix? Would it return the set of means, or the means assigned to the rows of a new matrix according to the values of the vector? Would there be cases when both behaviors would be useful?

[TimGrun]

The resulting vector would have fewer rows (unless the meansby vector had only one value).

vector(10) large=100 200 50 150 300 500 200
vector(10) byvec=1 2 1 1 2 2 2
vector means = @meansby(large,byvec)
Then means= 100 300

[EViews Gareth]

That would require the user to know/figure out the order of the values in the source vector.

I imagine we'd have to at least return a matrix with the first column being the sort by values and the second column being means. i.e.:

Code: Select all

matrix means = @meansby(large, byvec) then means = 1 100 2 300

[EViews Glenn]

If the @meansby vector had only a single value, you'd still have to return a single element vector. You wouldn't want to return something completely different depending on the contents of the @meansby vector.

I'll also point out that your preferred routine is the first form of the command that I sketched out, which returns the set of means and not the matched set of values and as such differs from the @meansby in the series form. I would argue that in the matrix world, returning the set of means is probably the more useful approach, but as Gareth points out, there are issues with what is returned and how someone would work with the result. His suggestion isn't a bad one, but I still think more thought is required (I'm not crazy about the fact that @meansby would work differently on a series, but we could get over that by using a different set of function names).

One other possibility uses something that I think we should add which is a @unique function which returns a vector of sorted unique values. That would handle this case without having to do the extended matrix return (which is a bit inelegant).

This discussion is quite useful, but does point out something that often happens when we spec out routines; while we are in principle very much in favor of the general functionality, there are a surprisingly large number of issues that one has to consider when deciding how the actual routine should work (otherwise, we're likely to have implemented it already :))

Any other suggestions are most welcome. We'll put this on the list and give it some more thought.

[damian_iordanov]

Hello all, I have a question concerning an issue very similar to this case. I have data in the same format (monthly time series) and I would also like to calculate different statistics about all January data, all February data, all ... etc. Is it possible to rearrange the workfile to have rows (or columns) of data for every month per year.

Essentially I would like to have 12 series named Jan, Feb, Mar,..etc on an annual frequency which will display a column for every month and a row for every year.

Thanks in advance.

[EViews Gareth]

Code: Select all

create m 1990 2010 series y=nrnd series x=nrnd alpha month = @left(@datestr(@date, "Month"),3) + "_" pageunstack(namepat=?*) month @year pagestruct @date(id01)

You can remove the first three lines to run it on your own data.