# 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

## 5 Answers

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}

• 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
• 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). – ciao Sep 8 '15 at 21:58

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, {#[],-#[],#[]}&]

• :) 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
• @Mr.Wizard Why does that work only on numerical data? I tried that on strings and it works well. Am i missing something? – leosenko Feb 25 '20 at 19: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] &
][] ~Flatten~ 2 &

f1[__] = Identity;

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


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

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[#[]], -#[], #[]} &]
(*==>
{{"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[#[]], -#[], #[]} &]
(*==>
{{"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}}*)


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.