How do I give same rank for same score?

[LearningGuy]

Hello,
How do I give same rank for same score?
Student d and e has the same score of 73, thus they both Rank 4, but student f has Rank 6. Rank 5 is skipped because Student d and e has the same score. 
Thank you for your help

Expected result:

Student	Score	Rank
a	100	1
b	95	2
c	84	3
d	73	4
e	73	4
f	54	6
g	43	7
h	37	8
i	22	9
j	12	10

This is what I figured out so far, but i won't take into consideration of same Score

| makeresults format=csv data="Student, Score  
a,100 
b,95
c,84
d,73
e,73
f,54
g,43
h,37
i,22
j,12"
| streamstats count

[bowesmana]

@LearningGuy as I said in the other post - you can probably solve that problem 😁

and as usual, @ITWhisperer comes up with the perfect elegant solution!

[ITWhisperer · Accepted solution]

| streamstats count as Rank
| streamstats window=2 range(Score) as range
| eval Rank=if(Rank=1 OR range != 0, Rank, null())
| filldown Rank

[LearningGuy]

Hello
I tried your suggestion and it worked. I accepted this solution and will try on real data.
1)  Can you explain what this eval for?
It looks like if range is 0, you replace the pos with NULL and fill down with previous value, except for position one?

| eval Rank=if(Rank=1 OR range != 0, Rank, null())

  
2) Would it be possible to use only 1 streamstats instead of 2 streamstats?  

Thank you so much for your help

[shalomsuresh]

If you want to avoid using 2 streamstats you shall try this way,

| streamstats count as Rank
| delta Score as Diff
| eval Rank=if(Diff=0,Rank-1,Rank)
| fields - Diff

And with 2 streamstats you shall try this so to avoid 1 extra filldown command,

| streamstats count as Rank
| streamstats window=2 range(Score) as range
| eval Rank=if(Rank=1 OR range != 0, Rank, Rank-1)

[ITWhisperer]

@shalomsuresh These solutions don't work if there are more than 3 with the same score, e.g. if "f" had a score of 73 as well.

[shalomsuresh]

Yep you are right, I wasn't thinking about that. We can still use the following,

| streamstats count as Rank
| delta Score as Diff
| eval Rank=if(Diff=0,null,Rank)
| filldown
| fields - Diff

[ITWhisperer]

1) If the Rank is 1, it needs to remain 1, or if there is a difference in values, the rank needs to remain the same (as it is already correct), otherwise, if there is no difference between the current and previous value, the rank should be the same as the previous rank. By setting it to null(), when the filldown happens, the rank is copied down to all positions with the same rank.

2) It is not possible to do with just one streamstats because the first streamstats has to operate over the whole pipeline, whereas the second has to operate with a (rolling) window of two events.

[PickleRick]

2) Actually you can get away with just one streamstats. Replace the other one with autoregress. (But yes, it will still give you two separate passes across your results)

[ITWhisperer]

Autoregress is the same as

| streamstats window=2 current=f last(Score) as Score_p1

[PickleRick]

Sure, it is. But it's formally a different command 😉

[PickleRick]

From the top of my head. Untested, might need some tweaking.

| stats values(App) as App count by Score
| streamstats sum(count) as rank
| mvexpand App