Reproducing a ranking function that behaves like Excel's RANK

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?

• I have to go now but after first look it seems that you are reimplementing ArrayComponents.
– Kuba
Feb 25, 2016 at 11:59
• @Kuba, what OP wants is almost the same as ArrayComponents, but subtly different Feb 25, 2016 at 12:31

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}

• This is nice and simple, you should get the cake Feb 25, 2016 at 13:53
• Great use of ReplaceAll's first rule behaviour. Was totally out of the box for me. Feb 25, 2016 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} *)

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}*)

• Apparently OP wants ties to have the same rank. Feb 25, 2016 at 12:10
• @J.M. Thanks. I've misread. Posted a different approach now. Feb 25, 2016 at 13:02
dat = {29400., 28200., 22300., 20900., 20300., 19800., 17400., 16600., 16300.,
16100., 15500., 15300., 15300., 15200., 15100., 14900., 14700., 14700., 14400.,
13900.};


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}

xlRanks = 1 + Length @ # - Ceiling @ StatisticsLibraryGetDataRankings @ # &;


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}

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}

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}

• 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]]] Nov 11, 2023 at 23:43
• Good point, but I think that your question concerns all answers given and can easily be solved by sorting the initial list.
– eldo
Nov 12, 2023 at 0:04
• You need something like ReplaceAll[p : {a_Integer, __} :> Table[a, Length@p]] for inputs with elements repeated more than twice.
– kglr
Nov 12, 2023 at 1:45
• Thank you very much, I updated the answer correspondingly
– eldo
Nov 12, 2023 at 8:15
• @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. Nov 12, 2023 at 18:38