Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

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 that doesn't work.

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

It's probably a simple question but apparently not obvious to me.

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

3 Answers 3

up vote 21 down vote accepted

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}
share|improve this answer
    
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]]}&]
share|improve this answer
    
:) now why didn't I think of that.. Easy points for you! Thx. –  Lou Mar 14 '12 at 9:09
3  
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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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