python - 如何根据多种条件在pandas中创建新列并通过分组来计算以前的成本和累计成本？

Question

我对 Pandas 比较陌生，在这里搜索解决方案后，我找不到任何可以很好地解决我的问题的方法。

我有一个像这样的 DataFrame (df_transactions):

 col_type      col_ticker      col_transacted_value
 'buy'         'TSLA'          '100'                           
 'buy'         'TSLA'          '100'                           
 'buy'         'TSLA'          '100'                           
 'sell'        'TSLA'          '300'                              
 ...           ...             ...

我想创建两个新列，其中包含以前的累计成本和累计成本。

获取我之前使用过的成本:

df_transactions[col_prev_cost] = df_transactions.groupby(col_ticker)[col_transacted_value].shift().fillna(0)

但后来意识到这只会找到该股票代码的先前出现并用它的 transacted_costs 填充一个新列。

我的想法是按代码分组，并根据 col_type 的值填充新列“col_prev_costs”，然后使用该列创建新列“cml_cost”。或者，如果您看到更简单的方法 - 请分享!

我希望它像这样运行:

 col_type      col_ticker      col_transacted_value        New_col_prev_cost       New_col_cml_cost
 'buy'         'TSLA'          '100'                       '0'                     '100'
 'buy'         'TSLA'          '100'                       '100'                   '200'
 'buy'         'TSLA'          '100'                       '200'                   '300'
 'sell'        'TSLA'          '300'                       '300'                   '0'     
 ...           ...             ...                         ...                      ...

我愿意接受所有建议!

提前致谢!/雅各布

---

更新:我的第一个问题有些缺陷，所以我会尝试再次解释。

我现在发现我错过了对卖出类型交易的正确处理，因为这甚至取决于之前持有的代码。

假设我有给定的数据框:

import pandas as pd
import numpy as np

# Create the dataframe:
data = {'date'              : ['2022-01-01', '2022-01-02', '2022-01-02', '2022-01-03', '2022-01-03', '2022-01-03', '2022-01-03', '2022-01-03', '2022-01-04'],
        'ticker'            : ['TSLA','TSLA', 'TSLA', 'TSLA', 'TSLA', 'TSLA', 'TSLA', 'AAPL', 'AAPL'],
        'type'              : ['buy', 'buy', 'split', 'sell', 'buy', 'sell', 'buy', 'buy', 'buy'],
        'units'             : [2, 2, 3, 2, 2, 2, 2, 2.5, 4],
        'price'             : [50.5, 50.5, 0, 52, 35, 52, 35, 50.5, 50.5],
        'fees'              : [4, 4, 0, 2.5, 3, 2.5, 3, 1.5, 2],
}

df = pd.DataFrame(data)
df
Out[2]: 
         date ticker   type  units  price  fees
0  2022-01-01   TSLA    buy    2.0   50.5   4.0
1  2022-01-02   TSLA    buy    2.0   50.5   4.0
2  2022-01-02   TSLA  split    3.0    0.0   0.0
3  2022-01-03   TSLA   sell    2.0   52.0   2.5
4  2022-01-03   TSLA    buy    2.0   35.0   3.0
5  2022-01-03   TSLA   sell    2.0   52.0   2.5
6  2022-01-03   TSLA    buy    2.0   35.0   3.0
7  2022-01-03   AAPL    buy    2.5   50.5   1.5
8  2022-01-04   AAPL    buy    4.0   50.5   2.0

而我计算以下列:

# Create the column transaction value

def calc_transaction_value(type_transaction, price_unit, transacted_units, fees):
    """
    Calculate the transaction value from three columns in a dataframe depending on the value of type.
    """
    if type_transaction == 'buy':
        return price_unit * transacted_units + fees # i.e. the transacted cost
    if type_transaction == 'sell':
        return price_unit * transacted_units - fees # i.e. the gross income of capital
    else:
        return np.nan # If other return filler return NA (i.e. not available)

