清空筛选条件
清空筛选条件

How can I merge similar rows in a matrix based on the first three columns' value.

10 次查看（过去 30 天）
Shayan Taheri
Shayan Taheri 2022-1-2
评论： Voss 2022-1-3
I have a very big matrix with 4 columns. The first three columns are coordinates of a point in a discrete 3D space, and the last column is the weight of that point. For example:
A = [1,1,1,0.2; 1,1,2,0.9; 1,2,1,1.2; ...]
Some of the coordinates, however, are duplicates with different weights. For example I might have:
A = [1,1,1,0.2; 1,1,1,2.3; 1,1,2,-0.3; ...]
What I want to achieve is to remove the duplicate coordinates, and use the mean of their weights as the weight for that coordinate. For example, after this operation, the last example will become:
A_new = [1,1,1,1.25; 1,1,2,-0.3; ...]
I have already written a code and it works is:
A_new = unique(A(:,1:3),"rows");
A_new = [A_new zeros(length(A_new),1)];
for i = 1:length(A_new)
coord = A_new(i,1:3);
dups = A(all(A(:,1:3)==coord,2), 4);
A_new(i,4) = mean(dups);
end
But it is very slow for large matrix (e.g., 1000000 rows). Can I optimize this code in anyway?
Thank you in advance.
Shayan

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

采纳的回答

Cris LaPierre
Cris LaPierre 2022-1-2
Ran in:
Use groupsummary. Group by the first 3 columns, and use 'mean' to determine the value of the fourth. I find it easier to use on tables, so I convert A to a table first.
A = [1,1,1,0.2; 1,1,1,2.3; 1,1,2,-0.3];
A = array2table(A);
B = groupsummary(A,1:3,'mean',4)
B = 2×5 table
A1 A2 A3 GroupCount mean_A4 __ __ __ __________ _______ 1 1 1 2 1.25 1 1 2 1 -0.3

更多回答（1 个）

Voss
Voss 2022-1-2
Ran in:
Generate some random data mimicking your situation:
[X,Y,Z] = ndgrid(1:2,1:3,1:2);
A = [X(:) Y(:) Z(:) rand(numel(X),1)];
A(:,3) = 1;
disp(A);
1.0000 1.0000 1.0000 0.5270 2.0000 1.0000 1.0000 0.5825 1.0000 2.0000 1.0000 0.3314 2.0000 2.0000 1.0000 0.3005 1.0000 3.0000 1.0000 0.4200 2.0000 3.0000 1.0000 0.1138 1.0000 1.0000 1.0000 0.1304 2.0000 1.0000 1.0000 0.1349 1.0000 2.0000 1.0000 0.4907 2.0000 2.0000 1.0000 0.8794 1.0000 3.0000 1.0000 0.3126 2.0000 3.0000 1.0000 0.2244
Use a loop like yours but comparing indices:
[A_new,~,ii] = unique(A(:,1:3),'rows');
A_new = [A_new zeros(size(A_new,1),1)];
for i = 1:size(A_new,1)
A_new(i,4) = mean(A(ii == i,4));
end
disp(A_new);
1.0000 1.0000 1.0000 0.3287 1.0000 2.0000 1.0000 0.4111 1.0000 3.0000 1.0000 0.3663 2.0000 1.0000 1.0000 0.3587 2.0000 2.0000 1.0000 0.5899 2.0000 3.0000 1.0000 0.1691
Or do the same thing with arrayfun():
[A_new,~,ii] = unique(A(:,1:3),'rows');
A_new(:,end+1) = arrayfun(@(i)mean(A(ii == i,4)),1:size(A_new,1));
disp(A_new);
1.0000 1.0000 1.0000 0.3287 1.0000 2.0000 1.0000 0.4111 1.0000 3.0000 1.0000 0.3663 2.0000 1.0000 1.0000 0.3587 2.0000 2.0000 1.0000 0.5899 2.0000 3.0000 1.0000 0.1691
  2 个评论
Shayan Taheri
Shayan Taheri 2022-1-2
Thank you very much for your suggestion. This method was definately cleaner than my code, though it wasnt't much different in terms of speed. I ran it for an array of 454000 rows and the processing time was 409 seconds. The other solution based on groupsummary achieved 14 seconds.
Voss
Voss 2022-1-3
Good to know. I wasn't sure either of these ways would be much different than what you had in terms of speed.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Matrix Indexing 的更多信息

标签

产品


版本

R2021a

Community Treasure Hunt

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

Start Hunting!

Translated by