Merging in R based on dates

Question

I'm using getSymbols to import stock data from Yahoo to R.

When I store it in a data frame, it's in the following format.

          IDEA.BO.Open IDEA.BO.High IDEA.BO.Low IDEA.BO.Close IDEA.BO.Volume
2007-03-09        92.40        94.25       84.00         85.55       63599400
2007-03-12        85.55        89.95       85.55         87.40       12490900
2007-03-13        88.50        91.25       86.20         89.85       16785000
2007-03-14        87.05        90.85       86.60         87.75        7763800
2007-03-15        90.00        94.00       88.80         91.45       14808200
2007-03-16        92.40        93.65       91.25         92.40        6365600

Now the date column has no name.

I want to import 2 stock data and merge closing prices (between any random set of rows) on the basis of dates. The problem is, the date column is not being recognized.

I want my final result to be like this.

            IDEA.BO.Close      BHARTIARTL.BO.Close
2007-03-12      123                   333
2007-03-13      456                   645
2007-03-14      789                   999

I tried the following:

> c <- merge(Cl(IDEA.BO),Cl(BHARTIARTL.BO))
> c['2013-08/']
           IDEA.BO.Close BHARTIARTL.BO.Close

2013-08-06            NA              323.40
2013-08-07            NA              326.80
2013-08-08        157.90              337.40
2013-08-09        157.90              337.40

The same data on excel looks like this:

8/6/2013    156.75  8/6/2013    323.4
8/7/2013    153.1   8/7/2013    326.8
8/8/2013    157.9   8/8/2013    337.4
8/9/2013    157.9   8/9/2013    337.4

I don't understand the reason behind the NA values in R and the way to obtain a merged data free of NA Values.

-1 for not showing what you tried. Why do you convert to a data.frame? Leave them as xts objects and just call `merge(Cl(IDEA.BO),Cl(BHARTIARTL.BO))`. There are examples of how to do this all over the place. — Joshua Ulrich, Aug 21 '13 at 21:36
okay this is what i did. d3 <- as.data.frame(IDEA.BO) gld <- zoo(d3[,1], as.Date(d3[,7])) I'm getting this error msg: Error in `[.data.frame`(d3, , 7) : undefined columns selected In addition: Warning message: In zoo(d3[, 7], as.Date(d3[, 1])) : some methods for “zoo” objects do not work if the index entries in ‘order.by’ are not unique I guess this is happening because IDEA.BO on being stored into d3 is getting converted into just 6 columns. My idea was that the date column would be the first column and the adjusted price column would be the seventh column. — user2127116, Aug 22 '13 at 20:07
So you're converting to a data.frame so you can create a zoo object? That's very inefficient when you could just call `as.zoo(IDEA.BO)`. You shouldn't assume that how data prints is how data is stored. `ncol(IDEA.BO)` would have saved you a lot of trouble. — Joshua Ulrich, Aug 22 '13 at 21:04
I also tried the following: > c <- merge(Cl(IDEA.BO),Cl(BHARTIARTL.BO)) > c['2013-08/'] IDEA.BO.Close BHARTIARTL.BO.Close 2013-08-06 NA 323.40 2013-08-07 NA 326.80 2013-08-08 157.90 337.40 2013-08-09 157.90 337.40 The same data on excel looks like this: 8/6/2013 156.75 8/6/2013 323.4 8/7/2013 153.1 8/7/2013 326.8 8/8/2013 157.9 8/8/2013 337.4 8/9/2013 157.9 8/9/2013 337.4 I don't understand the reason behind the NA values in R and the way to obtain a merged data free of NA Values. — user2127116, Aug 22 '13 at 21:22
I give up. Your example works for me: `getSymbols("IDEA.BO;BHARTIARTL.BO"); merge(Cl(IDEA.BO),Cl(BHARTIARTL.BO))["2013-08/"]`. See [How to make a great R reproducible example?](http://stackoverflow.com/q/5963269/271616), [How do I ask a good question?](http://stackoverflow.com/help/how-to-ask), and the [StackOverflow question checklist](http://meta.stackexchange.com/q/156810). — Joshua Ulrich, Aug 22 '13 at 21:38
thanks a lot! I had to type in getSymbols again for the NA values to go. Also, I'm new to R, stack overflow and programming in general. Will keep your advice in mind :) — user2127116, Aug 22 '13 at 21:47

score 1 · Answer 1 · answered Aug 22 '13 at 05:45

You need to do more reading about xts and zoo data structures. They are matrices with indices that are ordered. When you convert to data.frames they become lists with a 'rownames' attribute which gets displayed by print.data.frame with no header. The list elements are given names based on ht naming of the matrix columns. (I do understand Joshua's visible annoyance at this question since he has posted many SO examples of how to use xts-objects.)

Merging in R based on dates

1 Answers1