Hi all,
I encountered a weird behaviour of merge function while using it with
xts objects.
I have multiple xts objects (10 timeseries) that I want to merge in
order to conduct a multivariate analysis.
Here is an example with only two timeseries
#Let us define the time series
> t1
Close
2007-01-02 97.87
2007-01-03 97.32
2007-01-04 96.04
2007-01-05 95.97
2007-01-07 95.69
2007-01-08 96.23
> t2
Close
2007-01-02 94.59
2007-01-03 94.38
2007-01-04 92.55
2007-01-05 92.37
2007-01-07 92.23
2007-01-08 92.89
> c(class(t1),class(t2))
[1] "xts" "zoo" "xts" "zoo"
#I do the merge using merge.xts
> t<-merge(t1,t2)
> t
Close Close
2007-01-02 97.87 NA
2007-01-02 NA 94.59
2007-01-03 97.32 94.38
2007-01-04 96.04 92.55
2007-01-05 95.97 NA
2007-01-05 NA 92.37
2007-01-07 95.69 NA
2007-01-07 NA 92.23
2007-01-08 96.23 92.89
# I don't see where the problem lays since both xts objects have the
same index
> index(t1)
[1] "2007-01-02" "2007-01-03" "2007-01-04" "2007-01-05" "2007-01-07"
"2007-01-08"
> index(t2)
[1] "2007-01-02" "2007-01-03" "2007-01-04" "2007-01-05" "2007-01-07"
"2007-01-08"
# I tried using join but with no satisfying result
>t<-merge(head(t1),head(t2),join='inner')
> t
CHFJPY..2007.....Close AUDJPY..2007.....Close
2007-01-03 97.32 94.38
2007-01-04 96.04 92.55
2007-01-08 96.23 92.89
I am really out of tricks here :-[ .
So if someone is kind enough as to point me towards the right direction,
it would make my day.
Thx in advance,
Anass
[xts] merge function weird behaviour
7 messages · Anass Mouhsine, Mark Breman, Robert Iquiapaza +2 more
An embedded and charset-unspecified text was scrubbed... Name: not available URL: <https://stat.ethz.ch/pipermail/r-sig-finance/attachments/20101210/572608c8/attachment.pl>
Here,
you will find attached data used to illustrate the issue.
here is the code used as well
#reading file
t1=read.csv("AUDJPY.csv", sep=";")
rownames(t1)<-t1[,"Index"];t1<-t1[,-1]
#constructing xts object
t1<-as.xts(t1,tzone="America/New_York", src="csv", updated=Sys.time())
t2=read.csv("CHFJPY.csv", sep=";")
rownames(t2)<-t2[,"Index"];t2<-t2[,-1]
t2<-as.xts(t2,tzone="America/New_York", src="csv", updated=Sys.time())
# requires quantmod
library(quantmod)
#constructing daily close series
t1<-Cl(to.daily(t1))
t2<-Cl(to.daily(t2))
#trying to merge
merge(t1,t2)
#......and result....sadly...is
> merge(t1,t2)
t1.Close t2.Close
2007-01-02 NA 97.87
2007-01-02 94.59 NA
2007-01-03 94.38 97.32
2007-01-04 92.55 96.04
2007-01-05 NA 95.97
2007-01-05 92.37 NA
2007-01-07 NA 95.69
2007-01-07 92.23 NA
2007-01-08 92.89 96.23
On 10/12/2010 10:04, Mark Breman wrote:
Hi Anass,
Could it be the series have a (invisible) different time part, or the
timezone is different.
Regards,
-Mark-
2010/12/10 Anass Mouhsine <anass.mouhsine at gmail.com
<mailto:anass.mouhsine at gmail.com>>
Hi all,
I encountered a weird behaviour of merge function while using it
with xts objects.
I have multiple xts objects (10 timeseries) that I want to merge
in order to conduct a multivariate analysis.
Here is an example with only two timeseries
#Let us define the time series
> t1
Close
2007-01-02 97.87
2007-01-03 97.32
2007-01-04 96.04
2007-01-05 95.97
2007-01-07 95.69
2007-01-08 96.23
> t2
Close
2007-01-02 94.59
2007-01-03 94.38
2007-01-04 92.55
2007-01-05 92.37
2007-01-07 92.23
2007-01-08 92.89
> c(class(t1),class(t2))
[1] "xts" "zoo" "xts" "zoo"
#I do the merge using merge.xts
> t<-merge(t1,t2)
> t
Close Close
2007-01-02 97.87 NA
2007-01-02 NA 94.59
2007-01-03 97.32 94.38
2007-01-04 96.04 92.55
2007-01-05 95.97 NA
2007-01-05 NA 92.37
2007-01-07 95.69 NA
2007-01-07 NA 92.23
2007-01-08 96.23 92.89
# I don't see where the problem lays since both xts objects have
the same index
> index(t1)
[1] "2007-01-02" "2007-01-03" "2007-01-04" "2007-01-05"
"2007-01-07" "2007-01-08"
> index(t2)
[1] "2007-01-02" "2007-01-03" "2007-01-04" "2007-01-05"
"2007-01-07" "2007-01-08"
# I tried using join but with no satisfying result
>t<-merge(head(t1),head(t2),join='inner')
> t
CHFJPY..2007.....Close AUDJPY..2007.....Close
2007-01-03 97.32 94.38
2007-01-04 96.04 92.55
2007-01-08 96.23 92.89
I am really out of tricks here :-[ .
So if someone is kind enough as to point me towards the right
direction, it would make my day.
Thx in advance,
Anass
_______________________________________________
R-SIG-Finance at r-project.org <mailto:R-SIG-Finance at r-project.org>
mailing list
https://stat.ethz.ch/mailman/listinfo/r-sig-finance
-- Subscriber-posting only. If you want to post, subscribe first.
-- Also note that this is not the r-help list where general R
questions should go.
-------------- next part -------------- An HTML attachment was scrubbed... URL: <https://stat.ethz.ch/pipermail/r-sig-finance/attachments/20101210/9fd045e6/attachment.html> -------------- next part -------------- A non-text attachment was scrubbed... Name: AUDJPY.csv Type: application/vnd.ms-excel Size: 149313 bytes Desc: not available URL: <https://stat.ethz.ch/pipermail/r-sig-finance/attachments/20101210/9fd045e6/attachment.xlb> -------------- next part -------------- A non-text attachment was scrubbed... Name: CHFJPY.csv Type: application/vnd.ms-excel Size: 145700 bytes Desc: not available URL: <https://stat.ethz.ch/pipermail/r-sig-finance/attachments/20101210/9fd045e6/attachment-0001.xlb>
An embedded and charset-unspecified text was scrubbed... Name: not available URL: <https://stat.ethz.ch/pipermail/r-sig-finance/attachments/20101210/894b380d/attachment.pl>
An embedded and charset-unspecified text was scrubbed... Name: not available URL: <https://stat.ethz.ch/pipermail/r-sig-finance/attachments/20101210/8bf5a07a/attachment.pl>
An embedded and charset-unspecified text was scrubbed... Name: not available URL: <https://stat.ethz.ch/pipermail/r-sig-finance/attachments/20101210/68c3e371/attachment.pl>
Anass,
You're issue is indeed that the times aren't originally aligned, so
to.daily is just aligning to the end of the day as it sees it.
The simple solution is to re-index your data with "Date" times - since
that is really the end result of your to.daily processing expectation
(though not always what one would want, hence the additional step)
index(t1) <- as.Date(index(t1))
index(t2) <- as.Date(index(t2))
merge(t1,t2)
t1.Close t2.Close
2007-01-03 94.59 97.87
2007-01-04 94.38 97.32
2007-01-05 92.55 96.04
2007-01-05 92.37 95.97
2007-01-08 92.23 95.69
2007-01-09 92.89 96.23
to.period could probably automatically alter your index if days or
lower frequency is selected (e.g. period="days" or to.daily/monthly
etc is called), though I'll have to think about the full implication
of this (i.e. automagically or optionally).
Best,
Jeff
On Fri, Dec 10, 2010 at 5:00 AM, Anass Mouhsine
<anass.mouhsine at gmail.com> wrote:
Thks all, Robert, My packages are updated. I'll update my R version to be sure. Santosh, I'll try your methodology as well. On 10/12/2010 11:51, Santosh Srinivas wrote:
Hi ?Anass,
I like zoo more so here is what you need I think.
require(zoo)
f2 <- function(d, t, format = "%Y-%m-%d %H:%M:%S") {
as.POSIXct(strptime(paste(d, t),format=format))
}
t1=read.csv("AUDJPY.csv", sep=";")
t1.z <- read.zoo(t1, index=list(1,2), FUN =f2)
t2=read.csv("CHFJPY.csv", sep=";")
t2.z <- read.zoo(t2, index=list(1,2), FUN =f2)
head(t1.z)
head(t2.z)
t1.z.cl <- Cl(x=to.daily(t1.z$Close))
t2.z.cl <- Cl(x=to.daily(t2.z$Close))
t1.z.cl <- t1.z.cl[,4]
t2.z.cl <- t2.z.cl[,4]
head(t1.z.cl)
head(t2.z.cl)
t3 <- merge.zoo(t1.z.cl,t2.z.cl)
head(t3)
(there should ?be something similar for xts)
HTH.
*From:*r-sig-finance-bounces at r-project.org
[mailto:r-sig-finance-bounces at r-project.org] *On Behalf Of *Anass Mouhsine
*Sent:* 10 December 2010 14:50
*To:* Mark Breman; r-sig-finance at r-project.org
*Subject:* Re: [R-SIG-Finance] [xts] merge function weird behaviour
Here,
you will find attached data used to illustrate the issue.
here is the code used as well
#reading file
t1=read.csv("AUDJPY.csv", sep=";")
rownames(t1)<-t1[,"Index"];t1<-t1[,-1]
#constructing xts object
t1<-as.xts(t1,tzone="America/New_York", src="csv", updated=Sys.time())
t2=read.csv("CHFJPY.csv", sep=";")
rownames(t2)<-t2[,"Index"];t2<-t2[,-1]
t2<-as.xts(t2,tzone="America/New_York", src="csv", updated=Sys.time())
# requires quantmod
library(quantmod)
#constructing daily close series
t1<-Cl(to.daily(t1))
t2<-Cl(to.daily(t2))
#trying to merge
merge(t1,t2)
#......and result....sadly...is
merge(t1,t2)
? ? ? ? ? ?t1.Close t2.Close 2007-01-02 ? ? ? ? ?NA ? ? ? 97.87 2007-01-02 ? ? ? 94.59 ? ? ? ? ?NA 2007-01-03 ? ? ? 94.38 ? ? ? 97.32 2007-01-04 ? ? ? 92.55 ? ? ? 96.04 2007-01-05 ? ? ? ? ?NA ? ? ? 95.97 2007-01-05 ? ? ? 92.37 ? ? ? ? ?NA 2007-01-07 ? ? ? ? ?NA ? ? ? 95.69 2007-01-07 ? ? ? 92.23 ? ? ? ? ?NA 2007-01-08 ? ? ? 92.89 ? ? ? 96.23 On 10/12/2010 10:04, Mark Breman wrote: Hi Anass, Could it be the series have a (invisible) different time part, or the timezone is different. Regards, -Mark- 2010/12/10 Anass Mouhsine <anass.mouhsine at gmail.com <mailto:anass.mouhsine at gmail.com>> Hi all, I encountered a weird behaviour of merge function while using it with xts objects. I have multiple xts objects (10 timeseries) that I want to merge in order to conduct a multivariate analysis. Here is an example with only two timeseries #Let us define the time series
t1
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Close 2007-01-02 ? ? ? ? ? ? ? ? ?97.87 2007-01-03 ? ? ? ? ? ? ? ? ?97.32 2007-01-04 ? ? ? ? ? ? ? ? ?96.04 2007-01-05 ? ? ? ? ? ? ? ? ?95.97 2007-01-07 ? ? ? ? ? ? ? ? ?95.69 2007-01-08 ? ? ? ? ? ? ? ? ?96.23
t2
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Close 2007-01-02 ? ? ? ? ? ? ? ? ?94.59 2007-01-03 ? ? ? ? ? ? ? ? ?94.38 2007-01-04 ? ? ? ? ? ? ? ? ?92.55 2007-01-05 ? ? ? ? ? ? ? ? ?92.37 2007-01-07 ? ? ? ? ? ? ? ? ?92.23 2007-01-08 ? ? ? ? ? ? ? ? ?92.89
c(class(t1),class(t2))
[1] "xts" "zoo" "xts" "zoo" #I do the merge using merge.xts
t<-merge(t1,t2) t
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Close ? ? ? ? ? ? ? ? Close 2007-01-02 ? ? ? ? ? ? ? ? ?97.87 ? ? ? ? ? ? ? ? ? ? NA 2007-01-02 ? ? ? ? ? ? ? ? ? ? NA ? ? ? ? ? ? ? ? ?94.59 2007-01-03 ? ? ? ? ? ? ? ? ?97.32 ? ? ? ? ? ? ? ? ?94.38 2007-01-04 ? ? ? ? ? ? ? ? ?96.04 ? ? ? ? ? ? ? ? ?92.55 2007-01-05 ? ? ? ? ? ? ? ? ?95.97 ? ? ? ? ? ? ? ? ? ? NA 2007-01-05 ? ? ? ? ? ? ? ? ? ? NA ? ? ? ? ? ? ? ? ?92.37 2007-01-07 ? ? ? ? ? ? ? ? ?95.69 ? ? ? ? ? ? ? ? ? ? NA 2007-01-07 ? ? ? ? ? ? ? ? ? ? NA ? ? ? ? ? ? ? ? ?92.23 2007-01-08 ? ? ? ? ? ? ? ? ?96.23 ? ? ? ? ? ? ? ? ?92.89 # I don't see where the problem lays since both xts objects have the same index
index(t1)
[1] "2007-01-02" "2007-01-03" "2007-01-04" "2007-01-05" "2007-01-07" "2007-01-08"
index(t2)
[1] "2007-01-02" "2007-01-03" "2007-01-04" "2007-01-05" "2007-01-07" "2007-01-08" # I tried using join but with no satisfying result
t<-merge(head(t1),head(t2),join='inner') t
? ? ? ? ? CHFJPY..2007.....Close AUDJPY..2007.....Close 2007-01-03 ? ? ? ? ? ? ? ? ?97.32 ? ? ? ? ? ? ? ? ?94.38 2007-01-04 ? ? ? ? ? ? ? ? ?96.04 ? ? ? ? ? ? ? ? ?92.55 2007-01-08 ? ? ? ? ? ? ? ? ?96.23 ? ? ? ? ? ? ? ? ?92.89 I am really out of tricks here :-[ . So if someone is kind enough as to point me towards the right direction, it would make my day. Thx in advance, Anass
_______________________________________________ R-SIG-Finance at r-project.org <mailto:R-SIG-Finance at r-project.org> mailing list https://stat.ethz.ch/mailman/listinfo/r-sig-finance -- Subscriber-posting only. If you want to post, subscribe first. -- Also note that this is not the r-help list where general R questions should go.
? ? ? ?[[alternative HTML version deleted]]
_______________________________________________ R-SIG-Finance at r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-sig-finance -- Subscriber-posting only. If you want to post, subscribe first. -- Also note that this is not the r-help list where general R questions should go.
Jeffrey Ryan jeffrey.ryan at lemnica.com www.lemnica.com