how to re-arrange panel data

Hi all,

This is a question about cleaning data. I have some population data for a large number of countries, by year, by age bracket. It's set up vertically along 21 age brackets, so the first column would be 21 rows of 1950, the 2nd column the age brackets, the third column would be the population in a given country falling into each bracket. I need to re-arrange it so that I'd have the age brackets horizontally as headers, and each row corresponding to each year. Doing this in excel would take hours on end, I would have to manually do tranpose for each year. I've managed to tell matlab to perform this task for a single year, but to fit that into a loop, and create an ever expanding matrix, that's beyond me. My incomplete attempt is along these lines, but I've gotten stuck:

clc;

clear;

T = xlsread( 'LTSfromMortalityDatabase',2) ;

T(:,2)=[];

S=T(1:24,:);

W=zeros(1,25);

%[zzz,xxx] = size(S);

W(1)=S(1,1);

V=S(:,2)';

W(2:25)=V;

[zzz,xxx] = size(T);

for i=1:zzz

if T(i+1,1)=T(i,1) %if the nth row of column 1 is equal to the previous, keep going, otherwise stop

'''''''''''''

end

I've included a spreadsheet so you know what I'm talking about-so the first tab was me doing it manually, the three columns on the right contain the raw data, the array beside it is how I need to have the data set up.

I'd much appreciate your input.

[Jan]

Just a note: if T(i+1,1) = T(i,1) is an assignment. You mean a comparison: if T(i+1,1) == T(i,1) .

Answer 1

[Rohit]

To do this conversion for all years, you have to add some code as shown below in the for loop which would create the matrix with row corresponding to each year. You first need to clean your data to remove NaN values from years. After that you can run the code to generate matrix W will desired data.

You can also try to remove for loop by vectorizing the code.

curr_vec=[]; %will store present year population
for i=1:zzz
    if T(i+1,1)==T(i,1)  %if the nth row of column 1 is equal to the previous, keep going, otherwise stop
        curr_vec=[curr_vec T(i,2)]; %adding age-bracket population of present year
    else
        curr_vec=[curr_vec T(i,2)]; 
        curr_year=T(i);
        W=[W;curr_year curr_vec];
        curr_vec=[];    
    end
end

Refer to the below documentation links for further reference-

https://www.mathworks.com/help/matlab/math/creating-and-concatenating-matrices.html

https://www.mathworks.com/help/matlab/matlab_prog/vectorization.html