CODEWITHSUNDEEP
pythonpandas-groupby
Sheriff
Sheriff

Reputation: 155

I need to group by and get the rank in python

I have a dataframe , refer below code to generate it :

     df = pd.DataFrame({'customer': [1,2,1,3,1,2,3], 
               "group_code": ['111', '111', '222', '111', '111', '111', '333'],
              "ind_code": ['A', 'B', 'AA', 'A', 'AAA', 'C', 'BBB'],
              "amount": [100, 200, 140, 400, 225, 125, 600],
              "card": ['XXX', 'YYY', 'YYY', 'XXX', 'XXX', 'YYY', 'XXX']})

Suppose i wanted to group it by card and wanted to know for each card which group code has highest amount ? and create a new dataframe with that card number and group code with highest amount.

Kindly help at the earliest.

Upvotes: 0

Views: 105

Answers (1)

Dani Mesejo
Dani Mesejo

Reputation: 61930

You could do:

import pandas as pd

df = pd.DataFrame({'customer': [1,2,1,3,1,2,3],
               "group_code": ['111', '111', '222', '111', '111', '111', '333'],
              "ind_code": ['A', 'B', 'AA', 'A', 'AAA', 'C', 'BBB'],
              "amount": [100, 200, 140, 400, 225, 125, 600],
              "card": ['XXX', 'YYY', 'YYY', 'XXX', 'XXX', 'YYY', 'XXX']})
mask = df.groupby('card')['amount'].transform(max) == df['amount']

result = df[mask][['card', 'group_code', 'amount']]

print(result)

Output

  card group_code  amount
1  YYY        111     200
6  XXX        333     600

UPDATE

import pandas as pd

df = pd.DataFrame({'customer': [1,2,1,3,1,2,3],
               "group_code": ['111', '111', '222', '111', '111', '111', '333'],
              "ind_code": ['A', 'B', 'AA', 'A', 'AAA', 'C', 'BBB'],
              "amount": [100, 200, 140, 400, 225, 125, 600],
              "card": ['XXX', 'YYY', 'YYY', 'XXX', 'XXX', 'YYY', 'XXX']})
agg = df.groupby(['card', 'group_code']).agg({'amount':'sum'}).reset_index()
mask = agg.groupby('card')['amount'].transform(max) == agg['amount']
result = agg[mask]
print(result)

Output

  card group_code  amount
0  XXX        111     725
2  YYY        111     325

Upvotes: 2

Related Questions