16
$\begingroup$

I tried to find a function or expression in Mathematica that produces the same output as the RANK function in Excel (see its description here), but unfortunately I could not find an existing one.

For example consider the following list:

{29400., 28200., 22300., 20900., 20300., 19800., 17400., 16600., 16300., 16100., 15500., 15300., 15300., 15200., 15100., 14900.,14700., 14700., 14400., 13900.}

The RANK function in Excel will produce:

{1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 12, 14, 15, 16, 17, 17, 19, 20}
                                     (* *)               (* *)

Notice that ties are given the same rank and an appropriate number of ranks is skipped after that. I would like to reproduce that behavior.

In Mathematica, I used the following expression :

m = q /. Thread[# -> Ordering[#, All, Greater]] & @ Union@q

However, the output is different:

{1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 12, 13, 14, 15, 16, 16, 17, 18, 18, 19, 20}

Any suggestions on how to implement the desired behavior?

$\endgroup$
  • $\begingroup$ I have to go now but after first look it seems that you are reimplementing ArrayComponents. $\endgroup$ – Kuba Feb 25 '16 at 11:59
  • 2
    $\begingroup$ @Kuba, what OP wants is almost the same as ArrayComponents, but subtly different $\endgroup$ – Jason B. Feb 25 '16 at 12:31
  • 2
    $\begingroup$ Welcome to Mathematica.SE! I suggest the following: 0) Browse the common pitfalls question 1) As you receive help, try to give it too, by answering questions in your area of expertise. 2) Read the faq! 3) When you see good questions and answers, vote them up by clicking the gray triangles, because the credibility of the system is based on the reputation gained by users sharing their knowledge. Also, please remember to accept the answer, if any, that solves your problem, by clicking the checkmark sign! $\endgroup$ – Dr. belisarius Feb 25 '16 at 13:12
22
$\begingroup$
arr = {29400., 28200., 22300., 20900., 20300., 19800., 17400., 16600.,
 16300., 16100., 15500., 15300., 15300., 15200., 15100., 14900., 
 14700., 14700., 14400., 13900.}

From here

RANK gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers. For example, in a list of integers sorted in ascending order, if the number 10 appears twice and has a rank of 5, then 11 would have a rank of 7 (no number would have a rank of 6).

# /. Thread[Reverse@Sort@# -> Range[Length@#] ] &@arr

{1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 12, 14, 15, 16, 17, 17, 19, 20}

$\endgroup$
  • 1
    $\begingroup$ This is nice and simple, you should get the cake $\endgroup$ – Jason B. Feb 25 '16 at 13:53
  • 2
    $\begingroup$ Great use of ReplaceAll's first rule behaviour. Was totally out of the box for me. $\endgroup$ – Edmund Feb 25 '16 at 19:36
6
$\begingroup$

It feels clunky, but this gets you there,

q /. (Thread[# -> 
      First@First@Position[Reverse@Sort@q, #]] & /@ q)
(* {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 12, 14, 15, 16, 17, 17, 19, 20} *)
$\endgroup$
5
$\begingroup$
f[1, _] = 1;
f[n_, l1_] := If[l1[[n]] == l1[[n - 1]], f[n - 1, l1], n]
f[#, Sort[-l]] & /@ Range@Length@l

(* {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 12, 14, 15, 16, 17, 17, 19, 20}*)
$\endgroup$
  • $\begingroup$ Apparently OP wants ties to have the same rank. $\endgroup$ – J. M. will be back soon Feb 25 '16 at 12:10
  • $\begingroup$ @J.M. Thanks. I've misread. Posted a different approach now. $\endgroup$ – Dr. belisarius Feb 25 '16 at 13:02
3
$\begingroup$
dat = {29400., 28200., 22300., 20900., 20300., 19800., 17400., 16600., 16300., 
   16100., 15500., 15300., 15300., 15200., 15100., 14900., 14700., 14700., 14400., 
   13900.};

Similar to other answers already posted:

dat /. First /@ PositionIndex @ Reverse @ Sort @ dat
{1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 12, 14, 15, 16, 17, 17, 19, 20}

However for best performance consider using cleanPosIdx from Why is the new PositionIndex horribly slow?

Also this question is closely related to Ordering function with recognition of duplicates. Using myOrdering as a foundation:

Min[#] 1^# & /@ myOrdering[dat] // Reverse // Catenate
{1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 12, 14, 15, 16, 17, 17, 19, 20}
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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