% Rewrite WorldBank Data 
%
clear all;

% Read in Country Names and Latitude/Longitude
dname_1 = ['CountriesWithLatLong_centroid.xlsx'];
Tdata_1 = readtable(dname_1);
country_code = Tdata_1{:,'CountryCode'};
nc = size(country_code,1);

% Read in Variable Names
dname_2 = ['WDIEXCEL_Series.xlsx'];
Tdata_2 = readtable(dname_2);
series_code = Tdata_2{:,'SeriesCode'};
ns = size(series_code,1);

% Read in Variable Names
dname_3 = ['WDIEXCEL_Data.xlsx'];
Tdata_3 = readtable(dname_3);
cc_3 = Tdata_3{:,'CountryCode'};
nn = size(cc_3,1);
ii = ismember(cc_3,country_code);
Tdata_4 = Tdata_3(ii==1,:);
clear Tdata_3;

% Determine the number of countries for which each data series is available
% by year

cc = Tdata_4{:,'IndicatorCode'};

sumtab = zeros(ns,20);
for t = 1:20;
    str = ['x' num2str(t+1999)];
    bb = Tdata_4{:,str};
    for is = 1:ns;
        jj = ismember(cc,series_code(is));
        dd = bb(jj==1);
        ii = 1-isnan(dd);
        sumtab(is,t) = sum(ii);
    end;
end;
save('sumtab','sumtab');

load('sumtab');

% For each year number of series with more than 50, 100, 150 or 200
% countries
nsytab = zeros(20,4);
for t = 1:20;
    a = sumtab(:,t);
    i50 = sum(a >= 50);
    i100 = sum(a >= 100);
    i150 = sum(a >= 150);
    i200 = sum(a >= 200);
    b = [i50 i100 i150 i200];
    fprintf('%4i: %6i %6i %6i %6i \n',[t+1999 b]);
end;

% Spatial Data set ... Choose Variables with more than 100 countries in
% 2015
ii = (sumtab(:,16) >= 100);   % Years are 2000-2019 .. year 16 is 2015
Tdata_2_2015 = Tdata_2(ii==1,:);
series_code_2015 = Tdata_2_2015{:,'SeriesCode'};

% Save Tdata_2 .. Series Names of selected series
str_xlsx = 'WB_Series.xlsx';
writetable(Tdata_2_2015,str_xlsx,'Writemode','replacefile');

% Create Table of Entries for 2005 through 2015
% Column 1 is country label
% Column 2 is lattitude
% Column 3 is longitude
% Columns 4 - end are series

str_yr = 'x2005';
str_xlsx = 'WB_2005.xlsx'
Ty = Tdata_1(:,{'CountryCode','Lattitude','Longitude'});
% Add series one at a time;
cc = Tdata_4{:,'IndicatorCode'};
for is = 1:size(series_code_2015,1);
  varname = series_code_2015(is);
  x = NaN(nc,1);
  ii = ismember(cc,varname);
  tmp = Tdata_4(ii==1,:);
  for ic = 1:nc;
     jj = ismember(tmp(:,'CountryCode'),Ty(ic,'CountryCode'));
     x(ic) = tmp{jj==1,str_yr};
  end;
  Ty = [Ty table(x,'VariableNames',varname)];
end;
writetable(Ty,str_xlsx,'Writemode','replacefile');

str_yr = 'x2006';
str_xlsx = 'WB_2006.xlsx'
Ty = Tdata_1(:,{'CountryCode','Lattitude','Longitude'});
% Add series one at a time;
cc = Tdata_4{:,'IndicatorCode'};
for is = 1:size(series_code_2015,1);
  varname = series_code_2015(is);
  x = NaN(nc,1);
  ii = ismember(cc,varname);
  tmp = Tdata_4(ii==1,:);
  for ic = 1:nc;
     jj = ismember(tmp(:,'CountryCode'),Ty(ic,'CountryCode'));
     x(ic) = tmp{jj==1,str_yr};
  end;
  Ty = [Ty table(x,'VariableNames',varname)];
end;
writetable(Ty,str_xlsx,'Writemode','replacefile');

str_yr = 'x2007';
str_xlsx = 'WB_2007.xlsx'
Ty = Tdata_1(:,{'CountryCode','Lattitude','Longitude'});
% Add series one at a time;
cc = Tdata_4{:,'IndicatorCode'};
for is = 1:size(series_code_2015,1);
  varname = series_code_2015(is);
  x = NaN(nc,1);
  ii = ismember(cc,varname);
  tmp = Tdata_4(ii==1,:);
  for ic = 1:nc;
     jj = ismember(tmp(:,'CountryCode'),Ty(ic,'CountryCode'));
     x(ic) = tmp{jj==1,str_yr};
  end;
  Ty = [Ty table(x,'VariableNames',varname)];
end;
writetable(Ty,str_xlsx,'Writemode','replacefile');

str_yr = 'x2008';
str_xlsx = 'WB_2008.xlsx'
Ty = Tdata_1(:,{'CountryCode','Lattitude','Longitude'});
% Add series one at a time;
cc = Tdata_4{:,'IndicatorCode'};
for is = 1:size(series_code_2015,1);
  varname = series_code_2015(is);
  x = NaN(nc,1);
  ii = ismember(cc,varname);
  tmp = Tdata_4(ii==1,:);
  for ic = 1:nc;
     jj = ismember(tmp(:,'CountryCode'),Ty(ic,'CountryCode'));
     x(ic) = tmp{jj==1,str_yr};
  end;
  Ty = [Ty table(x,'VariableNames',varname)];
