29
$\begingroup$

I have some data:

data={{"a", 1, 1}, {"a", 1, 2}, {"a", 1, 3}, 
      {"c", 2, 1}, {"b", 2, 2}, {"b", 2, 3}, 
      {"c", 3, 1}, {"a", 3, 2}, {"a", 3, 3}}

When I use:

Sort[data]

I get the list ordered in incremental order first with column 1 then by 2 and last by the third. Suppose I want the first column in increasing order but the second in decreasing order and the third again in increasing order. How do I do that?

I tried with Sort and SortBy but I haven't managed to make it work.

The output should be:

a 3 2

a 3 3

a 1 1

a 1 2

a 1 3

b 2 2

b 2 3

c 3 1

c 2 1

$\endgroup$
1
  • $\begingroup$ An interesting variation of the usual and now boring sort-by-column question. +1 $\endgroup$
    – Mr.Wizard
    Mar 15, 2012 at 8:24

5 Answers 5

33
$\begingroup$

Caveat lector: Incorrect results are generated by this solution, e.g.,

sortByColumn[{{"a", 1, 1}, {"b", 2, 3}, {"a", 3, 2}}, {1, 1, -1}]

returns

{{"a", 1, 1}, {"b", 2, 3}, {"a", 3, 2}}

when the correct result is obviously

{{"a", 1, 1}, {"a", 3, 2}, {"b", 2, 3}}

I've commented on the answer to bring it to the attention of the author, however seeing as they've not been here in some time, I'm also putting this here: I think a highly upvoted and accepted answer needs to be correct. - ciao

Here is my contribution, which has the following benefits over previous answers:

  • It sorts both numbers and non-numeric structures
  • You can sort any column (not just the first, followed by the second, etc)
  • You can sort in either direction (ascending / descending)
  • Original order is kept: if you sort on the second column, the first entry will follow the order of the original list. See the example with {0,-1}
  • Edit also allow specifying the priority of the columns. So given {-1,1} for the ordering, you can specify {1,2} to give the higher priority to the second column.

The code is as follows, including my usage code for my own comments.

Clear[sortByColumn]
sortByColumn::usage = 
  "Arguments: [Table, Direction, Priority]. Returns the list sorted \
by the directions for each column specified in `Direction`. For \
ascending order, use `1`, and for descending order, use `-1`. For \
sorting more than one column, input `Direction` as a list. For \
example, Direction={-1,1} will sort the first column in descending \
order followed by the second column in ascending order, ignoring any \
other column. To sort on the second column, use {0,1} for the syntax.

  When sorting two or more columns, you can provide the `Priority` \
for which column should be sorted first. For example, \
`sortByColumn[data,{-1,1},{1,2}]` would sort first in ascending order \
on the second column (because it has a higher priority) and then in \
descending order on the first column.";

