CODEWITHSUNDEEP
rtime-serieszoo
StatsViaCsh
StatsViaCsh

Reputation: 2640

How do I get the column name (or number) of a multi series zoo object when I apply by row?

I have a zoo object of 12 sets of monthly returns on stock tickers. I want to get the symbol, which is the name of the series, or at least the column, of each month's best performing stock. I've been trying to do this with applying the max function, by row. How do I get the column name?

#Apply 'max' function across each row. I need to get the col number out of this.
apply(tsPctChgs, 1, max, na.rm = TRUE)

Upvotes: 1

Views: 904

Answers (2)

Joshua Ulrich
Joshua Ulrich

Reputation: 176648

Since apply converts to matrix, I would use rollapply with width=1:

require("zoo")
set.seed(1)
m <- matrix(runif(50), ncol=5)
mz <- setNames(zoo(m, seq(nrow(m))), paste0("Series",seq(ncol(m))))
rollapply(mz, 1, function(r) colnames(mz)[which.max(r)], by.column=FALSE)

Upvotes: 3

Gavin Simpson
Gavin Simpson

Reputation: 174813

The usual answer would be via which.max() however, do note that this will return only the first of the maximums if there are two or more observations taking the maximum value.

An alternative is which(x == max(x)), which would return all value taking the maximum in the result of a tie.

You can then use the index returned to select the series maximum. Handling NAs is covered below to try to keep the initial discussion simple.

require("zoo")
set.seed(1)
m <- matrix(runif(50), ncol = 5)
colnames(m) <- paste0("Series", seq_len(ncol(m)))
ind <- seq_len(nrow(m))

mz <- zoo(m, order.by = ind)

> apply(mz, 1, which.max)
 1  2  3  4  5  6  7  8  9 10 
 3  5  5  1  4  1  1  2  3  2

> apply(mz, 1, function(x) which(x == max(x)))
 1  2  3  4  5  6  7  8  9 10 
 3  5  5  1  4  1  1  2  3  2

So use that to select the series name

i1 <- apply(mz, 1, function(x) which(x == max(x)))
colnames(mz)[i1]

> i1 <- apply(mz, 1, function(x) which(x == max(x)))
> colnames(mz)[i1]
 [1] "Series3" "Series5" "Series5" "Series1" "Series4" "Series1" "Series1"
 [8] "Series2" "Series3" "Series2"

Handling tied maximums

To illustrate the different behaviour, copy the maximum from month 1 (series 3) into series 1

mz2 <- mz ## copy
mz2[1,1] <- mz[1,3]
mz2[1,]

> mz2[1,]

1 0.9347052 0.2059746 0.9347052 0.4820801 0.8209463

Now try the two approaches again

> apply(mz2, 1, which.max)
 1  2  3  4  5  6  7  8  9 10 
 1  5  5  1  4  1  1  2  3  2 
> apply(mz2, 1, function(x) which(x == max(x)))
$`1`
Series1 Series3 
      1       3
.... ## truncated output ###

Notice how which.max only returns the maximum in series 1.

To use this approach to select the series name, you need to apply something to the list returned by apply(), e.g.

i2 <- apply(mz2, 1, function(x) which(x == max(x)))
lapply(i2, function (i, zobj) colnames(zobj)[i], zobj = mz2)

$`1`
[1] "Series1" "Series3"

$`2`
[1] "Series5"

$`3`
[1] "Series5"

$`4`
[1] "Series1"

$`5`
[1] "Series4"

$`6`
[1] "Series1"

$`7`
[1] "Series1"

$`8`
[1] "Series2"

$`9`
[1] "Series3"

$`10`
[1] "Series2"

Handling NAs

As you have potential for NAs, I would do the following:

apply(mz, 1, which.max, na.rm = TRUE) ## as you did already
apply(mz, 1, function(x, na.rm = TRUE) {
               if(na.rm) {
                 x <- x[!is.na(x)]
               }
               which(x == max(x))
             })

Upvotes: 4

Related Questions