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>Try This: </p>

<pre><code>df     = df.set_index("Date")
retFac = np.fromfunction(lambda i,j : .97**(i), df.values.shape)[::-1]
df*retFac
df['ret_decay'] = retFac[:,0]
df

                AAPL        DE       IBM      MSFT  ret_decay
Date                                                         
2016-01-19       NaN       NaN       NaN       NaN   0.693842
2016-01-20 -0.021780 -0.019701 -0.078557 -0.019177   0.715301
2016-01-21  0.016271  0.014681  0.021823  0.024440   0.737424
2016-01-22  0.036128  0.034555  0.009910  0.019085   0.760231
2016-01-25  0.008539 -0.026477 -0.001068  0.007608   0.783743
2016-01-26 -0.011491 -0.000907  0.004933 -0.001936   0.807983
2016-01-27 -0.048231  0.021428 -0.013007 -0.010279   0.832972
2016-08-16  0.010455  0.010135 -0.006738 -0.005712   0.858734
2016-08-17 -0.007966 -0.006432 -0.005290 -0.000698   0.885293
2016-08-18  0.006277 -0.006603  0.003754  0.000699   0.912673
2016-08-19 -0.006054  0.034406 -0.005735 -0.001222   0.940900
2016-08-22 -0.004707  0.091092 -0.002445  0.001049   0.970000
2016-08-23  0.006305  0.007621  0.006913  0.010304   1.000000
</code></pre>

<p>Using the newly added code: and </p>

<pre><code>retFac = np.fromfunction(lambda i,j : .97**(i), ret.values.shape)[::-1]
ret*retFac
ret['ret_decay'] = retFac[:,0]
ret

    Symbol          AAPL        DE       IBM      MSFT  ret_decay
Date                                                         
2016-08-16       NaN       NaN       NaN       NaN   0.858734
2016-08-17 -0.007966 -0.006432 -0.005290 -0.000698   0.885293
2016-08-18  0.006277 -0.006603  0.003754  0.000699   0.912673
2016-08-19 -0.006054  0.034406 -0.005735 -0.001222   0.940900
2016-08-22 -0.004707  0.091092 -0.002445  0.001049   0.970000
2016-08-23  0.006305  0.007621  0.006913  0.010304   1.000000
</code></pre>

python pandas