Btukfyl

I am trying to calculate closing balance

Input dataframe:

    open   inOut    close

0   3      100      0

1   0      300      0

2   0      200      0

3   0      230      0

4   0      150      0

Output DataFrame

    open    inOut   close

0   3       100     103

1   103     300     403

2   403     200     603

3   603     230     833

4   833     150     983  

I am able to achieve this using crude for-loop and to optimize it i have used iterrow()

For-Loop

%%timeit

for i in range(len(df.index)):

    if i>0:

        df.iloc[i]['open'] = df.iloc[i-1]['close']

        df.iloc[i]['close'] = df.iloc[i]['open']+df.iloc[i]['inOut']

    else:

        df.iloc[i]['close'] = df.iloc[i]['open']+df.iloc[i]['inOut'] 



1.64 ms ± 51.1 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

iterrows

%%timeit

for index,row in dfOg.iterrows():

    if index>0:

        row['open'] = dfOg.iloc[index-1]['close']

        row['close'] = row['open']+row['inOut']

    else:

        row['close'] = row['open']+row['inOut']



627 µs ± 28.5 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

performance optimized from 1.64ms -> 627µs

As per this blog, I am struggling to figure out how to write the above logic using apply() and vectorization.

for vectorization, I tried shifting the columns but not able to achieve the desired output.

Edit: I changed things around to match the edits OP made to the question

You can do what you want in a vectorized way without any loops like this:

import pandas as pd



d = {'open': [3] + [0]*4, 'inOut': [100, 300, 200, 230, 150], 'close': [0]*5}

df = pd.DataFrame(d)



df['close'].values[:] = df['open'].values[0] + df['inOut'].values.cumsum()

df['open'].values[1:] = df['close'].values[:-1]

Timing with %%timeit:

529 µs ± 5.39 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

Output:

   close  inOut  open

0    103    100     3

1    403    300   103

2    603    200   403

3    833    230   603

4    983    150   833

So vectorizing your code this way is indeed somewhat faster. In fact, it's probably about as fast as possible. You can see this by timing just the dataframe creation code:

%%timeit

d = {'open': [3] + [0]*4, 'inOut': [100, 300, 200, 230, 150], 'close': [0]*5}

df = pd.DataFrame(d)

Result:

367 µs ± 5.67 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

Subtracting out the time it takes to create the dataframe, the vectorized version of filling in your dataframe only takes about ~160 µs.

You can use np.where

%%timeit

df['open'] = np.where(df.index==0, df['open'], df['inOut'].shift())

df['close'] = df['open'] + df['inOut']

# 1.07 ms ± 16.2 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

Output:

    open    inOut   close

0   3.0     100     103.0

1   100.0   300     300.0

2   300.0   200     200.0

3   200.0   230     230.0

4   230.0   150     150.0