python - 创建一个从特定点倒数的新列

Question

我想查看产品更改前和产品更改后的结果。这是一个示例 df:

import pandas as pd

ids = [1,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2]
date = ["11/4/2020", "12/5/2020", "01/5/2021", "02/5/2020", "03/5/2020", "04/5/2020", "05/5/2020", "06/5/2020", "07/5/2020", "08/5/2020", "09/5/2020",
        "01/3/2019", "02/3/2019", "03/3/2019", "04/3/2019", "05/3/2019", "06/3/2019", "07/3/2019", "08/3/2019", "09/3/2019", "10/3/2019"]
months = [0,1,2,3,4,0,1,2,3,4,5,0,1,2,3,4,0,1,2,3,4]

df = pd.DataFrame({'ids': ids,
                   'date': date,
                   'months': months
                   })

df

    ids       date  months
0     1  11/4/2020       0
1     1  12/5/2020       1
2     1  01/5/2021       2
3     1  02/5/2020       3
4     1  03/5/2020       4
5     1  04/5/2020       0
6     1  05/5/2020       1
7     1  06/5/2020       2
8     1  07/5/2020       3
9     1  08/5/2020       4
10    1  09/5/2020       5
11    2  01/3/2019       0
12    2  02/3/2019       1
13    2  03/3/2019       2
14    2  04/3/2019       3
15    2  05/3/2019       4
16    2  06/3/2019       0
17    2  07/3/2019       1
18    2  08/3/2019       2
19    2  09/3/2019       3
20    2  10/3/2019       4

这就是我希望的最终结果:

    ids       date  months  new_col
0     1  11/4/2020       0       -5
1     1  12/5/2020       1       -4
2     1  01/5/2021       2       -3
3     1  02/5/2020       3       -2
4     1  03/5/2020       4       -1
5     1  04/5/2020       0        0
6     1  05/5/2020       1        1
7     1  06/5/2020       2        2
8     1  07/5/2020       3        3
9     1  08/5/2020       4        4
10    1  09/5/2020       5        5
11    2  01/3/2019       0       -5
12    2  02/3/2019       1       -4
13    2  03/3/2019       2       -3
14    2  04/3/2019       3       -2
15    2  05/3/2019       4       -1
16    2  06/3/2019       0        0
17    2  07/3/2019       1        1
18    2  08/3/2019       2        2
19    2  09/3/2019       3        3
20    2  10/3/2019       4        4

换句话说，我想添加一个列，为特定 ID 找到 months = 0 的第二个实例，并从中倒数，这样我就可以查看该点之前的结果(所有负数)与之后的结果那一点(所有正数)。

有没有一种简单的方法可以在 pandas 中做到这一点？

提前致谢

Answer 1

假设每个组有 2 个且只有 2 个 0 的实例，所以我不关心 ids 因为:

• (id1, first 0) -> 负计数器,
• (id1, second 0) -> 正计数器，
• (id2, first 0) -> 负计数器,
• (id2, second 0) -> 正数等等。

创建虚拟组以了解您是否必须创建负计数器或正计数器:

• 奇数组:负计数器
• 偶数组:正反

df['new_col'] = (
    df.assign(new_col=df['months'].eq(0).cumsum())
      .groupby('new_col')['new_col']
      .apply(lambda x: range(-len(x), 0, 1) if x.name % 2 else range(len(x))) 
      .explode().values
)

输出:

>>> df
    ids       date  months new_col
0     1  11/4/2020       0      -5
1     1  12/5/2020       1      -4
2     1  01/5/2021       2      -3
3     1  02/5/2020       3      -2
4     1  03/5/2020       4      -1
5     1  04/5/2020       0       0
6     1  05/5/2020       1       1
7     1  06/5/2020       2       2
8     1  07/5/2020       3       3
9     1  08/5/2020       4       4
10    1  09/5/2020       5       5
11    2  01/3/2019       0      -5
12    2  02/3/2019       1      -4
13    2  03/3/2019       2      -3
14    2  04/3/2019       3      -2
15    2  05/3/2019       4      -1
16    2  06/3/2019       0       0
17    2  07/3/2019       1       1
18    2  08/3/2019       2       2
19    2  09/3/2019       3       3
20    2  10/3/2019       4       4