Search code examples
pythonpandasdataframegroup-by

Pandas Group by and get corresponding Value

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     |
+------------+-------+-------+
Solution

  • 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