df['transacted_value'] = df.apply(lambda x: calc_transaction_value(x['type'], x['price'], x['units'], x['fees']), axis=1).fillna(0)

df
Out[3]: 
         date ticker   type  units  price  fees  transacted_value
0  2022-01-01   TSLA    buy    2.0   50.5   4.0            105.00
1  2022-01-02   TSLA    buy    2.0   50.5   4.0            105.00
2  2022-01-02   TSLA  split    3.0    0.0   0.0              0.00
3  2022-01-03   TSLA   sell    2.0   52.0   2.5            101.50
4  2022-01-03   TSLA    buy    2.0   35.0   3.0             73.00
5  2022-01-03   TSLA   sell    2.0   52.0   2.5            101.50
6  2022-01-03   TSLA    buy    2.0   35.0   3.0             73.00
7  2022-01-03   AAPL    buy    2.5   50.5   1.5            127.75
8  2022-01-04   AAPL    buy    4.0   50.5   2.0            204.00

# create the flow of units, depends on transaction type (buy or sell)
df["flow_units"] = df.apply(lambda x: -x["units"] if x["type"] == "sell" else x["units"], axis=1)

# Create the cml_units and prev_units column
df = df.groupby("ticker").apply(lambda grp: grp.assign(cml_units=grp["flow_units"].cumsum().abs(),
                                                           prev_units=grp["flow_units"].shift(1).cumsum().abs().fillna(0)))
df
Out[4]: 
         date ticker   type  units  price  fees  transacted_value  flow_units  \
0  2022-01-01   TSLA    buy    2.0   50.5   4.0            105.00         2.0   
1  2022-01-02   TSLA    buy    2.0   50.5   4.0            105.00         2.0   
2  2022-01-02   TSLA  split    3.0    0.0   0.0              0.00         3.0   
3  2022-01-03   TSLA   sell    2.0   52.0   2.5            101.50        -2.0   
4  2022-01-03   TSLA    buy    2.0   35.0   3.0             73.00         2.0   
5  2022-01-03   TSLA   sell    2.0   52.0   2.5            101.50        -2.0   
6  2022-01-03   TSLA    buy    2.0   35.0   3.0             73.00         2.0   
7  2022-01-03   AAPL    buy    2.5   50.5   1.5            127.75         2.5   
8  2022-01-04   AAPL    buy    4.0   50.5   2.0            204.00         4.0   

   cml_units  prev_units  
0        2.0         0.0  
1        4.0         2.0  
2        7.0         4.0  
3        5.0         7.0  
4        7.0         5.0  
5        5.0         7.0  
6        7.0         5.0  
7        2.5         0.0  
8        6.5         2.5  

这是我从之前的评论 @SRawson 中获得的帮助。

但在这里我想创建列 prev_costs、cml_costs 和 cost_transaction。逻辑如下:

我想为每一行从 "ticker" 列获取代码，并且:

• 如果 "type" 列中的交易类型值等于 "buy"，则计算操作 "prev_costs + transacted_value" 并填充在 “cml_costs” 列中。
  • 其中该行的 "prev_costs" 是 "cml_costs" 但向上移动了一行(如果按代码分组)。
• 如果交易类型值等于 “sell”，则计算操作 “prev_costs - cost_transaction” 并填充到 “cml_costs” 中。
  • 其中该行的 "prev_costs" 是 "cml_costs" 但向上移动了一行(如果按代码分组)。
  • 其中 "cost_transaction" 是操作的结果(针对该行):(units/cml_units) * prev_costs
• 否则返回 "prev_costs" 并填充到 "cml_costs"。

我期望数据框:

data_2 = {'prev_costs'           : [0, 105, 210, 210, 150, 223, 159.29, 0, 127.75],
          'cml_costs'            : [105, 210, 210, 150, 223, 159.29, 232.29, 127.75, 331.75],
          'cost_transaction'     : [0, 0, 0, 60, 0, 63.71, 0, 0, 0],
}

