Re: Is it possible to mix up a list of numbers

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Dana DeLouis (delouis_at_bellsouth.net)
Date: 08/10/04 

Date: Mon, 9 Aug 2004 22:53:10 -0400

Hi Harlan. A long time ago I experimented in Excel with Rank on a large
array. Of course, I had no luck. The idea came from Mma...

Here's a (500,000 x 5) matrix of random numbers.

v = Table[Random[], {500000}, {5}];

Sort is hard-wired to use the first element of each Row, so it’s fairly
fast.

Sort[v]
 1.493 Second

To sort a 2-dim array on something else, you supply Sort with a custom
function, which slows it down. Here’s a sort on the 3rd column.

Sort[v,#1[[3]]<#2[[3]]&]
 84.221 Second

One technique is to use Ordering, similar in concept to Excel’s Rank.

v[[Ordering[v[[All,3]]]]]
 9.567 Second

Anyway, I was curious to use Excel vba and Rank at the time, and wanted to
experiment. I know there are other issues involved as well. Just doesn't
work the same in Excel thought...obviously. :>)
Dana

"hgrove >" <<hgrove.1aqphl@excelforum-nospam.com> wrote in message
news:hgrove.1aqphl@excelforum-nospam.com...
> Dana DeLouis wrote...
> >A little off topic. I notice that in Excel, the Rank technique is
> >about twice as fast as sorting when the size is relatively small
> >(say <100). However, there appears to be no timing advantage
> >when the size gets larger than this. (from timing point of view).
>
> To the extent that sorting involves juggling cell contents while using
> a multiple cell array formula calling RANK only involves returning
> different values, this isn't surprising.
>
> This leaves the question whether the unavoidable additional manual
> steps needed to freeze the RANK results would offset or swamp its
> recalc-only timing advantage.
>
> >In contrast, the "rank" technique is very effective in math
> >programs like Mathematica. With very large 2 dimension arrays,
> >and one wishing to sort on a specific column, this is a much
> >faster technique. . . .
>
> Do you mean that using an ordering function to return an array of
> indices that could be used to dereference the original array in sorted
> order is faster than sorting the array? If so, shouldn't you include
> the time involved to recreate the entire original array in sorted
> order?

Quantcast