Preserving original index when using pandas groupby

Question

I have the following dataframe that I'm wanting to groupby year and return the max value (but keep the index values as they are here):

import pandas as pd

dct = {
"date": ["2019-01-01", "2019-04-1", "2020-01-01"],
"high": [100, 150, 100],
}
df = pd.DataFrame(dct)
df.set_index("date",inplace=True)
df.index = [pd.Timestamp(i) for i in df.index]
df.index.name = "date" 

# date         high
# 2019-01-01   100
# 2019-04-01   150
# 2020-01-01   100

When using pandas groupby, Im able to group them by year, but not get the date that I want:

func = lambda x: x.year
df["high"].groupby(func).max()

# date    high
# 2019    150
# 2020    100

My desired output is to use pandas groupby and get:

 # NOTE : the date index is like the original

 # date         high
 # 2019-04-01   150
 # 2020-01-01   100

BENY · Accepted Answer · 2020-06-18 00:37:47Z

2

sort_values then do groupby with tail

df.sort_values('high').groupby(df.index.year).tail(1)
            high
date            
2020-01-01   100
2019-04-01   150

When you doing the df["high"].groupby(func).max(), it is series groupby not dataframe groupby , so the output will not carry over the dataframe index

edited Jun 18, 2020 at 0:37

answered Jun 18, 2020 at 0:31

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

thomas.mac Over a year ago

why does this work? what does the groupby object represent?

BENY Over a year ago

@thomas.mac when you func = lambda x: x.year df["high"].groupby(func).max(), you pass serise groupby not dataframe groupby , so it will not carry over the dataframe index

BENY Over a year ago

@thomas.mac pandas.pydata.org/pandas-docs/stable/reference/api/…

Quang Hoang · Accepted Answer · 2020-06-18 00:35:08Z

1

Another way is use idxmax and loc access:

df.loc[df.groupby(df.index.year).high.idxmax()]

Output:

            high
date            
2019-04-01   150
2020-01-01   100

answered Jun 18, 2020 at 0:35

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Comments

Andy L. · Accepted Answer · 2020-06-18 00:52:43Z

0

You also could use nlargest and droplevel

func = lambda x: x.year

df["high"].groupby(func).nlargest(1).droplevel(0)

Out[7]:
date
2019-04-01    150
2020-01-01    100
Name: high, dtype: int64

answered Jun 18, 2020 at 0:52

Andy L.

25.3k4 gold badges20 silver badges30 bronze badges

Collectives™ on Stack Overflow

Preserving original index when using pandas groupby

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related