I have a data frame with 3 columns, 'Supplier', 'Category', and 'Amount'. This df is the result of a grouping operation where I grouped by Supplier, Category, and summed the Amounts. There are still many instance where there are multiple rows per supplier where they used different categories and had different amounts per category. What I'm hoping to do is filter the df so that only the category with the maximum amount per supplier is remaining. I've tried implementing this as part of the groupby, but hasn't worked, I've also followed tutorials where it shows using the .max() operation, but that results in a single line that shows the highest amount in the entire df. Any assistance would be appreciated - I can't share the actual data, but here is an example of what the df looks like now:
And what I would like the resulting df to look like:
UPDATE: this solution is not correct please check user19077881 answer.
You can slice the dataframe by using the informatioa from GroupBy:
g = df.groupby("SN")["Amount"].max()
df = df.loc[df["SN"].isin(g.index) & df["Amount"].isin(g.values)].reset_index(drop=True)
display(df)
SN Category Amount
0 1 Cat2 3000
1 2 Cat22 5000