我想建立一个股票投资组合。我主要有两个dfs:一个是我的交易记录，一个是个股的股价记录。我的事务df看起来像这样:
Date Ticker Position
0 2022-11-01 MSFT 20
1 2022-11-15 PG 10
2 2022-11-25 JNJ 10
3 2022-11-22 MSFT 10
position列表示购买了多少股票。因此，在第三行，我买了第二个MSFT的头寸，加上10只股票，使我的MSFT股票总数达到30只。
我的股票价格df是这样的(来自yfinance):
Ticker Adj Close
Date
2022-11-01 MSFT 227.528793
2022-11-02 MSFT 219.481476
2022-11-03 MSFT 213.647903
2022-11-04 MSFT 220.767838
2022-11-07 MSFT 227.229630
... ... ...
2022-12-05 JNJ 178.779999
2022-12-06 JNJ 176.100006
2022-12-07 JNJ 177.169998
2022-12-08 JNJ 177.199997
2022-12-09 JNJ 175.740005
我想在我的股票价格中增加一列，名为Position，显示当时的股票头寸。
我认为这对于双条件应该不是那么困难:如果prices_date >= transaction_date AND transaction_ticker = prices_ticker那么prices_postion = prices_position + transaction_position
我想首先加载一个全为0的新列，这应该允许一个简单的加法(或减法，如果股票出售)
我尝试重置索引以便列允许更好的比较，嵌套for循环，但只发生错误。我不知道如何在Python中做到这一点，也没有在网上找到答案。
欢迎提出任何建议
这里有一个依赖于merge,fillna和groupby.cumsum()的解决方案:
import pandas as pd
import numpy as np
df1 = pd.DataFrame(
{
"Date": pd.to_datetime(
["2022-11-01", "2022-11-15", "2022-12-07", "2022-11-04"]
),
"Ticker": ["MSFT", "JNJ", "JNJ", "MSFT"],
"Position": [20, 10, 10, 10],
}
)
df1
df2 = pd.DataFrame(
{
"Date": pd.to_datetime(
[
"2022-11-01",
"2022-11-02",
"2022-11-03",
"2022-11-04",
"2022-11-07",
"2022-12-05",
"2022-12-06",
"2022-12-07",
"2022-12-08",
"2022-12-09",
]
),
"Ticker": [
"MSFT",
"MSFT",
"MSFT",
"MSFT",
"MSFT",
"JNJ",
"JNJ",
"JNJ",
"JNJ",
"JNJ",
],
"Close": [
227.528793,
219.481476,
213.647903,
220.767838,
227.229630,
178.779999,
176.100006,
177.169998,
177.199997,
175.740005,
],
}
)
df3 = df2.merge(df1,how='outer',on=['Ticker','Date'])
df3 = df3.sort_values(by='Date')
df3 = df3.dropna(axis=0,subset='Close')
df3['Position'] = df3['Position'].fillna(0)
df3['Position'] = df3.groupby('Ticker')['Position'].cumsum()
df3
结果:
Date Ticker Close Position
0 2022-11-01 MSFT 227.528793 20.0
1 2022-11-02 MSFT 219.481476 20.0
2 2022-11-03 MSFT 213.647903 20.0
3 2022-11-04 MSFT 220.767838 30.0
4 2022-11-07 MSFT 227.22963 30.0
5 2022-12-05 JNJ 178.779999 0.0
6 2022-12-06 JNJ 176.100006 0.0
7 2022-12-07 JNJ 177.169998 10.0
8 2022-12-08 JNJ 177.199997 10.0
9 2022-12-09 JNJ 175.740005 10.0