cancel
Showing results for 
Search instead for 
Did you mean: 

How can I get rankings like pandas?

kc3031
New Contributor
Hi,

I want to rank a list of numbers: 1 2 3 4 5 3 3 3

In kdb+/q, by using rank function, the result is 0 1 2 6 7 3 4 5. The duplicates do not get the same ranking results.

Ideally, I want to have: 0 1 3.5 6 7 3.5 3.5 3.5

Any help would be greatly appreciated!

Thanks,
Kelly
5 REPLIES 5

jim1
New Contributor
You can use the fby function, e.g.

q)l: 1 2 3 4 5 3 3 3

q)(avg;rank l) fby l
0 1 3.5 6 7 3.5 3.5 3.5


James

kc3031
New Contributor
Excellent! It works well. Many thanks James!

kc3031
New Contributor
Just a follow up question...

What if the data is in a table. Say:
t:([]a: 1 2 3 4 5 3 3 3)
How can I update ranks:... from t?

Thanks,
Kelly

jim1
New Contributor

q)update a,ranks:(avg;rank a) fby a from t
a ranks
-------
1 0    
2 1    
3 3.5  
4 6    
5 7    
3 3.5  
3 3.5  
3 3.5 

Or you could create a function, say rankp:

q)rankp:{(avg;rank x) fby x}

q)select a,ranks:rankp[a] from t
a ranks
-------
1 0    
2 1    
3 3.5  
4 6    
5 7    
3 3.5  
3 3.5  
3 3.5

Flying
New Contributor III
update rnk:avg rank a by a from t

or

update rnk😞avg;rank a)fby a from t