Access Zoo or Xts Index

Conversion from `zoo` to `xts` creates lots of NAs in the index

The problem is that your index is of class chron. I know very little about chron, but AFAIK it is generally preferred to use POSIX datetime objects in R, i.e. POSIXct or POSIXlt.

Somewhere in the conversion from zoo to xts the chron class information gets destroyed.

Converting your index to class POSIXct resolves the issue.

index(meto) <- as.POSIXct(index(meto)) 
as.xts(meto)

                    [,1]
2005-01-13 00:00:00   30
2005-01-13 01:00:00   25
2005-01-13 01:59:59   25
2005-01-13 03:00:00   25
2005-01-13 04:00:00   20
2005-01-13 04:59:59   20
2005-01-13 06:00:00   20
2005-01-13 07:00:00   20
2005-01-13 07:59:59   20
2005-01-13 09:00:00   20

---

For more information about working with R dates and time classes, see ?DateTimeClasses, ?POSIXct or ?strptime - which all leads to the same help page.

---

EDIT

If xts is supposed to handle chron objects when importing from zoo, you have probably found a bug in the function xts::xts.

The problem occurs in this line:

if (inherits(order.by, "dates")) 
    index <- as.numeric(as.POSIXct(strptime(as.character(order.by), 
        "(%m/%d/%y %H:%M:%S)")))

But notice that the format of your chron object is ("d/m/y", "h:m:s") - I know this from your str(meto). Look carefully - there is a misalignment between day and month.

This may well be a locale issue. I believe the package author lives in the USA, where the standard format is m/d/y, but in many other places the standard format is d/m/y.

So, somehow in the conversion between zoo and xts the conversion code should adjust for the locale of the user.

I suggest you contact the package author with this information.

Better way to apply rolling function to zoo or xts object?

Assuming the input z shown reproducibly in the Note at the end, if the width is 2 then:

library(zoo)

-diff(z)
##            V2 V3 V4
## 1970-04-12 -4 -4  6
## 1970-04-13 -1 -1 -2
## 1970-04-14  1  0 -1

and in general:

w <- 2 # modify as needed
-diff(z, w-1)
##            V2 V3 V4
## 1970-04-12 -4 -4  6
## 1970-04-13 -1 -1 -2
## 1970-04-14  1  0 -1

or using rollapplyr:

w <- 2 # modify as needed
rollapplyr(z, w, function(x) x[1] - x[w])
##            V2 V3 V4
## 1970-04-12 -4 -4  6
## 1970-04-13 -1 -1 -2
## 1970-04-14  1  0 -1

Note

Lines <- "
1970-04-11 1 3 8
1970-04-12 5 7 2
1970-04-13 6 8 4
1970-04-14 5 8 5"

library(zoo)
z <- read.zoo(text = Lines)

xts format assign name for index column

We assume that the question is asking how to plot an xts object such as x in the Note at the end.

There are numerous plotting functions for zoo and xts objects (every xts object is also a zoo object) so it is unnecessary to convert it to a data frame (although in the last example below we show this.) All the facilities of each of these plotting systems are available when using this. Also shown below is how to specify the x label in each case.

library(xts)  # this also pulls in zoo

# classic graphics
plot(as.zoo(x), xlab = "Time")

# lattice graphics
library(lattice)
xyplot(x, xlab = "Time")

## ggplot2 graphics
library(ggplot2)
autoplot(x) + xlab("Time")

# convert to data frame and then use matplot
d <- fortify.zoo(x)
matplot(d[[1]], d[-1], xlab = "Time")

Note

Lines <- "                 Elapsed Time  Total Inflow  Total Evap  Surface Infil
    2021-04-30 10:02:00        0.033            0      0.125             0
    2021-04-30 10:04:00        0.067            0      0.125             0
    2021-04-30 10:06:00        0.100            0      0.125             0
    2021-04-30 10:08:00        0.133            0      0.125             0
    2021-04-30 10:10:00        0.167            0      0.125             0
    2021-04-30 10:12:00        0.200            0      0.125             0"

# split into lines, trim whitespace off ends, replace 2+ spaces w comma
L <- Lines |>
  textConnection() |>
  readLines() |>
  trimws() |>
  gsub(pattern = "  +", replacement = ",")

z <- read.csv.zoo(text = L, index = 0, tz = "", check.names = FALSE)
x <- as.xts(z)

Difference between column of a xts zoo object

1) Assuming that differences of distinct pairs of columns is sufficient try combn:

library(xts)

a <- as.zoo(a)
a.combn <- combn(names(a), 2, function(nms) a[, nms[1]] - a[, nms[2]])
colnames(a.combn) <- combn(names(a), 2, paste, collapse = "-")
xts(a.combn, index(a))

giving (continued after output):

           EUSA.2-EUSA.3 EUSA.2-EUSA.4 EUSA.2-EUSA.5 EUSA.3-EUSA.4
