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!
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.