Pandas Function similar to SQL rank and partition

Question

I have the below data

ID        DD         DAYS   VALUE
 1     08-MAR-19      4      500
 1     09-MAR-19      1      1500
 2     13-MAR-19      0       0

I want to select the maximum number of days like for ID 1 it will only return the row with 4. In SQL I use the below query

select aa.*, rank() over (partition by ID order by DAYS desc) rank_n;

Which returns only

 ID        DD         DAYS   VALUE 
    1     08-MAR-19      4       500
    2     13-MAR-19      0       0

how can I do the same in pandas

score 0 · Answer 1 · answered Jan 21 '20 at 13:26

0

Here is another option using loc which is used for the old select function in pandas.

import pandas as pd
data = {'id':[1,1,4],'DD':['08-MAR-19','09-MAR-19','13-MAR-19'],'DAYS':[4,1,0],'VALUE':[500,1500,0]}
df = pd.DataFrame(data)

df = df.loc[(df['id'] == 1) & (df['DAYS'] == max(df['DAYS']))]
print(df)

Output:

   id         DD  DAYS  VALUE
0   1  08-MAR-19     4    500

answered Jan 21 '20 at 13:26

Celius Stingher

17,835
6
23
53

the data has many IDs that was just a sample, I therefore can't filter using df['id'] == 1 – FatmaJaffer Jan 21 '20 at 13:29
Then you should use a groupby and keep the index values.However if you wish to partition by `id` why is the `id` 2 not showing up? – Celius Stingher Jan 21 '20 at 13:31
sorry it should show, I have amended the question – FatmaJaffer Jan 21 '20 at 13:37

Pandas Function similar to SQL rank and partition

1 Answers1