R › R help

multivariate xts merge question

Classic

List

Threaded

2 messages Options

Soo Sun Park

multivariate xts merge question

Hi,

I have an xts starting with a number of columns (currency pairs see below),
then I add new ones which are derived from existing ones (like adding the
moving average of a column) by merging the new columns one by one. These
get the name of the column they are calculated from concatenated with ".1".
All done by merge.xts, easy.

Now, I have a function (procState below) which generates n columns looking
at rows of the xts and the column names.
I understand that an xts cannot contain vectors, lists and matrices, so I
am happy with adding n new columns from the returned data frame (vector).

Unfortunately, I have been unable to accomplish this.

apply(x,1,function(y,ns) procState(y,ns), colnames(x))

gives me a list, where a non-intrinsic attribute contains the timestamp.

$`2012-03-16 16:04:00`
aud cad chf eur gbp jpy nzd usd
1 -3 -5 7 5 1 -1 3 -7

$`2012-03-16 16:05:00`
aud cad chf eur gbp jpy nzd usd
1 -3 -5 7 5 -1 1 3 -7

How do I merge this with the original xts?
I tried merge(x,unlist(y)) to flatten it, but instead of getting n new
columns I get the timestamps repeated n times.
I do need to stick to merge.xts because there are 10 years of minute data
and the naive merge on data frames runs out of memory.

It might be that my approach isn't the Right one, I appreciate any guidance
from seasoned users.

I'm sorry for the long winded question, but tried to follow the posting
guide to the word.

Thanks,ssp

pairs<-c("audcad", "audchf", "audjpy", "audnzd", "audusd", "cadchf",
"cadjpy", "chfjpy", "euraud", "eurcad", "eurchf", "eurgbp", "eurjpy",
"eurnzd", "eurusd", "gbpaud", "gbpcad", "gbpchf", "gbpjpy", "gbpnzd",
"gbpusd", "nzdcad", "nzdchf", "nzdjpy", "nzdusd", "usdcad", "usdchf",
"usdjpy")

procState <- function(r, nams)
# r: row
# nams: column names
{
d<-data.frame(aud=0,cad=0,chf=0,eur=0,gbp=0,jpy=0,nzd=0,usd=0)

st<-head(grep(".1", nams, fixed=TRUE), n=1)
for (i in grep(".1", nams, fixed=TRUE))
{
# first and second currency

fc<-substring(pairs[[i-st+1]],1,3)
sc<-substring(pairs[[i-st+1]],4,6)

d[[fc]] <- d[[fc]]+r[i]
d[[sc]] <- d[[sc]]+(-1*r[i])
}
# as.matrix(d)
as.vector(d)
}

