1

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.

RWJ
  • 389
  • 2
  • 6
  • 10
  • Is the `Date` column a date class, or just a factor/character? – James Nov 23 '11 at 17:44
  • Date class (POSIXlt) after I converted it from factor – RWJ Nov 23 '11 at 17:58
  • Actually, I should say that 'DateTime' is a date class...'Date' in the aggregate output has a class of NULL...how do I get that into a date class? – RWJ Nov 23 '11 at 18:02
  • I think after using `format` it will become a character class again. To convert to a date class I think you will need to add a day to the string too. – James Nov 23 '11 at 18:10
  • Instead of using `format()` with aggregate, consider using `cut` (`cut.Date`) -- `cut(DateTime, "month")` will do exactly what you need. – user295691 Sep 10 '15 at 15:27

6 Answers6

3

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)
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
Oscar Perpiñán
  • 4,491
  • 17
  • 28
  • This is kind of what I already done but without zoo...even if i take your code i dont get plausible month-year tick marks concurrent with the data points – RWJ Nov 23 '11 at 19:19
  • @user1062431. Try converting the index to `Date` class for plotting or produce a custom axis. There is an example of using a custom axis in the help file `?xyplot.zoo` . Also see `?panel.axis` Another thing to try is classic graphics. Just replace `xyplot` with `plot`. – G. Grothendieck Nov 23 '11 at 22:49
0

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)
user295691
  • 7,108
  • 1
  • 26
  • 35
0

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")
Nick Reid
  • 63
  • 1
  • 4
0

Try using as.integer() on the date

North_Monthly_Avg <- aggregate(North, list(Date=as.integer(format(DateTime, "%Y-%m"))),mean)
abcde123483
  • 3,885
  • 4
  • 41
  • 41
  • When I try that I get the following: Warning message: In aggregate.data.frame(as.data.frame(x), ...) : NAs introduced by coercion – RWJ Nov 23 '11 at 18:11
0

@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")

Mischa Vreeburg
  • 1,576
  • 1
  • 13
  • 18
0

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'.

thequerist
  • 1,774
  • 3
  • 19
  • 27