Adding top n column based on another column in pandas

user_13201474 0
Viewed 215

Example dataframe:

   Item_Name   Price 
   Basket        2       
   Carpet        5       
   Basket        3      
   Carpet        10       
   Rug           2      
   Rug           3      
   foo           1

I want to add a column called top 3 which indicates whether or not the item is top 3 based on total price summed for each item. The total price or basket is 5, carpet is 15, rug is 5 and foo is 1. So, everything except foo falls in the top 3. How do I do this? I need a new column named "top" 3 that indicates yes or no.

pandas numpy python
2 Answers

You can use groupby and sum to find the sum of each Item_Name, then create a list of the 3 largest index values using the nlargest function as suggested by @Henry Yik.

top3 = df.groupby("Item_Name")["Price"].sum().nlargest(3).index

Then use .isin to check if each Item_name is in the top3 list we created

df["top3"] = df["Item_Name"].isin(top3)

Result:

>>> df
  Item_Name  Price   top3
0    Basket      2   True
1    Carpet      5   True
2    Basket      3   True
3    Carpet     10   True
4       Rug      2   True
5       Rug      3   True
6       foo      1  False
user_53270680

Given your dataframe:

import pandas as pd

df = pd.DataFrame({'Item_Name': ['Basket', 'Carpet', 'Basket', 'Carpet', 'Rug', 'Rug', 'foo'],
                   'Price': [2, 5, 3, 10, 2, 3, 1]})

First you can aggregate the prices based on the item name to get the sums of the prices.

agg_df = df.groupby('Item_Name').sum()
print(agg_df)

Output:

           Price
Item_Name       
Basket         5
Carpet        15
Rug            5
foo            1

Then you can use nlargest to get the top 3 items by price.

top_3_df = agg_df.nlargest(3, 'Price')
print(top_3_df)

Output:

           Price
Item_Name       
Carpet        15
Basket         5
Rug            5

Then you need to get a list of the top 3 values from the index of that dataframe:

top_3 = list(top_3_df.index.values)
print(top_3)

Output:

['Carpet', 'Basket', 'Rug']

Finally, you can add a new column indicating which item names are in the top 3 list.

df["top3"] = df["Item_Name"].isin(top_3)
print(df)

Output:

  Item_Name  Price   top3
0    Basket      2   True
1    Carpet      5   True
2    Basket      3   True
3    Carpet     10   True
4       Rug      2   True
5       Rug      3   True
6       foo      1  False
Bill_the_Lizard_12880
Related