Kelaref Kelaref - 1 year ago 181
Python Question

Pandas: Cumulative return function

I have a dataframe such as the following:

Index Return
2008-11-21 0.153419
2008-11-24 0.037421
2008-11-25 0.077500

What's the best way to calculate a cumulative return across all columns on the last row?

Following is the intended result:

Index Return
2008-11-21 0.153419
2008-11-24 0.037421
2008-11-25 0.077500
Cumulative 0.289316

Where cumulative return calculated as follows:

Cumulative=(1+return1)(1+return2)(1+return3) -1

Whats the best way to perform this in pandas?

Answer Source

Another solution:

df.ix["Cumulative"] = (df['Return']+1).prod() - 1

This will add 1 to the df['Return'] column, multiply all the rows together, and then subtract one from the result. This will result in a simple float value. The result will then be placed at the index "Cumulative". Since that index doesn't exist yet, it will be appended to the end of the DataFrame:

2008-11-21   0.153419
2008-11-25   0.077500
2008-11-24   0.037421
Cummulative  0.289316

If you want to apply this across multiple columns:

df.ix['Cummulative'] = df.apply(lambda x: (x+1).prod()-1)

This would output the following (I made a second column called "Return2" that is a copy of "Return"):

               Return   Return2
2008-11-21   0.153419  0.153419
2008-11-25   0.077500  0.077500
2008-11-24   0.037421  0.037421
Cummulative  0.289316  0.289316
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download