df2 = pd.DataFrame(data_2)

df_expected = pd.concat([df, df2], axis=1, join='inner')

df_expected
Out[5]: 
         date ticker   type  units  price  fees  transacted_value  flow_units  \
0  2022-01-01   TSLA    buy    2.0   50.5   4.0            105.00         2.0   
1  2022-01-02   TSLA    buy    2.0   50.5   4.0            105.00         2.0   
2  2022-01-02   TSLA  split    3.0    0.0   0.0              0.00         3.0   
3  2022-01-03   TSLA   sell    2.0   52.0   2.5            101.50        -2.0   
4  2022-01-03   TSLA    buy    2.0   35.0   3.0             73.00         2.0   
5  2022-01-03   TSLA   sell    2.0   52.0   2.5            101.50        -2.0   
6  2022-01-03   TSLA    buy    2.0   35.0   3.0             73.00         2.0   
7  2022-01-03   AAPL    buy    2.5   50.5   1.5            127.75         2.5   
8  2022-01-04   AAPL    buy    4.0   50.5   2.0            204.00         4.0   

   cml_units  prev_units  prev_costs  cml_costs  cost_transaction  
0        2.0         0.0        0.00     105.00              0.00  
1        4.0         2.0      105.00     210.00              0.00  
2        7.0         4.0      210.00     210.00              0.00  
3        5.0         7.0      210.00     150.00             60.00  
4        7.0         5.0      150.00     223.00              0.00  
5        5.0         7.0      223.00     159.29             63.71  
6        7.0         5.0      159.29     232.29              0.00  
7        2.5         0.0        0.00     127.75              0.00  
8        6.5         2.5      127.75     331.75              0.00  

我不确定以如此重要的方式“更新”一个问题是否合适，但希望是这样。如果我认为这是错误的，请发表评论。

我已经在 google 表格中完成了这项工作，但正如我在第一个问题中所说，我对 pandas 很陌生，再次迫切需要帮助。

(再次)提前致谢!

Answer 1

这是一种方法:

import pandas as pd

df = pd.DataFrame(data={"col_type": ["buy", "buy", "buy", "sell", "buy", "sell", "buy"],
                        "col_ticker": ["TSLA", "TSLA", "TSLA", "TSLA", "AAPL", "AAPL", "AAPL"],
                        "col_transacted_value": ["100", "100", "100", "300", "150", "200", "100"]})

df["col_transacted_value"] = df["col_transacted_value"].astype(int)
df["col_transacted_value_signs"] = df.apply(lambda x: -x["col_transacted_value"] if x["col_type"] == "sell" else x["col_transacted_value"], axis=1)
df = df.groupby("col_ticker").apply(lambda grp: grp.assign(New_col_cml_cost=grp["col_transacted_value_signs"].cumsum(),
                                                           New_col_prev_cost=grp["col_transacted_value_signs"].shift(1).cumsum().fillna(0)))

解释:

• 将您的字符串值转换为整数(如果您愿意，则为 float 并不总是整数)。
• 使用 -ve 创建一个新列“卖出”的值和“买入”的 +ve(您可以覆盖col_transacted_value 列(如果您不需要所有项目都为 +ve)。
• 最后，按 col_ticker 分组并使用 apply 分配新列 lambda 。

我从 krassowski 的评论中获得了 groupby apply assign 想法的灵感。 .

输出(TSLA 就像你的例子):

#Out: 
#             col_type col_ticker  ...  New_col_cml_cost  New_col_prev_cost
#col_ticker                        ...                                     
#AAPL       4      buy       AAPL  ...               150                0.0
#           5     sell       AAPL  ...               -50              150.0
#           6      buy       AAPL  ...                50              -50.0
#TSLA       0      buy       TSLA  ...               100                0.0
#           1      buy       TSLA  ...               200              100.0
#           2      buy       TSLA  ...               300              200.0
#           3     sell       TSLA  ...                 0              300.0
#
#[7 rows x 6 columns]