Transpose pivot reshape a MATLAB table from a cross-section wide 'row format' to a panel-data long 'column' format

I have a large table with cross-section data that I want to convert into a panel/long record, see example below i.e. 'Data structure wanted'. My question is how to get to the 'data structure wanted' from the source MATLAB table?

Note that the following MATLAB central code - which uses the cellmat and mat2cell function - works well for the problem at hand: https://nl.mathworks.com/matlabcentral/answers/73483-transposing-blocks-of-matrices-from-a-bigger-initial-matrix#answer_83415. The code is somewhat difficult to explain to new and inexperienced MATLAB users though.

What are code alternatives - without looping - to create a panel data structure for the example below? Peferably this code is flexible for a variable number of companies and variables.

clc,clear
% The data reads as: company-id (col 1), variable-id (col 2), and an 'array of values' for 3 years (col 3-5). 
data=[1 1 1 2 3 ;    
   1 2 11 22 33 ;   
   2 1 111 222 333;
   2 2 1111 2222 3333];  % 4*5 
T = array2table(data, 'VariableNames',{'CoId','VarId','Y1', 'Y2', 'Y3'}); % no multi column
display('Source data structure:');
T
%T =  4×5 table
%     CoId    VarId     Y1      Y2      Y3 
%     ____    _____    ____    ____    ____
%      1        1         1       2       3
%      1        2        11      22      33
%      2        1       111     222     333
%      2        2      1111    2222    3333

% Wanted: CoId VarId_1 VarId_2
b=[1 1 11; 1 2 22; 1 3 33; 2 111 4444; 2 222 4444; 2 333 4444];
T = array2table(b); 
T.Properties.VariableNames=["CoId","VarId_1","VarId_2"]; % Add variable names
display('Data structure wanted:');
T
% T =
%   6×3 table
%     CoId    VarId_1    VarId_2
%     ____    _______    _______
%      1          1         11  
%      1          2         22  
%      1          3         33  
%      2        111       4444  
%      2        222       4444  
%      2        333       4444  

Answer 1

[Steven Lord]

I'm not sure where those 4444 values came from in your second T variable, but I think what you want is to stack your table data. See the examples on the documentation page and see if they look like what you're trying to do.

If you're working in the Live Editor you could use the Stack Table Variables task to experiment with the various options interactively.

Or perhaps you're trying to do what I thought of when I first read the subject of your question which was to exchange rows and variables in the table using the rows2vars function.

[Rob Grim]

Dear Steven,

Thank for your help and directions! Sorry for the 4444 values these are not correct indeed. I have replicated the data structures below:

Source data structure:

T = 4×5 table

CoId VarId Y1 Y2 Y3

____ _____ ____ ____ ____

1 1 1 2 3

1 2 11 22 33

2 1 111 222 333

2 2 1111 2222 3333

Data structure wanted:

Tw = 6×3 table

CoId VarId_1 VarId_2

____ _______ _______

1 1 11

1 2 22

1 3 33

2 111 3333

2 222 3333

2 333 3333

I am not sure how to get the structure above with the stack function. With stack I can merge variables into a new variable - and make several groups and merge these into new variables. With stack all variables are stacked per company and per year (see S table below).

What I want though is to stack the years for which I have data per company (CoID) AND list all variables per company (see VarId in the source data) next to each other as in the Tw table above (see VarId_1 and VarId_2).

S =

12×4 table

CoId VarId Year VarAll

____ _____ ____ ______

1 1 Y1 1

1 1 Y2 2

1 1 Y3 3

1 2 Y1 11

1 2 Y2 22

1 2 Y3 33

2 1 Y1 111

2 1 Y2 222

2 1 Y3 333

2 2 Y1 1111

2 2 Y2 2222

2 2 Y3 3333

As said the following MATLAB central code - which uses the cellmat and mat2cell function - works well for the problem at hand: https://nl.mathworks.com/matlabcentral/answers/73483-transposing-blocks-of-matrices-from-a-bigger-initial-matrix#answer_83415.The code is somewhat difficult to explain to new and inexperienced MATLAB users though. If there are 'simpler' approaches to pivot these data structures I am curious to see the best practices.

All best,

Rob