getting corresponding column information with groupby based on condition-CodePudding

Current dataframe looks like :

Group      amount      uniqueID        status
  A          100          A32          Success
  A          120          B33           Failed
  A          80           C44           Failed

Expected Dataframe :

Group      amount      uniqueID        status
  A          120          B33           Failed

From the group it should pick the highest amount value and its respective uniqueID & status.

I have tried below code for getting the maximum amount value , how can we fetch its corresponding rows i.e. UniqueID and status

df_sorted = df.groupby(['Group']).agg({'amount': [np.max]})

CodePudding user response：

>> pdf1

Group   amount  uniqueID    status
0   A   100     A32       Success
1   A   120     B33       Failed
2   A   80      C44       Failed

pdf2 = pdf1.groupby("Group").max("amount").reset_index()

>> pdf2
    Group   amount
0    A       120

>> pd.merge(pdf1, pdf2, on=["Group", "amount"])

    Group   amount  uniqueID    status
0   A         120   B33         Failed

CodePudding user response：

You can do sort_values with groupby.tail.

df.sort_values('amount').groupby('Group').tail(1)

  Group  amount uniqueID  status
1     A     120      B33  Failed