2

I've been trying to figure this out for a few days now, and seem to be having difficulties doing a simple date column conversion in R.

I need to format the date and time before I am able to use the to.period command. I would like to format the mydata$Time column, and convert the entire series to 4hr OHLC bar.

Below is the code input;

> mydata = read.csv("AUDJPY.csv")
> colnames(mydata) <- c("Pair","Time","Bid","Ask")
> head(mydata)
     Pair                  Time    Bid    Ask
1 AUD/JPY 20140501 00:00:00.301 94.874 94.893
2 AUD/JPY 20140501 00:00:00.312 94.876 94.895
3 AUD/JPY 20140501 00:00:00.316 94.876 94.882
4 AUD/JPY 20140501 00:00:00.317 94.877 94.884
5 AUD/JPY 20140501 00:00:00.403 94.877 94.885
6 AUD/JPY 20140501 00:00:00.420 94.877 94.887
> mydata$Time <- strptime(as.character(mydata$Time),"%Y%m%d_%H:%M:%S.%f")
> head(mydata)
     Pair Time    Bid    Ask
1 AUD/JPY <NA> 94.874 94.893
2 AUD/JPY <NA> 94.876 94.895
3 AUD/JPY <NA> 94.876 94.882
4 AUD/JPY <NA> 94.877 94.884
5 AUD/JPY <NA> 94.877 94.885
6 AUD/JPY <NA> 94.877 94.887
> class(mydata$Time)
[1] "POSIXlt" "POSIXt" 
>

Please help!

Gavin Simpson
  • 170,508
  • 25
  • 396
  • 453
user3773444
  • 333
  • 3
  • 12

1 Answers1

4

For seconds with decimals, you want to use "%OS" rather than "%S". The latter ignores the value all together. And if your format has a space, you can just include a space in the format string.

Observe

x<-c("20140501 00:00:00.301","20140501 00:00:00.420")

a<-strptime(x,"%Y%m%d %H:%M:%OS");
a
# [1] "2014-05-01 00:00:00 EDT" "2014-05-01 00:00:00 EDT"
a$sec
# [1] 0.301 0.420

While R doesn't show the fractional defaults by default when you display the values of the date class, you can see they are being stored in the object itself.

MrFlick
  • 195,160
  • 17
  • 277
  • 295
  • I am trying to format the entire column as there are millions of rows in the column, and below are my results; > mydata = read.csv("AUDJPY.csv") > colnames(mydata) <- c("Pair","Time","Bid","Ask") > x<-c('mydata[2]') > a<-strptime(x,"%Y%m%d %H:%M:%OS") > head(mydata) Pair Time Bid Ask 1 AUD/JPY 20140501 00:00:00.301 94.874 94.893 2 AUD/JPY 20140501 00:00:00.312 94.876 94.895 3 AUD/JPY 20140501 00:00:00.316 94.876 94.882 > head(a) [1] NA > head(mydata[2]) Time 1 20140501 00:00:00.301 – user3773444 Jun 26 '14 at 01:11
  • Why did you do `x<-c('mydata[2]')`?? Just do `a<-strptime(as.character(mydata$Time),"%Y%m%d %H:%M:%OS")` – MrFlick Jun 26 '14 at 02:56