如何根据 Pandas 中的年份和周数定义 5-4-4 周的期间列

Question

我的公司使用 5-4-4 日历进行报告。每个月（又称周期）长 4 周，但每个第一个月长 5 周。

Pandas 似乎对 自定义日历周期并发现此文档也是如此。

我正在使用下面的代码从周和年创建期间列

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)

，但是我的公司对期间使用 5-4-4 格式，即

• 前 5 周 [Wk01 到 Wk05] 应被视为期间 1，
• 接下来的 4 周 [Wk06 到 Wk09] 应被视为期间 1被视为周期 2
• 被视为周期 3

接下来 4 周 [第 10 周到第 13 周] 应该再次

• 接下来 5 周 [第 14 周到第 18 周] 应该被视为周期 接下来的 4周
• [第 19 周到第 22 周] 应被视为第 5 期
• 接下来的 4 周 [第 23 周到第 26 周] 应被视为第 6 期

，每年依此类推......

当前产出

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

预期产出

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

Answer 1

您可以使用一些数学来计算映射器：

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

输出：

    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

Answer 2

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)