Transforming a square matrix to three variables (row, column and value)

[Marina Ngoma]

Hi,

I am trying to transform a matrix into three variables: row number, column number, and matrix value (value referring to every row-column combination). Is there an efficient way of doing it in Stata?
Please find below an example of the transformation using a 15*15 matrix

Input matrix:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11 x12 x13 x14 x15)
 2  3  4  5  6  7  8  9 10 11 12 13 14 15 16
 3  4  5  6  7  8  9 10 11 12 13 14 15 16 17
 4  5  6  7  8  9 10 11 12 13 14 15 16 17 18
 4  5  6  7  8  9 10 11 12 13 14 15 16 17 18
 6  7  8  9 10 11 12 13 14 15 16 17 18 19 20
 7  8  9 10 11 12 13 14 15 16 17 18 19 20 21
 8  9 10 11 12 13 14 15 16 17 18 19 20 21 22
 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
 0  1  2  3  4  5  6  7  8  9 10 11 12 13 14
 4  5  6  7  8  9 10 11 12 13 14 15 16 17 18
 5  6  7  8  9 10 11 12 13 14 15 16 17 18 19
 7  8  9 10 11 12 13 14 15 16 17 18 19 20 21
end

I declared the matrix with the command:

mkmat x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11 x12 x13 x14 x15 , matrix(A)

Here is the desired transformation:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(row column valeur)
1  1  2
1  2  3
1  3  4
1  4  5
1  5  6
1  6  7
1  7  8
1  8  9
1  9 10
1 10 11
1 11 12
1 12 13
1 13 14
1 14 15
1 15 16
2  1  3
2  2  4
2  3  5
2  4  6
2  5  7
2  6  8
2  7  9
2  8 10
2  9 11
2 10 12
2 11 13
2 12 14
2 13 15
2 14 16
2 15 17
3  1  4
3  2  5
3  3  6
3  4  7
3  5  8
3  6  9
3  7 10
3  8 11
3  9 12
3 10 13
3 11 14
3 12 15
3 13 16
3 14 17
3 15 18
4  1  4
4  2  5
4  3  6
4  4  7
4  5  8
4  6  9
4  7 10
4  8 11
4  9 12
4 10 13
4 11 14
4 12 15
4 13 16
4 14 17
4 15 18
5  1  6
5  2  7
5  3  8
5  4  9
5  5 10
5  6 11
5  7 12
5  8 13
5  9 14
5 10 15
5 11 16
5 12 17
5 13 18
5 14 19
5 15 20
6  1  7
6  2  8
6  3  9
6  4 10
6  5 11
6  6 12
6  7 13
6  8 14
6  9 15
6 10 16
6 11 17
6 12 18
6 13 19
6 14 20
6 15 21
7  1  8
7  2  9
7  3 10
7  4 11
7  5 12
7  6 13
7  7 14
7  8 15
7  9 16
7 10 17
end

Best regards,

[FernandoRios]

If that is the only thing you have in your data, perhaps the following code would do the trick

mata:mata clear
mata: 
    void mtrans(string scalar xvars, string scalar v1 , string scalar v2 , string scalar v3) {
        real matrix x, rslt
        real scalar rx ,cx ,i,j,k
        x=st_data(.,xvars,.)
        rx=rows(x)
        cx=cols(x)
        
        rslt=J(rx*cx,3,0)
        k=0
        for(i=1;i<=rx;i++) {
            for(j=1;j<=cx;j++) {
                k++
                rslt[k,1]=i
                rslt[k,2]=j
                rslt[k,3]=x[i,j]
            }
        }
        st_addobs(cx*rx-rx)
        st_addvar("double", v1)
        st_addvar("double", v2)
        st_addvar("double", v3)
        st_store(.,v1,.,rslt[.,1])
        st_store(.,v2,.,rslt[.,2])
        st_store(.,v3,.,rslt[.,3])
    }
end 

mata:mtrans("x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11 x12 x13 x14 x15","v1","v2","v3")

[Roman Mostazir]

Is this something you want? :

input byte(x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11 x12 x13 x14 x15)

   x1        x2        x3        x4        x5        x6        x7        x8        x9       x10       x11       x12       x13       x14       x15
  1.  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16
  2.  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17
  3.  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18
  4.  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18
  5.  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20
  6.  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21
  7.  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22
  8.  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
  9. 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
 10. 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
 11. 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
 12.  0  1  2  3  4  5  6  7  8  9 10 11 12 13 14
 13.  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18
 14.  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19
 15.  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21
 16. end

gen row = _n

reshape long x, i(row) j(column)
ren x valuer

li row column valuer in 1/15, clean noobs

    row   column   valuer  
      1        1        2  
      1        2        3  
      1        3        4  
      1        4        5  
      1        5        6  
      1        6        7  
      1        7        8  
      1        8        9  
      1        9       10  
      1       10       11  
      1       11       12  
      1       12       13  
      1       13       14  
      1       14       15  
      1       15       16

[John Mullahy]

If your original matrix is z, then this command should give you a new Stata matrix "newmat" whose first column is the row index, whose second column is the column index, and whose third column is the element z[r,c]:

mata st_matrix("newmat",(vec(J(1,cols(st_matrix("z")),1::rows(st_matrix("z")))),vec(J(1,rows(st_matrix("z")),1::cols(st_matrix("z")))'),vec(st_matrix("z"))))

[Marina Ngoma]

That is exactly what I want, thank you so much Roman and Fernando!

Best,
Marina