0

I am trying to do an excel equivalent of vlookup for two irregular time series.

One zoo object with DAILY frequency holds ranks of a statistic in each column. 

> head(sdRANK.z); dim(sdRANK.z)
           B1 WTI1 NG1 HO1 RB1 W1 SB1 CO1 C1 S1 LU1 CT1 LE1 SM1 KC1 BO1 LC1 FC1 HG1 GC1 SI1
2011-02-28  9    8   7  11  15  6   1  10  5 18   4   2  13  17  12  16  19  20  14  21   3
2011-03-01  9    8   7  11  14  6   1  10  5 18   4   2  13  17  12  16  19  20  15  21   3
2011-03-02  9    7   8  12  14  6   3  10  5 18   4   1  13  17  11  16  19  20  15  21   2
2011-03-03  9    8   7  12  15  6   1  10  5 18   4   2  13  17  11  16  19  20  14  21   3
2011-03-04  9    8   7  12  15  6   1  10  5 18   4   2  13  17  11  16  19  20  14  21   3
2011-03-07  9    8   7  12  15  6   1  11  5 18   4   2  13  17  10  16  19  20  14  21   3
[1] 633  21
>

The 2nd zoo object with monthly frequency has the last weekday of each month in its rows. The column of this matrix are currently populated with 0's.

> head(volMAT.z); dim(volMAT.z)
           B1 WTI1 NG1 HO1 RB1 W1 SB1 CO1 C1 S1 LU1 CT1 LE1 SM1 KC1
2011-02-28  0    0   0   0   0  0   0   0  0  0   0   0   0   0   0
2011-03-31  0    0   0   0   0  0   0   0  0  0   0   0   0   0   0
2011-04-29  0    0   0   0   0  0   0   0  0  0   0   0   0   0   0
2011-05-31  0    0   0   0   0  0   0   0  0  0   0   0   0   0   0
2011-06-30  0    0   0   0   0  0   0   0  0  0   0   0   0   0   0
2011-07-29  0    0   0   0   0  0   0   0  0  0   0   0   0   0   0
           BO1 LC1 FC1 HG1 GC1 SI1
2011-02-28   0   0   0   0   0   0
2011-03-31   0   0   0   0   0   0
2011-04-29   0   0   0   0   0   0
2011-05-31   0   0   0   0   0   0
2011-06-30   0   0   0   0   0   0
2011-07-29   0   0   0   0   0   0
[1] 30 21

I am looking to merge the rankings from the daily dataset into the monthly dataset with the result resembling the vlookup function in Excel with "approximate" look up. This means the approximate match is always the nearest value that happened before the end of the month. So, the first row in the final matrix would look like this:

2011-03-01  9    8   7  11  14  6   1  10  5 18   4   2  13  17  12  16  19  20  15  21   3

I have spend several days experimenting with various combinations of merge and merge.zoo without much progress. Any help would be greatly appreciated.

Thanks and happy Easter Sunday!!

darius
  • 1
  • 1
  • Please provide [reproducible examples](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) and the intended output in your question. – Adam Quek Apr 17 '17 at 04:56
  • Rolling joins in the `data.table` package may be what you need. [Here is a short tutorial](https://r-norberg.blogspot.com/2016/06/understanding-datatable-rolling-joins.html). – eipi10 Apr 17 '17 at 05:07
  • @eipi10 That tutorial is way over my head. Care to expand a little? Thanks. – darius Apr 17 '17 at 23:56

1 Answers1

0

SOLVED!! 

y=as.timeSeries(sdMAT.z)
y2=daily2monthly(y)

2 lines of code and 4 full days of agony!!

darius
  • 1
  • 1