Is there a way in Pandas to use previous row value in dataframe.apply when previous value is also calculated in the apply?
First, create the derived value:
df.loc[0, 'C'] = df.loc[0, 'D']
Then iterate through the remaining rows and fill the calculated values:
for i in range(1, len(df)):
df.loc[i, 'C'] = df.loc[i-1, 'C'] * df.loc[i, 'A'] + df.loc[i, 'B']
Index_Date A B C D
0 2015-01-31 10 10 10 10
1 2015-02-01 2 3 23 22
2 2015-02-02 10 60 290 280
Given a column of numbers:
lst = []
cols = ['A']
for a in range(100, 105):
lst.append([a])
df = pd.DataFrame(lst, columns=cols, index=range(5))
df
A
0 100
1 101
2 102
3 103
4 104
You can reference the previous row with shift:
df['Change'] = df.A - df.A.shift(1)
df
A Change
0 100 NaN
1 101 1.0
2 102 1.0
3 103 1.0
4 104 1.0
numba
For recursive calculations which are not vectorisable, numba
, which uses JIT-compilation and works with lower level objects, often yields large performance improvements. You need only define a regular for
loop and use the decorator @njit
or (for older versions) @jit(nopython=True)
:
For a reasonable size dataframe, this gives a ~30x performance improvement versus a regular for
loop:
from numba import jit
@jit(nopython=True)
def calculator_nb(a, b, d):
res = np.empty(d.shape)
res[0] = d[0]
for i in range(1, res.shape[0]):
res[i] = res[i-1] * a[i] + b[i]
return res
df['C'] = calculator_nb(*df[list('ABD')].values.T)
n = 10**5
df = pd.concat([df]*n, ignore_index=True)
# benchmarking on Python 3.6.0, Pandas 0.19.2, NumPy 1.11.3, Numba 0.30.1
# calculator() is same as calculator_nb() but without @jit decorator
%timeit calculator_nb(*df[list('ABD')].values.T) # 14.1 ms per loop
%timeit calculator(*df[list('ABD')].values.T) # 444 ms per loop