Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
    
I have to go now but after first look it seems that you are reimplementing ArrayComponents. – Kuba Feb 25 at 11:59
2  
@Kuba, what OP wants is almost the same as ArrayComponents, but subtly different – JasonB Feb 25 at 12:31
2  
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! – Dr. belisarius Feb 25 at 13:12
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}

share|improve this answer
1  
This is nice and simple, you should get the cake – JasonB Feb 25 at 13:53
1  
Great use of ReplaceAll's first rule behaviour. Was totally out of the box for me. – Edmund Feb 25 at 19:36

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} *)
share|improve this answer
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}*)
share|improve this answer
    
Apparently OP wants ties to have the same rank. – J. M. Feb 25 at 12:10
    
@J.M. Thanks. I've misread. Posted a different approach now. – Dr. belisarius Feb 25 at 13:02
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}
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.