-2
Date    Timestamp   Open    High    Low Close   Volume
1   20131020    22:00:00    1.61730 1.61730 1.61727 1.61727 0.30
2   20131020    22:01:00    1.61722 1.61727 1.61686 1.61686 23.28
3   20131020    22:02:00    1.61682 1.61707 1.61670 1.61698 41.77
4   20131020    22:03:00    1.61695 1.61701 1.61695 1.61695 9.41
5   20131020    22:04:00    1.61680 1.61680 1.61680 1.61680 4.62
6   20131020    22:05:00    1.61682 1.61698 1.61682 1.61698 2.13
7   20131020    22:06:00    1.61684 1.61706 1.61684 1.61706 9.64
8   20131020    22:07:00    1.61701 1.61701 1.61683 1.61686 7.01
9   20131020    22:08:00    1.61692 1.61693 1.61686 1.61686 6.00
10  20131020    22:09:00    1.61686 1.61692 1.61670 1.61692 3.28
11  20131020    22:10:00    1.61683 1.61683 1.61681 1.61681 2.00
12  20131020    22:11:00    1.61687 1.61687 1.61662 1.61681 3.03
13  20131020    22:12:00    1.61664 1.61688 1.61664 1.61688 5.78
14  20131020    22:13:00    1.61688 1.61695 1.61688 1.61695 8.93

As you can see this is a snippet of the dataframe that I have in R. How do I combine the first two columns and convert the data into xts format so I can analyse the time series?

Joshua Ulrich
  • 173,410
  • 32
  • 338
  • 418
shellsnail
  • 3
  • 1

2 Answers2

2

1) read.zoo in the zoo package makes this fairly easy. index = 1:2 tells it to use columns 1 and 2 as the index. The tz = "" is enough to let it know that you intend a POSIXct index. (In the development version of zoo tz = "" could be omitted and it will still figure it out.)

library(xts)

z <- read.zoo(DF, index = 1:2, tz = "", format = "%Y%m%d %H:%M:%S")
as.xts(z)

2) To get a chron class index instead pass it a custom FUN:

library(chron)

FUN = function(d, t) as.chron(paste(d, t), format = "%Y%m%d %H:%M:%S")
z <- read.zoo(DF, index = 1:2, FUN = FUN)
as.xts(z)

3) Since the first column is the same in all rows if you just wanted chron times:

library(chron)

z <- read.zoo(DF[-1], FUN = times)
as.xts(z)

Note: To get further help on tihs try: ?read.zoo . Also note that this type of question is so common that there is an entire vignette just on using read.zoo in the zoo package: vignette("zoo-read")

Also note that read.zoo can read directly from a file so if DF is the result of read.table you could get rid of that and just use read.zoo.

G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
1

Try

indx <- as.POSIXct(paste(df[,1], df[,2]), format="%Y%m%d %H:%M:%S")
nm1 <- c("Date", "Timestamp")
library(xts)
xt1 <- xts(df[setdiff(colnames(df), nm1)], order.by=indx)
head(xt1,3)
                         Open    High     Low   Close Volume
 #2013-10-20 22:00:00 1.61730 1.61730 1.61727 1.61727   0.30
 #2013-10-20 22:01:00 1.61722 1.61727 1.61686 1.61686  23.28
 #2013-10-20 22:02:00 1.61682 1.61707 1.61670 1.61698  41.77

data

df <- structure(list(Date = c(20131020L, 20131020L, 20131020L, 20131020L, 
 20131020L, 20131020L, 20131020L, 20131020L, 20131020L, 20131020L, 
20131020L, 20131020L, 20131020L, 20131020L), Timestamp = c("22:00:00", 
"22:01:00", "22:02:00", "22:03:00", "22:04:00", "22:05:00", "22:06:00", 
"22:07:00", "22:08:00", "22:09:00", "22:10:00", "22:11:00", "22:12:00", 
"22:13:00"), Open = c(1.6173, 1.61722, 1.61682, 1.61695, 1.6168, 
1.61682, 1.61684, 1.61701, 1.61692, 1.61686, 1.61683, 1.61687, 
1.61664, 1.61688), High = c(1.6173, 1.61727, 1.61707, 1.61701, 
1.6168, 1.61698, 1.61706, 1.61701, 1.61693, 1.61692, 1.61683, 
1.61687, 1.61688, 1.61695), Low = c(1.61727, 1.61686, 1.6167, 
1.61695, 1.6168, 1.61682, 1.61684, 1.61683, 1.61686, 1.6167, 
1.61681, 1.61662, 1.61664, 1.61688), Close = c(1.61727, 1.61686, 
1.61698, 1.61695, 1.6168, 1.61698, 1.61706, 1.61686, 1.61686, 
1.61692, 1.61681, 1.61681, 1.61688, 1.61695), Volume = c(0.3, 
23.28, 41.77, 9.41, 4.62, 2.13, 9.64, 7.01, 6, 3.28, 2, 3.03, 
5.78, 8.93)), .Names = c("Date", "Timestamp", "Open", "High", 
"Low", "Close", "Volume"), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", 
"14"))
akrun
  • 874,273
  • 37
  • 540
  • 662