python - 数据框条件列减去直到零

Question

这与此处常见的“减去直到 0”问题不同，因为它以另一列为条件。这个问题是关于创建条件列。

该数据框由三列组成。

“数量”列告诉您要添加/减去的数量。

“in”列告诉您何时进行减法。

列“cumulative_in”告诉您您有多少。

+----------+----+---------------+
| quantity | in | cumulative_in |
+----------+----+---------------+
|        5 |  0 |               |
|        1 |  0 |               |
|        3 |  1 |             3 |
|        4 |  1 |             7 |
|        2 |  1 |             9 |
|        1 |  0 |               |
|        1 |  0 |               |
|        3 |  0 |               |
|        1 | -1 |               |
|        2 |  0 |               |
|        1 |  0 |               |
|        2 |  0 |               |
|        3 |  0 |               |
|        3 |  0 |               |
|        1 |  0 |               |
|        3 |  0 |               |
+----------+----+---------------+

每当列“in”等于-1时，从下一行开始，我想创建一个列“out”(0/1)，告诉它继续减去直到 'cumulative_in' 达到 0。手动执行，

列“out”告诉您何时继续减法。

“cumulative_subtracted”列告诉您已经减去了多少。

我将列“cumulative_in”减去“cumulative_subtracted”，直到达到 0，输出如下所示:

+----------+----+---------------+-----+-----------------------+
| quantity | in | cumulative_in | out | cumulative_subtracted |
+----------+----+---------------+-----+-----------------------+
|        5 |  0 |               |     |                       |
|        1 |  0 |               |     |                       |
|        3 |  1 |             3 |     |                       |
|        4 |  1 |             7 |     |                       |
|        2 |  1 |             9 |     |                       |
|        1 |  0 |               |     |                       |
|        1 |  0 |               |     |                       |
|        3 |  0 |               |     |                       |
|        1 | -1 |               |     |                       |
|        2 |  0 |             7 |   1 |                     2 |
|        1 |  0 |             6 |   1 |                     3 |
|        2 |  0 |             4 |   1 |                     5 |
|        3 |  0 |             1 |   1 |                     8 |
|        3 |  0 |             0 |   1 |                     9 |
|        1 |  0 |               |     |                       |
|        3 |  0 |               |     |                       |
+----------+----+---------------+-----+-----------------------+

Answer 1

我找不到这个问题的向量解决方案。我很想看看。然而，当逐行进行处理时，问题并不那么难。我希望你的数据框不要太大!!

首先设置数据。

data = {
    "quantity": [
        5,1,3,4,2,1,1,3,1,2,1,2,3,3,1,3
    ], 
    "in":[
        0,0,1,1,1,0,0,0,-1,0,0,0,0,0,0,0
    ], 
    "cumulative_in":  [
        np.NaN,np.NaN,3,7,9,np.NaN,np.NaN,np.NaN,np.NaN,np.NaN,np.NaN,np.NaN,np.NaN,np.NaN,np.NaN,np.NaN
    ]

}

然后设置数据框和额外的列。我使用 np.NaN 作为“out”，但 0 对于“cumulative_subtracted”更容易

df=pd.DataFrame(data)
df['out'] = np.NaN
df['cumulative_subtracted'] = 0

设置初始变量

last_in = 0.
reduce = False

不幸的是，逐行浏览数据框。

for i in df.index:
    # check if necessary to adjust last_in value.
    if ~np.isnan(df.at[i, "cumulative_in"]) and reduce == False:
        last_in = df.at[i, "cumulative_in"]
    # check if -1 and change reduce to true
    elif df.at[i, "in"] == -1:
        reduce = True
    # check if reduce true, the implement reductions
    elif reduce == True:
        df.at[i, "out"] = 1
        if df.at[i, "quantity"] <= last_in:
            last_in -= df.at[i, "quantity"]
            df.at[i, "cumulative_in"] = last_in
            df.at[i, "cumulative_subtracted"] = (
                df.at[i - 1, "cumulative_subtracted"] + df.at[i, "quantity"]
            )
        elif df.at[i, "quantity"] > last_in:
            df.at[i, "cumulative_in"] = 0
            df.at[i, "cumulative_subtracted"] = (
                df.at[i - 1, "cumulative_subtracted"] + last_in
            )
            last_in = 0
            reduce = False

这适用于给定的数据，并希望适用于您的所有数据集。

打印(df)

    quantity  in  cumulative_in  out  cumulative_subtracted
0          5   0            NaN  NaN                      0
1          1   0            NaN  NaN                      0
2          3   1            3.0  NaN                      0
3          4   1            7.0  NaN                      0
4          2   1            9.0  NaN                      0
5          1   0            NaN  NaN                      0
6          1   0            NaN  NaN                      0
7          3   0            NaN  NaN                      0
8          1  -1            NaN  NaN                      0
9          2   0            7.0  1.0                      2
10         1   0            6.0  1.0                      3
11         2   0            4.0  1.0                      5
12         3   0            1.0  1.0                      8
13         3   0            0.0  1.0                      9
14         1   0            NaN  NaN                      0
15         3   0            NaN  NaN                      0