1

I have an xts object that contain several columns with one numeric values per row and I need to select the name and the value of columns where value is greater than 0 for the day n-1.

Object1 <- colnames(xxx)[apply(xxx[Sys.Date()-1],1,which.max)]

I use this to select the name of the column for the row where value > 0 but it works only if there is one column. My xts object can have several.

Please find how the xts object xxx looks like : 

           AAAAAA BBBBBB CCCCCC DDDDDD EEEEEE FFFFFF GGGGGG HHHHHH  IIIIII  JJJJJJ  KKKKKK  LLLLLL  MMMMMM  NNNNNN  OOOOOO  PPPPPP 
2015-02-25      0      0   0.00      0      0      0      0   0.33    0.00       0       0       0       0    0.33    0.33       0      
2015-02-26      0      0   0.33      0      0      0      0   0.33    0.00       0       0       0       0    0.33    0.00       0      
2015-02-27      0      0   0.33      0      0      0      0   0.33    0.00       0       0       0       0    0.33    0.00       0      
2015-03-02      0      0   0.00      0      0      0      0   0.33    0.33       0       0       0       0    0.33    0.00       0      
2015-03-03      0      0   0.00      0      0      0      0   0.33    0.33       0       0       0       0    0.33    0.00       0      
2015-03-04      0      0   0.00      0      0      0      0   0.33    0.33       0       0       0       0    0.33    0.00       0

I would like to retieve for day n-1 an object like this :

>Object
            HHHHHH  IIIIII  NNNNNN
2015-03-03    0.33    0.33    0.33

Or it would be even better if it could be one row per line:

>Object
2015-03-03   HHHHHH    0.33 
2015-03-03   IIIIII    0.33
2015-03-03   NNNNNN    0.33

I have tried with apply and which(xxx[Sys.Date()-1]>0 but unfortunatly it returns TRUE of FALSE for each column.

Thank you

Joshua Ulrich
  • 173,410
  • 32
  • 338
  • 418
Florent
  • 1,791
  • 22
  • 40

1 Answers1

1

You could try

res <- t(apply(xxx[Sys.Date()-1], 1, function(x) x[x==max(x)]))

to get the second format

library(reshape2)
melt(res)
#        Var1   Var2 value
#1 2015-03-03 HHHHHH  0.33
#2 2015-03-03 IIIIII  0.33
#3 2015-03-03 NNNNNN  0.33

Update

You could also do

indx <- xxx[Sys.Date()-1]==max(xxx[Sys.Date()-1])
xxx[Sys.Date()-1][,indx]
#           HHHHHH IIIIII NNNNNN
#2015-03-03   0.33   0.33   0.33

melt(as.matrix( xxx[Sys.Date()-1][,indx]))
#       Var1   Var2 value
#1 2015-03-03 HHHHHH  0.33
#2 2015-03-03 IIIIII  0.33
#3 2015-03-03 NNNNNN  0.33

For the one column case

xxx[Sys.Date()-1, 8:9] <- 0
indx <- xxx[Sys.Date()-1]==max(xxx[Sys.Date()-1])
xxx[Sys.Date()-1][,indx]
#             NNNNNN
#2015-03-03   0.33

Or an option using apply would be

 res <- do.call(rbind,apply(xxx[Sys.Date()-1], 1,
         function(x) as.list(x[x==max(x)])))
 res
 #            NNNNNN
 #2015-03-03 0.33  

  melt(res)
  #                Var1   Var2 value
  #2015-03-03 2015-03-03 NNNNNN  0.33
akrun
  • 874,273
  • 37
  • 540
  • 662
  • For some reason, when there is only one column with value greater than 0 (i.e. value equal to 1), `res` does not have the name of the column but `[1,]` instead. I'm not familiar with function in R and try with `x[x>0]` and `x[x==max(x) | x==1]` but with no success. – Florent Mar 04 '15 at 13:42
  • To be more clear `res <- apply(xxx[Sys.Date()-1], 1, function(x) x[x==max(x)])` retrieve the value but is unable to get column name when there is only one colum that is greater than 0 (the row name remains blank). Then the matrix transposition add [1,] instead. – Florent Mar 04 '15 at 14:33
  • It works when there are at least 2 columns with value greater than 0. If only one column (with value equal to 1), then it get the value but not the name of the column. – Florent Mar 04 '15 at 14:43
  • @Florent I updated the solution. The `apply` based solutions with `MARGIN=1` is used mainly for multiple rows. Here, you are working with only one row as per the code you used. In that case, the updated solution would work even if there is only one column that satisfies the condition – akrun Mar 04 '15 at 14:53
  • Thank you very much. I didn't notice in the documentation that `MARGIN=1` indicates row**s** (plural). I like the one column case. – Florent Mar 04 '15 at 15:11
  • @Florent apply is basically a loop. If you have only one row, why go for a loop. – akrun Mar 04 '15 at 15:14