Hello,
I have a data.frame of a time series sampled every 15 minutes. Also the
data are in reverse order (recent to older). It looks like the following:
Date 0:00 0:15 0:30 0:45 1:00 1:15 1:30 1:45 2:00 2:15 2:30 2:45 3:00 3:15
3:30 3:45 4:00 4:15 4:30 4:45 5:00 5:15 5:30 5:45 6:00 6:15 6:30 6:45 7:00
7:15 7:30 7:45 8:00 8:15 8:30 8:45 9:00 9:15 9:30 9:45 10:00 10:15 10:30
10:45 11:00 11:15 11:30 11:45 12:00 12:15 12:30 12:45 13:00 13:15 13:30
13:45 14:00 14:15 14:30 14:45 15:00 15:15 15:30 15:45 16:00 16:15 16:30
16:45 17:00 17:15 17:30 17:45 18:00 18:15 18:30 18:45 19:00 19:15 19:30
19:45 20:00 20:15 20:30 20:45 21:00 21:15 21:30 21:45 22:00 22:15 22:30
22:45 23:00 23:15 23:30 23:45 03/04/14 403 405 397 439 427 496 437 375 339
366 371 339 313 264 219 266 325 331 357 354 334 322 347 309 285 430 603 716
764 756 638 571 549
02/04/14 46 63 58 58 60 78 69 73 102 105 105 121 147 155 133 126 143 148
182 164 175 188 200 190 295 240 201 211 174 165 177 219 233 195 158 103 61
23 36 30 27 52 127 178 282 340 542 701 764 493 522 414 459 320 388 407 395
226 317 425 253 162 78 90 93 42 56 58 41 87 118 85 88 65 53 59 101 135 161
164 170 223 239 237 259 284 286 300 307 326 361 400 404 391 398 407
01/04/14 604 727 673 644 544 474 344 363 336 326 441 550 379 278 222 241 269
227 267 235 256 296 344 404 431 448 433 419 421 378 328 358 313 294 315 278
240 182 138 105 78 45 21 16 16 14 10 10 15 16 22 36 33 28 36 44 41 67 76 67
45 43 49 59 76 84 87 69 69 50 52 66 67 52 27 17 18 26 21 23 31 41 60 75 77
81 93 109 99 91 84 59 39 35 43 43 01/04/14 604 727 673 644 544 474 344 363
336 326 441 550 379 278 222 241 269 227 267 235 256 296 344 404 431 448 433
419 421 378 328 358 313 294 315 278 240 182 138 105 78 45 21 16 16 14 10 10
15 16 22 36 33 28 36
4 1 0 0 2 2 0 0 0 1 3 4 4 3 11 17 18 38 87 140 141 153 188 224 242 259 275
258 251 264 372 295 305 267 241
01/04/14 604 727 673 644 544 474 344 363 336 326 441 550 379 278 222 241
269 227 267 235 256 296 344 404 431 448 433 419 421 378 328 358 313 294 315
278 240 182 138 105 78 45 21 16 16 14 10 10 15 16 22 36 33 28 36 44 41 279
290 262 278 306 339 338 404 423 470 441 446 449 487 497 538 675 665 822 990
1145 1173 1278 1229 1200 1221 1219 1208 1148 1167 1245 1163 1130 1164 1030
990 997 976 918 30/03/14 916 890 870 851 793 691 635 540 472 410 370 341
317 330 330 288 252 219 209 198 198 182 173 167 165 172 175 183 188 170 140
117 97 60 34 18 4 2 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 6 11 7 6 1019 892 996 1041
1087 1079 1098 1198 1037 1076 962 1002 1041 1016 1118 1098 939 917 902 862
887 1026 1119 1185 1189 1290 1473 1582 1599 1654 1802 1785 1751 1693 1722
1628 1766 1780 1527 29/03/14 1459 1415 1308 1295 1469 1513 1414 1423 1436
1461 1310 1106 1047 993 938 901 928 913 852 850 859 874 842 871 928 983 1083
1085 1010 948 737 551 418 415 377 346 304 303 298 318 273 275 308 313 306
237 230 242 299 311 296 331 387 406 363 284 242 1758 1746 1746 1612 1540
1758 1778 1589 1538 1601 1449 1490 1347 1253 1126 1165 1166 1048 1098 1299
1294 1461 1452 1577 1746 1814 1869 1847 1796 1794 1864 1822 1837 1769 1746
1680 1682 1703 1745 28/03/14 1670 1551 1496 1437 1411 1501 1556 1498 1486
1495 1494 1361 1217 1185 1214 1308 1253 1134 1092 1105 1224 1277 1298 1351
1484 1520 1544 1514 1479 1512 1451 1270 1117 971 888 825 756 830 795 743 848
939 928 875 905 868 980 922 939 994 1143 1142 1160 1123 1139 1129 1046 90 87
73 219 240 264 189 128 167 209 253 301 462 309 311 350 363 324 294 494 403
412 437 451 503 524 456 553 727 768 814 807 725 863 756 777 857 960 1040
27/03/14 1088 1155 1233 1247 1331 1325 1161 1164 1168 1153 1193 1110 1111
1154 1189 1228 1280 1370 1355 1273 1260 1330 1366 1329 1342 1370 1385 1508
1555 1548 1344 1129 1088 1067 955 859 893 1025 1070 1125 1020 1088 1199 1346
1406 1587 1568 1653 1833 1836 1752 1774 1873 1896 1988 1855 1845 118 87 46
46 130 58 74 84 57 71 102 181 255 354 246 296 306 345 390 403 368 367 338
313 332 371 382 413 400 477 451 407 424 488 460 414 399 362 370 26/03/14
375 397 377 285 223 199 206 236 233 214 209 208 193 202 238 330 314 174 195
167 172 167 161 169 236 291 232 226 213 114 43 53 49 32 19 43 27 30 19 30 48
47 49 62 56 72 42 42 28 77 123 58 79 68 72 121 142 79 62 78 83 151 86 83 93
120 130 128 120 124 105 55 27 38 59 86 95 85 44 46 62 92 90 82 86 81 66 46
38 59 55 55 56 51 60 53 25/03/14 61 74 88 77 57 39 38 40 34 22 22 27 43 43
38 43 49 45 44 41 35 40 36 30 31 26 16 20 17 12 10 5 4 4 1 1 1 1 1 1 2 4 7 2
4 12 15 12 32 27 8 37 36 34 6 40 70 675 516 613 643 602 760 1004 846 616 449
360 151 152 309 310 157 136 87 53 58 96 79 42 28 38 51 56 98 141 136 134 100
98 98 103 114 134 136 129
Each row has 96 elements (excluding the date column), i.e., there are 96
columns of 15 minute intervals from 0:00 time to 23:45 apart from the 1st
column that has the dates in dd/mm/yy format. The data start on the 30th of
June, 2011, 0:00.
I made a rudimentary function to read the data.frame from its csv file,
reverse it and create a single time series (xts) that has a 15min frequency.
preparehfdata<-function(x,ISOdate=ISOdatetime(2011,06,29,23,45,0))
{
mydata<-x[nrow(x):1,]
temp<-(as.matrix(mydata))
temp2<-temp[,-1]
temp3<-as.numeric(apply(temp2, MARGIN=1, FUN=as.vector))
hoursminutes <- ISOdate + seq(0:(length(temp3)-1))*15*60
temp4<-xts(temp3, order.by=hoursminutes)
return(temp4)
}
which works fine except from the part that the data.frame is missing some
days and my time alignment overshoots. I.e., although there should have
been observations (96 15-minute prices) for every day since June 30th 2011,
some days are missing and some days are repeated (see rows 3, 4 and 5 for
example above) in the data.frame, with the same prices...
Is there an easier way to omit copies of same date data and account for the
missing days when collapsing the dataframe to a single time series (xts or
zoo)?
Preferably I would like to avoid treating this as an irrelevantly spaced
time series (its) if possible...
Many thanks in advance for your input,
Best,
Costas
[[alternative HTML version deleted]]
On Wed, Jun 4, 2014 at 3:52 PM, Costas Vorlow <costas.vorlow at gmail.com> wrote:> Hello, > > I have a data.frame of a time series sampled every 15 minutes. Also the > data are in reverse order (recent to older). It looks like the following: ><snip lots of data>> Each row has 96 elements (excluding the date column), i.e., there are 96 > columns of 15 minute intervals from 0:00 time to 23:45 apart from the 1st > column that has the dates in dd/mm/yy format. The data start on the 30th of > June, 2011, 0:00. > > I made a rudimentary function to read the data.frame from its csv file, > reverse it and create a single time series (xts) that has a 15min frequency. > > preparehfdata<-function(x,ISOdate=ISOdatetime(2011,06,29,23,45,0)) > { > mydata<-x[nrow(x):1,] > temp<-(as.matrix(mydata)) > temp2<-temp[,-1] > temp3<-as.numeric(apply(temp2, MARGIN=1, FUN=as.vector)) > hoursminutes <- ISOdate + seq(0:(length(temp3)-1))*15*60 > temp4<-xts(temp3, order.by=hoursminutes) > return(temp4) > } > > which works fine except from the part that the data.frame is missing some > days and my time alignment overshoots. I.e., although there should have > been observations (96 15-minute prices) for every day since June 30th 2011, > some days are missing and some days are repeated (see rows 3, 4 and 5 for > example above) in the data.frame, with the same prices... > > Is there an easier way to omit copies of same date data and account for the > missing days when collapsing the dataframe to a single time series (xts or > zoo)? > > Preferably I would like to avoid treating this as an irrelevantly spaced > time series (its) if possible... > > Many thanks in advance for your input, > > Best, > Costas > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code.It will be a lot easier, and therefore more likely, for others to help you if you follow the instructions in the footer. Here are some suggestions of how to create a *minimal*, reproducible example: http://stackoverflow.com/q/5963269/271616 Best, -- Joshua Ulrich | about.me/joshuaulrich FOSS Trading | www.fosstrading.com