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 中查找有关 Descriptive Statistics 的更多信息

标签

产品


版本

R2018a

Community Treasure Hunt

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

Start Hunting!

Translated by