I'm trying to subset a DataFrame on the condition that is the last of the month. I used:
df['Month_End'] = df.index.is_month_end
sample = df[df['Month_End'] == 1]
This works, but I'm working with stock market data, so I'm missing all the instances where the actual end of the month is during the weekend, I need a way to select the "last business day of the month".
You can generate a time series with the last business day of each month by passing in freq='BM'
.
For example, to create a series of the last business days of 2014:
>>> pd.date_range('1/1/2014', periods=12, freq='BM')
[2014-01-31 00:00:00, ..., 2014-12-31 00:00:00]
Length: 12, Freq: BM, Timezone: None
You could then use this timeseries to subset/reindex your DataFrame.
pd.Instead of generating the series, you can also parse the business month end from your datetime index as this:
df['BMonthEnd'] = (df.index + pd.offsets.BMonthEnd(1)).day
Though note this currently throws a harmless warning - see http://pandas.pydata.org/pandas-docs/stable/timeseries.html#using-offsets-with-series-datetimeindex