Transform table to another format

3 views (last 30 days)
Monkey Coder
Monkey Coder on 8 Mar 2022
Edited: Monkey Coder on 9 Mar 2022
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

Accepted Answer

Simon Chan
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 = 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
  2 Comments

Sign in to comment.

More Answers (1)

Arif Hoq
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)
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
  1 Comment
Monkey Coder
Monkey Coder on 8 Mar 2022
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?

Sign in to comment.

Products


Release

R2018a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!