Creating a new column by multiplying the value of row above with the value of the other column

Question

I am trying to create a new columns of percentages which is a product of value in a row above and the value in the same row of another column. I have tried using shift and loc with no luck.

I have tried using:

df.at[0,'new_col'] = df.at[0,'other_col']

This first part works well and then

df['new_col'] = df['new_col'].shift(1)*df['other_col']

this however does not work.

My data example is as follows:

time	val	adj_val
0	1	1
1	0.5	0.5
2	0.6	0.3
3	0.7	0.21
4	0.9	0.189

I have been trying to work around this for a well such as using df.loc but with no luck.

The values in adj_col are calculated as follows:

1 = 1 as per the first line of code - this works then

0.5 = 1 * 0.5 - the 1 is the first value in adj_val and 0.5 is in the val colum

0.21 = 0.3*0.7 
0.189 = 0.9*0.21 ```

Answer 1

You cannot achieve the desired result with shift. shift will give you access to the previous row before any computation is performed, but you need the new value to become the next reference.

What you want is a cumulative product, there is already a method for that in pandas: cumprod:

df['adj_val'] = df['val'].cumprod()

It is also achievable with numpy.multiply.accumulate:

import numpy as np

df['adj_val'] = np.multiply.accumulate(df['val'])

Output:

   time  val  adj_val
0     0  1.0    1.000
1     1  0.5    0.500
2     2  0.6    0.300
3     3  0.7    0.210
4     4  0.9    0.189