Transform table to another format

1 次查看（过去 30 天）
Monkey Coder
Monkey Coder 2022-3-8
编辑： Monkey Coder 2022-3-9
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

请先登录，再回答此问题。

采纳的回答

Simon Chan
Simon Chan 2022-3-8
Ran in:
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 个评论
Peter Perkins
Peter Perkins 2022-3-9
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

请先登录，再进行评论。

更多回答（1 个）

Arif Hoq
Arif Hoq 2022-3-8
编辑：Arif Hoq 2022-3-8
Ran in:
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 个评论
Monkey Coder
Monkey Coder 2022-3-8
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?

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Tables 的更多信息

标签

产品


版本

R2018a

Community Treasure Hunt

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

Start Hunting!

Translated by