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
采纳的回答
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 个评论
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
Monkey Coder
2022-3-9
编辑：Monkey Coder
2022-3-9
更多回答（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)
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)
类别
在 帮助中心 和 File Exchange 中查找有关 Tables 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
