How do I convert following raw data to zoo or xts timestamp?

Question

My raw data in a CSV-file looks like this, i.e. the date-time format is %Y%m%d, the letter "T", followed by %H%M%S:

20151230T090029, 33.04
20151230T090029, 33.06
20151230T090029, 33.07
20151230T090029, 33.05
20151230T090029, 33.04
20151230T090029, 33.05
20151230T090029, 33.04

How can I make this first column a time-index in zoo or xts?

Perhaps use `read.zoo()`, setting its `format=` argument to `format="%Y%m%dT%H%M%S"`. — Josh O'Brien, Jan 04 '16 at 19:33
Note that zoo does not support series with multiple identical indexes. — G. Grothendieck, Jan 04 '16 at 20:53
@JoshO'Brien I tried what u suggested, but then everything after the T disappears, so I'm left with the date only.. — cJc, Jan 05 '16 at 09:34
@ChrisJ Hmm. Worked for me when I tried it, just as it seems to work in Spacedman's answer below. In any case, looks like you've got an acceptable answer with that. — Josh O'Brien, Jan 05 '16 at 16:17

score 2 · Answer 1 · answered Jan 05 '16 at 10:36

Given your data as d:

> d
               V1    V2
1 20151230T090029 33.04
2 20151230T090029 33.06
3 20151230T090029 33.07
4 20151230T090029 33.05
5 20151230T090029 33.04
6 20151230T090029 33.05
7 20151230T090029 33.04

Then conversion to POSIX time classes can be done using the format string given in comments:

> as.POSIXct(d$V1,format="%Y%m%dT%H%M%S")
[1] "2015-12-30 09:00:29 GMT" "2015-12-30 09:00:29 GMT"
[3] "2015-12-30 09:00:29 GMT" "2015-12-30 09:00:29 GMT"
[5] "2015-12-30 09:00:29 GMT" "2015-12-30 09:00:29 GMT"
[7] "2015-12-30 09:00:29 GMT"

And a zoo object constructed:

> zoo(d$V2, as.POSIXct(d$V1,format="%Y%m%dT%H%M%S"))
2015-12-30 09:00:29 2015-12-30 09:00:29 2015-12-30 09:00:29 2015-12-30 09:00:29 
              33.04               33.06               33.07               33.05 
2015-12-30 09:00:29 2015-12-30 09:00:29 2015-12-30 09:00:29 
              33.04               33.05               33.04 
Warning message:
In zoo(d$V2, as.POSIXct(d$V1, format = "%Y%m%dT%H%M%S")) :
  some methods for “zoo” objects do not work if the index entries in ‘order.by’ are not unique

with that warning because all the time points are the same.

score 2 · Accepted Answer · edited May 23 '17 at 11:59

As Josh O'Brien suggested, you can do this with read.zoo:

library(zoo)
Lines <- "20151230T090029, 33.04
20151230T090029, 33.06
20151230T090029, 33.07
20151230T090029, 33.05
20151230T090029, 33.04
20151230T090029, 33.05
20151230T090029, 33.04"
z <- read.zoo(text=Lines, sep=",", FUN=as.POSIXct, format="%Y%m%dT%H%M%S")

Then you could deal with the identical timestamp issue Gabor mentioned by converting to xts and using xts::make.index.unique.

library(xts)
x <- as.xts(z)
options(digits.secs=3)
(u <- make.index.unique(x, 0.001))
#                          [,1]
# 2015-12-30 09:00:29.000 33.04
# 2015-12-30 09:00:29.000 33.06
# 2015-12-30 09:00:29.001 33.07
# 2015-12-30 09:00:29.002 33.05
# 2015-12-30 09:00:29.003 33.04
# 2015-12-30 09:00:29.004 33.05
# 2015-12-30 09:00:29.005 33.04

See How R formats POSIXct with fractional seconds for why the fractional seconds print in a way that makes them look incorrect.

How do I convert following raw data to zoo or xts timestamp?

2 Answers2