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

2 次查看（过去 30 天）
Iro
Iro 2014-3-1
回答： Andrei Bobrov 2014-3-1
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!

请先登录，再回答此问题。

采纳的回答

Andrei Bobrov
Andrei Bobrov 2014-3-1
[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]);

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Shifting and Sorting Matrices 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by