2014-06-11       -0.0876       -0.2090       -0.3770       -0.1214
2014-06-12       -0.0775       -0.2157       -0.3760       -0.1382
2014-06-13       -0.0723       -0.2140       -0.3800       -0.1417
2014-06-16       -0.0874       -0.2291       -0.4012       -0.1417
2014-06-17       -0.0837       -0.2100       -0.3770       -0.1263
2014-06-18       -0.0712       -0.2024       -0.3711       -0.1312
           EUSA.3-EUSA.5 EUSA.4-EUSA.5
2014-06-11       -0.2894       -0.1680
2014-06-12       -0.2985       -0.1603
2014-06-13       -0.3077       -0.1660
2014-06-16       -0.3138       -0.1721
2014-06-17       -0.2933       -0.1670
2014-06-18       -0.2999       -0.1687

2) A subscript-free alternative is the following. It creates a 3d array of combinations which it reduces to a matrix. In this case we don't have to convert to "zoo" first:

a.combn <- apply(combn(as.data.frame(a), 2, as.matrix), 3, `%*%`, c(1, -1))
colnames(a.combn) <- combn(names(a), 2, paste, collapse = "-")
xts(a.combn, index(a))

Note: Here is a in reproducible form:

a <- structure(c(0.314, 0.319, 0.318, 0.3255, 0.318, 0.321, 0.4016, 
0.3965, 0.3903, 0.4129, 0.4017, 0.3922, 0.523, 0.5347, 0.532, 
0.5546, 0.528, 0.5234, 0.691, 0.695, 0.698, 0.7267, 0.695, 0.6921
), .Dim = c(6L, 4L), .Dimnames = list(NULL, c("EUSA.2", "EUSA.3", 
"EUSA.4", "EUSA.5")), index = structure(c(1402444800, 1402531200, 
1402617600, 1402876800, 1402963200, 1403049600), 
tzone = "UTC", tclass = "Date"), class = c("xts", "zoo"), 
.indexCLASS = "Date", tclass = "Date", .indexTZ = "UTC", tzone = "UTC")

Comparing xts index elements

You can use function which. Here is example with simulated data:

    x<- c(1,2,3,4)
    y<-c(1,2,3,4,5,6,7,8)

    which(y==x[1])

filter date index in xts format

It it is an xts object, then we extract the year from index to create a logical vector for subsetting

library(xts)
xt1[lubridate::year(index(xt1)) >= 2010]

---

Or use subset with %>%

library(dplyr)
xt1 %>%
       subset(year(index(.))>= 2010)

data

set.seed(24)
xt1 <- xts(rnorm(50), order.by = seq(as.Date('2009-01-01'), 
    length.out = 50, by = '1 month'))

How to use zoo or xts with large data?

I have had a similar problem (albeit I was only playing with 9-10 GBs). My experience is that there is no way R can handle so much data on its own, especially since your dataset appears to contain time series data.

If your dataset contains a lot of zeros, you may be able to handle it using sparse matrices - see Matrix package ( http://cran.r-project.org/web/packages/Matrix/index.html ); this manual may also come handy ( http://www.johnmyleswhite.com/notebook/2011/10/31/using-sparse-matrices-in-r/ )

I used PostgreSQL - the relevant R package is RPostgreSQL ( http://cran.r-project.org/web/packages/RPostgreSQL/index.html ). It allows you to query your PostgreSQL database; it uses SQL syntax. Data is downloaded into R as a dataframe. It may be slow (depending on the complexity of your query), but it is robust and can be handy for data aggregation.

Drawback: you would need to upload data into the database first. Your raw data needs to be clean and saved in some readable format (txt/csv). This is likely to be the biggest issue if your data is not already in a sensible format. Yet uploading "well-behaved" data into the DB is easy ( see http://www.postgresql.org/docs/8.2/static/sql-copy.html and How to import CSV file data into a PostgreSQL table? )

I would recommend using PostgreSQL or any other relational database for your task. I did not try Hadoop, but using CouchDB nearly drove me round the bend. Stick with good old SQL

How can I extract the timestamp of an xts object in R?

Use index(xtsobject)

data(sample_matrix)
sample.xts <- as.xts(sample_matrix, descr='my new xts object')
index(sample.xts)

> index(sample.xts)
  [1] "2007-01-02 EST" "2007-01-03 EST" "2007-01-04 EST" "2007-01-05 EST" "2007-01-06 EST" "2007-01-07 EST"
  [7] "2007-01-08 EST" "2007-01-09 EST" "2007-01-10 EST" "2007-01-11 EST" "2007-01-12 EST" "2007-01-13 EST"
 [13] "2007-01-14 EST" "2007-01-15 EST" "2007-01-16 EST" "2007-01-17 EST" "2007-01-18 EST" "2007-01-19 EST"
 [19] "2007-01-20 EST" "2007-01-21 EST" "2007-01-22 EST" "2007-01-23 EST" "2007-01-24 EST" "2007-01-25 EST"

---

Related Topics