how to re-arrange panel data
2 views (last 30 days)
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:
T = xlsread( 'LTSfromMortalityDatabase',2) ;
%[zzz,xxx] = size(S);
[zzz,xxx] = size(T);
if T(i+1,1)=T(i,1) %if the nth row of column 1 is equal to the previous, keep going, otherwise stop
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.