How do I lookup a value in another zoo object?

0
votes

I am trying to find a way to lookup a value in another zoo object. Suppose I have the following timeseries:

a <- read.zoo(data.frame(date=as.Date('2011-12-31') + 0:8, reldt=c(rep(as.Date("2012-02-01"),3), rep(as.Date("2012-03-01"),3), rep(as.Date("2012-01-01"),3)), col2=seq(11,19)), FUN = as.Date)
a
           reldt      col2
2011-12-31 2012-02-01 11  
2012-01-01 2012-02-01 12  
2012-01-02 2012-02-01 13  
2012-01-03 2012-03-01 14  
2012-01-04 2012-03-01 15  
2012-01-05 2012-03-01 16  
2012-01-06 2012-01-01 17  
2012-01-07 2012-01-01 18  
2012-01-08 2012-01-01 19  

mon <- read.zoo(data.frame(date=c(as.Date('2012-01-01'), as.Date('2012-02-01'), as.Date('2012-03-01')), mc=letters[1:3], mc2=c(100,200,300)LETTERS[1:3]), FUN = as.Date)
mon
           mc mc2
2012-01-01 a  100
2012-02-01 b  200
2012-03-01 c  300

For every reldt in a I would like to lookup the value in the column mon.mc2. In other words, I would like to append the corresponding values in column mon.mc2 to a so I would have a column a.mc2 that is matched by a.reldt=index(mon)

Here is what I tried (as I know this works for data.frame but doesn't seem to work for zoo objects). IT DOESN'T ALIGN CORRECTLY FOR SOME REASON (I suspect zoo objects don't respect the order of the indexes as returned by match(...)):

a$mc2 <- as.numeric(coredata(mon[match(as.Date(coredata(a$reldt)), as.Date(index(mon)))]$mc2))

Here is the zoo object I would like to output:

          reldt      col2 mc2
2011-12-31 2012-02-01 11   200
2012-01-01 2012-02-01 12   200
2012-01-02 2012-02-01 13   200
2012-01-03 2012-03-01 14   300
2012-01-04 2012-03-01 15   300
2012-01-05 2012-03-01 16   300
2012-01-06 2012-01-01 17   100
2012-01-07 2012-01-01 18   100
2012-01-08 2012-01-01 19   100

NOTE: I would prefer a base R solution using zoo but it would be interesting to see other packages that might be useful

2
rjoindplyrtime-serieszoo
zoo objects must be all of the same type and if you try to mix types, as in the question, it will use the lowest common denominator so both of your zoo objects are character objects. – G. Grothendieck
@G.Grothendieck: I’m ok with that. This is just an example. Any idea on how to do the match properly here without downgrading to a data.frame? I’m really just looking how to do the match properly.... – Denis

2 Answers

2
votes

As noted in the comments both a and mon are character zoo objects which is unusual; however, we can still do it by matching a$reldt to as.character(time(mon)). From that create a new zoo object mc2 and merge that with a.

 mc2 <- zoo( coredata(mon$mc2)[match(a$reldt, as.character(time(mon)))], time(a) )
 cbind(a, mc2)
1
votes

I would recommend to first work with the dataframes and join them and then to transform into a zoo object (if you really want that). The join can easily be done with the dplyr package. In the following code, a2 and mon2 are the data.frame versions of a and mon.

a2 <- data.frame(date=as.Date('2011-12-31') + 0:8, reldt=c(rep(as.Date("2012-02-01"),3), rep(as.Date("2012-03-01"),3), rep(as.Date("2012-01-01"),3)), col2=seq(11,19))

mon2 <- data.frame(date = c(as.Date('2012-01-01'), as.Date('2012-02-01'), as.Date('2012-03-01')), mc=letters[1:3], 
                  mc2=c(100,200,300))

library(dplyr)
library(zoo)
left_join(a2, mon2 %>% select(-mc), by = c("reldt" = "date")) %>%
  read.zoo(FUN = as.Date)

           reldt      col2 mc2
2011-12-31 2012-02-01 11   200
2012-01-01 2012-02-01 12   200
2012-01-02 2012-02-01 13   200
2012-01-03 2012-03-01 14   300
2012-01-04 2012-03-01 15   300
2012-01-05 2012-03-01 16   300
2012-01-06 2012-01-01 17   100
2012-01-07 2012-01-01 18   100
2012-01-08 2012-01-01 19   100