0

I have one main XTS object "Data" with ~1M rows spanning 22 days. I have another XTS object "Set" with 22 rows, with 1 entry per day. I would like to combine this smaller XTS object into the larger one, such that it would have an additional column containing the value in Set for that day.

First I tried:

> Data=cbind(Data,as.numeric(Set[as.Date(index(Data[]))]))
Error in error(x, ...) : 
improper length of one or more arguments to merge.xts

Then I tried:

> Data=cbind(Data,1)
> Data[,6]=as.numeric(Set[as.Date(index(Data[,6]))])
Error in NextMethod(.Generic) : 
  number of items to replace is not a multiple of replacement length

I also tried without the as.numeric but received the same error. I tried turning Data into a data.frame and got the error:

Error in `[<-.data.frame`(`*tmp*`, , 6, value = c(1, 397.16, 397.115,  : 
  replacement has 22 rows, data has 835771

What am I doing wrong and how do I make this happen? I've only been using R the past two weeks.

Thanks!

> str(Data)
An ‘xts’ object from 2012-01-03 05:01:05 to 2012-01-31 14:59:59 containing:
  Data: num [1:835771, 1:5] 397 397 397 397 397 ...
 - attr(*, "dimnames")=List of 2
  ..$ : NULL
  ..$ : chr [1:5] "SYN" "\"WhitePack.BID_SIZE\"" "\"WhitePack.BID_PRICE\"" "\"WhitePack.ASK_PRICE\"" ...
  Indexed by objects of class: [POSIXct,POSIXt] TZ: 
  xts Attributes:  
 NULL
> str(Set)
An ‘xts’ object from 2012-01-02 to 2012-01-31 containing:
  Data: chr [1:22, 1] "  1.000" "397.160" "397.115" "397.175" "397.200" "397.390" "397.560" "397.580" "397.715" ...
 - attr(*, "dimnames")=List of 2
  ..$ : NULL
  ..$ : chr "Settle"
  Indexed by objects of class: [POSIXct,POSIXt] TZ: 
  xts Attributes:  
 NULL
user1266555
  • 3
  • 1
  • 3

1 Answers1

1

Do you get success with :

df3 <- merge(Data, Set)

To address my lack of full understanding of the original problem, I think the only additional step would be:

df3[, 6]  <- na.locf( df3[, 6] )
IRTFM
  • 258,963
  • 21
  • 364
  • 487
  • No, this adds 22 rows to Data with NA's in every new data cell except these 22. – user1266555 Mar 13 '12 at 13:43
  • You need to post the results of str(Data) and str(Set). I'm guessing your index formats are not what you think they are. – IRTFM Mar 13 '12 at 13:49
  • You don't need the calls to `coredata`. And if the 22 index values in `Set` are slightly different than those in `Data`, you can remove them via: `df3 <- df3[index(Data),]`. – Joshua Ulrich Mar 13 '12 at 14:34
  • After a couple of false steps I think the above addendum should succeed. You may need not to remove the extra 22 lines where the implicit midnight times were inserted. – IRTFM Mar 13 '12 at 14:35
  • > df3$Settle=na.locf(df$Settle) Error in df[, 6] : object of type 'closure' is not subsettable > df4=as.data.frame(df3) > df4$settle=na.locf(df4$settle) > df3=as.xts(df4) This worked. Thank you! – user1266555 Mar 13 '12 at 14:36
  • It worked without changing to a data frame first, must have had a typo. Thanks again. – user1266555 Mar 13 '12 at 14:42
  • Calling it a typo is being charitable. I just didn't know the right way to do it on the first pass. Now I know that there is a `[<-.zoo` method that works properly on the "coredata" columns. – IRTFM Mar 13 '12 at 14:47