3
$\begingroup$

I have the following problem: I have a (a lot)*3 table, meaning that I have 3 columns, say X, Y and Z, with real values. In this table some of the rows have the same (X,Y) values, but with different value of Z. For instance

{{12.123, 4.123, 513.423}, {12.123, 4.123, 33.43}} 

have the same (X,Y) but different Z. This is a case of multiplicity=2, but in principle it could be higher. What I want to do is to take all the unique rows, AND in case they have multiplicity >1 (i.e. repeated (X,Y)), pick the one with minimum Z value. In the previous example it would be the second one.

I hope I have been clear! Thank you very much indeed!

$\endgroup$
2
  • 1
    $\begingroup$ First /@ (SortBy[#, Last] & /@ GatherBy[data, Most]) or more compactly First /@ (Sort /@ GatherBy[data, Most]) $\endgroup$ – Bob Hanlon Apr 30 '15 at 15:00
  • $\begingroup$ @Bob I think you should post that as an answer. $\endgroup$ – C. E. Apr 30 '15 at 15:03
7
$\begingroup$
data = Table[{x, y, RandomReal[]},
   {x, 3}, {y, 3}, {3}] // Flatten[#, 2] &

{{1, 1, 0.987008}, {1, 1, 0.682772}, {1, 1, 0.923863}, {1, 2, 0.464991}, {1, 2, 0.963954}, {1, 2, 0.829995}, {1, 3, 0.773942}, {1, 3, 0.550081}, {1, 3, 0.0821332}, {2, 1, 0.466804}, {2, 1, 0.964716}, {2, 1, 0.679577}, {2, 2, 0.820486}, {2, 2, 0.10238}, {2, 2, 0.810867}, {2, 3, 0.87029}, {2, 3, 0.530354}, {2, 3, 0.91676}, {3, 1, 0.345255}, {3, 1, 0.939562}, {3, 1, 0.456195}, {3, 2, 0.497948}, {3, 2, 0.13677}, {3, 2, 0.544248}, {3, 3, 0.271007}, {3, 3, 0.539826}, {3, 3, 0.927424}}

First /@ (SortBy[#, Last] & /@ GatherBy[data, Most])

{{1, 1, 0.682772}, {1, 2, 0.464991}, {1, 3, 0.0821332}, {2, 1, 0.466804}, {2, 2, 0.10238}, {2, 3, 0.530354}, {3, 1, 0.345255}, {3, 2, 0.13677}, {3, 3, 0.271007}}

However, since {x,y} are identical in each grouping, then SortBy[#,Last]& is equivalent to Sort

First /@ (Sort /@ GatherBy[data, Most])

{{1, 1, 0.682772}, {1, 2, 0.464991}, {1, 3, 0.0821332}, {2, 1, 0.466804}, {2, 2, 0.10238}, {2, 3, 0.530354}, {3, 1, 0.345255}, {3, 2, 0.13677}, {3, 3, 0.271007}}

% == %%

True

$\endgroup$
3
$\begingroup$
DeleteDuplicates[SortBy[data, Last],( #1[[1]]==#2[[1]]  && #1[[2]]==#2[[2]] & ) ]

Explanation: data is your data of the form {{x1,y1,z1},...,{xn,yn,zn}}. With SortBy[#, Last] & we sort this dataset with respect to the last coordinate, e.g. z.

With DeleteDuplicates you can simply delete duplicate elements of a list. By specifighing a SameTest one can define which elements are treated to be equal.

The following SameTest

#1[[1]]==#2[[1]]  && #1[[2]]==#2[[2]] &

says that both x and y-coordinates should be equal so that two elements are treated to be the same.

This is a shorter form of this test:

#1[[1;;2]]==#2[[1;;2]] & 

EDIT: Probably most simple form of this approach:

DeleteDuplicates[SortBy[data, Last], Most[#1] ==Most[#2]&]

as suggested by Bob-Hanlon

$\endgroup$
5
  • 1
    $\begingroup$ The SameTest could also be written Most[#1] == Most[#2] & $\endgroup$ – Bob Hanlon Apr 30 '15 at 15:19
  • $\begingroup$ @Bob-Hanlon hey I was not aware of this function! Thanks for the hint. I'll add this to my answer! $\endgroup$ – sacratus Apr 30 '15 at 15:26
  • $\begingroup$ Very clean, but note that it gets really slow for large lists. $\endgroup$ – 2012rcampion Apr 30 '15 at 15:43
  • $\begingroup$ @2012rcampion interesting, is that because of DeleteDuplicates? I can't make a performance test at the moment, because I am on a phone. $\endgroup$ – sacratus Apr 30 '15 at 15:56
  • $\begingroup$ Yeah, I think DeleteDuplicates is slow. $\endgroup$ – 2012rcampion Apr 30 '15 at 15:57
2
$\begingroup$

Using the new Association method GroupBy:

Append @@@ Normal[GroupBy[data, Most -> Last, Min]]

This method is slightly slower than Bob Hanlon's and alephalpha's (and produces identical results), but I thought I'd show off one of the v10 features.

$\endgroup$
1
$\begingroup$
Min /@ Transpose@# & /@ GatherBy[data, Most]

Or if you're using Mathematica 10:

First@*MinimalBy[Last] /@ GatherBy[data, Most]
$\endgroup$
0
$\begingroup$
f1 = Composition[DeleteDuplicatesBy[Most], SortBy[Last]]

SeedRandom[1]
data = Append @@@ Transpose[{RandomInteger[2,{10,2}], RandomReal[1,{10}]}];
data2 = f1 @ data;
Row[MatrixForm /@ {data, data2}]

Mathematica graphics

Alternatively,

f2 = DeleteDuplicatesBy[Most] @ SortBy[Last] @ #&;
f1 @ data == f2 @ data
(* True *)
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.