3

I was learning rank function in SQL and found that it uses different kind of ranking than pandas methods. How to get the same answer?

Question link: https://www.windowfunctions.com/questions/ranking/1

SQL code

select rank() over (order by weight desc) as ranking, weight, name from cats order by ranking, name

My Pandas code

df[['weight','name']].assign(ranking=df['weight'].rank(method='dense',ascending=False)).sort_values('weight',ascending=False)

# I am getting a different answer than SQL
# How to get the same answer?

Data

df = pd.DataFrame({'name': ['Molly', 'Ashes', 'Felix', 'Smudge', 'Tigger', 'Alfie', 'Oscar', 'Millie', 'Misty', 'Puss', 'Smokey', 'Charlie'],
          'breed': ['Persian', 'Persian', 'Persian', 'British Shorthair', 'British Shorthair', 'Siamese', 'Siamese', 'Maine Coon', 'Maine Coon', 'Maine Coon', 'Maine Coon', 'British Shorthair'],
          'weight': [4.2, 4.5, 5.0, 4.9, 3.8, 5.5, 6.1, 5.4, 5.7, 5.1, 6.1, 4.8],
          'color': ['Black', 'Black', 'Tortoiseshell', 'Black', 'Tortoiseshell', 'Brown', 'Black', 'Tortoiseshell', 'Brown', 'Tortoiseshell', 'Brown', 'Black'],
          'age': [1, 5, 2, 4, 2, 5, 1, 5, 2, 2, 4, 4]})

My output

    weight  name    ranking
6   6.1 Oscar   1.0
10  6.1 Smokey  1.0
8   5.7 Misty   2.0 # this should be 3 and so on
5   5.5 Alfie   3.0
7   5.4 Millie  4.0
9   5.1 Puss    5.0
2   5.0 Felix   6.0
3   4.9 Smudge  7.0
11  4.8 Charlie 8.0
1   4.5 Ashes   9.0
0   4.2 Molly   10.0
4   3.8 Tigger  11.0
ALollz
  • 57,915
  • 7
  • 66
  • 89
BhishanPoudel
  • 15,974
  • 21
  • 108
  • 169
  • you're doing a dense rank on the Python side. https://stackoverflow.com/questions/11183572/whats-the-difference-between-rank-and-dense-rank-functions-in-oracle/11183610 – MattR Jul 20 '20 at 19:29

1 Answers1

0

As suggested in comments, We can get the same answer in SQL and pandas using this method:

df[['weight','name']].assign(ranking=df['weight'].rank(method='min',ascending=False)).sort_values('weight',ascending=False)
BhishanPoudel
  • 15,974
  • 21
  • 108
  • 169