22

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?

J. M.'s missing motivation
  • 124,525
  • 11
  • 401
  • 574
Mehmet
  • 587
  • 2
  • 10

7 Answers7

27
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}

user1066
  • 17,923
  • 3
  • 31
  • 49
7

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} *)
Jason B.
  • 68,381
  • 3
  • 139
  • 286
6
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}*)
Dr. belisarius
  • 115,881
  • 13
  • 203
  • 453
5
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}
Community
  • 1
Mr.Wizard
  • 271,378
  • 34
  • 587
  • 1,371
5
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}
kglr
  • 394,356
  • 18
  • 477
  • 896
4
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}

eldo
  • 67,911
  • 5
  • 60
  • 168
  • 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]]] – Daniel Lichtblau Nov 11 '23 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 '23 at 00:04
  • You need something like ReplaceAll[p : {a_Integer, __} :> Table[a, Length@p]] for inputs with elements repeated more than twice. – kglr Nov 12 '23 at 01:45
  • Thank you very much, I updated the answer correspondingly – eldo Nov 12 '23 at 08: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. – Daniel Lichtblau Nov 12 '23 at 18:38
4
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}

Syed
  • 52,495
  • 4
  • 30
  • 85