aggregate data frame to typical year/week

Question

so i have a large data frame with a date time column of class POSIXct and a another column with price data of class numeric. the date time column has values of the form "1998-12-07 02:00:00 AEST" that are half hour observations across 20 years. a sample data set can be generated with the following code (vary the 100 to whatever number of observations are necessary):

data.frame(date.time = seq.POSIXt(as.POSIXct("1998-12-07 02:00:00 AEST"), as.POSIXct(Sys.Date()+1), by = "30 min")[1:100], price = rnorm(100))

i want to look at a typical year and typical week. so for the typical year i have the following code:

mean.year <- aggregate(df$price, by = list(format(df$date.time, "%m-%d %H:%M")), mean)

it seems to give me what i want:

      Group.1        x
1 01-01 00:00 31.86200
2 01-01 00:30 34.20526
3 01-01 01:00 28.40105
4 01-01 01:30 26.01684
5 01-01 02:00 23.68895
6 01-01 02:30 23.70632

however the column "Group.1" is of class character and i would like it to be of class POSIXct. how can i do this?

for the typical week i have the following code

mean.week <- aggregate(df$price, by = list(format(df$date.time, "%wday %H:%M")), mean)

the output is as follows

     Group.1        x
1 0day 00:00 33.05613
2 0day 00:30 30.92815
3 0day 01:00 29.26245
4 0day 01:30 29.47959
5 0day 02:00 29.18380
6 0day 02:30 25.99400

again, column "Group.1" is of class character and i would like POSIXct. also, i would like to have the day of the week as "Monday", "Tuesday", etc. instead of 0day. how would i do this?

You might already know that you should [provide example data](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/5963610#5963610) next to your code? — jay.sf, Dec 16 '18 at 08:39
I do not think `Group.1` can be POSIXct, since it does not contain a date-part... — Wimpel, Dec 16 '18 at 11:23

score 1 · Accepted Answer · answered Dec 16 '18 at 11:38

Convert the datetime to a character string that can validly be converted back to POSIXct and then do so:

mean.year <- aggregate(df["price"], 
  by = list(time = as.POSIXct(format(df$date.time, "2000-%m-%d %H:%M"))), mean)
head(mean.year)
##                  time       price
## 1 2000-12-07 02:00:00 -0.56047565
## 2 2000-12-07 02:30:00 -0.23017749
## 3 2000-12-07 03:00:00  1.55870831
## 4 2000-12-07 03:30:00  0.07050839
## 5 2000-12-07 04:00:00  0.12928774
## 6 2000-12-07 04:30:00  1.71506499

To get the day of the week use %a or %A -- see ?strptime for the list of percent codes.

mean.week <- aggregate(df["price"], 
  by = list(time = format(df$date.time, "%a %H:%M")), mean)
head(mean.week)
##        time       price
## 1 Mon 02:00 -0.56047565
## 2 Mon 02:30 -0.23017749
## 3 Mon 03:00  1.55870831
## 4 Mon 03:30  0.07050839
## 5 Mon 04:00  0.12928774
## 6 Mon 04:30  1.71506499

Note

The input df in reproducible form -- note that set.seed is needed to make it reproducible:

set.seed(123)
df <- data.frame(date.time = seq.POSIXt(as.POSIXct("1998-12-07 02:00:00 AEST"),
  as.POSIXct(Sys.Date()+1), by = "30 min")[1:100], price = rnorm(100))

aggregate data frame to typical year/week

1 Answers1

Note