I was trying to calculate the week number starting from first Monday of October. Is there any functions in pandas or datetime to do the calculation efficiently?
MWE
import pandas as pd from datetime import date,datetime df = pd.DataFrame({'date': pd.date_range('2021-10-01','2022-11-01',freq='1D')}) df['day_name'] = df['date'].dt.day_name() df2 = df.head() df2['Fiscal_Week'] = [52,52,52,1,1] # 2021-10-4 is monday, so for oct4, week is 1 # How to do it programatically for any year? df2 date day_name Fiscal_Week 0 2021-10-01 Friday 52 1 2021-10-02 Saturday 52 2 2021-10-03 Sunday 52 3 2021-10-04 Monday 1 4 2021-10-05 Tuesday 1
Advertisement
Answer
Shift dates by the number of days to new year, they use standard Monday week number formatting (%W
):
df['Fiscal_Week'] = ( df['date'] + pd.DateOffset(days=91) ).dt.strftime('%W').astype(int).replace({0:None}).fillna(method='ffill')
- The offset in days can be calculated manually (I assume the fiscal year start is fixed)
- The
replace
part is needed because leftovers from the previous year are considered week 0. The previous week might be 52 or 53, so replacing withNA
and then fill forward