python - 根据 Pandas 中的约束生成列

Question

F_Date      B_Date      col   is_B
01/09/2019  02/08/2019  2200    1
01/09/2019  03/08/2019  672     1
02/09/2019  03/08/2019  1828    1
01/09/2019  04/08/2019  503     0
02/09/2019  04/08/2019  829     1
03/09/2019  04/08/2019  1367    0
02/09/2019  05/08/2019  559     1
03/09/2019  05/08/2019  922     1
04/09/2019  05/08/2019  1519    0
01/09/2019  06/08/2019  376     1

我想生成一个列 c_a，这样对于 flight_date 的第一个条目，最初的值为 25000 并根据 col 值减少。例如:

预期输出:

F_Date      B_Date      col   is_B   c_a
01/09/2019  02/08/2019  2200    1    25000
01/09/2019  03/08/2019  672     1    25000 - 2200
02/09/2019  03/08/2019  1828    1    25000
01/09/2019  04/08/2019  503     0    25000 - 2200 - 672
02/09/2019  04/08/2019  829     1    25000 - 1828
03/09/2019  04/08/2019  1367    0    25000
02/09/2019  05/08/2019  559     1    25000 - 1828 - 829
03/09/2019  05/08/2019  922     1    25000 (since last value had is_B as 0)
04/09/2019  05/08/2019  1519    0    25000
01/09/2019  06/08/2019  376     1    25000 - 2200 - 672 (Since last appearance had is_B as 0)

任何人都可以找到实现相同目标的 pandas 方法吗？

Answer 1

我想，我找到了一个相当简洁的解决方案:

df['c_a'] = df.groupby('F_Date').apply(lambda grp:
    25000 - grp.col.where(grp.is_B.eq(1), 0).shift(fill_value=0)
    .cumsum()).reset_index(level=0, drop=True)

结果是:

       F_Date      B_Date   col  is_B    c_a
0  01/09/2019  02/08/2019  2200     1  25000
1  01/09/2019  03/08/2019   672     1  22800
2  02/09/2019  03/08/2019  1828     1  25000
3  01/09/2019  04/08/2019   503     0  22128
4  02/09/2019  04/08/2019   829     1  23172
5  03/09/2019  04/08/2019  1367     0  25000
6  02/09/2019  05/08/2019   559     1  22343
7  03/09/2019  05/08/2019   922     1  25000
8  04/09/2019  05/08/2019  1519     0  25000
9  01/09/2019  06/08/2019   376     1  22128

这个想法，以及基于 F_Date == '01/09/2019' 组的例子:

1. grp.col.where(grp.is_B.eq(1), 0) - 要减去的值组中的下行:

   0    2200
   1     672
   3       0
   9     376
   

2. .shift(fill_value=0) - 从当前中减去的值分组行:

   0       0
   1    2200
   3     672
   9       0
   

3. .cumsum() - 要减去的累积值:

   0       0
   1    2200
   3    2872
   9    2872
   

4. 25000 - ... - 目标值:

   0    25000
   1    22800
   3    22128
   9    22128