CODEWITHSUNDEEP
pythonpython-3.xpandas
Michael
Michael

Reputation: 16142

Calculate Daily Returns with Pandas DataFrame

Here is my Pandas data frame:

prices = pandas.DataFrame([1035.23, 1032.47, 1011.78, 1010.59, 1016.03, 1007.95, 
              1022.75, 1021.52, 1026.11, 1027.04, 1030.58, 1030.42,
              1036.24, 1015.00, 1015.20])

Here is my daily_return function:

def daily_return(prices):
    return prices[:-1] / prices[1:] - 1

Here is output that comes from this function:

0    NaN
1      0
2      0
3      0
4      0
5      0
6      0
7      0
8      0
9      0
10     0
11     0
12     0
13     0
14   NaN

Why am I having this output?

Upvotes: 29

Views: 103966

Answers (4)

TanuAD
TanuAD

Reputation: 776

I like all the above methods. However, we can do it this way as well:

daily_returns = (prices/prices.shift(1)) -1
daily_returns.iloc[0,:] = 0

*prices is pandas dataframe and daily_returns will be Series object.

Upvotes: 4

SimZhou
SimZhou

Reputation: 555

Just a bit of complement to @YaOzl 's answer, and in case if someone would read this. If your return data is a panel spreadsheet with several stocks:

>>> prices = pandas.DataFrame(
{"StkCode":["StockA","StockA","StockA","StockA","StockA","StockB","StockB","StockB","StockB","StockB","StockC","StockC","StockC","StockC","StockC",], 
"Price":[1035.23, 1032.47, 1011.78, 1010.59, 1016.03, 1007.95, 1022.75, 1021.52, 1026.11, 1027.04, 1030.58, 1030.42, 1036.24, 1015.00, 1015.20]}
)

Which gives you:

      Price StkCode
0   1035.23  StockA
1   1032.47  StockA
2   1011.78  StockA
3   1010.59  StockA
4   1016.03  StockA
5   1007.95  StockB
6   1022.75  StockB
7   1021.52  StockB
8   1026.11  StockB
9   1027.04  StockB
10  1030.58  StockC
11  1030.42  StockC
12  1036.24  StockC
13  1015.00  StockC
14  1015.20  StockC

Then you could simply jointly use .pct_change(k) with .groupby(StkCode). And it is thound times faster than using an iterator...(I tried on my dataset, successfully shrink the process time from 10 hrs to 20 seconds!!)

>>> prices["Return"] = prices.groupby("StkCode")["Price"].pct_change(1)

Gives you:

      Price StkCode    Return
0   1035.23  StockA       NaN
1   1032.47  StockA -0.002666
2   1011.78  StockA -0.020039
3   1010.59  StockA -0.001176
4   1016.03  StockA  0.005383
5   1007.95  StockB       NaN
6   1022.75  StockB  0.014683
7   1021.52  StockB -0.001203
8   1026.11  StockB  0.004493
9   1027.04  StockB  0.000906
10  1030.58  StockC       NaN
11  1030.42  StockC -0.000155
12  1036.24  StockC  0.005648
13  1015.00  StockC -0.020497
14  1015.20  StockC  0.000197

Upvotes: 4

YaOzI
YaOzI

Reputation: 17478

Why not use the very convenient pct_change method provided by pandas by default:

import pandas as pd

prices = pandas.DataFrame([1035.23, 1032.47, 1011.78, 1010.59, 1016.03, 1007.95, 
          1022.75, 1021.52, 1026.11, 1027.04, 1030.58, 1030.42,
          1036.24, 1015.00, 1015.20])

daily_return = prices.pct_change(1) # 1 for ONE DAY lookback
monthly_return = prices.pct_change(21) # 21 for ONE MONTH lookback
annual_return = prices.pct_change(252) # 252 for ONE YEAR lookback

Original prices:

print(prices)
          0                                                                    
0   1035.23                                                                    
1   1032.47                                                                    
2   1011.78                                                                    
3   1010.59                                                                    
4   1016.03                                                                    
5   1007.95                                                                    
6   1022.75                                                                    
7   1021.52                                                                    
8   1026.11                                                                    
9   1027.04                                                                    
10  1030.58                                                                    
11  1030.42                                                                    
12  1036.24                                                                    
13  1015.00                                                                    
14  1015.20

Daily Return as prices.pct_change(1): 

print(prices.pct_change(1))
           0                                                                   
0        NaN                                                                   
1  -0.002666                                                                   
2  -0.020039                                                                   
3  -0.001176                                                                   
4   0.005383                                                                   
5  -0.007953                                                                   
6   0.014683                                                                   
7  -0.001203                                                                   
8   0.004493                                                                   
9   0.000906                                                                   
10  0.003447                                                                   
11 -0.000155                                                                   
12  0.005648                                                                   
13 -0.020497                                                                   
14  0.000197

Upvotes: 86

HYRY
HYRY

Reputation: 97271

Because operations will do alignment on index, you can convert one of the DataFrames to array:

prices[:-1].values / prices[1:] - 1

or

prices[:-1] / prices[1:].values - 1

depends on what the index of the result you want.

or use shift() method:

prices.shift(1) / prices - 1

and:

prices / prices.shift(1) - 1

Upvotes: 31

Related Questions