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

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}

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

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.

Tags:

Sorting

Recent Posts