Find max by year and return date on which max occurred in Pandas DataFrame

user_5105285 0
Viewed 1235

Suppose I have a count of candies I sold everyday since 2010. For each year (2010, 2011,2012...2019), how can I find the date when I sold the maximum candies using pandas?

date        Count
01/01/2010   525
01/02/2010   136
01/03/2010   125
01/04/2010   84
01/05/2010   446
...         
01/01/2011   301
01/02/2011   700
...
11/16/2019   807

I've tried this and it gives me the max by year, but I want the date and count for each year.

df.groupby(lambda x: df['date'][x].year)["Count"].max()

date  Count
2010  825
2011  973
2012  900
2013  830
2014  879
2015  690
2016  827
2017  954
2018  1032
2019  968

Thanks for the help!

pandas-groupby pandas dataframe python
2 Answers

Fix your code change to idxmax

idx = df.groupby(lambda x: df['date'][x].year)["Count"].idxmax()

out = df.loc[idx]
user_79645270

Assuming your date column consists of pandas timestamps, you can use the dt date accessor method to access the year property on which to group the annual sum:

gb = df.groupby(df['date'].dt.year)['Count'].sum()
max_year = gb.idxmax()
max_annual_sales = gb.loc[max_year]

If not, first convert them via df['date'] = pd.to_datetime(df['date']).

Then used the idxmax method to get the year index containing the max annual count. Finally, use this year to find the max value via gb.loc[max_year] (or just use gb.max()).

user_24118020
Related