Sort data after specific ordering (ascending/descending) in multiple columns

Question

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

Answer 1

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}

Answer 2

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

Answer 3

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.

Answer 4

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}}*)

Answer 5

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.