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

2 answers

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 < & ( < 15)
>>> d[mask]

<class 'pandas.tseries.index.DatetimeIndex'>
[2014-09-09, 2014-10-14]
Length: 2, Freq: None, Timezone: None




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()
            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




All Articles