Subtract every column from each other in an xts object

Question

I am a newbie learning to code and have an xts object of 1000 rows and 10 columns. I need to subtract every column from each other creating a new xts object keeping the date column. I've tried to use combn but could not get it to create B-A result since it did A-B. What I'm looking for is below.

 DATA                                           RESULT
            A  B   C     --->               A-B   A-C  B-A  B-C  C-A  C-B
2010-01-01  1  3   5           2010-01-01   -2    -4    2    -2    4    2 
2010-01-02  2  4   6           2010-01-02   -2    -4    2    -2    4    2
2010-01-03  3  5   2           2010-01-03   -2     1    2     3   -1   -3

You can try with `outer` to get all the combinations of the column names. — akrun, Aug 11 '15 at 16:12
Akrun- not familiar with outer. using test as xts object I tried: z<- outer(colnames(test)[2:ncol(test)], test, function(x) test[[x[1]]] - test[[x[2]]]) — doug, Aug 11 '15 at 16:24
For an `xts` object, there is no column called 'Date'. It should be `index` — akrun, Aug 11 '15 at 16:36

akrun · Answer 1 · 2015-08-11T16:29:34.130

1

We could use outer to get pairwise combinations of the column names, subset the dataset 'xt1' based on the column names, get the difference in a list.

f1 <- Vectorize(function(x,y) list(setNames(xt1[,x]-xt1[,y],
                                         paste(x,y, sep='_'))))
lst <- outer(colnames(xt1), colnames(xt1), FUN = f1)

We Filter out the list elements that have sum=0 i.e. the difference between columns A-A, B-B, and C-C, and cbind to get the expected output.

 res <- do.call(cbind,Filter(sum, lst))
 res[,order(colnames(res))]
 #            A_B A_C B_A B_C C_A C_B
 #2010-01-01  -2  -4   2  -2   4   2
 #2010-01-02  -2  -4   2  -2   4   2
 #2010-01-03  -2   1   2   3  -1  -3

data

 d1 <- data.frame(A=1:3, B=3:5, C=c(5,6,2))
 library(xts)
  xt1 <- xts(d1, order.by=as.Date(c('2010-01-01', '2010-01-02', '2010-01-03')))

edited Aug 11 '15 at 16:29

answered Aug 11 '15 at 16:23

akrun

874,273
37
540
662

akun- my xts object test when I use str(test) after xts Attributes: it says List of 2 $ src : chr "yahoo" $ updated: POSIXct[1:1], format: "2015-08-10 14:00:08" so when i try your code using test it gives me NULL. is this the reason? If so any suggestions on how to fix? Thanks – doug Aug 11 '15 at 16:39
@doug Anyway, I created an xts object based on what you showed and got the exact output you showed. – akrun Aug 11 '15 at 16:40
@doug Are you talking about the `NULL` in the `dimnames` attribute? In my case it is NULL because I didn't create one. – akrun Aug 11 '15 at 16:45

sdgfsdh · Accepted Answer · 2015-08-11T16:47:24.880

I built the data using:

x <- zoo::zoo(
    data.frame(
        A = c(1, 2, 3), 
        B = c(3, 4, 5), 
        C = c(5, 6, 2)), 
    order.by = as.Date(c("2010-01-01", "2010-01-02", "2010-01-03")))

Then I defined a function for creating all possible pairs of two sets:

cross <- function(x, y = x) {

    result <- list()

    for (a in unique(x)) {

        for (b in unique(y)) {

            result <- append(result, list(list(left = a, right = b)))
        }
    }

    result
}

To answer your question:

# Build a list of column combinations
combinations <- cross(names(x), setdiff(names(x), names(x)[1]))

# Remove any entries where the left equals the right
combinations <- combinations[vapply(combinations, function(x) { x$left != x$right }, logical(1))]

# Build a user friendly list of names
names(combinations) <- vapply(combinations, function(x) { paste0(x$left, "-", x$right) }, character(1))

# Do the actual computation and combine the results into one object 
do.call(cbind, lapply(combinations, function(x, data) { data[, x$left, drop = T] - data[, x$right, drop = T] }, data = x))

This worked great. I'm now trying to work through the logic so I can become a better programmer. Thank you and akrun for your help and fast responses. Doug — doug, Aug 11 '15 at 16:51
In RStudio, click on a function and press F1 to read more about it. Good luck! — sdgfsdh, Aug 11 '15 at 16:53

Subtract every column from each other in an xts object

2 Answers2

data