比较当前年份的一个月值与前一年的值加或减多个参数

Question

给出以下数据格式的df

          date  mom_pct
0    2020-1-31      1.4
1    2020-2-29      0.8
2    2020-3-31     -1.2
3    2020-4-30     -0.9
4    2020-5-31     -0.8
5    2020-6-30     -0.1
6    2020-7-31      0.6
7    2020-8-31      0.4
8    2020-9-30      0.2
9   2020-10-31     -0.3
10  2020-11-30     -0.6
11  2020-12-31      0.7
12   2021-1-31      1.0
13   2021-2-28      0.6
14   2021-3-31     -0.5
15   2021-4-30     -0.3
16   2021-5-31     -0.2
17   2021-6-30     -0.4
18   2021-7-31      0.3
19   2021-8-31      0.1
20   2021-9-30      0.0
21  2021-10-31      0.7
22  2021-11-30      0.4
23  2021-12-31     -0.3
24   2022-1-31      0.4
25   2022-2-28      0.6
26   2022-3-31      0.0
27   2022-4-30      0.4
28   2022-5-31     -0.2

我想把今年一个月的连锁比率值和上一年的一个月的值进行比较。假设去年同期的值为y_t-1，而今年的当前值为y_t。我将根据以下规则创建一个新列：

如果

• = y_t-1，则新列返回0；如果y_t∈( y_t-1，y_t-1 +0.3)，返回1；如果y_t∈( y_t -1 + 0.3，y_t -1+ 0.5)，返回2；
• 如果y_t∈[ y_t -1 - 0.3，y_t-1)，返回-1；
• 如果y_t∈[y_t-1-0.5，y_t-1-0.3)，返回-2；
• 如果y_t<(y_t-1-0.5)，则返回-3

。

预期结果：

          date  mom_pct  categorial_mom_pct
0    2020-1-31      1.0                 NaN
1    2020-2-29      0.8                 NaN
2    2020-3-31     -1.2                 NaN
3    2020-4-30     -0.9                 NaN
4    2020-5-31     -0.8                 NaN
5    2020-6-30     -0.1                 NaN
6    2020-7-31      0.6                 NaN
7    2020-8-31      0.4                 NaN
8    2020-9-30      0.2                 NaN
9   2020-10-31     -0.3                 NaN
10  2020-11-30     -0.6                 NaN
11  2020-12-31      0.7                 NaN
12   2021-1-31      1.0                 0.0
13   2021-2-28      0.6                -1.0
14   2021-3-31     -0.5                 3.0
15   2021-4-30     -0.3                 3.0
16   2021-5-31     -0.2                 3.0
17   2021-6-30     -0.4                -1.0
18   2021-7-31      0.3                -1.0
19   2021-8-31      0.1                -1.0
20   2021-9-30      0.0                -1.0
21  2021-10-31      0.7                 3.0
22  2021-11-30      0.4                 3.0
23  2021-12-31     -0.3                -3.0
24   2022-1-31      0.4                -3.0
25   2022-2-28      0.6                 0.0
26   2022-3-31      0.0                 2.0
27   2022-4-30      0.4                 3.0
28   2022-5-31     -0.2                 0.0

我尝试创建多个列和范围，然后检查mom_pct在哪个范围内。是否有可能以更有效的方式做到这一点？谢谢。

df1['mom_pct_zero'] = df1['mom_pct'].shift(12)
df1['mom_pct_pos1'] = df1['mom_pct'].shift(12) + 0.3
df1['mom_pct_pos2'] = df1['mom_pct'].shift(12) + 0.5
df1['mom_pct_neg1'] = df1['mom_pct'].shift(12) - 0.3
df1['mom_pct_neg2'] = df1['mom_pct'].shift(12) - 0.5

Answer 1

我会这样做的

def categorize(v):
    if np.isnan(v) or v == 0.:
        return v
    sign = -1 if v < 0 else 1
    eps = 1e-10
    if abs(v) <= 0.3 + eps:
        return sign * 1
    if abs(v) <= 0.5 + eps:
        return sign * 2
    return sign * 3

df['categorial_mom_pct'] = df['mom_pct'].diff(12).map(categorize)
print(df)

请注意，我在阈值中添加了一个非常小的eps，以便用浮点算法解决精度问题。

abs(-0.3) <= 0.3  # True
abs(-0.4 + 0.1) <= 0.3  # False
abs(-0.4 + 0.1) <= 0.3 + 1e-10  # True

退出：

          date  mom_pct  categorial_mom_pct
0    2020-1-31      1.0                 NaN
1    2020-2-29      0.8                 NaN
2    2020-3-31     -1.2                 NaN
3    2020-4-30     -0.9                 NaN
4    2020-5-31     -0.8                 NaN
5    2020-6-30     -0.1                 NaN
6    2020-7-31      0.6                 NaN
7    2020-8-31      0.4                 NaN
8    2020-9-30      0.2                 NaN
9   2020-10-31     -0.3                 NaN
10  2020-11-30     -0.6                 NaN
11  2020-12-31      0.7                 NaN
12   2021-1-31      1.0                 0.0
13   2021-2-28      0.6                -1.0
14   2021-3-31     -0.5                 3.0
15   2021-4-30     -0.3                 3.0
16   2021-5-31     -0.2                 3.0
17   2021-6-30     -0.4                -1.0
18   2021-7-31      0.3                -1.0
19   2021-8-31      0.1                -1.0
20   2021-9-30      0.0                -1.0
21  2021-10-31      0.7                 3.0
22  2021-11-30      0.4                 3.0
23  2021-12-31     -0.3                -3.0
24   2022-1-31      0.4                -3.0
25   2022-2-28      0.6                 0.0
26   2022-3-31      0.0                 2.0
27   2022-4-30      0.4                 3.0
28   2022-5-31     -0.2                 0.0