Pandas Dataframe: Can I fetch other column values along with the column on which group by clause has been applied?

user_11754825 0
Viewed 36

I have this data set which contains price for items sold across various stores on a week wise basis.

The dataframe: price_df looks like this:

price_df.head()
   store_id      item_id week    sell_price
0      S1         item1    w1          9.58
1      S1         item1    w2          9.00
2      S2         item1    w1          8.30
3      S2         item1    w2          8.50
4      S2         item2    w1          8.26

I want to find out : for each 'item and store' combination the highest price

My code:

item_store_max_prices = price_df.groupby(["store_id","item_id"]).agg({"sell_price":["max"]})

But this would only show the store_id, item_id and for that combination the max price ever listed.

Problem statement:

However, I want to show the week as well on which that max price has been observed for that 'store - item' combination in my resultset.

For example:

    store_id     item_id  week    sell_price
0      S1         item1    w1          9.58
1      S2         item1    w2          8.50

Could you please help me with how to obtain that result?

Thanks in advance.

pandas-groupby pandas dataframe python
1 Answers

You can find indexes of the rows having the max values using df.idxmax().

Then subset the dataframe using the above indices using df.loc, like this:

idx = price_df.groupby(["store_id","item_id"])['sell_price'].idxmax().tolist()
price_df = price_df.loc[idx]

Output:

  store_id item_id week  sell_price
0       S1   item1   w1        9.58
3       S2   item1   w2        8.50
4       S2   item2   w1        8.26
user_58208140
Related