how to re-arrange panel data

2 views (last 30 days)
Fede C 2018 London
Fede C 2018 London on 12 Oct 2022
Edited: Jan on 12 Oct 2022
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.
  1 Comment
Jan
Jan on 12 Oct 2022
Edited: Jan on 12 Oct 2022
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) .

Sign in to comment.

Answers (0)

Categories

Find more on Numeric Types in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!

Translated by