rdataframetime-seriesxtshft

R: converting data to an xts object


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.


Solution

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