Python: Create some sort of cumsum between two columns

Question

I am trying to figure out how to get some sort of running total using multiple columns and I can't figure out where to even start. I've used cumsum before but only for just one single column and this won't work.

I have this table :

      Index       A         B       C    
        1        10        12      20    
        2        10        14      20   
        3        10         6      20

I am trying to build out this table that looks like this:

      Index       A         B       C       D
        1        10        12      20      10
        2        10        14      20      18
        3        10         6      20      24

The formula for D is as follows: D2 = ( D1 - B1 ) + C1

D1 = Column A

Any ideas on how I could do this? I am totally out of ideas on this.

Does this answer your question? [Cumulative Sum using 2 columns](https://stackoverflow.com/questions/48033351/cumulative-sum-using-2-columns) — new Q Open Wid, Nov 22 '19 at 20:51
Oh yea, good catch. So the output shoudl be `[10, -12, 8]` ? — rafaelc, Nov 22 '19 at 21:10

score 0 · Answer 1 · answered Nov 22 '19 at 21:01

You can get your answer by using shift, reference the answer here

import pandas as pd
raw_data = {'Index':      ['1/1/2020', '1/2/2020', '1/3/2020', '1/4/2020', '1/5/2020'],
        'Inventory':     [10, 10, 10, 10, 10],
         'Booked':       [12,14,6,3,5] }

df = pd.DataFrame(raw_data)
df['New_Inventory'] = 10 # need to initialize
df['New_Inventory'] = df['Inventory'] - df['Booked'].shift(1) - df['New_Inventory'].shift(1)
df

Your requested output seems wrong. The calculation for above New_Inventory is what was requested.

`NewInventory(i)` depends on `NewInvetory(i-1)`. You have to expand the formula to create a generalized one — rafaelc, Nov 22 '19 at 21:05

score 0 · Accepted Answer · answered Nov 22 '19 at 21:59

0

This should work:


df.loc[0, 'New_Inventory'] = df.loc[0, 'Inventory']   
for i in range(1, len(df)):
    df.loc[i, 'New_Inventory'] = df.loc[i-1, 'Inventory'] - df.loc[i-1, 'Booked'] - abs(df.loc[i-1, 'New_Inventory'])
df.New_Inventory = df.New_Inventory.astype(int)  

df
#      Index  Inventory  Booked  New_Inventory
#0  1/1/2020         10      12             10
#1  1/2/2020         10      14            -12
#2  1/3/2020         10       6            -16

answered Nov 22 '19 at 21:59

oppressionslayer

6,942
2
7
24

This solved my problem for the most part, after a bit of modifications like below, i got it to work as i needed for i in range(2, len(df)+1): – Dane Nov 24 '19 at 02:52

Python: Create some sort of cumsum between two columns

2 Answers2