change date format with Fun in read.zoo

Question

I try to use this code to read multiple CSV file with read.zoo but I have a problem. the direction is different so I used file.path(). The problem is that: Date.Time into original file has Date format= "%m/%d %HH:%MM:%SS" and I need to add "%Y/" before or inside read.zoo() function. How could I do this? I try to use sub function but I need an argument. which? Is it possible to bypass this problem?

library(zoo)
for(i in Dir1){
   filepath <- file.path(paste(i,".csv",sep=""))
   #f<-function(x) sub("([[:print:]]{15})$", "2005/\\1",x)
   dat<-read.zoo(filepath,header=TRUE,sep=",",
                 FUN = function(x) sub("([[:print:]]{15})$", "2005/\\1"))
   filenames <- substr(filepath,122,155)
   names <-substr(filenames,1,21)
   assign(names, dat)
}

   Some of my data:
   Date.time,Outdoor Dry Bulb [C],Outdoor Relative Humidity [%],Air Temperature [C],Surface Temperature [C]
   01/01  00:03:00,0.0,50.,23.,16.3588068633603
   01/01  00:06:00,0.0,50.,23.,16.1696661072302
   01/01  00:09:00,0.0,50.,23.,13.8864861630478 
   01/01  00:12:00,0.0,50.,23.,13.006618496734
   01/01  00:15:00,0.0,50.,23.,12.5542552024807 
   01/01  00:18:00,0.0,50.,23.,11.6201669301972

Answer 1

I think you want to think of this from an R point of view and paste() the strings together rather than use a regular expression, which is overkill here. For example::

f <- function(x, format) {
    as.POSIXct(paste0("2005/", as.character(x)), format = format)
}

Then call as:

read.zoo(filepath, format = "%Y/%m/%d %H:%M:%S", header=TRUE,
         sep=",", FUN = f)

Explanation of f()

To explain f(), notice that read.zoo() will pass FUN the index.column column from the file read in. This will be a character vector or a factor (depending on the setting used) containing the data from your file. So we can coerce to character via as.character(x) (x is what we use to refer to the data from the index.column). Next we paste the string "2005/" on to the data from the index.column column of the file x. I use paste0() for this as we want to paste(...., sep = "") and this is a fast way of doing it.

The final stage is to convert the resulting character vector into an appropriate DateTime class for R. I use as.POSIXct() for this. read.zoo() ensures that the format argument is passed to FUN so we have it available for using our f(), and we pass that to the format argument of the as.POSIXct() function so that it understands the structure of the date-time information we are passing it.

Example Usage

Here is an example:

set.seed(1)
dat <- data.frame(Index = seq(Sys.time(), by = "2 hours", length = 50),
                  Data  = rnorm(50))
dat <- transform(dat, Index = strftime(Index, format = "%m/%d %H:%M:%S"))
head(dat)

> head(dat)
           Index       Data
1 10/17 12:53:35 -0.6264538
2 10/17 14:53:35  0.1836433
3 10/17 16:53:35 -0.8356286
4 10/17 18:53:35  1.5952808
5 10/17 20:53:35  0.3295078
6 10/17 22:53:35 -0.8204684

## write to file
write.csv(dat, file = "mydata.csv", row.names = FALSE)

Ok now assume that mydata.csv is your data file, read it in using the read.zoo() function above and the user function f:

myzoo <- read.zoo("mydata.csv", format = "%Y/%m/%d %H:%M:%S",
                  header = TRUE, sep = ",", FUN = f)
head(myzoo)

which gives:

> head(myzoo)
2005-10-17 12:53:35 2005-10-17 14:53:35 2005-10-17 16:53:35 2005-10-17 18:53:35 
         -0.6264538           0.1836433          -0.8356286           1.5952808 
2005-10-17 20:53:35 2005-10-17 22:53:35 
          0.3295078          -0.8204684