Pandas resample from daily to monthly using original datetime index

Question

I have daily OHLC stock data.

             open   high    low  close
date                                  
1980-01-04  491.8  491.8  491.8  491.8
1980-01-07  492.5  492.5  492.5  492.5
1980-01-08  494.1  494.1  494.1  494.1
1980-01-09  498.0  498.0  498.0  498.0
1980-01-10  497.2  497.2  497.2  497.2
1980-01-11  495.9  495.9  495.9  495.9
1980-01-14  493.0  493.0  493.0  493.0
1980-01-15  492.6  492.6  492.6  492.6
1980-01-16  491.4  491.4  491.4  491.4
1980-01-17  489.6  489.6  489.6  489.6
1980-01-18  484.2  484.2  484.2  484.2
1980-01-21  483.1  483.1  483.1  483.1
1980-01-22  488.7  488.7  488.7  488.7
1980-01-23  490.8  490.8  490.8  490.8
1980-01-24  496.5  496.5  496.5  496.5
1980-01-25  494.3  494.3  494.3  494.3
1980-02-01  509.1  509.1  509.1  509.1
1980-02-04  513.5  513.5  513.5  513.5
1980-02-05  513.4  513.4  513.4  513.4
1980-02-06  509.6  509.6  509.6  509.6
1980-02-07  515.3  515.3  515.3  515.3
1980-02-08  517.8  517.8  517.8  517.8
1980-02-18  520.1  520.1  520.1  520.1
1980-02-19  525.3  525.3  525.3  525.3
1980-02-20  523.8  523.8  523.8  523.8
1980-02-21  528.9  528.9  528.9  528.9
1980-02-22  531.9  531.9  531.9  531.9
1980-02-25  535.2  535.2  535.2  535.2
1980-02-26  528.8  528.8  528.8  528.8
1980-02-29  521.5  521.5  521.5  521.5
1980-03-03  518.7  518.7  518.7  518.7
1980-03-04  513.3  513.3  513.3  513.3
1980-03-05  516.3  516.3  516.3  516.3

I need to resample to a monthly time frame. Usually, I would use the following code:

output = df_daily.resample('BM').agg({'open': "first", 'high': 'max', 'low': 'min', 'close': "last"})

This will result in the following dataframe.

             open   high    low  close
date                                  
1980-01-31  491.8  498.0  483.1  494.3
1980-02-29  509.1  535.2  509.1  521.5
1980-03-31  518.7  518.7  513.3  516.3

However, I can't use BM Instead, I need to use the actual datetimes, which are provided in the daily dataframe. That is, I'm looking for the following result:

             open   high    low  close
date                                  
1980-01-25  491.8  498.0  483.1  494.3
1980-02-29  509.1  535.2  509.1  521.5
1980-03-05  518.7  518.7  513.3  516.3

score 1 · Accepted Answer · answered Aug 23 '21 at 09:30

Copy your index as column, aggregate date to get the max (or last if sorted) and set back as index:

df['date'] = df.index
(df.resample('BM')
   .agg({'open': 'first',
         'high': 'max',
         'low': 'min',
         'close': 'last',
         'date': 'max',
         })
   .set_index('date')
)

output:

             open   high    low  close
date                                  
1980-01-25  491.8  498.0  483.1  494.3
1980-02-29  509.1  535.2  509.1  521.5
1980-03-05  518.7  518.7  513.3  516.3

Pandas resample from daily to monthly using original datetime index

1 Answers1