Repeat pandas fetch on "1st Monday of the month" etc.
I have (daily) pandas timeSeries that I need to filter to monthly using the nth occurrence of a specific day of the week as a rule to select dates
My thinking so far is the best way to do this is to first make a list or Series of all the dates that interest me and then ask the timers for those dates?
But that still leaves the question of how should I compose the list, for example. all the "second Tuesday of the month" that occurred between two dates?
source to share
Take September and October 2014 for example:
from datetime import datetime
import pandas as pd
start = datetime(2014, 9, 1)
end = datetime(2014, 10, 30)
d = pd.date_range(start, end) # use bdate_range for business days
Now you can create a mask containing only the dates you are interested in:
>>> mask = (d.weekday == 1) & (7 < d.day) & (d.day < 15)
>>> d[mask]
<class 'pandas.tseries.index.DatetimeIndex'>
[2014-09-09, 2014-10-14]
Length: 2, Freq: None, Timezone: None
source to share
Some bogus data:
In [44]: df = pd.DataFrame(data=range(1000), index=pd.date_range('1999-01-01', periods=1000), columns=['value'])
Let's say you want the second Tuesday of every month. You can reselect on a specific day of the week using pandas inline offset
In [45]: df = df.resample('W-TUE', how='last')
Then you can calculate the week column of the month and use that to filter.
In [50]: df['wom'] = df.groupby(pd.TimeGrouper('M'))['value'].transform(lambda x: range(len(x))) + 1
In [53]: df[df['wom'] == 2].head()
Out[53]:
value wom
1999-01-12 11 2
1999-02-09 39 2
1999-03-09 67 2
1999-04-13 102 2
1999-05-11 130 2
source to share