cancel
Showing results for
Did you mean:

How can I get rankings like pandas?

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

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

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
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
New Contributor III
`update rnk:avg rank a by a from t`

or

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