R: Computing monthly averages from hourly data and

Question

After converting a date/time character string into POSIXlt using strptime, I am left with the following (data truncated for ease here):

         DateTime  North  South   West   East  Seast  System

1 2008-09-12 01:00:00 1919.9 3721.4 2085.9 2565.5 2571.1 12863.8

2 2008-09-12 02:00:00 1827.0 3518.1 1965.3 2396.9 2410.7 12118.0

3 2008-09-12 03:00:00 1755.4 3388.4 1866.8 2338.7 2335.2 11684.5

4 2008-09-12 04:00:00 1733.5 3327.1 1810.0 2295.6 2290.2 11456.4

5 2008-09-12 05:00:00 1742.7 3327.3 1831.4 2314.2 2302.3 11517.9

6 2008-09-12 06:00:00 1912.2 3504.4 1986.7 2515.0 2502.6 12420.9

I then have aggregated the data (seemingly right) into year-month averages using the following snippet of code: North_Monthly_Avg <- aggregate(North, list(Date=format(DateTime, "%Y-%m")),mean) which yields the following:

 Date        x

1 2008-09 2192.066

2 2008-10 1885.074

3 2008-11 1675.373

4 2008-12 1637.231

5 2009-01 1752.693

6 2009-02 1743.393

I can plot the 'x' values but cannot get the year-months to label properly on the x-axis since it is only plotting the index. Not sure what I am missing...I have played around with axis.POSIXct, but have no luck.

Answer 1

Try zoo and lattice:

library(zoo)
library(lattice)

dat <- 'Date  Time  North  South   West   East  Seast  System
2008-09-12 01:00:00 1919.9 3721.4 2085.9 2565.5 2571.1 12863.8
2008-09-12 02:00:00 1827.0 3518.1 1965.3 2396.9 2410.7 12118.0
2008-09-12 03:00:00 1755.4 3388.4 1866.8 2338.7 2335.2 11684.5
2008-09-12 04:00:00 1733.5 3327.1 1810.0 2295.6 2290.2 11456.4
2008-09-12 05:00:00 1742.7 3327.3 1831.4 2314.2 2302.3 11517.9
2008-09-12 06:00:00 1912.2 3504.4 1986.7 2515.0 2502.6 12420.9'

z <- read.zoo(text = dat,  header = TRUE,  index.column = 1:2, tz = "")
xyplot(z)

zAgg <- aggregate(z$North,  by = as.yearmon,  FUN = mean)

dat2 <- 'Date        x
2008-09 2192.066
2008-10 1885.074
2008-11 1675.373
2008-12 1637.231
2009-01 1752.693
2009-02 1743.393'

zAgg <- read.zoo(text = dat2, header = TRUE,  FUN = as.yearmon)

plot(zAgg, xaxt = "n")

tt <- time(zAgg)
m <- format(tt, "%m")
axis(side = 1, at = tt, labels = ifelse(m == "01", trunc(tt), m), cex.axis = .7)

Answer 2

Try using as.integer() on the date

North_Monthly_Avg <- aggregate(North, list(Date=as.integer(format(DateTime, "%Y-%m"))),mean)

Answer 3

The problem you're having is because you are using format to create the groupings to use for the subdivision. This makes the values into strings, so that plotting functions don't know to plot them like dates.

The cut function has a cut.POSIXlt variant that will do exactly what you need, and preserve the type information so that all the plotting stuff will just work.

Instead of

North_Monthly_Avg <- aggregate(North, list(Date=format(DateTime, "%Y-%m")),mean) 

Just use

North_Monthly_Avg <- aggregate(North, cut(DateTime, "month"), mean) 

Answer 4

I think the problem is that there is no date. You will have to settle with a 1st of the month or 15th of the month and apply that to your aggregated table.

I came up with this:

North_Monthly_Avg=aggregate(North,by=list(format(DateTime,'%Y-%m')),mean)
names(North_Monthly_Avg)=c('Month','North')
North_Monthly_Avg$day=15
North_Monthly_Avg$Date=paste(North_Monthly_Avg$Month,North_Monthly_Avg$day,sep='-')
North_Monthly_Avg$Date=strptime(North_Monthly_Avg$Date,'%Y-%m-%d')
plot(m$Date,m$North,xaxt='n') # the xaxt='n' removes any ticks on the x axis
axis(1,as.numeric(m$Date),labels=format(m$Date,'%Y-%m')) # formats the x axis to your liking

I am fairly new to R, so this may not be the most elegant solution, but it will work.

Replace the 15 with 1 in the $day line if you prefer 1st of the month and the sep in paste should be changed to '-0'.

Answer 5

You could can try the package openair and use it's function timeAverage

Hourly to monthly

library(openair)  
mydata$date <- as.POSIXct(strptime(mydata$date, format = "%d/%m/%Y %H:%M", tz = "GMT"))
hourly<-timeAverage(mydata, average.time = "day")

Answer 6

@user1062431, To edit the tick names to your preferred format, edit the m <- format(tt, "%m") line in the answer of Oscar.

To get the format 12 - 2008 you need to modify:

m <- format(tt, "%m") to m <- format(tt, "%m - %Y")

To get the format dec 2008 you need to modify:

m <- format(tt, "%m") to m <- format(tt, "%b %Y")