Limiting the calculation in a column when it reaches a certain value and restarting it again when specific conditions are met

Question

For a few days I have been struggling with some modification of the column.. The batt_status column is calculated as follows:

if (batt_input) > 0:
batt_status[i] = batt_status[i-1] + (batt_input[i] / 4)
else:
batt_status[i] = batt_status[i-1] - (batt_output[i] / 4).

   batt_input   batt_output batt_status
0   0.000000    0.000000    0.000000
1   2.739314    0.000000    0.684829
2   5.000000    0.000000    1.934829
3   5.000000    0.000000    3.184829
4   4.190054    0.000000    4.232342
5   4.627677    0.000000    5.389261
6   4.237302    0.000000    6.448587
7   1.251996    0.000000    6.761586
8   4.147673    0.000000    7.798504
9   2.921009    0.000000    8.528756
10  4.877213    0.000000    9.748060
11  5.000000    0.000000    10.998060
12  5.000000    0.000000    12.248060
13  5.000000    0.000000    13.498060
14  0.000000    3.120185    12.718013
15  0.000000    3.094523    11.944382
16  0.000000    3.711843    11.016422
17  0.000000    4.338085    9.931900
18  0.000000    4.173286    8.888579
19  0.000000    4.312411    7.810476
20  0.000000    4.345891    6.724003
21  0.000000    4.512739    5.595818
22  0.000000    4.543866    4.459852
23  0.000000    4.450718    3.347172
24  0.000000    4.511852    2.219209
25  0.000000    4.765721    1.027779
26  0.000000    4.713985    -0.150717
27  0.000000    4.604684    -1.301888

I used code:

df['batt_status'] =  np.where(df['batt_input'] == 0, 0, np.nan)

for i in range(1, len(df)):
    if(df.loc[i, 'batt_input'] > 0):
        df.loc[i, 'batt_status'] = df.loc[i-1, 'batt_status'] + (df.loc[i, 'batt_input']/4)
    else:
        df.loc[i, 'batt_status'] = df.loc[i-1, 'batt_status'] - (df.loc[i, 'batt_output']/4)

As you noted in the batt_status column, there can be accumulating negative or strong positive values. Now I would like to limit the values in the batt_status column so that they reach min value 0 and max value 20.

Then, when zero is reached, subsequent values should duplicate zero until something else can be added (batt_input > 0).

Once the value 20 is reached, it should also be duplicated until something can be subtracted (batt_output > 0). And so on.

Expected output (example):

    batt_input  batt_output batt_status
0   0.000000    0.000000    0.000000
1   2.739314    0.000000    0.684829
2   5.000000    0.000000    1.934829
3   5.000000    0.000000    3.184829
4   4.190054    0.000000    4.232342
5   4.627677    0.000000    5.389261
6   4.237302    0.000000    6.448587
7   1.251996    0.000000    6.761586
8   4.147673    0.000000    7.798504
9   2.921009    0.000000    8.528756
10  4.877213    0.000000    9.748060
11  5.000000    0.000000    10.998060
12  5.000000    0.000000    12.248060
13  5.000000    0.000000    13.498060
14  0.000000    3.120185    12.718013
15  0.000000    3.094523    11.944382
16  0.000000    3.711843    11.016422
17  0.000000    4.338085    9.931900
18  0.000000    4.173286    8.888579
19  0.000000    4.312411    7.810476
20  0.000000    4.345891    6.724003
21  0.000000    4.512739    5.595818
22  0.000000    4.543866    4.459852
23  0.000000    4.450718    3.347172
24  0.000000    4.511852    2.219209
25  0.000000    4.765721    1.027779
26  0.000000    4.713985    0.000000 # (here batt_status reaches lower than 0, so put the smallest possible i.e. 0)
27  0.000000    4.604684    0.000000 # repeat 0
28  0.000000    3.567943    0.000000 # repeat 0 
29  0.000000    2.344556    0.000000 # repeat 0
30  2.739314    0.000000    0.684829 # can add (batt_input/4)
31  10.35678    0.000000    3.274024
32  65.03452    0.000000    19.53265
33  3.452341    0.000000    20.00000 # (here batt_status reaches value greater than 20, so put as much as possible, i.e. 20)
34  2.345566    0.000000    20.00000 # repeat 20
35  45.56677    0.000000    20.00000 # repeat 20 
36  0.000000    25.45600    13.63600 # can substract (batt_output/4) 
37  0.000000    2.445552    13.02462

Do you have any idea for that?

Can you provide your dataset in a [reproducible](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) manner? — sophocles, Jan 18 '22 at 14:05
Thank you, still not ideal though. Can you also add your desired output please? — sophocles, Jan 18 '22 at 14:44
Sorry, I'm forgetting that what's most important is in my head. I added the expected output — sdom, Jan 18 '22 at 15:02

score 1 · Accepted Answer · answered Jan 18 '22 at 15:10

It is fairly easy to modify your code with min and max; the below implements that plus a small change

df['batt_change'] = np.where(df['batt_input']>0, df['batt_input']/4, -df['batt_output']/4)

df['batt_status'] = 0
for i in range(1, len(df)):
    df.loc[i, 'batt_status']  = min(max(df.loc[i-1, 'batt_status'] + df.loc[i, 'batt_change'],0),20)

the reason for introducing 'batt_change' column is that your original code could have then been implemented as simply

df['batt_status'] = df['batt_change'].cumsum()

but we cannot do that unfortunately since you want to cap and floor the output. so still need that loop

df looks like this (for your second example)

      batt_input    batt_output    batt_change    batt_status
--  ------------  -------------  -------------  -------------
 0       0              0            -0              0
 1       2.73931        0             0.684828       0.684828
 2       5              0             1.25           1.93483
 3       5              0             1.25           3.18483
 4       4.19005        0             1.04751        4.23234
 5       4.62768        0             1.15692        5.38926
 6       4.2373         0             1.05933        6.44859
 7       1.252          0             0.312999       6.76159
 8       4.14767        0             1.03692        7.7985
 9       2.92101        0             0.730252       8.52876
10       4.87721        0             1.2193         9.74806
11       5              0             1.25          10.9981
12       5              0             1.25          12.2481
13       5              0             1.25          13.4981
14       0              3.12019      -0.780046      12.718
15       0              3.09452      -0.773631      11.9444
16       0              3.71184      -0.927961      11.0164
17       0              4.33809      -1.08452        9.9319
18       0              4.17329      -1.04332        8.88858
19       0              4.31241      -1.0781         7.81048
20       0              4.34589      -1.08647        6.724
21       0              4.51274      -1.12818        5.59582
22       0              4.54387      -1.13597        4.45985
23       0              4.45072      -1.11268        3.34717
24       0              4.51185      -1.12796        2.21921
25       0              4.76572      -1.19143        1.02778
26       0              4.71399      -1.1785         0
27       0              4.60468      -1.15117        0
28       0              3.56794      -0.891986       0
29       0              2.34456      -0.586139       0
30       2.73931        0             0.684828       0.684828
31      10.3568         0             2.5892         3.27402
32      65.0345         0            16.2586        19.5327
33       3.45234        0             0.863085      20
34       2.34557        0             0.586391      20
35      45.5668         0            11.3917        20
36       0             25.456        -6.364         13.636
37       0              2.44555      -0.611388      13.0246

nice.. you saved my life man, it works – sdom Jan 18 '22 at 18:15 — sdom, Jan 18 '22 at 18:15

Limiting the calculation in a column when it reaches a certain value and restarting it again when specific conditions are met

1 Answers1