Applying Decay Factor to Return Data in Pandas and Saving as New Variable
<p>I have return data for stocks (thousands of them but am only showing 4 here) as below:</p>

<pre><code>In[3]:ret

Out[3]: 
Symbol          AAPL        DE       IBM      MSFT
Date                                              
2016-01-19       NaN       NaN       NaN       NaN
2016-01-20 -0.021780 -0.019701 -0.078557 -0.019177
2016-01-21  0.016271  0.014681  0.021823  0.024440
2016-01-22  0.036128  0.034555  0.009910  0.019085
2016-01-25  0.008539 -0.026477 -0.001068  0.007608
2016-01-26 -0.011491 -0.000907  0.004933 -0.001936
2016-01-27 -0.048231  0.021428 -0.013007 -0.010279

             ...       ...       ...       ...

2016-08-16  0.010455  0.010135 -0.006738 -0.005712
2016-08-17 -0.007966 -0.006432 -0.005290 -0.000698
2016-08-18  0.006277 -0.006603  0.003754  0.000699
2016-08-19 -0.006054  0.034406 -0.005735 -0.001222
2016-08-22 -0.004707  0.091092 -0.002445  0.001049
2016-08-23  0.006305  0.007621  0.006913  0.010304

[152 rows x 4 columns]
</code></pre>

<p>I need to weight the data by applying a 0.97 decay factor where ret(i) = ret(i)*(0.97**i) where ret(0) is my first data-point and is from the most recent day (2016-08-23) and would effectively be unchanged as (i) would be 0</p>

<p>I then need to have the ret variable saved as ret_decay</p>

<p>any assistance would be greatly appreciated.</p>

<p><strong>ADDED INFORMATION DATA and CODE</strong></p>

<p><strong>CloseWeightmini.csv data:</strong></p>

<pre><code>Date    Symbol  ClosingPrice    Weight
8/16/2016   AAPL    109.21  0.0006944
8/17/2016   AAPL    108.34  0.0006944
8/18/2016   AAPL    109.02  0.0006944
8/19/2016   AAPL    108.36  0.0006944
8/22/2016   AAPL    107.85  0.0006944
8/23/2016   AAPL    108.53  0.0006944
8/16/2016   DE      77.74   -0.0007157
8/17/2016   DE      77.24   -0.0007157
8/18/2016   DE      76.73   -0.0007157
8/19/2016   DE      79.37   -0.0007157
8/22/2016   DE      86.6    -0.0007157
8/23/2016   DE      87.26   -0.0007157
8/16/2016   IBM    160.69   -0.0001277
8/17/2016   IBM    159.84   -0.0001277
8/18/2016   IBM    160.4401 -0.0001277
8/19/2016   IBM    159.52   -0.0001277
8/22/2016   IBM    159.13   -0.0001277
8/23/2016   IBM    160.23   -0.0001277
8/16/2016   MSFT    57.27   0.00009
8/17/2016   MSFT    57.23   0.00009
8/18/2016   MSFT    57.27   0.00009
8/19/2016   MSFT    57.2    0.00009
8/22/2016   MSFT    57.26   0.00009
8/23/2016   MSFT    57.85   0.00009
</code></pre>

<p><strong>here is my code:</strong></p>

<pre><code>import numpy as np
import pandas as pd
import pandas.io.data as web
from scipy.stats import norm
import datetime as dt

Days = 1
Value=1e6 # $1,000,000
CI=0.99 # set the confidence interval
decay = 0.97
df=pd.read_csv('CloseWeightsmini.csv')
df['Date'] = pd.to_datetime(df['Date'])
df=df.drop_duplicates(['Date','Symbol'], keep='first')
df2=df.iloc[:,0:3].pivot('Date', 'Symbol', 'ClosingPrice')
df3=df.iloc[:,[1,3]].drop_duplicates().set_index('Symbol')
df4=df.iloc[:,[1,3]].drop_duplicates().reset_index(drop=True)
tickers=df4['Symbol']
numbers=len(tickers)
data=df2

ret=data/data.shift(1)-1 # calculate the simple returns
</code></pre>

<p>The rest of my code works, I just need to have the decay factor (0.97 in this case) applied to each date such that the most recent data has more weight as i outlined in the original post.</p>

<p>Adding column row_index:</p>

<pre><code>In [229]: df['row_index'] = df.apply(lambda x: x.name, axis = 1)

In [230]: df
Out[230]: 
         Date      AAPL        DE       IBM      MSFT  row_index
0  2016-01-19       NaN       NaN       NaN       NaN          0
1  2016-01-20 -0.021780 -0.019701 -0.078557 -0.019177          1
2  2016-01-21  0.016271  0.014681  0.021823  0.024440          2
3  2016-01-22  0.036128  0.034555  0.009910  0.019085          3
4  2016-01-25  0.008539 -0.026477 -0.001068  0.007608          4
5  2016-01-26 -0.011491 -0.000907  0.004933 -0.001936          5
6  2016-01-27 -0.048231  0.021428 -0.013007 -0.010279          6
</code></pre>

<p>Apply decay function to a set of columns:</p>

<pre><code>In [231]: df[df.columns[1:5]].apply(lambda x: df[x.name]*(0.97**df['row_index']))
Out[231]: 
       AAPL        DE       IBM      MSFT
0       NaN       NaN       NaN       NaN
1 -0.021127 -0.019110 -0.076200 -0.018602
2  0.015309  0.013813  0.020533  0.022996
3  0.032973  0.031537  0.009045  0.017418
4  0.007560 -0.023440 -0.000945  0.006735
5 -0.009868 -0.000779  0.004236 -0.001663
6 -0.040175  0.017849 -0.010834 -0.008562
</code></pre>

<p>Probably, ignore the first row?:    </p>

<pre><code>In [232]: df[df.columns[1:5]].apply(lambda x: df[x.name]*(0.97**(df['row_index'] - 1)))
Out[232]: 
       AAPL        DE       IBM      MSFT
0       NaN       NaN       NaN       NaN
1 -0.021780 -0.019701 -0.078557 -0.019177
2  0.015783  0.014241  0.021168  0.023707
3  0.033993  0.032513  0.009324  0.017957
4  0.007793 -0.024165 -0.000975  0.006944
5 -0.010173 -0.000803  0.004367 -0.001714
6 -0.041418  0.018401 -0.011170 -0.008827
</code></pre>

<p>If the index should start from bottom:</p>

<pre><code>In [262]: newdf = df[df.columns[1:5]].apply(lambda x: df[x.name]*(0.97**(len(df) - df['row_index'] - 1)))

In [263]: newdf['Date'] = df['Date']

In [264]: newdf
Out[264]: 
       AAPL        DE       IBM      MSFT        Date
0       NaN       NaN       NaN       NaN  2016-01-19
1 -0.018703 -0.016918 -0.067460 -0.016468  2016-01-20
2  0.014405  0.012997  0.019320  0.021637  2016-01-21
3  0.032973  0.031537  0.009045  0.017418  2016-01-22
4  0.008034 -0.024912 -0.001005  0.007158  2016-01-25
5 -0.011146 -0.000880  0.004785 -0.001878  2016-01-26
6 -0.048231  0.021428 -0.013007 -0.010279  2016-01-27
</code></pre>

python pandas