Transform table to another format
2 views (last 30 days)
Show older comments
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
0 Comments
Accepted Answer
Simon Chan
on 8 Mar 2022
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.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))
2 Comments
Peter Perkins
on 9 Mar 2022
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
More Answers (1)
Arif Hoq
on 8 Mar 2022
Edited: Arif Hoq
on 8 Mar 2022
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)
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)
See Also
Categories
Find more on Industrial Statistics in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!