I had a .csv file containing highfrequency data for SIZ5(silver futures) and I am trying to bring it to an xts object so I can use some of the functions in the "highfrequency" package.
I loaded the data on R using the read.csv function. After doing all the necessary fixes of removing Nas and changing the time format.
f<-read.csv(file = "SIZ5_20150918_ob2.csv",header = FALSE,sep = "",col.names = c("DateTime","Seq","BP","BQ","BO","AP","AQ","AO","BP1","BQ1","BO1","AP1","AQ1","AO1","BP2","BQ2","BO2","AP2","AQ2","AO2","BP3","BQ3","BO3","AP3","AQ3","AO3","BP4","BQ4","BO4","AP4","AQ4","AO4","BP5","BQ5","BO5","AP5","AQ5","AO5","BP6","BQ6","BO6","AP6","AQ6","AO6","BP7","BQ7","BO7","AP7","AQ7","AO7","BP8","BQ8","BO8","AP8","AQ8","AO8","BP9","BQ9","BO9","AP9","AQ9","AO9"),nrows = 600000)
f$DateTime=as.POSIXct(f$DateTime/(10^9), origin="1970-01-01") #timestamp conversion
good1<-complete.cases(f)
f2<-f[good1,]
My result is:
DateTime Seq BP BQ BO AP AQ AO BP1 BQ1 BO1 AP1 AQ1 AO1 BP2 BQ2 BO2 AP2 AQ2 AO2 BP3 BQ3 BO3 AP3 AQ3 AO3 BP4 BQ4 BO4 AP4
1 2015-09-17 22:00:00 11539422 15110 1 1 15120 4 3 15105 1 1 15125 17 2 15100 4 3 15130 8 2 15095 7 6 15135 3 1 15090 33 3 15140
2 2015-09-17 22:00:00 11539422 15110 1 1 15120 4 3 15105 1 1 15125 17 2 15100 4 3 15130 8 2 15095 7 6 15135 3 1 15090 33 3 15140
3 2015-09-17 22:00:00 11539422 15110 1 1 15120 2 2 15105 1 1 15125 17 2 15100 4 3 15130 8 2 15095 7 6 15135 3 1 15090 33 3 15140
4 2015-09-17 22:00:00 11539479 15115 1 1 15120 2 2 15110 1 1 15125 17 2 15105 1 1 15130 8 2 15100 4 3 15135 3 1 15095 7 6 15140
5 2015-09-17 22:00:00 11539480 15115 2 2 15120 2 2 15110 1 1 15125 17 2 15105 1 1 15130 8 2 15100 4 3 15135 3 1 15095 7 6 15140
6 2015-09-17 22:00:00 11539482 15115 2 2 15120 2 2 15110 1 1 15125 17 2 15105 1 1 15130 8 2 15100 4 3 15135 3 1 15095 7 6 15140
*I have ommited few columns just to be concise
Then:
> as.xts(f2)
Error in as.POSIXlt.character(x, tz, ...) :
character string is not in a standard unambiguous format
So I tried:
fxts<-xts(f2[,-1],order.by = f2[,1])
But this is what I get:
Seq BP BQ BO AP AQ AO BP1 BQ1 BO1 AP1 AQ1 AO1 BP2 BQ2 BO2 AP2 AQ2 AO2 BP3 BQ3 BO3 AP3 AQ3 AO3 BP4 BQ4 BO4 AP4 AQ4 AO4
1970-01-01 12247553 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1970-01-01 12247553 15025 1 1 15030 11 9 15020 10 10 15035 19 18 15015 18 17 15040 22 19 15010 27 21 15045 23 17 15005 14 13 15050 36 28
1970-01-01 12836196 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1970-01-01 12836196 15240 1 1 15245 6 5 15235 12 11 15250 11 11 15230 10 9 15255 13 12 15225 12 11 15260 18 16 15220 13 12 15265 16 13
1970-01-01 14167856 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1970-01-01 14167856 15225 11 9 15230 4 4 15220 19 19 15235 14 14 15215 32 26 15240 27 22 15210 29 25 15245 21 17 15205 24 19 15250 28 22
*again ommitted last few colums
First, the time column doesnt have a header and then all of them are 1970-01-01(origin) without any time associated with it. Also a bunch of 0s where they shouldnt be. I also tried converting it first to a dataframe using as.data.frame and then converting to xts but I get a similar result as above.
edit: I just realised that it completely ignores the "Seq" column and is pasting the end of the timestamp(nanoseconds) in the "Seq" column. Is it reading the row nos. as the time stamp in seconds and hence only showing the origin in all of them?
Am I missing something? Please help. Let me know if I need to put up any more information.
as requested:
str(f2)
'data.frame': 494140 obs. of 62 variables:
$ DateTime: POSIXct, format: "2015-09-17 22:00:00" "2015-09-17 22:00:00" "2015-09-17 22:00:00" "2015-09-17 22:00:00" ...
$ Seq : int 11539422 11539422 11539422 11539479 11539480 11539482 11539555 11539556 11539565 11539565 ...
$ BP : int 15110 15110 15110 15115 15115 15115 15115 15115 15115 15115 ...
$ BQ : int 1 1 1 1 2 2 2 2 2 2 ...
$ BO : int 1 1 1 1 2 2 2 2 2 2 ...
$ AP : int 15120 15120 15120 15120 15120 15120 15120 15120 15120 15120 ...
$ AQ : int 4 4 2 2 2 2 2 2 2 2 ...
$ AO : int 3 3 2 2 2 2 2 2 2 2 ...
$ BP1 : int 15105 15105 15105 15110 15110 15110 15110 15110 15110 15110 ...
$ BQ1 : int 1 1 1 1 1 1 1 1 1 1 ...
$ BO1 : int 1 1 1 1 1 1 1 1 1 1 ...
$ AP1 : int 15125 15125 15125 15125 15125 15125 15125 15125 15125 15125 ...
$ AQ1 : int 17 17 17 17 17 17 17 17 17 17 ...
$ AO1 : int 2 2 2 2 2 2 2 2 2 2 ...
$ BP2 : int 15100 15100 15100 15105 15105 15105 15105 15105 15105 15105 ...
$ BQ2 : int 4 4 4 1 1 1 1 1 1 1 ...
$ BO2 : int 3 3 3 1 1 1 1 1 1 1 ...
$ AP2 : int 15130 15130 15130 15130 15130 15130 15130 15130 15130 15130 ...
$ AQ2 : int 8 8 8 8 8 8 8 8 8 8 ...
$ AO2 : int 2 2 2 2 2 2 2 2 2 2 ...
$ BP3 : int 15095 15095 15095 15100 15100 15100 15100 15100 15100 15100 ...
$ BQ3 : int 7 7 7 4 4 4 4 4 4 4 ...
$ BO3 : int 6 6 6 3 3 3 3 3 3 3 ...
$ AP3 : int 15135 15135 15135 15135 15135 15135 15135 15135 15135 15135 ...
$ AQ3 : int 3 3 3 3 3 3 3 4 4 4 ...
$ AO3 : int 1 1 1 1 1 1 1 2 2 2 ...
$ BP4 : int 15090 15090 15090 15095 15095 15095 15095 15095 15095 15095 ...
$ BQ4 : int 33 33 33 7 7 7 7 7 7 7 ...
$ BO4 : int 3 3 3 6 6 6 6 6 6 6 ...
$ AP4 : int 15140 15140 15140 15140 15140 15140 15140 15140 15140 15140 ...
$ AQ4 : int 3 3 3 3 3 3 3 3 3 3 ...
$ AO4 : int 1 1 1 1 1 1 1 1 1 1 ...
$ BP5 : int 15085 15085 15085 15090 15090 15090 15090 15090 15090 15090 ...
$ BQ5 : int 1 1 1 33 33 33 33 33 33 33 ...
$ BO5 : int 1 1 1 3 3 3 3 3 3 3 ...
$ AP5 : int 15145 15145 15145 15145 15145 15145 15145 15145 15145 15145 ...
$ AQ5 : int 4 4 4 4 4 4 4 4 4 4 ...
$ AO5 : int 4 4 4 4 4 4 4 4 4 4 ...
$ BP6 : int 15080 15080 15080 15085 15085 15085 15085 15085 15085 15085 ...
$ BQ6 : int 2 2 2 1 1 1 1 1 2 2 ...
$ BO6 : int 1 1 1 1 1 1 1 1 2 2 ...
$ AP6 : int 15150 15150 15150 15150 15150 15150 15150 15150 15150 15150 ...
$ AQ6 : int 2 2 2 2 2 2 2 2 2 2 ...
$ AO6 : int 2 2 2 2 2 2 2 2 2 2 ...
$ BP7 : int 15075 15075 15075 15080 15080 15080 15080 15080 15080 15080 ...
$ BQ7 : int 2 2 2 2 2 2 2 2 2 2 ...
$ BO7 : int 2 2 2 1 1 1 1 1 1 1 ...
$ AP7 : int 15160 15160 15160 15160 15160 15160 15160 15160 15160 15160 ...
$ AQ7 : int 2 2 2 2 2 2 2 2 2 2 ...
$ AO7 : int 2 2 2 2 2 2 2 2 2 2 ...
$ BP8 : int 15070 15070 15070 15075 15075 15075 15075 15075 15075 15075 ...
$ BQ8 : int 1 1 1 2 2 2 3 3 3 2 ...
$ BO8 : int 1 1 1 2 2 2 3 3 3 2 ...
$ AP8 : int 15165 15165 15165 15165 15165 15165 15165 15165 15165 15165 ...
$ AQ8 : int 3 3 3 3 3 3 3 3 3 3 ...
$ AO8 : int 2 2 2 2 2 2 2 2 2 2 ...
$ BP9 : int 0 15060 15060 15070 15070 15070 15070 15070 15070 15070 ...
$ BQ9 : int 0 3 3 1 1 1 1 1 1 1 ...
$ BO9 : int 0 3 3 1 1 1 1 1 1 1 ...
$ AP9 : int 15170 15170 15170 15170 15170 15170 15170 15170 15170 15170 ...
$ AQ9 : int 6 6 6 6 6 7 7 7 7 7 ...
$ AO9 : int 1 1 1 1 1 2 2 2 2 2 ...
EDIT(25/11/15):
midpoint.agg<- function(file, skip = 0, lines = 50000, on = "minutes", k = 5){
whole_data<- read.csv(file = file,header = FALSE,sep = "",col.names = c("DateTime","Seq","BP","BQ","BO","AP","AQ","AO","BP1","BQ1","BO1","AP1","AQ1","AO1","BP2","BQ2","BO2","AP2","AQ2","AO2","BP3","BQ3","BO3","AP3","AQ3","AO3","BP4","BQ4","BO4","AP4","AQ4","AO4","BP5","BQ5","BO5","AP5","AQ5","AO5","BP6","BQ6","BO6","AP6","AQ6","AO6","BP7","BQ7","BO7","AP7","AQ7","AO7","BP8","BQ8","BO8","AP8","AQ8","AO8","BP9","BQ9","BO9","AP9","AQ9","AO9"),nrows = lines,skip = skip)
whole_data$DateTime= as.POSIXct(whole_data$DateTime/(10^9), origin="1970-01-01") #timestamp conversion
completecase<- whole_data[complete.cases(whole_data),]
p<- data.frame(DateTime=completecase$DateTime, BP=completecase$BP, AP=completecase$AP, MP=(completecase$BP+completecase$AP)/2) #midpoint dataframe
my_xts<- as.xts(x = p$MP,order.by = p$DateTime)
myagg<-aggregatets(my_xts, on = on, k = k)
myagg
}
Link to file : https://drive.google.com/folderview?id=0BwwnLqZcphbEb09qa0hzVUlQdGs&usp=sharing
So I am uploading the data file on google drive as requested. Ive changed my approach a little bit, in terms of how I come to the end but am still looking for the same ouput and am facing the same problem. And its even more weird now.
Like I said in my original post, I have a file of a days worth of high frequency data for SIZ5 but now instead of running my functions on the entire file I have split it up into different files with 50000rows of data for each file.(you will see in the data folder I upload). The files are named "split_aa.csv", "split_ab.csv" and so on.
When I run my midpoint.agg(given above) function for the first file. It runs perfectly and produces exactly what I want:
> a<- midpoint.agg("split_aa.csv")
> head(a)
ts
2015-09-18 03:35:00 15122.5
2015-09-18 03:40:00 15117.5
2015-09-18 03:45:00 15110.0
2015-09-18 03:50:00 15112.5
2015-09-18 03:55:00 15117.5
2015-09-18 04:00:00 15117.5
> length(a)
[1] 86
But then when I run it on the "split-ab.csv" for some reason the xts function reads the first few time stamps as the origin date of 1970/01/01 and then aggregates the data from there. Hence "b" contains 4808544 rows and is a 73.4mb xts object:
> b<- midpoint.agg("split_ab.csv")
> head(b)
ts
1970-01-01 05:35:00 15027.5
1970-01-01 05:40:00 15027.5
1970-01-01 05:45:00 15027.5
1970-01-01 05:50:00 15027.5
1970-01-01 05:55:00 15027.5
1970-01-01 06:00:00 15027.5
> length(b)
[1] 4808544
I am lost as to why this is happening. Its even more weird that it happens with a few files in the folder and not with the rest. I have pinpointed that the problem occurs at the as.xts function, when it tries to convert the "p" data frame to an xts object. The first few rows then read 1970-01-01 and then converges back to normality. I even tried splitting the data into 100,000 rows each instead of 50,000 and I still face the same problem. I suspected, like many people suggested, it might be a problem with the data but I cant seem to pinpoint where the problem is or if its even the data at all. A few more eyes on it would be of great help.
Please let me know if any additional information is needed.
It is not true that, "the xts function reads the first few time stamps as the origin date of 1970/01/01". xts orders all the timestamps in your file. If any of them are zero, they will be the first observations in the xts object.
As I suspected, the data in your CSV is not what you expect. In file "split_ab.csv", lines 23669 and 23670 have timestamps of 0.
1442558305629290858 12247553 15025 8 7 15030 5 3 15020 12 11 15035 16 16 15015 20 18 15040 21 18 15010 27 21 15045 24 18 15005 14 13 15050 36 28 15000 23 17 15055 21 20 14995 11 10 15060 24 23 14990 32 10 15065 16 14 14985 8 7 15070 13 10 14980 12 9 15075 15 13
0 12247553 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 12247553 15025 1 1 15030 11 9 15020 10 10 15035 19 18 15015 18 17 15040 22 19 15010 27 21 15045 23 17 15005 14 13 15050 36 28 15000 23 17 15055 21 20 14995 11 10 15060 24 23 14990 32 10 15065 16 14 14985 8 7 15070 13 10 14980 12 9 15075 15 13
1442558318615424792 12248063 15025 1 1 15030 11 9 15020 10 10 15035 19 18 15015 19 18 15040 22 19 15010 27 21 15045 23 17 15005 14 13 15050 36 28 15000 23 17 15055 21 20 14995 11 10 15060 24 23 14990 32 10 15065 16 14 14985 8 7 15070 13 10 14980 12 9 15075 15 13
You need to clean your data before converting to an xts object. I don't know enough about your data to help you decide what to do with rows that have a timestamp of zero.