CODEWITHSUNDEEP
pythonpandas
Ashraf Khaled
Ashraf Khaled

Reputation: 91

Get Multiple values over group by

I have a DataFrame

                date     close
        0  1997-10-22    8.4273
        1  1998-02-09    5.4130
        2  1998-04-30    5.4130
        1  1998-02-20    4.4130
        2  1998-04-30    3.4130
        3  1999-07-09    9.8670
        4  2000-02-25   11.4410
        5  2001-01-29    8.7326

i wanna groupby year in the DateTime column but preserve the equal values of close like

            date     close
        0  1997-10-22    8.4273
        1  1998-02-09    5.4130
        2  1998-04-30    5.4130
        3  1999-07-09    9.8670
        4  2000-02-25   11.4410
        5  2001-01-29    8.7326

ive tried this :

x = df.loc[df.groupby(df.date.dt.year)["close"].idxmax()].reset_index(drop = True)

but its output was

            date     close
        0  1997-10-22    8.4273
        1  1998-02-09    5.4130
        2  1999-07-09    9.8670
        3  2000-02-25   11.4410
        4  2001-01-29    8.7326

what can i do?

Upvotes: 0

Views: 127

Answers (1)

Giorgos Myrianthous
Giorgos Myrianthous

Reputation: 39870

So here's your DataFrame:

import pandas as pd 
import datetime as dt 

df = pd.DataFrame(
    [
        (dt.datetime(1997, 10, 22), 8.4273),
        (dt.datetime(1998, 2, 9), 5.4130), 
        (dt.datetime(1998, 4, 30), 5.4130),
        (dt.datetime(1998, 2, 20), 4.4130),
        (dt.datetime(1998, 4, 30), 3.4130), 
        (dt.datetime(1999, 7, 9), 9.8670),
        (dt.datetime(2000, 2, 25), 11.4410), 
        (dt.datetime(2001, 1, 29), 8.7326), 
    ], 
    columns=['date', 'close']
)

All you need to do is:

df = df[df.groupby(df.date.dt.year)['close'].transform('max') == df['close']]

and the result will be

print(df)
        date    close
0 1997-10-22   8.4273
1 1998-02-09   5.4130
2 1998-04-30   5.4130
5 1999-07-09   9.8670
6 2000-02-25  11.4410
7 2001-01-29   8.7326

Upvotes: 1

Related Questions