如何在python中获得数据帧中的函数（def）输出并合并多个数据帧

Question

我有一个代码，用股票价格来计算最大提款。你知道吗

#max drawdown function code...
def max_drawdown(X):
    mdd = 0
    peak = X[0]
    for x in X:
        if x > peak: 
            peak = x
        dd = (x - peak) / peak
        if dd < mdd
            mdd = dd

    return mdd

我使用以下函数来显示缩编

#shows max drawdown
stocks_prices.xs(key='close',axis=1,level='Stock Info').apply(max_drawdown)

万一你想见股票价格主管

Ticker  NOW BBY NOW Return  BBY Return
Stock Info  open    high    low close   volume  open    high    low close   volume      
date                                                
2013-09-30  52.10   52.34   51.170  51.95   1383145 31.9605 32.3721 31.8061 32.1577 3826963 NaN NaN
2013-10-01  51.60   51.89   50.610  51.49   1288635 32.2092 32.7923 32.1834 32.5436 3607267 -0.008855   0.012000
2013-10-02  51.35   52.42   51.215  52.27   1158196 32.2606 32.4750 31.9005 32.2949 2990664 0.015149    -0.007642
2013-10-03  52.39   52.90   51.560  52.15   1512797 32.4236 32.5093 31.6089 32.3206 3413673 -0.002296   0.000796
2013-10-04  53.09   55.46   52.810  54.43   1688824 32.3206 32.9724 31.9862 32.7151 3311713 0.043720    0.012206

输出为

Ticker
NOW   -0.476164
BBY   -0.485378
dtype: float64

由于计算是在函数中进行的，因此如何在数据帧中输入输出。我不能使用下面的代码从drawdown函数的输出创建数据帧

mdd = mdd.to_frame('Maximum Drawdown')
mdd.index.name = 'Ticker'
mdd = mdd.reset_index()

我有多个数据帧，比如

 Ticker    Sharpe
0    NOW  8.061887
1    BBY  7.174034

Ticker    Var
0    NOW  -0.1
1    BBY  0.2

如何将所有这些数据框合并为一个，以便从ticker值开始导出到excel

例如，我希望输出

Ticker    Var   Sharpe Ratio
    0    NOW  -0.1 0.5
    1    BBY  0.2 0.3

两件事都请帮忙，真的很感激

Answer 1

从你的代码开始：

mdd = stocks_prices.xs(key='close',axis=1,level='Stock Info').apply(max_drawdown)

mdd系列看起来像：

Ticker
NOW   -0.008855
BBY   -0.007642
dtype: float64

你可能有不同的号码。你知道吗

不确定您是否希望在stocks_prices中使用它-如果您希望，则如下所示：

最简单的方法是创建一个新列

stocks_prices[ 'NOW mdd' ] = float('NaN')

获取数据帧中的最后一天：

last_day = stocks_prices.index[-1]

对我来说是2013-10-04，但我认为你有更多的行。总之，把它放到NOW mdd列的最后一行：

stocks_prices.loc[ last_day, 'NOW mdd' ] = mdd[ 'NOW' ]

所以我的stocks_prices看起来像：

Ticker        NOW                       BBY                    NOW Return BBY Return   NOW mdd
Stock Info   open high  low  close     open high  low    close
2013-09-30  50.95  NaN  NaN  51.95  31.1577  NaN  NaN  32.1577        NaN        NaN       NaN
2013-10-01  50.49  NaN  NaN  51.49  31.5436  NaN  NaN  32.5436  -0.008855   0.012000       NaN
2013-10-02  51.27  NaN  NaN  52.27  31.2949  NaN  NaN  32.2949   0.015149  -0.007642       NaN
2013-10-03  51.15  NaN  NaN  52.15  31.3206  NaN  NaN  32.3206  -0.002296   0.000796       NaN
2013-10-04  53.43  NaN  NaN  54.43  31.7151  NaN  NaN  32.7151   0.043720   0.012206 -0.008855

合并

将数据帧放在一起通常是在一个公共键上完成的，比如Ticker。你知道吗

因此sharpe数据帧：

     Sharpe Ticker
0  8.061887    NOW
1  7.174034    BBY

以及var数据帧：

  Ticker  Var
0    NOW -0.1
1    BBY  0.2

可以与合并

result = sharpe.merge( var, on='Ticker', suffixes=('','') )

给予

     Sharpe Ticker  Var
0  8.061887    NOW -0.1
1  7.174034    BBY  0.2

如果我们想将mdd合并到其中，我们可以首先将mdd转换成一个数据帧：

result2 = result.merge( mdd.to_frame('mdd'), on='Ticker', suffixes=('','') )

给予

     Sharpe Ticker  Var       mdd
0  8.061887    NOW -0.1 -0.008855
1  7.174034    BBY  0.2 -0.007642

连续最大降深

顺便说一句，你可以改变你的max_drawdown函数来输出一个列表

def max_drawdown2(X):
    mdd = 0
    peak = X[0]
    values = []  # <  NEW LINE
    for x in X:
        if x > peak: 
            peak = x
        dd = (x - peak) / peak
        if dd < mdd:
            mdd = dd
        values.append( mdd )  # <  NEW LINE

    return values   # <  NEW LINE

stocks_prices[ 'NOW mdd' ] = max_drawdown2( stocks_prices.loc[ :, ('NOW','close') ] )

得到

Ticker        NOW                       BBY                    NOW Return BBY Return   NOW mdd
Stock Info   open high  low  close     open high  low    close
2013-09-30  50.95  NaN  NaN  51.95  31.1577  NaN  NaN  32.1577        NaN        NaN  0.000000
2013-10-01  50.49  NaN  NaN  51.49  31.5436  NaN  NaN  32.5436  -0.008855   0.012000 -0.008855
2013-10-02  51.27  NaN  NaN  52.27  31.2949  NaN  NaN  32.2949   0.015149  -0.007642 -0.008855
2013-10-03  51.15  NaN  NaN  52.15  31.3206  NaN  NaN  32.3206  -0.002296   0.000796 -0.008855
2013-10-04  53.43  NaN  NaN  54.43  31.7151  NaN  NaN  32.7151   0.043720   0.012206 -0.008855

注意，.loc[ :, ('NOW','close') ]使用:获取所有日期，然后('NOW','close')通过Ticker然后Stock Info级别获取

可以使用相同的(Ticker, close)语法来添加子列：

stocks_prices[ ('NOW', 'mdd' ) ] = max_drawdown2( stocks_prices.loc[ :, ('NOW','close') ] )