Geek asks and answers

Creating xts time series

zz <- textConnection("strtimestamp, jstimestamp, 61757, 61754, 61760, 61753, 61758, 61762, 61756, 61759, 61761, 61755, 61752
'01/01/2007 00:00:00', 1167606000000, 1145.3, 1647.3, 612, 963.3, 1063.5, 2294.3, 726.9, 280.8, 5182.4, 739.5, 1631.7
'01/01/2007 00:05:00', 1167606300000, 1116.7, 1625.4, 695.3, 948.4, 1111.8, 2262, 736.5, 202.7, 5135.8, 733, 1608.1
'01/01/2007 00:10:00', 1167606600000, 1100.3, 1613.4, 614.9, 957.5, 1059.8, 2261.6, 723.2, 238.5, 5114.5, 733, 1599.1
'01/01/2007 00:13:41', 1167606821000, 1101.2, 1619.6, 600.9, 947.7, 1047.3, 2254.1, 715.5, 269.7, 5124.3, 726.5, 1608.2
'01/01/2007 00:15:41', 1167606941000, 1097.6, 1607.6, 626.2, 944.2, 1032.2, 2224.9, 715.5, 260.6, 5094.3, 728.9, 1602.3
'01/01/2007 00:25:00', 1167607500000, 1081.1, 1622, 616.7, 955.1, 1047, 2240.1, 717.7, 259.8, 5088.6, 737.8, 1578.7
'01/01/2007 00:30:00', 1167607800000, 1079.5, 1602.3, 601.6, 953.2, 1019.7, 2195.7, 710.8, 266, 5043.4, 742.8, 1560.9
'01/01/2007 00:35:00', 1167608100000, 1055.6, 1597.3, 599.4, 947.9, 1050.8, 2168, 723.9, 242.1, 5063.7, 732.9, 1554.5
'01/01/2007 00:40:00', 1167608400000, 1050.5, 1585.7, 618.4, 942.3, 1025.1, 2142.6, 705.5, 236.6, 5012.4, 745, 1555.3
'01/01/2007 00:45:00', 1167608700000, 1054.4, 1588.8, 607.7, 917.4, 1030.4, 2143.6, 696.9, 246.4, 5011.9, 736.5, 1565.8
'01/01/2007 00:50:00', 1167609000000, 1063, 1580.7, 587.7, 937.5, 1002.2, 2136.6, 694.5, 247.4, 5007.5, 740.8, 1535
'01/01/2007 00:55:00', 1167609300000, 1054.2, 1576.5, 623.6, 932.1, 1017, 2101, 708.6, 208.9, 4999.9, 740.5, 1532.4
'01/01/2007 01:00:00', 1167609600000, 1061.8, 1561.2, 574.1, 906.4, 1011.5, 2102.9, 705.1, 235.7, 4963.5, 729.1, 1546.6
'01/01/2007 01:05:00', 1167609900000, 1066.2, 1563.7, 642.4, 923, 1024.1, 2075.3, 682.5, 200.8, 4945, 738, 1523.2
'01/01/2007 01:10:00', 1167610200000, 1025.4, 1556.3, 577, 931.9, 1009.8, 2064.6, 684.7, 237.2, 4917.2, 725.1, 1503.6
'01/01/2007 01:15:00', 1167610500000, 1022.7, 1545.5, 573, 918, 992.6, 2052.3, 695.2, 224.7, 4926.7, 725.2, 1496
'01/01/2007 01:20:00', 1167610800000, 1018.4, 1544.9, 582.7, 897.7, 985.3, 2039.5, 681.9, 225.2, 4891.7, 728.2, 1534.1
'01/01/2007 01:25:00', 1167611100000, 1012.4, 1529.3, 558.9, 915.4, 969.7, 2029.1, 694, 241.4, 4874.9, 731.9, 1491.4
'01/01/2007 01:30:00', 1167611400000, 1013.1, 1525, 581.9, 911.6, 970.4, 2017.1, 685.2, 231.2, 4866.2, 735.8, 1489.1
'01/01/2007 01:35:00', 1167611700000, 1011.4, 1530.9, 567.4, 890.1, 964.3, 2001, 691.8, 227.8, 4821.6, 735.6, 1492.5
'01/01/2007 01:40:00', 1167612000000, 1006.6, 1523.6, 574.5, 882, 963.9, 1966.6, 662, 228, 4800, 749.3, 1501
'01/01/2007 01:42:55', 1167612175000, 1004, 1514.5, 544.7, 896, 949.5, 1974.1, 682.5, 240, 4814, 735.4, 1480.4
'01/01/2007 01:44:29', 1167612269000, 1007.1, 1513.4, 561.7, 873, 948, 1976.1, 677, 229.4, 4778.9, 723.8, 1492.4
'01/01/2007 01:50:00', 1167612600000, 1015, 1506, 547.7, 895.7, 923.6, 1980.8, 667.7, 238.4, 4772.8, 731.1, 1476.9
'01/01/2007 01:55:00', 1167612900000, 990.2, 1490.8, 582.7, 869.9, 933.7, 1941.4, 663.7, 225.2, 4745.7, 738.5, 1468.8
'01/01/2007 02:00:00', 1167613200000, 978, 1484.9, 569.6, 890.9, 945.7, 1905.7, 679.3, 215.7, 4711.1, 723.8, 1455.3
'01/01/2007 02:05:00', 1167613500000, 994.7, 1489.5, 544.9, 861.1, 934.3, 1927.1, 679.4, 212.7, 4697.3, 717.8, 1468.9
'01/01/2007 02:10:00', 1167613800000, 990.6, 1471.4, 569, 883, 920, 1894.5, 661.4, 215.6, 4672.8, 731.3, 1439.6
'01/01/2007 02:15:00', 1167614100000, 976.5, 1480.8, 531.4, 880.3, 935.9, 1903.4, 659.9, 224.9, 4691.2, 720.1, 1442.9
")
Data <- read.table(zz,  header = TRUE,  sep = ",",  dec=".")

How can I read this time series in xts, zoo, or some other R-time class supporting irregular time series?

first column format "%m/%d/%Y %H:%M:%S"

===================

Vincent's first example works, so I accept his answer. The other two do not work on real data. It says that the index is not unique, although in my data I do not have a duplicate datetime.

0
source share
1 answer

You can read the data in data.frame, manually parse the date, and convert it to an xts object. If something goes wrong, you will see more clearly what is happening.

d <- read.csv(textConnection(s))
d <- xts( 
  d[,-1], 
  as.POSIXct( 
    as.character(d[,1]), 
    format="'%m/%d/%Y %H:%M:%S'"  # Note the single quotes...
  ) 
)

You can also use read.zoodata.frame for analysis.

d <- read.csv(textConnection(s))
d <- read.zoo(d, 
  format="'%m/%d/%Y %H:%M:%S'", 
  FUN=as.POSIXct  # The default is to use dates, not times
)

read.zoo.

d <- read.zoo(textConnection(s), 
  sep=",", header=TRUE, 
  format="%m/%d/%Y %H:%M:%S",  # No single quotes: read.zoo calls 
  FUN=as.POSIXct               # read.table, not read.csv:   
)                              # it has different default parameters
+4

More articles:


All Articles