sortByColumn[list_?MatrixQ, dir : _Integer | {__Integer}, priority_: {}] := 
 Module[{l = Length@list[[1, All]], w, p, d},
  w = Reverse@Range@l;
  p = If[Length@priority > 0, PadRight[Flatten@{priority}, l], 
    p = Range@l];
  w = w[[Ordering@p]];
  d = PadRight[Flatten@{dir}, l];
  Sort[list, NonNegative@Total[(w d MapThread[Order, {##}])] &]]

For example, using the data set provided by Mr. Wizard:

data={{"a", 1, 1}, {"a", 1, 5}, {"a", 1, 3}, 
      {"c", 2, 1}, {"b", 2, 2}, {"b", 2, 3}, 
      {"c", 3, 1}, {"a", 3, 2}, {"a", 3, 3}};
data[[All, 2]] = data[[All, 2]] /. {1 -> "q", 2 -> "r", 3 -> "s"};

Here are the results of some trial runs. First the original:

{a,q,1}
{a,q,2}
{a,q,3}
{c,r,1}
{b,r,2}
{b,r,3}
{c,s,1}
{a,s,2}
{a,s,3}

The result of sortByColumn[data,-1].

{c,r,1}
{c,s,1}
{b,r,2}
{b,r,3}
{a,q,1}
{a,q,2}
{a,q,3}
{a,s,2}
{a,s,3}

Result of sortByColumn[data,{0,-1}]

{c,s,1}
{a,s,2}
{a,s,3}
{c,r,1}
{b,r,2}
{b,r,3}
{a,q,1}
{a,q,2}
{a,q,3}

And finally, the result the OP wanted, sortByColumn[data,{1,-1,1}]

{a,s,2}
{a,s,3}
{a,q,1}
{a,q,2}
{a,q,3}
{b,r,2}
{b,r,3}
{c,s,1}
{c,r,1}

An example showing the use of the priority argument: sortByColumn[data, {-1, 1}, {1, 2}]

{a,q,1}
{a,q,5}
{a,q,3}
{c,r,1}
{b,r,2}
{b,r,3}
{c,s,1}
{a,s,2}
{a,s,3}
$\endgroup$
12
  • $\begingroup$ I would love to know if there is a handy way of checking is this a number or vector of numbers to check the second argument of my solution? $\endgroup$
    – tkott
    Mar 15, 2012 at 17:44
  • $\begingroup$ I don't know how efficient it is, but I don't see any problem. What I like is that it would be very easy to add a new parameter to change not only the direction of each sorting but the priority of columns (in your function it would mean building the w considering it). $\endgroup$
    – FJRA
    Mar 16, 2012 at 14:08
  • $\begingroup$ Not sure about efficiency either, but I'm a bit confused as to what you mean about the priority of the columns. Oh, do you mean that given an ordering {1,-1,1} you could still specify a column designation {1,0,2} so that it sorts by the third column first, then the first, and then the second? $\endgroup$
    – tkott
    Mar 16, 2012 at 15:28
  • $\begingroup$ @FJRA is this version what you had in mind? $\endgroup$
    – tkott
    Mar 16, 2012 at 16:38
  • 2
    $\begingroup$ A warning to readers - this appears to get incorrect results under various conditions, e.g. sortByColumn[{{2, 2, 1, 1}, {-2, -1, 2, -2}, {2, 2, 1, 1}, {-2, -1, 2, -2}, {2, 2, 1, 1}, {-2, -1, 2, -2}, {2, 2, 1, 1}, {-2, -1, 2, -2}, {2, 2, 1, 1}, {-2, -1, 2, -2}}, {-1, 1, -1}] gets it wrong (matches neither an explicit SortBy or pulling via Ordering on the same data set). $\endgroup$
    – ciao
    Sep 8, 2015 at 21:58
31
$\begingroup$

If you want to keep the rows and your preferences of ordering is first ascending, second descending and third ascending, you can use SortBy:

SortBy[data, {#[[1]],-#[[2]],#[[3]]}&]
$\endgroup$
6
  • $\begingroup$ :) now why didn't I think of that.. Easy points for you! Thx. $\endgroup$
    – Lou
    Mar 14, 2012 at 9:09
  • 4
    $\begingroup$ This only works on numeric data. $\endgroup$
    – Mr.Wizard
    Mar 15, 2012 at 8:23
  • $\begingroup$ @Mr.Wizard yes, it works only on numeric data, but I think it is the easiest way to be remember. Your generalization is very nice, but needs to be added to your personal package of utilities (something worth having). $\endgroup$
    – FJRA
    Mar 15, 2012 at 12:59
  • $\begingroup$ Would love your opinion on the solution I posted below. Might see some error that I haven't. $\endgroup$
    – tkott
    Mar 15, 2012 at 18:11
  • $\begingroup$ @Mr.Wizard Why does that work only on numerical data? I tried that on strings and it works well. Am i missing something? $\endgroup$
    – atapaka
    Feb 25, 2020 at 19:11
11
$\begingroup$

FJRA's method is clever, but it will fail if the reverse-order column is non-numeric.

For example:

data={{"a", 1, 1}, {"a", 1, 5}, {"a", 1, 3}, 
      {"c", 2, 1}, {"b", 2, 2}, {"b", 2, 3}, 
      {"c", 3, 1}, {"a", 3, 2}, {"a", 3, 3}};
data[[All, 2]] = data[[All, 2]] /. {1 -> "q", 2 -> "r", 3 -> "s"};

New method

Here is a new method that I believe is a bit cleaner.

ClearAll[f1, f2]

f1[idx_, {d_, ir___}] :=
  Join @@ f1[idx + 1, {ir}] /@ 
    If[d == -1, Reverse, # &] @
      SplitBy[SortBy[#, #[[idx]] &], #[[idx]] &] &

f1[__] := Identity

f2[dat_?MatrixQ, dir_?VectorQ] := dat // f1[1, dir]

The second argument is a list of sort directions by column, -1 representing reverse.

f2[data, {1, -1, 1}] // Column
{a,s,2}
{a,s,3}
{a,q,1}
{a,q,3}
{a,q,5}
{b,r,2}
{b,r,3}
{c,s,1}
{c,r,1}

Old method

Preserved for reference, along with a bug fix.

I cannot think of a way to do this sort with arbitrary data outside of implementing my own sort function. Something like this:

f1[idx_, {d_, ir___}, max_] /; idx <= max :=
  Reap[
    Sow[#, #[[idx]]] & ~Scan~ #,
    If[d == -1, Reverse, # &] @ Union @ #[[All, idx]],
    f1[idx + 1, {ir}, max][#2] &
  ][[2]] ~Flatten~ 2 &

f1[__] = Identity;

f2[dat_?MatrixQ, dir_?VectorQ] := dat // f1[1, dir, Dimensions[dat][[2]]]

That's none too pretty and I hope there is a cleaner way, but it eludes me.

$\endgroup$
3
$\begingroup$

Inspired by @FJRA 's post and @Mr.Wizard 's comment, I come up with a direct way to solve it.

The key is to define a priority function which returns MMA comparible values for individual terms. These values are not limited to be numbers. As I quote from the documentation:

SortBy[{$e_1,e_2,e_3$},...,f] sorts the $e_i$ so that the f[$e_i$] lie in canonical order.
If some of the f[$e_i$] are the same, then the canonical order of the corresponding $e_i$ is used.

So in order to sort string in a decending order, we have to rewrite it in a "reverse order" for individual characters.

Lets define a function as the following (only for unicode characters, one can easily generalize the code to deal with another characters)

reverseString[a_String] := FromCharacterCode[59391- ToCharacterCode@a]

we can sort the data by (decending, decending, ascending) order:

SortBy[data, {reverseString[#[[1]]], -#[[2]], #[[3]]} &]
(*==>
{{"c", 3, 1}, {"c", 2, 1}, {"b", 2, 2}, {"b", 2, 3}, {"a", 3,  2}, {"a", 3, 3}, {"a", 1, 1}, {"a", 1, 2}, {"a", 1, 3}}*)

or even for the case where strings are composed of many characters:

data2 = {{"aa", 1, 1}, {"ab", 1, 2}, {"ac", 1, 3}, {"c", 2, 1}, {"b", 2, 2}, {"b", 2, 3}, {"c", 3, 1}, {"a", 3, 2}, {"a", 3, 3}};
SortBy[data, {reverseString[#[[1]]], -#[[2]], #[[3]]} &]
(*==>
{{"c", 3, 1}, {"c", 2, 1}, {"b", 2, 2}, {"b", 2, 3}, {"a", 3, 2}, {"a", 3, 3}, {"ac", 1, 3}, {"ab", 1, 2}, {"aa", 1, 1}}*)
$\endgroup$
2
$\begingroup$

Note to readers - this is not an answer, per se, but answer-ish responding to a query by Mr. Wizard, so caveat lector....

This is what I use for such things. Note there are chunks of code devoted to specific cases and optimizations that are not needed for purely numeric sort columns.

colsorter[data_, cols_, highdupes_: False] := Module[{td, td1, td2},
   If[MatrixQ[data[[All, Abs@Pick[cols, Sign@cols, -1]]], NumericQ],
    data[[
     Ordering[
      Transpose[Transpose@data[[All, Abs[cols]]]*Sign[cols]]]]],
    td = Transpose@data;
    data[[Ordering@Transpose[With[{td1 = td[[Abs@#]]},
           Sign[#] If[VectorQ[td1, NumericQ], td1, Replace[td1,

              AssociationThread[(td2 = 
                  If[highdupes, Sort@DeleteDuplicates@td1, 
                   Union@td1]) -> Range@Length@td2], {1}]]] & /@ 
         cols]]]]];

Arguments are the data to be sorted, the list of columns to sort by, and optional duplicate density flag.

Columns are listed as e.g. (3,-2,4}, meaning sort by columns 3, 2, and 4 in that order of priority, and with column 2 in reverse sort order.

Sorry for the brevity of exposition, a bit busy and as noted in the preamble, not a full answer.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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