x<-structure(c(1.04932739130435, 1.04916, 0.969513333333333, 0.96952,
88.161, 88.141, 1.28334615384615, 1.2832350877193, 1.05805333333333,
1.05801, 0.923845, 0.923921428571429, 84.0127777777778, 84.0036666666667,
90.9286666666667, 90.909, 1.24416368421053, 1.24423, 1.30570214285714,
1.30553, 1.20644257575758, 1.20643348484848, 0.831628, 0.831781428571429,
109.7156, 109.685, 1.596928, 1.59683, 1.31658928571429, 1.31648,
1.49588142857143, 1.49558736842105, 1.56994, 1.569414, 1.450548,
1.45026, 131.9072, 131.854, 1.9200925, 1.91961195121951, 1.583044,
1.58268, 0.8174425, 0.81738, 0.7552675, 0.755292857142857,
68.6788571428571,
68.6663333333333, 0.8242475, 0.82426, 0.991685, 0.991576666666667,
0.916344615384615, 0.916331428571429, 83.3262857142857, 83.312,
1, 1, -1, -1, -1, -1, -1, -1, 1, 1, -1, -1, -1, -1, 1, 1, 1,
1, 1, 1, -1, -1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, -1, -1,
1, -1, -1, -1, 1, 1, 1, 1, -1, -1, 1, 1, 1, 1, -1, -1, -1, -1,
-1, -1), class = c("xts", "zoo"), .indexCLASS = c("POSIXct",
"POSIXt"), .indexTZ = "GMT", tclass = c("POSIXct", "POSIXt"), tzone =
"GMT", index = structure(c(1331913840,
1331913900), tzone = "GMT", tclass = c("POSIXct", "POSIXt")), .Dim = c(2L,
56L), .Dimnames = list(NULL, c("audcad", "audchf", "audjpy",
"audnzd", "audusd", "cadchf", "cadjpy", "chfjpy", "euraud", "eurcad",
"eurchf", "eurgbp", "eurjpy", "eurnzd", "eurusd", "gbpaud", "gbpcad",
"gbpchf", "gbpjpy", "gbpnzd", "gbpusd", "nzdcad", "nzdchf", "nzdjpy",
"nzdusd", "usdcad", "usdchf", "usdjpy", "audcad.1", "audchf.1",
"audjpy.1", "audnzd.1", "audusd.1", "cadchf.1", "cadjpy.1", "chfjpy.1",
"euraud.1", "eurcad.1", "eurchf.1", "eurgbp.1", "eurjpy.1", "eurnzd.1",
"eurusd.1", "gbpaud.1", "gbpcad.1", "gbpchf.1", "gbpjpy.1", "gbpnzd.1",
"gbpusd.1", "nzdcad.1", "nzdchf.1", "nzdjpy.1", "nzdusd.1", "usdcad.1",
"usdchf.1", "usdjpy.1")))
>

