R reshape to comparable long

Question

I have a matrix x

  A  B  C  D
1 11 12 13 14
2 21 22 23 24
3 31 32 33 34
4 41 42 43 44
5 51 52 53 54

and two vetors

     [,1] [,2]
[1,] "A"  "B" 
[2,] "A"  "C" 
[3,] "A"  "D" 
[4,] "B"  "C" 
[5,] "B"  "D" 
[6,] "C"  "D"

what I want to get is the following

Asset1 Asset2 Time Value1 Value2
A   B   1   11  12
A   B   2   21  22
A   B   3   31  32
A   B   4   41  42
A   B   5   51  52
A   C   1   11  13
A   C   2   21  23
A   C   3   31  33
A   C   4   41  43
A   C   5   51  53
A   D   1   11  14
A   D   2   21  24
A   D   3   31  34
A   D   4   41  44
A   D   5   51  54
…   

This is for a plot, where I want every combination of two assets. Unfortunately I can't change anything about this form.

I tried to get this result with the reshape package, but I wasn't able to work this out.

Here is also a piece of code to get the initial matrices:

x <- matrix(c(11,12,13,14, 21,22,23,24 ,31,32,33,34, 41,42,43,44, 51,52,53,54), ncol=4, nrow=5, byrow=T)
colnames(x) <- c("A", "B", "C", "D")
rownames(x) <- 1:5
c <- matrix(c("A","B", "A","C", "A","D", "B", "C", "B", "D", "C", "D"), ncol=2, byrow=T)

Thank you for your help!

Answer 1

Perhaps something using matrix indexing would be more intuitive.

# set up output matrix
out <- as.data.frame(t(combn(colnames(x), 2)), stringsAsFactors=FALSE)
names(out) <- paste0("Asset", 1:2)
out <- cbind(out[rep(1:nrow(x), each=nrow(out)),], Time=rep(1:nrow(x), nrow(out)))
rownames(out) <- NULL

# now fill it in with matrix indexing
out$Value1 <- x[cbind(out$Time, out$Asset1)]
out$Value2 <- x[cbind(out$Time, out$Asset2)]

Answer 2

I assume your first data is a data.frame. And I don't require the second matrix/data. This is what I could come up with as of now:

m <- as.data.frame(m) # convert named matrix to data.frame
do.call(rbind, lapply(as.data.frame(combn(names(m), 2)), 
               function(x) cbind(id1=rep(x[1], 5), 
               id2=rep(x[2], 5), time=1:5, setNames(m[,as.character(x)], 
               c("value1", "value2")))))

#      id1 id2 time value1 value2
# V1.1   A   B    1     11     12
# V1.2   A   B    2     21     22
# V1.3   A   B    3     31     32
# V1.4   A   B    4     41     42
# V1.5   A   B    5     51     52
# V2.1   A   C    1     11     13
# V2.2   A   C    2     21     23
# V2.3   A   C    3     31     33
# V2.4   A   C    4     41     43
# V2.5   A   C    5     51     53
# V3.1   A   D    1     11     14
# V3.2   A   D    2     21     24
# V3.3   A   D    3     31     34
# V3.4   A   D    4     41     44
# V3.5   A   D    5     51     54
# V4.1   B   C    1     12     13
# V4.2   B   C    2     22     23
# V4.3   B   C    3     32     33
# V4.4   B   C    4     42     43
# V4.5   B   C    5     52     53
# V5.1   B   D    1     12     14
# V5.2   B   D    2     22     24
# V5.3   B   D    3     32     34
# V5.4   B   D    4     42     44
# V5.5   B   D    5     52     54
# V6.1   C   D    1     13     14
# V6.2   C   D    2     23     24
# V6.3   C   D    3     33     34
# V6.4   C   D    4     43     44
# V6.5   C   D    5     53     54

Answer 3

If i am correct u can do this with only 1 command:

merge(x,c)

This produces the following:

    A  B  C  D V1 V2
1  11 12 13 14  A  B
2  21 22 23 24  A  B
3  31 32 33 34  A  B
4  41 42 43 44  A  B
5  51 52 53 54  A  B
6  11 12 13 14  A  C
7  21 22 23 24  A  C
8  31 32 33 34  A  C
9  41 42 43 44  A  C
10 51 52 53 54  A  C
11 11 12 13 14  A  D
12 21 22 23 24  A  D
13 31 32 33 34  A  D
14 41 42 43 44  A  D
15 51 52 53 54  A  D
16 11 12 13 14  B  C
17 21 22 23 24  B  C
18 31 32 33 34  B  C
19 41 42 43 44  B  C
20 51 52 53 54  B  C
21 11 12 13 14  B  D
22 21 22 23 24  B  D
23 31 32 33 34  B  D
24 41 42 43 44  B  D
25 51 52 53 54  B  D
26 11 12 13 14  C  D
27 21 22 23 24  C  D
28 31 32 33 34  C  D
29 41 42 43 44  C  D
30 51 52 53 54  C  D