CODEWITHSUNDEEP
pythonpandas
Alexander
Alexander

Reputation: 4645

Rank values in grouped data

I'm looking for an efficient way to re-number the rank vector of a grouped data frame in Python.

In a simple data first I rank the soldier_type based on their counts. This is simple so far! OTOH, I need to re-rank the soldiers based on the following condition:

if soldier_type == S1, in each group of regiment and trucks, I want it to be always ranked to be 1 and then re-rank other soldier types starting from rank 2 (from highest counts to lowest).

Here is my attempt to solve this problem:

import pandas as pd

from numpy.random import seed
from numpy.random import randint

seed(1234)

raw_data = {'regiment': ['51st', '51st', '51st', '51st', '51st', '51st', '51st', '51st', '51st', '51st', '51st', '51st'], 
            'trucks': ['MAZ-7310', 'MAZ-7310', 'MAZ-7310', 'MAZ-7310', 'Tatra 810', 'Tatra 810', 'Tatra 810', 'Tatra 810', 'ZIS-150', 'ZIS-150', 'ZIS-150', 'ZIS-150'],
            'soldier_type': ['S1', 'S2', 'S3', 'S4', 'S1', 'S3', 'S4', 'S5', 'S1', 'S2', 'S4', 'S5'],            
            'counts': randint(1,100,12)}


df = pd.DataFrame(raw_data, columns = ['regiment', 'trucks','soldier_type', 'counts'])


   regiment     trucks soldier_type  counts
0      51st   MAZ-7310           S1      48
1      51st   MAZ-7310           S2      84
2      51st   MAZ-7310           S3      39
3      51st   MAZ-7310           S4      54
4      51st  Tatra 810           S1      77
5      51st  Tatra 810           S3      25
6      51st  Tatra 810           S4      16
7      51st  Tatra 810           S5      50
8      51st    ZIS-150           S1      24
9      51st    ZIS-150           S2      27
10     51st    ZIS-150           S4      31
11     51st    ZIS-150           S5      44

def rank_soldier_type (df):

    df = df.assign(rank_ = df.groupby(['regiment','trucks'])['counts'].rank(ascending = False,method='dense'))

    return df #1st part
    #%%
    if  df.soldier_type != 'S1' and df.rank_ == 1 :

        df['new_rank_'] = 1
    else:
        df['new_rank_'] = df['rank_'].rank(ascending = False,method='dense')

    return df

df = rank_soldier_type(df)

If I run the 1st part of this function I can create rank_ column:

df = rank_soldier_type(df)  

   regiment     trucks soldier_type  counts  rank_
0      51st   MAZ-7310           S1      48    3.0
1      51st   MAZ-7310           S2      84    1.0
2      51st   MAZ-7310           S3      39    4.0
3      51st   MAZ-7310           S4      54    2.0
4      51st  Tatra 810           S1      77    1.0
5      51st  Tatra 810           S3      25    3.0
6      51st  Tatra 810           S4      16    4.0
7      51st  Tatra 810           S5      50    2.0
8      51st    ZIS-150           S1      24    4.0
9      51st    ZIS-150           S2      27    3.0
10     51st    ZIS-150           S4      31    2.0
11     51st    ZIS-150           S5      44    1.0

The expected output;

        regiment     trucks soldier_type  counts  rank_  new_rank_
    0      51st   MAZ-7310           S1      48    3.0       1.0
    1      51st   MAZ-7310           S2      84    1.0       2.0
    2      51st   MAZ-7310           S3      39    4.0       4.0
    3      51st   MAZ-7310           S4      54    2.0       3.0
    4      51st  Tatra 810           S1      77    1.0       1.0
    5      51st  Tatra 810           S3      25    3.0       3.0
    6      51st  Tatra 810           S4      16    4.0       4.0
    7      51st  Tatra 810           S5      50    2.0       2.0
    8      51st    ZIS-150           S1      24    4.0       1.0
    9      51st    ZIS-150           S2      27    3.0       4.0 
    10     51st    ZIS-150           S4      31    2.0       3.0
    11     51st    ZIS-150           S5      44    1.0       2.0

Upvotes: 2

Views: 75

Answers (1)

BENY
BENY

Reputation: 323376

Fix your code by add duplicated

df['New']=df[df[['regiment', 'trucks']].duplicated()].\
            groupby(['regiment', 'trucks'])['counts'].rank(ascending=False, method='dense')+1
df.New.fillna(1,inplace=True)
df
Out[35]: 
   regiment     trucks soldier_type  counts  New
0      51st   MAZ-7310           S1      48  1.0
1      51st   MAZ-7310           S2      84  2.0
2      51st   MAZ-7310           S3      39  4.0
3      51st   MAZ-7310           S4      54  3.0
4      51st  Tatra 810           S1      77  1.0
5      51st  Tatra 810           S3      25  3.0
6      51st  Tatra 810           S4      16  4.0
7      51st  Tatra 810           S5      50  2.0
8      51st    ZIS-150           S1      24  1.0
9      51st    ZIS-150           S2      27  4.0
10     51st    ZIS-150           S4      31  3.0
11     51st    ZIS-150           S5      44  2.0

Upvotes: 1

Related Questions