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

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

-
An interesting variation of the usual and now boring sort-by-column question. +1 –  Mr.Wizard Mar 15 '12 at 8:24

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

-
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? –  tkott Mar 15 '12 at 17:44
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). –  FJRA Mar 16 '12 at 14:08
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? –  tkott Mar 16 '12 at 15:28
@FJRA is this version what you had in mind? –  tkott Mar 16 '12 at 16:38
Yes, it does! I was thinking about adding the priority as the number of columns instead of using a number to define ordering but that does the same trick. –  FJRA Mar 16 '12 at 19:57

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

-
:) now why didn't I think of that.. Easy points for you! Thx. –  Lou Mar 14 '12 at 9:09
This only works on numeric data. –  Mr.Wizard Mar 15 '12 at 8:23
@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). –  FJRA Mar 15 '12 at 12:59
Would love your opinion on the solution I posted below. Might see some error that I haven't. –  tkott Mar 15 '12 at 18:11

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.

-