CODEWITHSUNDEEP
raggregate
RWJ
RWJ

Reputation: 389

aggregating monthly sums and then getting the mean of all monthly sums

The list below is sum of data corresponding to each month in a time series using the following snippet:

aggregate(data, by=list(Year=format(DateTime, "%Y"), Month=format(DateTime, "%m")), sum, na.rm=TRUE)

Year Month        x
1   1981    01 62426.43
2   1982    01 70328.87
3   1983    01 67516.34
4   1984    01 64454.00
5   1985    01 78801.46
6   1986    01 73865.18
7   1987    01 64224.96
8   1988    01 72362.39
9   1981    02 74835.16
10  1982    02 75275.58
11  1983    02 67457.39
12  1984    02 64981.99
13  1985    02 56490.10
14  1986    02 62759.89
15  1987    02 65144.44
16  1988    02 67704.67

This part is easy...but I am tripping up on trying to get an average of all the monthly sums for each month (i.e. one average of the sums for each month) If I do the following:

aggregate(data, by=list(Month=format(DateTime, "%m")), sum, na.rm=TRUE)

I just get a sum of all months in the time series, which is what i dont want. Can i achieve the desired result in one aggregate statement, or do I need more code...Any help would be appreciated.

Upvotes: 4

Views: 2737

Answers (2)

IRTFM
IRTFM

Reputation: 263471

You could also have done it with a single call to aggregate:

aggregate(data, 
          by=list(Year=format(DateTime, "%Y"), Month=format(DateTime, "%m")), 
          FUN= function(x){ sum(x, na.rm=TRUE)/sum(!is.na(x))}
           )

Upvotes: 5

James
James

Reputation: 66874

You can do it with 2 aggregate statements:

aggregate(x~Month, aggregate(data, by=list(Year=format(DateTime, "%Y"), Month=format(DateTime, "%m")), sum, na.rm=TRUE), mean)

Upvotes: 4

Related Questions