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

以下是一种方法:

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

解释如下:

• 将字符串值转换为整数(如果愿意的话，也可以浮动)
• 使用-ve创建一个新列
• 最后，按col_ticker分组，并使用apply分配新列

我从krassowski年前的 comments 中获得了groupby应用分配 idea 的灵感.

输出(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]