Aggregating Data using R

Question

I'm trying to plot the data using R by reading my CSV file which contains some values which were logged on per-second basis. I would like R to aggregate the data to per-minute basis so that I can plot the per-minute data using plot(TIME,VALUE.). My CSV file contains something like this;

Store No.,Date,Time,Watt
33,2011/09/26,09:11:01,0.0599E+03
34,2011/09/26,09:11:02,0.0597E+03
35,2011/09/26,09:11:03,0.0598E+03
36,2011/09/26,09:11:04,0.0596E+03
37,2011/09/26,09:11:05,0.0593E+03
38,2011/09/26,09:11:06,0.0595E+03
39,2011/09/26,09:11:07,0.0595E+03
40,2011/09/26,09:11:08,0.0595E+03
41,2011/09/26,09:11:09,0.0591E+03

I'm having trouble aggregating the Time and Watt column on per-minute basis as I'm a newbie to R. Any help would be highly appreciated.

Answer 1

Assuming Store No. is irrelevant and changing the last three rows in the example data shown in the question to be 09:12:.. rather than 09:11:.. so we have at least two different minutes:

# create test data

Lines <- "Store No.,Date,Time,Watt
33,2011/09/26,09:11:01,0.0599E+03
34,2011/09/26,09:11:02,0.0597E+03
35,2011/09/26,09:11:03,0.0598E+03
36,2011/09/26,09:11:04,0.0596E+03
37,2011/09/26,09:11:05,0.0593E+03
38,2011/09/26,09:11:06,0.0595E+03
39,2011/09/26,09:12:07,0.0595E+03
40,2011/09/26,09:12:08,0.0595E+03
41,2011/09/26,09:12:09,0.0591E+03"
cat(Lines, "\n", file = "data.txt")

# read in aggregating at the same time

library(zoo)
library(chron)
z <- read.zoo("data.txt", header = TRUE, sep = ",", index = 2:3,
    FUN = paste, FUN2 = function(x) trunc(as.chron(x), "00:01:00"), 
    aggregate = mean)[, -1]

Here FUN is applied to the columns specified by index. It pastes them together and then FUN2 is applied to the result of FUN creating a chron date/time. Finally rows with the same values of FUN2 are then aggregated taking the mean of Watt giving:

> z
(09/26/11 09:11:00) (09/26/11 09:12:00) 
           59.63333            59.36667 

Depending on what is wanted the aggregate argument could be changed to aggregate = function(x) tail(x, 1) in place of the aggregate argument shown.

For more info and examples, load the zoo package and look in ?read.zoo, ?aggregate.zoo and vignette("zoo-read") as well as the other vignettes and help files.

UPDATE: Slight simplification by using the FUN2 argument. Not sure but that read.zoo argument may not have existed at the time this question was first answered.