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
    Commented Apr 30, 2015 at 15:00
  • $\begingroup$ @Bob I think you should post that as an answer. $\endgroup$
    – C. E.
    Commented Apr 30, 2015 at 15:03

5 Answers 5

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
    Commented Apr 30, 2015 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
    Commented Apr 30, 2015 at 15:26
  • $\begingroup$ Very clean, but note that it gets really slow for large lists. $\endgroup$ Commented Apr 30, 2015 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
    Commented Apr 30, 2015 at 15:56
  • $\begingroup$ Yeah, I think DeleteDuplicates is slow. $\endgroup$ Commented Apr 30, 2015 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 and acknowledge you have read our privacy policy.

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