1

I have the following pandas df:

         date  Price_Prev2  Price_Prev1         Ctr   ER
0  2021-02-26       150.62       150.41  Price_Prev2  100.0
1  2021-03-01       150.81       150.60  Price_Prev2    NaN
2  2021-03-02       151.14       150.94  Price_Prev2    NaN
3  2021-03-03       151.12       150.93  Price_Prev2    NaN
4  2021-03-04       151.00       150.79  Price_Prev2    NaN
5  2021-03-05       151.50       151.33  Price_Prev2    NaN
6  2021-03-08       151.14       150.98  Price_Prev2    NaN
7  2021-03-09       151.05       150.88  Price_Prev2    NaN
8  2021-03-10       151.11       150.93  Price_Prev1    NaN
9  2021-03-11       151.27       151.05  Price_Prev1    NaN
10 2021-03-12          NaN       150.99  Price_Prev1    NaN
11 2021-03-15          NaN       151.07  Price_Prev1    NaN

For all rows except 0, I want ER to be calculated referencing the target column, whose name is stored in column 'Ctr'. The formula is: ER = ER(previous row) * Target_column (current row) / Target column (previous row)

So, for example:

  • ER at row 7 = ER(6) * 151.05 / 151.14.

  • ER at row 8 = ER(7) * 150.93 / 150.88

This is the expected output:

    date    Price_Prev2 Price_Prev1 Ctr1    ER_1
0   2021-02-26  150.62  150.41  Price_Prev2 100.00
1   2021-03-01  150.81  150.60  Price_Prev2 100.13
2   2021-03-02  151.14  150.94  Price_Prev2 100.35
3   2021-03-03  151.12  150.93  Price_Prev2 100.33
4   2021-03-04  151.00  150.79  Price_Prev2 100.25
5   2021-03-05  151.50  151.33  Price_Prev2 100.58
6   2021-03-08  151.14  150.98  Price_Prev2 100.35
7   2021-03-09  151.05  150.88  Price_Prev2 100.29
8   2021-03-10  151.11  150.93  Price_Prev1 100.32
9   2021-03-11  151.27  151.05  Price_Prev1 100.40
10  2021-03-12  NaN     150.99  Price_Prev1 100.36
11  2021-03-15  NaN     151.07  Price_Prev1 100.41

ANy idea of how to achieve it? Thanks.

younggotti
  • 762
  • 2
  • 15
  • Why don't you just divide ER by your target column and shift the outcome? After shifting you could simply multiply with your target column and be done – Zephyrus Jul 25 '21 at 14:19
  • How can I refer to a column whose name is stored in another column? – younggotti Jul 25 '21 at 14:23
  • Why row 8 is `ER(7) * 150.93 / 150.88`? Shouldn't it be `ER(7)*150.93/151.05`? – Henry Yik Jul 25 '21 at 14:57
  • @HenryYik It's essentially the value in the same row divided by the value *in the same column* above. I initially thought the same as you, for some reason I read it as "if `Price_Prev1` is the numerator, take the denominator from `Price_Prev2`". – fsimonjetz Jul 25 '21 at 15:03
  • 1
    @HenryYik: fsimonjetz is right (and his solution works great): thanks! – younggotti Jul 25 '21 at 15:18

1 Answers1

1

Due to the iterative nature of the task, I'd say this is one of the rare cases where it's actually legitimate to use df.iterrows() (also see this question).

# Copy ER
df['ER_1'] = df.ER

rows = df.iterrows()
next(rows) # skip first line

for i, row in rows:
    df.loc[i, 'ER_1'] = df.loc[i-1, 'ER_1'] * df.loc[i, row.Ctr] / df.loc[i-1, row.Ctr]

          date  Price_Prev2  Price_Prev1          Ctr     ER        ER_1
0   2021-02-26       150.62       150.41  Price_Prev2  100.0  100.000000
1   2021-03-01       150.81       150.60  Price_Prev2    NaN  100.126145
2   2021-03-02       151.14       150.94  Price_Prev2    NaN  100.345240
3   2021-03-03       151.12       150.93  Price_Prev2    NaN  100.331961
4   2021-03-04       151.00       150.79  Price_Prev2    NaN  100.252291
5   2021-03-05       151.50       151.33  Price_Prev2    NaN  100.584252
6   2021-03-08       151.14       150.98  Price_Prev2    NaN  100.345240
7   2021-03-09       151.05       150.88  Price_Prev2    NaN  100.285487
8   2021-03-10       151.11       150.93  Price_Prev1    NaN  100.318720
9   2021-03-11       151.27       151.05  Price_Prev1    NaN  100.398481
10  2021-03-12          NaN       150.99  Price_Prev1    NaN  100.358600
11  2021-03-15          NaN       151.07  Price_Prev1    NaN  100.411774
fsimonjetz
  • 5,644
  • 3
  • 5
  • 21