I am making a table, where i want to show that if there's no income, no expense can happen it's a cumulative sum table

This is what I've

Required output

I've tried

df.clip(lower=0)

and

df['new_column'].apply(lambda x : df['outgoing']-df['incoming'] if df['incoming']>df['outgoing'])

That doesn't work as well

is there any other way?

CodePudding user response：

Update:

A more straightforward approach inspired by your code using clip and without numpy:

diff = df['Incoming'].sub(df['Outgoing'])
df['Total'] = diff.mul(diff.ge(0).cumsum().clip(0, 1)).cumsum()
print(df)

# Output:
   Incoming  Outgoing  Total
0         0       150      0
1        10        20      0
2       100        30     70
3        50        70     50

Old answer:

Find the row where the balance is positive for the first time then compute the cumulative sum from this point:

start = np.where(df['Incoming'] - df['Outgoing'] >= 0)[0][0]

df['Total'] = df.iloc[start:]['Incoming'].sub(df.iloc[start:]['Outgoing']) \
                .cumsum().reindex(df.index, fill_value=0)

Output:

>>> df
   Incoming  Outgoing  Total
0         0       150      0
1        10        20      0
2       100        30     70
3        50        70     50

CodePudding user response：

IIUC, you can check when Incoming is greater than Outgoing using np.where and assign a helper column. Then you can check when this new column is not null, using notnull(), calculate the difference, and use cumsum() on the result:

df['t'] = np.where(df['Incoming'].ge(df['Outgoing']),0,np.nan)
df['t'].ffill(axis=0,inplace=True)

df['Total'] = np.where(df['t'].notnull(),(df['Incoming'].sub(df['Outgoing'])),df['t'])
df['Total'] = df['Total'].cumsum()

df.drop('t',axis=1,inplace=True)

This will give back:

   Incoming  Outgoing  Total
0         0       150    NaN
1        10        20    NaN
2       100        30   70.0
3        50        70   50.0