时间戳

首页 > 解决方案 > 如何在python的数据框中获取函数（def）输出并合并多个数据框

python - 如何在python的数据框中获取函数（def）输出并合并多个数据框

问题描述

我有这段代码可以使用股票价格计算最大回撤。 

#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

我如何将所有这些数据框合并为一个，以便可以从股票代码然后值开始导出到 excel

例如，我希望输出为

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

请在这两件事上提供帮助，非常感谢

标签： python

解决方案

所以从你的代码开始：

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')

并获取 DataFrame 中的最后一天：

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

合并

将 DataFrame 放在一起通常是在一个公共键上完成的，比如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先变成一个DataFrame：

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') ] )

推荐阅读