Pandas Week of the Month Business Day Stock Price Data

Question

My question is different than the following:

Question1: Week of a month pandas Quesiton2: Week number of the month

The above question deals with assuming 7 days in a week. It attempts to count the number of 7 days week there are. My data is composed of (business days) daily prices and there can be at times missing days of the week because the market was closed for holiday.

My question is how does one find the week of the month given a date. Note I highlighted "given a date" because this process is processed daily so any answers that looks ahead till the end of the month will likely not work.

My attempts has been the look ahead which isn't optimal:

def is_third_friday(s):
    d = datetime.datetime.strptime(s, '%Y-%m-%d')
    return d.weekday() == 5 and 15 <= d.day <= 21

dow = deepcopy(data['Close'] * np.nan).to_frame()
dow.columns = ['OpexFriday']
dow['next_date'] = pd.Series([str(i.date() + datetime.timedelta(days=1)) for i in dow.index]).values
dow['OpexFriday'] = pd.Series([is_third_friday(str(i)) for i in dow['next_date']]).values
dow['OpexWeek'] = (dow['OpexFriday'] * 1).replace(0, np.nan).fillna(method='bfill', limit=4).replace(np.nan, 0) == True

I don't know how to provide some sample data but if you go to "https://aroussi.com/post/python-yahoo-finance" page and use the authors yfinance package, you will be able to get some price data to work with.

The functions above will find the 3rd week of the month (all True). In addition, it will set the Friday of that week too.

Let me know if you see any problem with the question or if its a duplicate. I have searched a while for a solution.

Answer 1

One way could be to use timedelta to change any date to the next Friday, then check if this following Friday is between 15 and 21.

from datetime import datetime, timedelta
def OpexWeek (s):
    d = datetime.strptime(s, '%Y-%m-%d')
    day = (d+timedelta(days=(4-d.weekday())%7)).day
    return (day>=15) & (day<=21)

then you get

#for the example the second Friday of June 2020:
OpexWeek('2020-06-12')
False

# the Monday after is True because part of the OpexWeek
OpexWeek('2020-06-15')
True

Note: one thing to know is that the Saturday and the Sunday before the OpexWeek are True, but because you said your data is business days, then it should not matter.

The pandas version to use on Series of datetime could be:

def OpexWeekPd (ser):
    return (ser+pd.to_timedelta((4-ser.dt.weekday)%7, unit='d')).dt.day.between(15,21)

With a small example:

print (
    pd.DataFrame({'date':pd.bdate_range('2020-06-01', '2020-06-30').astype(str)})
      .assign(isOpexWeek=lambda x: x['date'].apply(OpexWeek), 
              isIpexWeekPd=lambda x: OpexWeekPd(pd.to_datetime(x['date'])))
    )
          date  isOpexWeek  isIpexWeekPd
0   2020-06-01       False         False
1   2020-06-02       False         False
2   2020-06-03       False         False
3   2020-06-04       False         False
4   2020-06-05       False         False
5   2020-06-08       False         False
6   2020-06-09       False         False
7   2020-06-10       False         False
8   2020-06-11       False         False
9   2020-06-12       False         False
10  2020-06-15        True          True
11  2020-06-16        True          True
12  2020-06-17        True          True
13  2020-06-18        True          True
14  2020-06-19        True          True
15  2020-06-22       False         False
16  2020-06-23       False         False
17  2020-06-24       False         False
18  2020-06-25       False         False
19  2020-06-26       False         False
20  2020-06-29       False         False
21  2020-06-30       False         False

Answer 2

We can easily modify your function to work on the index:

# sample data
dow = pd.DataFrame(index=pd.date_range('2020-01-01', '2020-01-31'),
                   columns=['OpexFriday'])

isFriday = dow.index.dayofweek == 5
thirdWeek = dow.index.day.to_series().between(15,21)

# third Friday
dow['OpexFriday'] = (isFriday & thirdWeek).values

# third work week
dow['OpexWeek'] = dow['OpexFriday'].where(dow['OpexFriday']).bfill(limit=4).fillna(0)

# extract the third week:
dow[dow['OpexWeek']==1]

Output:

            OpexFriday  OpexWeek
2020-01-14       False       1.0
2020-01-15       False       1.0
2020-01-16       False       1.0
2020-01-17       False       1.0
2020-01-18        True       1.0

Answer 3

import datetime
from math import ceil


def week_of_month(dt):
    """ Returns the week of the month for the specified date.
    """

    adjusted_dom = dt.day + dt.replace(day=1).day

    return int(ceil(adjusted_dom / 7.0))


def week_of_month_from_str(d_str):
    return week_of_month(datetime.datetime.strptime(d_str, '%Y-%m-%d'))


assert week_of_month_from_str("2020-03-02") == 1
assert week_of_month_from_str("2020-03-07") == 2
assert week_of_month_from_str("2020-03-13") == 2
assert week_of_month_from_str("2020-03-14") == 3
assert week_of_month_from_str("2020-03-20") == 3
assert week_of_month_from_str("2020-06-01") == 1
assert week_of_month_from_str("2020-06-06") == 1
assert week_of_month_from_str("2020-06-07") == 2
assert week_of_month_from_str("2020-06-08") == 2

Answer 4

Though the title of the question is "Pandas Week of the Month", based on your comments to other answers, it seems you are primarily interested in identifying "OpEx week", which is the trading week (i.e. Monday through Friday) that contains third Friday.

If above assumption and definition is correct, then this function will do the job:

def isOpexWeek(d):
    first_week_day = datetime.date(d.year, d.month, 1).weekday()
    first_friday = 1 + ((4 - first_week_day + 7) % 7)
    third_friday = first_friday + 14
    return d.day in range(third_friday-4, third_friday+1)

dow = pd.DataFrame(index=pd.date_range('2020-01-01', '2020-02-01'), columns=['OpexWeek'])
dow['OpexWeek'] = dow.index.to_series().apply(isOpexWeek)
print(dow)

dow = pd.DataFrame(index=pd.date_range('2020-01-01', '2021-01-01'), columns=['OpexWeek'])
dow['OpexWeek'] = dow.index.to_series().apply(isOpexWeek)
print(dow[dow.OpexWeek])