Transform table to another format

I have correlation data in the following format:

Headers Fund1 Fund2 Fund3

_______ _____ _____ _____

"Fund1" 1 0.3 0.5

"Fund2" 0.1 1 0.6

"Fund3" 0.2 0.4 1

Headers = ["Fund1";"Fund2";"Fund3"];

Fund1 = [1;0.1;0.2];

Fund2 = [0.3;1;0.4];

Fund3 = [0.5;0.6;1];

correlData = table(Headers, Fund1, Fund2, Fund3)

How can I transform it to the below format?

Fund_ID1 Fund_ID2 Correlation

________ ________ __________

1 1 1

1 2 0.3

1 3 0.5

2 1 0.1

2 2 1

2 3 0.6

3 1 0.5

3 2 0.6

3 3 1

Answer 1

[Simon Chan]

Use function stack is another option.

clear;clc;
Headers = ["Fund1";"Fund2";"Fund3"];
Fund1 = [1;0.1;0.2];
Fund2 = [0.3;1;0.4];
Fund3 = [0.5;0.6;1];
correlData = table(Headers, Fund1, Fund2, Fund3);
S = stack(correlData,2:4,'NewDataVariableName','Correlation','IndexVariableName','Fund_ID2')

S = 9×3 table
    Headers    Fund_ID2    Correlation
    _______    ________    ___________

    "Fund1"     Fund1            1    
    "Fund1"     Fund2          0.3    
    "Fund1"     Fund3          0.5    
    "Fund2"     Fund1          0.1    
    "Fund2"     Fund2            1    
    "Fund2"     Fund3          0.6    
    "Fund3"     Fund1          0.2    
    "Fund3"     Fund2          0.4    
    "Fund3"     Fund3            1    

S.Properties.VariableNames{1}='Fund_ID1';
S.Fund_ID1 = arrayfun(@(x) sscanf(x,'Fund%d'),S.Fund_ID1);
S.Fund_ID2 = arrayfun(@(x) sscanf(x,'Fund%d'),string(S.Fund_ID2))

S = 9×3 table
    Fund_ID1    Fund_ID2    Correlation
    ________    ________    ___________

       1           1              1    
       1           2            0.3    
       1           3            0.5    
       2           1            0.1    
       2           2              1    
       2           3            0.6    
       3           1            0.2    
       3           2            0.4    
       3           3              1    

[Peter Perkins]

It might even be usefl to start from here:

>> correlData.Headers = categorical(correlData.Headers)
correlData =
  3×4 table
    Headers    Fund1    Fund2    Fund3
    _______    _____    _____    _____
     Fund1        1      0.3      0.5 
     Fund2      0.1        1      0.6 
     Fund3      0.2      0.4        1 

and stick with with categoricals, not numbers:

> stack(correlData,2:4,'NewDataVariableName','Correlation','IndexVariableName','Fund_ID2')
ans =
  9×3 table
    Headers    Fund_ID2    Correlation
    _______    ________    ___________
     Fund1      Fund1            1    
     Fund1      Fund2          0.3    
     Fund1      Fund3          0.5    
     Fund2      Fund1          0.1    
     Fund2      Fund2            1    
     Fund2      Fund3          0.6    
     Fund3      Fund1          0.2    
     Fund3      Fund2          0.4    
     Fund3      Fund3            1    

[Monkey Coder]

Ok, thanks Peter.

Answer 2

[Arif Hoq]

Headers = ["Fund1";"Fund2";"Fund3"];
Fund1 = [1;0.1;0.2];
Fund2 = [0.3;1;0.4];
Fund3 = [0.5;0.6;1];
correlData = table(Headers, Fund1, Fund2, Fund3)

correlData = 3×4 table
    Headers    Fund1    Fund2    Fund3
    _______    _____    _____    _____

    "Fund1"       1      0.3      0.5 
    "Fund2"     0.1        1      0.6 
    "Fund3"     0.2      0.4        1 

A=table2array(correlData);
B=A(1:2,2:end)';
Correlation=[str2double(B(:)); str2double(A(:,4))];
Fund_ID1=[1 1 1 2 2 2 3 3 3]';
Fund_ID2=[1 2 3 1 2 3 1 2 3]';
Table2=table(Fund_ID1,Fund_ID2,Correlation)

Table2 = 9×3 table
    Fund_ID1    Fund_ID2    Correlation
    ________    ________    ___________

       1           1              1    
       1           2            0.3    
       1           3            0.5    
       2           1            0.1    
       2           2              1    
       2           3            0.6    
       3           1            0.5    
       3           2            0.6    
       3           3              1    

[Monkey Coder]

Thanks Arif Hoq.

Fund_ID1=[1 1 1 2 2 2 3 3 3]';

Fund_ID2=[1 2 3 1 2 3 1 2 3]';

Is there a way to read from correlData table instead of passing the Fund_ID1 and Fund_ID2 again?