Gus
Gus

Reputation: 193

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

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:

    A
1   0
2  -1
3   2
4   3
5  -2
6  -3
7   1
8  -1
9   1
10 -2
11  1
12  2 
13 -1
14 -2

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:

df['B'] = 0
df['B'][0] = 0
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

Upvotes: 3

Views: 149

Answers (1)

furas
furas

Reputation: 143216

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:

    A  B
0   0  0
1  -1 -1
2   2  0
3   3  0
4  -2 -2
5  -3 -3
6   1 -2
7  -1 -3
8   1 -2
9  -2 -3
10  1 -2
11  2  0
12 -1 -1
13 -2 -2

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)

Upvotes: 1

Related Questions