How to define a period column for 5-4-4 weeks from year and week number in Pandas

user_10962477 0
Viewed 98

My company uses a 5-4-4 calendar for reporting purposes. Each month (aka period) is 4-weeks long, except every first month is 5-weeks long.

Pandas seems to have good support for custom calendar periods and found this documentation too.

I'm creating Period column from Week and Year using below code

dff['Period'] = pd.to_datetime(dff["Week ID"].astype("str") + "-0", format="%Y%U-%w").dt.month
dff['Period'] ='Period ' + dff['Period'].astype(str).str.zfill(2)

However my company is using 5-4-4 format for period i.e.

  • First 5 weeks [Wk01 to Wk05] should be considered as Period 1
  • next 4 weeks [Wk06 to Wk09] should be considered as Period 2
  • next 4 weeks [Wk10 to Wk13] should be considered as Period 3

again

  • next 5 weeks [Wk14 to Wk18] should be considered as Period 4
  • next 4 weeks [Wk19 to Wk22] should be considered as Period 5
  • next 4 weeks [Wk23 to Wk26] should be considered as Period 6

and so on for every year.......

Current output

Year    Period      Week    Week ID Site
2020    Period 01   Wk01    202001  ABC
2020    Period 01   Wk02    202002  ABC
2020    Period 01   Wk03    202003  ABC
2020    Period 01   Wk04    202004  ABC
2020    Period 02   Wk05    202005  ABC
2020    Period 02   Wk06    202006  ABC
2020    Period 02   Wk07    202007  ABC
2020    Period 02   Wk08    202008  ABC
2020    Period 03   Wk09    202009  ABC
2020    Period 03   Wk10    202010  ABC
2020    Period 03   Wk11    202011  ABC
2020    Period 03   Wk12    202012  ABC
2020    Period 03   Wk13    202013  ABC
2020    Period 04   Wk14    202014  ABC
2020    Period 04   Wk15    202015  ABC

Expected output

Year    Period      Week    Week ID Site
2020    Period 01   Wk01    202001  ABC
2020    Period 01   Wk02    202002  ABC
2020    Period 01   Wk03    202003  ABC
2020    Period 01   Wk04    202004  ABC
2020    Period 01   Wk05    202005  ABC
2020    Period 02   Wk06    202006  ABC
2020    Period 02   Wk07    202007  ABC
2020    Period 02   Wk08    202008  ABC
2020    Period 02   Wk09    202009  ABC
2020    Period 03   Wk10    202010  ABC
2020    Period 03   Wk11    202011  ABC
2020    Period 03   Wk12    202012  ABC
2020    Period 03   Wk13    202013  ABC
2020    Period 04   Wk14    202014  ABC
2020    Period 04   Wk15    202015  ABC
pandas time-series date datetime python
2 Answers

You can use some math to compute a mapper:

# define week pattern
pat = np.array([5,4,4,4])
# weeks
weeks = np.arange(52)+1
# get divider/remainder of full period (=divmod 17 here)
d,r = np.divmod((weeks-1), pat.sum())
# determine periods
a = (r==pat.cumsum()[:,None]).cumsum(axis=1).sum(axis=0)+d+1

# define mapper
week_to_period = dict(enumerate(a, start=1))
# {1: 1, 2: 1, 3: 1, 4: 1, 5: 1, 6: 2, 7: 2, 8: 2, 9: 2, 10: 3, 11: 3,...}

# map week numbers to custom periods
s = (pd.to_datetime(dff["Week ID"].astype("str") + "-0", format="%Y%U-%w")
       .dt.isocalendar().week()
     )
dff['Period'] = 'Period '+s.map(week_to_period).astype(str).str.zfill(2)

output:

    Year     Period  Week  Week ID Site
0   2020  Period 01  Wk01   202001  ABC
1   2020  Period 01  Wk02   202002  ABC
2   2020  Period 01  Wk03   202003  ABC
3   2020  Period 01  Wk04   202004  ABC
4   2020  Period 01  Wk05   202005  ABC
5   2020  Period 02  Wk06   202006  ABC
6   2020  Period 02  Wk07   202007  ABC
7   2020  Period 02  Wk08   202008  ABC
8   2020  Period 02  Wk09   202009  ABC
9   2020  Period 03  Wk10   202010  ABC
10  2020  Period 03  Wk11   202011  ABC
11  2020  Period 03  Wk12   202012  ABC
12  2020  Period 03  Wk13   202013  ABC
13  2020  Period 04  Wk14   202014  ABC
14  2020  Period 04  Wk15   202015  ABC
user_163434640 
import numpy as np
import pandas as pd

# Define range of months
x = list(range(1,13))

# Define week pattern
y = [5, 4, 4]*4

period1 = list(np.repeat(x, y))
wk = ['Wk'+str(i) for i in range(1,53)]

di_wk = dict(zip(wk, period1))
'''{'Wk1': 1, 'Wk2': 1, 'Wk3': 1, 'Wk4': 1, 'Wk5': 1, 'Wk6': 2, 'Wk7': 2, 'Wk8': 2,
 'Wk9': 2, 'Wk10': 3, 'Wk11': 3, 'Wk12': 3, 'Wk13': 3, 'Wk14': 4, 'Wk15': 4,......}'''

dff['Period'] = 'Period ' + dff['Week'].map(di_wk).astype(str).str.zfill(2)
user_109624770
Related