1

Assuming that we have a Pandas Data Frame as below

data = {'date':['2022-10-01', '2022-10-01', '2022-10-02', '2022-10-02', '2022-10-02'],
'price': [10, 20, 30, 40, 50],
'store': ['A', 'B', 'A', 'C', 'B']
}
df = pd.DataFrame(data)

I want to group by date and get max price value and for the max price I want the corresponding store value i.e. I do not want to apply max aggregation on store column.

How can I achieve that?

Expected Output

+------------+-------+-------+
|    date    | price | store |
+------------+-------+-------+
| 2022-10-01 |    20 | B     |
| 2022-10-02 |    50 | B     |
+------------+-------+-------+
I'mahdi
  • 23,382
  • 5
  • 22
  • 30
Lopez
  • 461
  • 5
  • 19

1 Answers1

1

Another funny way is to sort values than drop duplicated dates:

>>> (df.sort_values(['date', 'price'], ascending=[True, False])
       .drop_duplicates('date'))

         date  price store
1  2022-10-01     20     B
4  2022-10-02     50     B
Corralien
  • 109,409
  • 8
  • 28
  • 52