How can I calculate the needed value field,

for 1st row it takes the value field as it is,

for 2nd row it takes 2nd row needed value - 3rd row Agg value

for 3rd row, 3rd row needed value -4th row agg

CodePudding user response：

use cumsum to get cumulative sum of Agg, then subtract Value:

first_val = float(df['Value'].iloc[0])
df['Needed'] = first_val - df['Agg'].iloc[1:].cumsum()
df['Needed'] = df['Needed'].fillna(first_val)

Output:

        Time    Agg   Value        Needed Value  Needed
0  10:55:00   178.0  322.0                  322   322.0
1  11:00:00   354.0      -         (322-354)-32   -32.0
2  11:05:00   354.0      -        (-32-354)-386  -386.0
3  11:10:00   354.0      -   (-386-next Agg nu)  -740.0

CodePudding user response：

For improve performance is used numba:

from numba import jit

@jit(nopython=True)
def f(a, b):
    d = np.empty(a.shape)
    d[0] = b[0]
    for i in range(1, a.shape[0]):
        d[i] = d[i-1] - a[i]
    return d

df['Needed Value'] = f(df['Agg'].to_numpy(), df['Value'].to_numpy())
print (df)
       Time    Agg  Value  Needed Value
0  10:55:00  178.0  322.0         322.0
1  11:00:00  354.0    NaN         -32.0
2  11:05:00  354.0    NaN        -386.0
3  11:10:00  354.0    NaN        -740.0