Cumsum column while skipping rows or setting fixed values on a condition based on the result of the-CodePudding

I'm trying to find a vectorized solution in pandas that is quite common in spreadsheets which is to cumsum while skipping or setting fixed values on a condition based on the result of the actual cumsum. I have the following:

What I need is to add a second column with the cumsum of 'A' and if one of these sums gives a positive value replace it with 0 and continue the cumsum using that 0. At the same time if the cumsum gives a negative value that is lower than the lowest value in column A recorded after a 0 in column B I will need to replace it with that lowest value in column A. I know this is quite a problem but is there a vectorized solution for this? Maybe using an auxiliary column. The result should look like this:

    A   B
1   0   0
2  -1  -1  # -1 0 = -1
3   2   0  # -1   2 = 1 but  1>0 so this is 0
4   3   0  # same as previous row
5  -2  -2  # -2 0 = -2
6  -3  -3  # -2-3 = -5 but the lowest value in column A since last 0 is -3 so this is replaced by -3
7   1  -2  #  1-3 = -2
8  -1  -3  # -1-2 = -3
9   1  -2  # -3   1 = -2
10 -2  -3  # -2-2 = -4 but the lowest value in column A since last 0 is -3 so this is replaced by -3 
11  1  -2  # -3  1 = -2
12  2   0  # -2 2 = 0
13 -1  -1  # 0-1 = -1
14 -2  -2  # -1-2 = -3 but the lowest value in column A since last cap is -2 so this is -2 instead of -3

For the moment I made this but does not work 100% and again is not really efficient:

for x in range(len(df)-1):
    A = df['A'][x   1]
    B = df['B'][x]   A
    if B >= 0:
        df['B'][x 1] = 0
    elif B < 0 and A < 0 and B < A:
        df['B'][x 1] = A
    else:
        df['B'][x   1] = B

CodePudding user response：

Using df['A'].expanding(1).apply(function) I could run own function which first get only one row, next 2 rows, next 3 rows, etc. I doesn't give result from previous calculation and it needs to make all calculations again and again but it doesn't need global variables and hardcoded df['A']

Doc: Series.expanding

A = [0, -1, 2, 3, -2, -3, 1, -1, 1, -2, 1, 2, -1, -2]

import pandas as pd

df = pd.DataFrame({"A": A})

def function(values):
    #print(values)
    #print(type(valuse)
    #print(len(values))

    result = 0

    last_zero = 0

    for index, value in enumerate(values):
        result  = value

        if result >= 0:
            result = 0
            last_zero = index
        else:
            minimal = min(values[last_zero:])
            #print(index, last_zero, minimal)
                        
            #if result < minimal:
            #   result = minimal
            result = max(result, minimal)
            
    #print('result:', result)
    return result

df['B'] = df['A'].expanding(1).apply(function)

df['B'] = df['B'].astype(int)

print(df)

Result:

The same but with normal apply() - it needs global variables and hardcoded df['A']

A = [0, -1, 2, 3, -2, -3, 1, -1, 1, -2, 1, 2, -1, -2]

import pandas as pd

df = pd.DataFrame({"A": A})

result = 0
last_zero = 0
index = 0

def function(value):
    global result
    global last_zero
    global index
    
    result  = value

    if result >= 0:
        result = 0
        last_zero = index
    else:        
        minimal = min(df['A'][last_zero:])
        #print(index, last_zero, minimal)
                        
        #if result < minimal:
        #   result = minimal
        result = max(result, minimal)
       
    index  = 1
    
    #print('result:', result)
    return result

df['B'] = df['A'].apply(function)
df['B'] = df['B'].astype(int)

print(df)

The same using normal for-loop

A = [0, -1, 2, 3, -2, -3, 1, -1, 1, -2, 1, 2, -1, -2]

import pandas as pd

df = pd.DataFrame({"A": A})

all_values = []

result = 0
last_zero = 0

for index, value in df['A'].iteritems():
    
    result  = value
    
    if result >= 0:
        result = 0
        last_zero = index
    else:    
        minimal = min(df['A'][last_zero:])
        #print(index, last_zero, minimal)
                            
        #if result < minimal:
        #   result = minimal
        result = max(result, minimal)
           
    all_values.append(result)

df['B'] = all_values

print(df)

CodePudding user response：

I hope to help you with my code as I can't find a way to functionally add a method that uses a condition to modify the values generated by cumsum() of the pandas.DataFrame() instance.

# pandas
import pandas as pd

df = pd.DataFrame()

# pre-defined A column
a = [0, -1, 2, 3, -2, -3, 1, -1, 1, -2, 1, 2, -1, -2]
df["A"] = a

# create a new column (array) based on A or df length
b_col = [0 for i in range(len(df))]

# minimal value and last index cap minimal value (or zero 0)
last_val = min(df["A"])
idx = 0

# define first B row 
b_col[0] = max(min_val, min(df["A"][0] (b_col[0]), 0))

In the core part of the loop, it is responsible for assigning the last index with a result of 0 and then using it as the minimum value and then choosing the value between the previous one assigned and the number 0

for i in range(0, len(df)-1):
    if (df["A"][i 1]>=0):
        idx = i

    if (df["A"][i 1] (b_col[i]) < last_val):
        b_col[i 1] = last_val
        last_val = min(df["A"][idx:])
        
    else:
        b_col[i 1] = min(df["A"][i 1] (b_col[i]), 0)
    
df["B"] = b_col

output: