Calculating Drawdown in Pandas

Question

I have the following DataFrame:

              Profit       Cumulative
Date                            
1/6/2005    248.8500      248.85
1/12/2005    48.3500      297.20
1/13/2005    29.2900      326.49
1/13/2005  -500.0000     -173.51
1/13/2005  -250.9500     -424.46
1/14/2005   126.6600     -297.80
1/16/2005    58.7400     -239.06
1/19/2005    68.3500     -170.71
1/21/2005   140.0000      -30.71
1/23/2005   200.0000      169.29
1/26/2005  -250.6800      -81.39
1/27/2005   162.5000       81.11
1/27/2005   135.5100      216.62
1/27/2005  -650.0000     -433.38
1/28/2005    96.8800     -336.50
1/28/2005 -1000.0000    -1336.50
1/31/2005   140.0000    -1196.50
2/1/2005    140.0000    -1056.50

The first column are the dollar profits of my portfolio. I have calculated the second column with the following formula:

df['Cumulative'] = df.Profit.cumsum().round(2)

So, is there a formula where I can calculate the dollar (not %) drawdowns of my portfolio? The column should look like this:

Drawdown

 0.00
 0.00
 0.00
-500.00
-750.95
-624.29
-565.55
-497.20
-357.20
-157.20
-407.88
-245.38
-109.87
-759.87
-662.99
-1,662.99
-1,522.99
-1,382.99
-1,382.99

Answer 1

df['Cumulative'] = df.Profit.cumsum().round(2)
df['HighValue'] = df['Cumulative'].cummax()

df['Drawdown'] = df['Cumulative'] - df['HighValue']

That is the simplest solution I can found.

Answer 2

From what I remember, a drawdown is the amount by which your portfolio profit is less than the high. (In the future, you should explicitly define your desired output and don't assume people know what you are asking.)

There may be better ways, but you can calculate this in pandas using a itertuples():

import pandas as pd
from StringIO import StringIO

# read the data
df = pd.DataFrame.from_csv(StringIO("""Date               Profit       Cumulative
1/6/2005    248.8500      248.85
1/12/2005    48.3500      297.20
1/13/2005    29.2900      326.49
1/13/2005  -500.0000     -173.51
1/13/2005  -250.9500     -424.46
1/14/2005   126.6600     -297.80
1/16/2005    58.7400     -239.06
1/19/2005    68.3500     -170.71
1/21/2005   140.0000      -30.71
1/23/2005   200.0000      169.29
1/26/2005  -250.6800      -81.39
1/27/2005   162.5000       81.11
1/27/2005   135.5100      216.62
1/27/2005  -650.0000     -433.38
1/28/2005    96.8800     -336.50
1/28/2005 -1000.0000    -1336.50
1/31/2005   140.0000    -1196.50
2/1/2005    140.0000    -1056.50"""), sep="\s+").reset_index()

# calculate drawdown
prev_high = 0
for i, date, profit, cumulative in df.itertuples():
    prev_high = max(prev_high, cumulative)
    dd = cumulative - prev_high
    df.loc[i, 'Drawdown'] = dd if dd < 0 else 0

The resultant dataframe:

>>> print(df)
         Date   Profit  Cumulative  Drawdown
0  2005-01-06   248.85      248.85      0.00
1  2005-01-12    48.35      297.20      0.00
2  2005-01-13    29.29      326.49      0.00
3  2005-01-13  -500.00     -173.51   -500.00
4  2005-01-13  -250.95     -424.46   -750.95
5  2005-01-14   126.66     -297.80   -624.29
6  2005-01-16    58.74     -239.06   -565.55
7  2005-01-19    68.35     -170.71   -497.20
8  2005-01-21   140.00      -30.71   -357.20
9  2005-01-23   200.00      169.29   -157.20
10 2005-01-26  -250.68      -81.39   -407.88
11 2005-01-27   162.50       81.11   -245.38
12 2005-01-27   135.51      216.62   -109.87
13 2005-01-27  -650.00     -433.38   -759.87
14 2005-01-28    96.88     -336.50   -662.99
15 2005-01-28 -1000.00    -1336.50  -1662.99
16 2005-01-31   140.00    -1196.50  -1522.99
17 2005-02-01   140.00    -1056.50  -1382.99

---

Investopedia definition of drawdown.