Getting The Monthly Maximum Of A Daily Dataframe With The Corresponding Index Value

I have dowloaded daily data from yahoo finance Open High Low Close Volume \ Date

Solution 1:

You can get the max value per month using TimeGrouper together with groupby:

from pandas.io.data import DataReader

aapl = DataReader('AAPL', data_source='yahoo', start='2015-6-1')
>>> aapl.groupby(pd.TimeGrouper('M')).Close.max()
Date
2015-06-30    130.539993
2015-07-31    132.070007
2015-08-31    119.720001
2015-09-30    116.410004
2015-10-31    120.529999
2015-11-30    122.570000
2015-12-31    119.029999
2016-01-31    105.349998
2016-02-29     98.120003
2016-03-31    100.529999
Freq: M, Name: Close, dtype: float64

Using idxmax will get the corresponding dates of the max price.

>>> aapl.groupby(pd.TimeGrouper('M')).Close.idxmax()
Date
2015-06-30   2015-06-01
2015-07-31   2015-07-20
2015-08-31   2015-08-10
2015-09-30   2015-09-16
2015-10-31   2015-10-29
2015-11-30   2015-11-03
2015-12-31   2015-12-04
2016-01-31   2016-01-04
2016-02-29   2016-02-17
2016-03-31   2016-03-01
Name: Close, dtype: datetime64[ns]

To get the results side-by-side:

>>> aapl.groupby(pd.TimeGrouper('M')).Close.agg({'max date': 'idxmax', 'max price': np.max})
             max price   max date
Date                             
2015-06-30  130.539993 2015-06-01
2015-07-31  132.070007 2015-07-20
2015-08-31  119.720001 2015-08-10
2015-09-30  116.410004 2015-09-16
2015-10-31  120.529999 2015-10-29
2015-11-30  122.570000 2015-11-03
2015-12-31  119.029999 2015-12-04
2016-01-31  105.349998 2016-01-04
2016-02-29   98.120003 2016-02-17
2016-03-31  100.529999 2016-03-01

Solution 2:

My dataset is an electricity dataset where I am only interested in kW which a column in my df.

This works for me to find max values of the kW for each month in my dataset that is on 15 minute intervals.

max_kW_per_month = df.groupby(df.index.month)['kW'].agg(['idxmax', 'max'])