python - pandas 中的数据框数学

Question

完全重写原始问题

我从 csv 文件“CloseWeight4.csv”读取原始数据

df=pd.read_csv('CloseWeights4.csv')
Date      Symbol    ClosingPrice    Weight
3/1/2010    OGDC    116.51         0.1820219
3/2/2010    OGDC    117.32         0.1820219
3/3/2010    OGDC    116.4          0.1820219
3/4/2010    OGDC    116.58         0.1820219
3/5/2010    OGDC    117.61         0.1820219
3/1/2010    WTI      78.7          0.5348142
3/2/2010    WTI      79.68         0.5348142
3/3/2010    WTI      80.87         0.5348142
3/4/2010    WTI      80.21         0.5348142
3/5/2010    WTI      81.5          0.5348142
3/1/2010    FX       85.07         0.1312427
3/2/2010    FX       85.1077       0.1312427
3/3/2010    FX       85.049        0.1312427
3/4/2010    FX       84.9339       0.1312427
3/5/2010    FX       84.8          0.1312427
3/1/2010    PIB      98.1596499    0.1519211
3/2/2010    PIB      98.1596499    0.1519211
3/3/2010    PIB      98.1764222    0.1519211
3/4/2010    PIB      98.1770656    0.1519211
3/5/2010    PIB      98.1609364    0.1519211

从中我生成数据帧 df2

df2=df.iloc[:,0:3].pivot('Date', 'Symbol', 'ClosingPrice')

df2
Out[10]: 
Symbol           FX    OGDC        PIB    WTI
Date                                         
2010-03-01  85.0700  116.51  98.159650  78.70
2010-03-02  85.1077  117.32  98.159650  79.68
2010-03-03  85.0490  116.40  98.176422  80.87
2010-03-04  84.9339  116.58  98.177066  80.21
2010-03-05  84.8000  117.61  98.160936  81.50

据此我计算返回:

ret=np.log(df2/df2.shift(1))

In [12] ret

Out[12]: 
Symbol            FX      OGDC       PIB       WTI
Date                                              
2010-03-01       NaN       NaN       NaN       NaN
2010-03-02  0.000443  0.006928  0.000000  0.012375
2010-03-03 -0.000690 -0.007873  0.000171  0.014824
2010-03-04 -0.001354  0.001545  0.000007 -0.008195
2010-03-05 -0.001578  0.008796 -0.000164  0.015955

我有来自 df 的每种证券的权重

df3=df.iloc[:,[1,3]].drop_duplicates().reset_index(drop=True)

df3
Out[14]: 
          Weight
Symbol          
OGDC    0.182022
WTI     0.534814
FX      0.131243
PIB     0.151921

我试图获得每天的以下加权返回结果，但不知道如何在 pandas 中进行数学计算:

Date        Portfolio_weighted_returns
2010-03-02        0.008174751
2010-03-03        0.006061657
2010-03-04       -0.005002414
2010-03-05        0.009058151
where the Portfolio_weighted_returns of 2010-03-02 is calculated as follows:
0.006928*0.182022+.012375*0.534814+0.000443*0.131243+0*0.151921 = 0.007937512315

然后我需要将这些结果乘以衰减因子，其中衰减因子定义为 decFac =decay^(t)。使用 Decay = 0.5 得出 decFac 值为:

Date        decFac
2010-03-02  0.0625
2010-03-03  0.125
2010-03-04  0.25
2010-03-05  0.5

然后，我需要将每天的 Portfolio_weighted_returns 平方和乘以相应的 decFac 求 SQRT，如下所示:

SQRT(Sum(0.008174751^2*.0625+0.006061657^2*.125+(-0.005002414^2)*.25+.009058151^2*.5)) = 0.007487

Answer 1

IIUC 你可以这样做:

In [267]: port_ret = ret.dot(df3)

In [268]: port_ret
Out[268]:
              Weight
Date
2010-03-01       NaN
2010-03-02  0.007938
2010-03-03  0.006431
2010-03-04 -0.004278
2010-03-05  0.009902

In [269]: decay = 0.5

In [270]: decay_df = pd.DataFrame({'decFac':decay**np.arange(len(ret), 0, -1)}, index=ret.index)

In [271]: decay_df
Out[271]:
             decFac
Date
2010-03-01  0.03125
2010-03-02  0.06250
2010-03-03  0.12500
2010-03-04  0.25000
2010-03-05  0.50000

In [272]: (port_ret.Weight**2 * decay_df.decFac).sum() ** 0.5
Out[272]: 0.007918790111274962

port_ret.Weight**2 * Decay_df.decFac

In [277]: port_ret.Weight**2 * decay_df.decFac
Out[277]:
Date
2010-03-01         NaN
2010-03-02    0.000004
2010-03-03    0.000005
2010-03-04    0.000005
2010-03-05    0.000049
dtype: float64