# Select a certain row among the duplicates

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!

• First /@ (SortBy[#, Last] & /@ GatherBy[data, Most]) or more compactly First /@ (Sort /@ GatherBy[data, Most]) – Bob Hanlon Apr 30 '15 at 15:00
• @Bob I think you should post that as an answer. – C. E. Apr 30 '15 at 15:03

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

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

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

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.

Min /@ Transpose@# & /@ GatherBy[data, Most]


Or if you're using Mathematica 10:

First@*MinimalBy[Last] /@ GatherBy[data, Most]

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}]


Alternatively,

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