How do I group/merge an O-D matrix?

Hi, I have an excel sheet with an OD matrix (lets say 200x200) which looks like that:

        KK  KK  KK  LL  KK  LL
  200x200      10010  10020  10030  10040  10100  10110
    Name    alpha  beta  gamma  delta  epsilon  zeta
      Sum  5,53  3,202  4,263  2,181  9,931  11,141
KK  10010  alpha  0,694  0,009  0,148  0,197  0,101  0,106  0,133
KK  10020  beta  6,081  1,017  0,503  0,67  0,343  1,764  1,784
KK  10030  gamma  9,337  1,562  0,773  1,029  0,526  2,708  2,739
LL  10040  delta  15,296  2,559  1,266  1,685  0,862  4,437  4,487
KK  10100  epsilon  3,854  0,303  0,326  0,434  0,222  0,788  1,781
LL  10110  zeta  0,986  0,08  0,186  0,248  0,127  0,128  0,217

The first column/row describes the category (KK,LL etc - not sorted) of each zone (zoneIDs 10010,10020,10030 etc -column/row 2, zoneName alpha, beta, gamma etc - column/row 3). The 4th column/row contains the respective sums for all zones of each row/column respectively.

What I want to do is merge this table into one that contains information only according to zone category (in this example only KK and LL - lets say in total 30 categories), so that it looks like this (the merged sums here are random numbers):

30x30    KK  LL
  Sum  14,1  11,65
KK  13  4,8  8,2
LL  12,75  9,3  3,45

Any ideas how to do it with the least possible amount of for loops?

Thanks!

Answer 1

[Andrei Bobrov]

[n,t] = xlsread('data2.xlsx');

tt = {t(:,1),t(1,:)'};
v = cell(2);
for jj = 1:2
    t1 = tt{jj}(~cellfun(@isempty,tt{jj}));
    [a,b,c] = unique(t1,'first');
    [~,ii]=sort(b);
    a=a(ii);
    [~,iii]=sort(ii);
    c=iii(c);
    v(:,jj) = {a;c};
end
[x,y] = ndgrid(v{2,:});
d = accumarray([x(:),y(:)],reshape(n(4:end,4:end),[],1));
out = cell(size(d)+2);
out(3:end,1) = v{1,1};
out(1,3:end) = v{1,2}';
out(2:end,2:end) = num2cell([nan,sum(d);sum(d,2),d]);