MATLAB Answers

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

16 views (last 30 days)
Rob Grim
Rob Grim on 8 Sep 2021
Commented: Rob Grim on 10 Sep 2021
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

Answers (1)

Steven Lord
Steven Lord on 8 Sep 2021
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.
  1 Comment
Rob Grim
Rob Grim on 10 Sep 2021
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

Sign in to comment.

Products


Release

R2021a

Community Treasure Hunt

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

Start Hunting!