22
$\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?

Improve this question
$\endgroup$
2
  • $\begingroup$ I have to go now but after first look it seems that you are reimplementing ArrayComponents. $\endgroup$
    – Kuba
    Commented Feb 25, 2016 at 11:59
  • 2
    $\begingroup$ @Kuba, what OP wants is almost the same as ArrayComponents, but subtly different $\endgroup$
    – Jason B.
    Commented Feb 25, 2016 at 12:31

7 Answers 7

Reset to default
27
$\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}

Improve this answer
$\endgroup$
2
  • 1
    $\begingroup$ This is nice and simple, you should get the cake $\endgroup$
    – Jason B.
    Commented Feb 25, 2016 at 13:53
  • 3
    $\begingroup$ Great use of ReplaceAll's first rule behaviour. Was totally out of the box for me. $\endgroup$
    – Edmund
    Commented Feb 25, 2016 at 19:36
7
$\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} *)
Improve this answer
$\endgroup$
6
$\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}*)
Improve this answer
$\endgroup$
2
  • $\begingroup$ Apparently OP wants ties to have the same rank. $\endgroup$
    – J. M.'s missing motivation ♦
    Commented Feb 25, 2016 at 12:10
  • $\begingroup$ @J.M. Thanks. I've misread. Posted a different approach now. $\endgroup$
    – Dr. belisarius
    Commented Feb 25, 2016 at 13:02
5
$\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}
Improve this answer
$\endgroup$
5
$\begingroup$ 
xlRanks = 1 + Length @ # - Ceiling @ Statistics`Library`GetDataRankings @ # &;

Examples:

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

xlRanks @ list1

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

list2 = {3, 100, 3, 100, 6, 5, 3, 5, 4, 3, 3, 3, 2, 1};

xlRanks @ list2

{9, 1, 9, 1, 3, 4, 9, 4, 6, 9, 9, 9, 13, 14}

xlRanks @ ReverseSort @ list2

{1, 1, 3, 4, 4, 6, 9, 9, 9, 9, 9, 9, 13, 14}
Improve this answer
$\endgroup$
4
$\begingroup$ 
list = {29400., 28200., 22300., 20900., 20300., 19800., 17400., 
   16600., 16300., 16100., 15500., 15300., 15300., 15200., 15100., 
   14900., 14700., 14700., 14400., 13900.};

EDIT

Using FirstPosition:

First@FirstPosition[list, #] & /@ list

Using PositionIndex:

v = Values@PositionIndex[list];
Sequence @@ Table[First@#1, Length@#1] & /@ v

Result

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

Improve this answer
$\endgroup$
4
$\begingroup$ 
list =
  {29400., 28200., 22300., 20900., 20300., 19800., 17400., 16600., 
   16300., 16100., 15500., 15300., 15300., 15200., 15100., 14900., 
   14700., 14700., 14400., 13900.};

Flatten @ ReplaceAll[{a_, _} :> {a, a}] @ Values @ PositionIndex[list]

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

Update following kglr's comment

If there are more than 2 duplicates:

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

we can do:

Flatten @ ReplaceAll[p : {a_?NumberQ, __} :> Table[a, Length @ p]] @
  Values @ PositionIndex[list]

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

Improve this answer
$\endgroup$
5
  • $\begingroup$ Is this doing the right thing when the list isn't sorted? I had thought this might be what is wanted: Flatten@Map[ReplaceAll[#, (_Integer :> Min[#])] &, PositionLargest[list, Length[list]]] $\endgroup$
    – Daniel Lichtblau
    Commented Nov 11, 2023 at 23:43
  • $\begingroup$ Good point, but I think that your question concerns all answers given and can easily be solved by sorting the initial list. $\endgroup$
    – eldo
    Commented Nov 12, 2023 at 0:04
  • $\begingroup$ You need something like ReplaceAll[p : {a_Integer, __} :> Table[a, Length@p]] for inputs with elements repeated more than twice. $\endgroup$
    – kglr
    Commented Nov 12, 2023 at 1:45
  • $\begingroup$ Thank you very much, I updated the answer correspondingly $\endgroup$
    – eldo
    Commented Nov 12, 2023 at 8:15
  • $\begingroup$ @eldo (1) Correct-- I was not intending to pick on your method specifically. I commented under it because, in my view, PositionLargest is closer to PositionIndex than to methods that use other functions. (2) Use of Sort repositions elements in a way that, unless (again) I misunderstand the question, will destroy the result. One would need to apply the reverse of the original ordering to the result for the sorted list. $\endgroup$
    – Daniel Lichtblau
    Commented Nov 12, 2023 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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