end;
writetable(Ty,str_xlsx,'Writemode','replacefile');

str_yr = 'x2009';
str_xlsx = 'WB_2009.xlsx'
Ty = Tdata_1(:,{'CountryCode','Lattitude','Longitude'});
% Add series one at a time;
cc = Tdata_4{:,'IndicatorCode'};
for is = 1:size(series_code_2015,1);
  varname = series_code_2015(is);
  x = NaN(nc,1);
  ii = ismember(cc,varname);
  tmp = Tdata_4(ii==1,:);
  for ic = 1:nc;
     jj = ismember(tmp(:,'CountryCode'),Ty(ic,'CountryCode'));
     x(ic) = tmp{jj==1,str_yr};
  end;
  Ty = [Ty table(x,'VariableNames',varname)];
end;
writetable(Ty,str_xlsx,'Writemode','replacefile');

str_yr = 'x2010';
str_xlsx = 'WB_2010.xlsx'
Ty = Tdata_1(:,{'CountryCode','Lattitude','Longitude'});
% Add series one at a time;
cc = Tdata_4{:,'IndicatorCode'};
for is = 1:size(series_code_2015,1);
  varname = series_code_2015(is);
  x = NaN(nc,1);
  ii = ismember(cc,varname);
  tmp = Tdata_4(ii==1,:);
  for ic = 1:nc;
     jj = ismember(tmp(:,'CountryCode'),Ty(ic,'CountryCode'));
     x(ic) = tmp{jj==1,str_yr};
  end;
  Ty = [Ty table(x,'VariableNames',varname)];
end;
writetable(Ty,str_xlsx,'Writemode','replacefile');

str_yr = 'x2011';
str_xlsx = 'WB_2011.xlsx'
Ty = Tdata_1(:,{'CountryCode','Lattitude','Longitude'});
% Add series one at a time;
cc = Tdata_4{:,'IndicatorCode'};
for is = 1:size(series_code_2015,1);
  varname = series_code_2015(is);
  x = NaN(nc,1);
  ii = ismember(cc,varname);
  tmp = Tdata_4(ii==1,:);
  for ic = 1:nc;
     jj = ismember(tmp(:,'CountryCode'),Ty(ic,'CountryCode'));
     x(ic) = tmp{jj==1,str_yr};
  end;
  Ty = [Ty table(x,'VariableNames',varname)];
end;
writetable(Ty,str_xlsx,'Writemode','replacefile');

str_yr = 'x2012';
str_xlsx = 'WB_2012.xlsx'
Ty = Tdata_1(:,{'CountryCode','Lattitude','Longitude'});
% Add series one at a time;
cc = Tdata_4{:,'IndicatorCode'};
for is = 1:size(series_code_2015,1);
  varname = series_code_2015(is);
  x = NaN(nc,1);
  ii = ismember(cc,varname);
  tmp = Tdata_4(ii==1,:);
  for ic = 1:nc;
     jj = ismember(tmp(:,'CountryCode'),Ty(ic,'CountryCode'));
     x(ic) = tmp{jj==1,str_yr};
  end;
  Ty = [Ty table(x,'VariableNames',varname)];
end;
writetable(Ty,str_xlsx,'Writemode','replacefile');

str_yr = 'x2013';
str_xlsx = 'WB_2013.xlsx'
Ty = Tdata_1(:,{'CountryCode','Lattitude','Longitude'});
% Add series one at a time;
cc = Tdata_4{:,'IndicatorCode'};
for is = 1:size(series_code_2015,1);
  varname = series_code_2015(is);
  x = NaN(nc,1);
  ii = ismember(cc,varname);
  tmp = Tdata_4(ii==1,:);
  for ic = 1:nc;
     jj = ismember(tmp(:,'CountryCode'),Ty(ic,'CountryCode'));
     x(ic) = tmp{jj==1,str_yr};
  end;
  Ty = [Ty table(x,'VariableNames',varname)];
end;
writetable(Ty,str_xlsx,'Writemode','replacefile');

str_yr = 'x2014';
str_xlsx = 'WB_2014.xlsx'
Ty = Tdata_1(:,{'CountryCode','Lattitude','Longitude'});
% Add series one at a time;
cc = Tdata_4{:,'IndicatorCode'};
for is = 1:size(series_code_2015,1);
  varname = series_code_2015(is);
  x = NaN(nc,1);
  ii = ismember(cc,varname);
  tmp = Tdata_4(ii==1,:);
  for ic = 1:nc;
     jj = ismember(tmp(:,'CountryCode'),Ty(ic,'CountryCode'));
     x(ic) = tmp{jj==1,str_yr};
  end;
  Ty = [Ty table(x,'VariableNames',varname)];
end;
writetable(Ty,str_xlsx,'Writemode','replacefile');

str_yr = 'x2015';
str_xlsx = 'WB_2015.xlsx'
Ty = Tdata_1(:,{'CountryCode','Lattitude','Longitude'});
% Add series one at a time;
cc = Tdata_4{:,'IndicatorCode'};
for is = 1:size(series_code_2015,1);
  varname = series_code_2015(is);
  x = NaN(nc,1);
  ii = ismember(cc,varname);
  tmp = Tdata_4(ii==1,:);
  for ic = 1:nc;
     jj = ismember(tmp(:,'CountryCode'),Ty(ic,'CountryCode'));
     x(ic) = tmp{jj==1,str_yr};
  end;
  Ty = [Ty table(x,'VariableNames',varname)];
end;
writetable(Ty,str_xlsx,'Writemode','replacefile');