3

My time series data looks like to 8/18/2012 11:18:00 PM for 6 month, how I can subset them monthly and average for a variable within a month? (using R)

Thank you so much

APC
  • 144,005
  • 19
  • 170
  • 281
Mary
  • 33
  • 4
  • @Mary welcome to SO. It is important that you provide a reproducible example otherwise all our answers are just a guess. You can(must maybe) read [this](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) for more information on how you can do this. – agstudy Mar 14 '13 at 17:01

2 Answers2

2

You can use xts package. First I generate your data. here I create a 6 month, half daily data. long data 

dat <- data.frame(date = as.POSIXct('8/18/2012 11:18:00',
                                    format='%m/%d/%Y %H:%M:%S') + 
                         seq(0,by = 60*60*12,length.out=365),
                  value = rnorm(365))

Then I create an xts object 

library(xts)
dat.xts <- xts(x= dat$value,order.by = dat$date)

Finaly I use the handy function apply.monthly equivalent to lapply to get something like this :

apply.monthly(dat.xts,mean)
2012-08-31 23:18:00  0.03415933
2012-09-30 23:18:00  0.02884122
2012-10-31 22:18:00 -0.27767240
2012-11-30 22:18:00 -0.15614567
2012-12-31 22:18:00 -0.02595911
2013-01-31 22:18:00 -0.23284335
2013-02-16 10:18:00  0.14537790
agstudy
  • 119,832
  • 17
  • 199
  • 261
  • cool. thanks. I looked for apply.hourly but I didnot find. Is there any way that I can get the average of data hourly and make a condition for it? (if the hourly average is less than 2, discard that amount in that specific hour from my data and get a new hourly average or even new monthly average? thanks – Mary Mar 15 '13 at 16:36
  • `apply.hourly` exist. you can call it once then you remove hours where the average > threhold.... – agstudy Mar 15 '13 at 17:06
1

You can format the dates and compute the averages with aggregate (thanks to @agstudy for the sample data):

aggregate(value~format(date,"%Y-%m"),dat,FUN=mean)
  format(date, "%Y-%m")       value
1               2012-08 -0.31409786
2               2012-09 -0.37585310
3               2012-10 -0.04552703
4               2012-11 -0.05726177
5               2012-12  0.04822608
6               2013-01  0.03412790
7               2013-02 -0.10157931
James
  • 65,548
  • 14
  • 155
  • 193
  • Thanks alot but does this function needs xts package? what is the value in it? when I use it, R gives error ! how I can modify date in this function through my data? – Mary Mar 15 '13 at 16:32
  • @Mary No, these functions come in the standard packages included with R. `value` is a variable in the data.frame specified in the example in agstudy's answer – James Mar 15 '13 at 20:20