用平均成本法计算成本

Question

我有一个pandas数据框（我按“Ticker”升序，然后按“Date”和“Type”升序排列），它表示与“Ticker”列中的股票相关的交易：

            Type Ticker  Qty  Price  Amount  TotalQty
Date                                                 
2020-03-01   Buy    AAA   40    1.0    40.0        40
2020-03-04   Buy    AAA   50    1.5    75.0        90
2020-03-08  Sell    AAA   60    1.2    72.0        30
2020-03-10   Buy    AAA   20    2.0    40.0        50
2020-03-15  Sell    AAA   50    2.0   100.0         0
2020-03-18   Buy    AAA   50    1.5    75.0        50
2020-03-01   Buy    BBB   50    2.0   100.0        50
2020-03-04   Buy    BBB   50    1.5    75.0       100
2020-03-09  Sell    BBB   50    1.0    50.0        50
2020-03-15   Buy    BBB   50    3.0   150.0       100
2020-03-01   Buy    CCC   10    3.0    30.0        10
2020-03-10   Buy    CCC   10    2.0    20.0        20
2020-03-11  Sell    CCC   20    2.2    44.0         0
2020-03-12   Buy    DDD   90    2.0   180.0        90

在“TotalQty”列中，我将“Qty”列的累计和放入其中（当“Type”列等于Sell时，考虑“Qty”的负号）

现在，我想为使用平均成本基础方法计算的平均成本添加另一列

例如，考虑到股票代码“AAA”，在第一次购买之后，显然我的平均成本为1.0。然后，在第二次“购买”后，我的平均成本为（40.0+75.0）/90，即“金额”（40.0+75.0）除以“总数量”（90）的累积总和，即1.278。然后，在第三排，我有一个60只股票的“卖出”，所以“总数量”从90变为30，但平均成本不变。最后，在第四行，我有一个20件的“购买”，新的平均成本可以用“总数量”除以当前“金额”和以前的平均成本之和乘以以前的总数量，即（40+1.278*30）/50或1.567

所需输出应为：

            Type Ticker  Qty  Price  Amount  TotalQty  AverageCost
Date                                                              
2020-03-01   Buy    AAA   40    1.0    40.0        40        1.000
2020-03-04   Buy    AAA   50    1.5    75.0        90        1.278
2020-03-08  Sell    AAA   60    1.2    72.0        30        1.278
2020-03-10   Buy    AAA   20    2.0    40.0        50        1.567
2020-03-15  Sell    AAA   50    2.0   100.0         0        1.567
2020-03-18   Buy    AAA   50    1.5    75.0        50        1.500
2020-03-01   Buy    BBB   50    2.0   100.0        50        2.000
2020-03-04   Buy    BBB   50    1.5    75.0       100        1.750
2020-03-09  Sell    BBB   50    1.0    50.0        50        1.750
2020-03-15   Buy    BBB   50    3.0   150.0       100        2.375
2020-03-01   Buy    CCC   10    3.0    30.0        10        3.000
2020-03-10   Buy    CCC   10    2.0    20.0        20        2.500
2020-03-11  Sell    CCC   20    2.2    44.0         0        2.500
2020-03-12   Buy    DDD   90    2.0   180.0        90        2.000

我试过使用这个代码

df['AverageCost']=df.apply(lambda x: x.Amount if x.Type=='Buy' else np.NaN,axis=1)
df['AverageCost']=df.groupby('Ticker')['AverageCost'].cumsum().div(df['TotalQty'])
df.fillna(method='ffill',inplace=True)

但这显然给出了错误的结果。我无法使用df.apply()获得正确的结果，因为在“卖出”（这确实不会改变平均成本）之后，对于新的“买入”，我需要访问以前的平均成本来计算新的成本

我应该使用for循环吗？如何使用

谢谢你的支持

Answer 1

1. 您可以筛选Buy并获取'CumAmountBuy'和'CumQtyBuy'。然后，您可以将其合并回原始dtaframe和ffill()。这允许我们获得不包括带有Sell的行的累积信息
2. 创建一个系列s，用于计算不直接位于同一组中sell之后的Buy行的总数。我们将有条件地使用它来计算np.select条件计算中的一些行
3. 然而，还有两个条件：a。是Buy但直接位于Sell之后的行以及Sell行。对于Buy之后的Sell行，您可以使用`shift（）~执行注释中提到的计算，以作为前一行计算的一部分
4. 如果Sell，那么它将返回NaN，但我们使用.ffill()来填充前面的行

---

df1 = (df.copy()[df['Type'] == 'Buy']
       .assign(CumAmountBuy=df.groupby('Ticker')['Amount'].cumsum())
       .assign(CumQtyBuy=df.groupby('Ticker')['Qty'].cumsum()))
df2 = pd.merge(df,df1,how='left',
                on=['Date','Type', 'Ticker', 'Qty', 'Price', 
                    'Amount', 'TotalQty']).ffill()
s = df2['CumAmountBuy'] / df2['CumQtyBuy']
df2['AverageCost'] = np.select([((df2['Type'] == 'Buy') & (df2['Type'].shift() == 'Sell')),
                         (df2['Type'] == 'Sell')],
                       [((df2['Qty'] * df2['Price'] + df2['TotalQty'].shift() * s.shift()) / df2['TotalQty']),
                        np.nan],
                       s)
df2['AverageCost'] = round(df2['AverageCost'],3).ffill()
df2 = df2.drop(['CumQtyBuy', 'CumAmountBuy'], axis=1)
df2
Out[1]: 
          Date  Type Ticker  Qty  Price  Amount  TotalQty  AverageCost
0   2020-03-01   Buy    AAA   40    1.0    40.0        40        1.000
1   2020-03-04   Buy    AAA   50    1.5    75.0        90        1.278
2   2020-03-08  Sell    AAA   60    1.2    72.0        30        1.278
3   2020-03-10   Buy    AAA   20    2.0    40.0        50        1.567
4   2020-03-15  Sell    AAA   50    2.0   100.0         0        1.567
5   2020-03-18   Buy    AAA   50    1.5    75.0        50        1.500
6   2020-03-01   Buy    BBB   50    2.0   100.0        50        2.000
7   2020-03-04   Buy    BBB   50    1.5    75.0       100        1.750
8   2020-03-09  Sell    BBB   50    1.0    50.0        50        1.750
9   2020-03-15   Buy    BBB   50    3.0   150.0       100        2.375
10  2020-03-01   Buy    CCC   10    3.0    30.0        10        3.000
11  2020-03-10   Buy    CCC   10    2.0    20.0        20        2.500
12  2020-03-11  Sell    CCC   20    2.2    44.0         0        2.500
13  2020-03-12   Buy    DDD   90    2.0   180.0        90        2.000