1

I have a 207x7 xts object (called temp). I have a 207x3 matrix (called ac.topn), each row of which contains the columns I'd like from the corresponding row in the xts object.

For example, given the following top two rows of temp and ac.topn,

temp
             v1   v2    v3  v4  v5    v6   v7
1997-09-30 14.5  8.7  -5.8 2.6 4.7   1.9 17.2
1997-10-31  6.0 -2.0 -25.7 2.9 4.9   9.6  8.4

head(ac.topn)
           Rank1 Rank2 Rank3
1997-09-30     7     4     2
1997-10-31     6     5     7

I would like to get the result: 1997-09-30 17.2 2.6 8.7 (elements 7, 4, and 2 from the first row of temp) 1997-10-31 9.6 4.9 8.4 (elements 6, 5, 7 from the second row of temp)

My first attempt was temp[,ac.topn]. I've browsed for help, but am struggling to word my request effectively.

Thank you.

rmacey
  • 599
  • 3
  • 22
  • In future, use the `{}` button to post code or results. – jlhoward Nov 15 '14 at 18:59
  • Even if you could subset an xts object by a matrix, your attempt wouldn't work because of this paragraph in `?Extract`: "When indexing arrays by `[` a single argument `i` can be a matrix with as many columns as there are dimensions of `x`; the result is then a vector with elements corresponding to the sets of indices in each row of `i`." – Joshua Ulrich Nov 16 '14 at 15:54

2 Answers2

1

Well, this works, but I've got to think there's a better way...

result <- do.call(rbind,lapply(index(temp),function(i)temp[i,ac.topn[i]]))
colnames(result) <- colnames(as.topn)
result
#            Rank1 Rank2 Rank3
# 1997-09-30  17.2   2.6   8.7
# 1997-10-31   9.6   4.9   8.4
jlhoward
  • 58,004
  • 7
  • 97
  • 140
  • I couldn't get that answer to work, but it gave me the idea for something that does `t(sapply(seq(1,n),function(i) ac.ret[i,ac.topn[i,]]))` where n is the number of rows. I think my problem with the answer is the index(temp) is a date, not an integer sequence. – rmacey Nov 15 '14 at 19:39
  • 1
    This answer assumes that both `temp` and `ac.topn` are `xts` objects. If not, use `ac.topn <- as.xts(ac.topn)`. Using integer indexing will create big problems if `temp` and `ac.topn` do not have the same rows in the same order. – jlhoward Nov 15 '14 at 23:24
1

You may subset a matrix version of the xts object, using indexing via a numeric matrix:

m <- as.matrix(temp)
cols <- as.vector(ac.topn)
rows <- rep(1:nrow(ac.topn), ncol(ac.topn))

vals <- m[cbind(rows, cols)]
xts(x = matrix(vals, nrow = nrow(temp)), order.by = index(temp))
#            [,1] [,2] [,3]
# 1997-09-30 17.2  2.6  8.7
# 1997-10-31  9.6  4.9  8.4

However, I say the same as @jlhoward: I've got to think there's a better way...

Henrik
  • 65,555
  • 14
  • 143
  • 159