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:

frompandas.io.dataimportDataReaderaapl=DataReader('AAPL',data_source='yahoo',start='2015-6-1')>>>aapl.groupby(pd.TimeGrouper('M')).Close.max()Date2015-06-30    130.5399932015-07-31    132.0700072015-08-31    119.7200012015-09-30    116.4100042015-10-31    120.5299992015-11-30    122.5700002015-12-31    119.0299992016-01-31    105.3499982016-02-29     98.1200032016-03-31    100.529999Freq:M,Name:Close,dtype:float64

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

>>>aapl.groupby(pd.TimeGrouper('M')).Close.idxmax()Date2015-06-30   2015-06-012015-07-31   2015-07-202015-08-31   2015-08-102015-09-30   2015-09-162015-10-31   2015-10-292015-11-30   2015-11-032015-12-31   2015-12-042016-01-31   2016-01-042016-02-29   2016-02-172016-03-31   2016-03-01Name:Close,dtype:datetime64[ns]

To get the results side-by-side:

>>>aapl.groupby(pd.TimeGrouper('M')).Close.agg({'maxdate':'idxmax','max price':np.max})maxpricemaxdateDate2015-06-30  130.5399932015-06-012015-07-31  132.0700072015-07-202015-08-31  119.7200012015-08-102015-09-30  116.4100042015-09-162015-10-31  120.5299992015-10-292015-11-30  122.5700002015-11-032015-12-31  119.0299992015-12-042016-01-31  105.3499982016-01-042016-02-29   98.1200032016-02-172016-03-31  100.5299992016-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'])