[[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

Michael Weylandt

Re: multivariate xts merge question

If I understand what you are looking to do, this might be the most efficient:

y < - apply(x,1,function(y,ns) procState(y,ns), colnames(x))
y <- as.xts(do.call(rbind, y))

merge(x, y)

But you might benefit from looking at zoo::rollapplyr() rather than
apply -- I think the result will be an xts (or maybe a zoo) which you
could then merge on directly.

Michael

On Fri, Apr 27, 2012 at 11:50 AM, Soo Sun Park <[hidden email]> wrote:

> Hi,
>
> I have an xts starting with a number of columns (currency pairs see below),
> then I add new ones which are derived from existing ones (like adding the
> moving average of a column) by merging the new columns one by one. These
> get the name of the column they are calculated from concatenated with ".1".
> All done by merge.xts, easy.
>
> Now, I have a function (procState below) which generates n columns looking
> at rows of the xts and the column names.
> I understand that an xts cannot contain vectors, lists and matrices, so I
> am happy with adding n new columns from the returned data frame (vector).
>
> Unfortunately, I have been unable to accomplish this.
>
> apply(x,1,function(y,ns) procState(y,ns), colnames(x))
>
> gives me a list, where a non-intrinsic attribute contains the timestamp.
>
> $`2012-03-16 16:04:00`
> aud cad chf eur gbp jpy nzd usd
> 1 -3 -5 7 5 1 -1 3 -7
>
> $`2012-03-16 16:05:00`
> aud cad chf eur gbp jpy nzd usd
> 1 -3 -5 7 5 -1 1 3 -7
>
> How do I merge this with the original xts?
> I tried merge(x,unlist(y)) to flatten it, but instead of getting n new
> columns I get the timestamps repeated n times.
> I do need to stick to merge.xts because there are 10 years of minute data
> and the naive merge on data frames runs out of memory.
>
> It might be that my approach isn't the Right one, I appreciate any guidance
> from seasoned users.
>
> I'm sorry for the long winded question, but tried to follow the posting
> guide to the word.
>
> Thanks,ssp
>
> pairs<-c("audcad", "audchf", "audjpy", "audnzd", "audusd", "cadchf",
> "cadjpy", "chfjpy", "euraud", "eurcad", "eurchf", "eurgbp", "eurjpy",
> "eurnzd", "eurusd", "gbpaud", "gbpcad", "gbpchf", "gbpjpy", "gbpnzd",
> "gbpusd", "nzdcad", "nzdchf", "nzdjpy", "nzdusd", "usdcad", "usdchf",
> "usdjpy")
>
> procState <- function(r, nams)
> # r: row
> # nams: column names
> {
> d<-data.frame(aud=0,cad=0,chf=0,eur=0,gbp=0,jpy=0,nzd=0,usd=0)
>
> st<-head(grep(".1", nams, fixed=TRUE), n=1)
> for (i in grep(".1", nams, fixed=TRUE))
> {
> # first and second currency
>
> fc<-substring(pairs[[i-st+1]],1,3)
> sc<-substring(pairs[[i-st+1]],4,6)
>
> d[[fc]] <- d[[fc]]+r[i]
> d[[sc]] <- d[[sc]]+(-1*r[i])
> }
> # as.matrix(d)
> as.vector(d)
> }
>
>
> x<-structure(c(1.04932739130435, 1.04916, 0.969513333333333, 0.96952,
> 88.161, 88.141, 1.28334615384615, 1.2832350877193, 1.05805333333333,
> 1.05801, 0.923845, 0.923921428571429, 84.0127777777778, 84.0036666666667,
> 90.9286666666667, 90.909, 1.24416368421053, 1.24423, 1.30570214285714,
> 1.30553, 1.20644257575758, 1.20643348484848, 0.831628, 0.831781428571429,
> 109.7156, 109.685, 1.596928, 1.59683, 1.31658928571429, 1.31648,
> 1.49588142857143, 1.49558736842105, 1.56994, 1.569414, 1.450548,
> 1.45026, 131.9072, 131.854, 1.9200925, 1.91961195121951, 1.583044,
> 1.58268, 0.8174425, 0.81738, 0.7552675, 0.755292857142857,
> 68.6788571428571,
> 68.6663333333333, 0.8242475, 0.82426, 0.991685, 0.991576666666667,
> 0.916344615384615, 0.916331428571429, 83.3262857142857, 83.312,
> 1, 1, -1, -1, -1, -1, -1, -1, 1, 1, -1, -1, -1, -1, 1, 1, 1,
> 1, 1, 1, -1, -1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, -1, -1,
> 1, -1, -1, -1, 1, 1, 1, 1, -1, -1, 1, 1, 1, 1, -1, -1, -1, -1,
> -1, -1), class = c("xts", "zoo"), .indexCLASS = c("POSIXct",
> "POSIXt"), .indexTZ = "GMT", tclass = c("POSIXct", "POSIXt"), tzone =
> "GMT", index = structure(c(1331913840,
> 1331913900), tzone = "GMT", tclass = c("POSIXct", "POSIXt")), .Dim = c(2L,
> 56L), .Dimnames = list(NULL, c("audcad", "audchf", "audjpy",
> "audnzd", "audusd", "cadchf", "cadjpy", "chfjpy", "euraud", "eurcad",
> "eurchf", "eurgbp", "eurjpy", "eurnzd", "eurusd", "gbpaud", "gbpcad",
> "gbpchf", "gbpjpy", "gbpnzd", "gbpusd", "nzdcad", "nzdchf", "nzdjpy",
> "nzdusd", "usdcad", "usdchf", "usdjpy", "audcad.1", "audchf.1",
> "audjpy.1", "audnzd.1", "audusd.1", "cadchf.1", "cadjpy.1", "chfjpy.1",
> "euraud.1", "eurcad.1", "eurchf.1", "eurgbp.1", "eurjpy.1", "eurnzd.1",
> "eurusd.1", "gbpaud.1", "gbpcad.1", "gbpchf.1", "gbpjpy.1", "gbpnzd.1",
> "gbpusd.1", "nzdcad.1", "nzdchf.1", "nzdjpy.1", "nzdusd.1", "usdcad.1",
> "usdchf.1", "usdjpy.1")))
>>
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

______________________________________________
[hidden email] mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.