R attach dates to time series

Question

I have a spreadsheet in excel which consists of first row of dates and then subsequent columns that refer to prices of different securities on those dates.

I saved the excel file as a csv and then imported to excel using

prices=read.csv(file="C:/Documents and Settings/Hugh/My Documents/PhD/Option prices.csv",header = TRUE, sep = ",")

This creates the correct time series data

x<-ts(prices[,2])

but does not have the dates attached.

However the dates refer to working days. So although in general they represent Monday-Friday this is not always the case because of holidays etc.

How then can I create a time series where the dates are read in from the first column of the csv file? I can not find an example in R where this is done

Answer 1

As you didn't give any data, here is a made-up data.frame:

R> DF <- data.frame(date="2011-05-15", time=c("08:25:00", "08:45:00", 
+                   "09:05:11"), val=rnorm(3, 100, 5))
R> DF
        date     time     val
1 2011-05-15 08:25:00 99.5926
2 2011-05-15 08:45:00 95.8724
3 2011-05-15 09:05:11 96.6436
R> DF <- within(DF, posix <- as.POSIXct(paste(date, time)))
R> DF
        date     time     val               posix
1 2011-05-15 08:25:00 99.5926 2011-05-15 08:25:00
2 2011-05-15 08:45:00 95.8724 2011-05-15 08:45:00
3 2011-05-15 09:05:11 96.6436 2011-05-15 09:05:11
R> 

I used within(), you can use other means to in order to assign new columns. The key is that paste() allows you to combine columns, and you could use other R functions to modify the data as needed.

The key advantage of having dates and times parsed in a suitable type (like POSIXct) is that other functions can then use it. Here is zoo:

R> z <- with(DF, zoo(val, order.by=posix))
R> summary(z)
     Index                              z       
 Min.   :2011-05-15 08:25:00.00   Min.   :95.9  
 1st Qu.:2011-05-15 08:35:00.00   1st Qu.:96.3  
 Median :2011-05-15 08:45:00.00   Median :96.6  
 Mean   :2011-05-15 08:45:03.67   Mean   :97.4  
 3rd Qu.:2011-05-15 08:55:05.50   3rd Qu.:98.1  
 Max.   :2011-05-15 09:05:11.00   Max.   :99.6  
R>