How to create a rolling difference between two different conditions?-CodePudding

I got to create a new column in Pandas DataFrame with rolling profit between buy and sell (holding period).. buy=1 is buying sell=1 is selling .. between buying and selling is the holding period.

So, the rolling profit should be the different between the current days' price (close column) and the last buy price (close value of the buy =1 condition), until the position is closed.

trading_df = pd.DataFrame({'Date': ['10/1/2020', '10/2/2020', '10/3/2020', '10/4/2020', '10/5/2020', '10/6/2020', '10/7/2020', '10/8/2020', '10/9/2020'],
 'Close': [90, 91, 89, 92, 93, 92, 94, 91, 89],
 'Buy': [0, 1, 0, 0, 0, 0, 1, 0, 0],
 'Sell': [0, 0, 0, 1, 0, 0, 0, 0, 1],
 'Rolling Profit': [None, 0.0, -2.0, 1.0, None, None, 0.0, -3.0, -5.0]})

CodePudding user response：

You can use:

df['no_trading'] = df['Sell'].replace({0:np.nan, 1:'no_trade'}).fillna(df['Buy'].replace({1:'bought'})).astype(str).replace('0', np.nan).ffill()
df['no_trading'] = df['no_trading'].mask(((df['no_trading'] == 'no_trade') & (df['Sell'] == 1)), 'sold')


df['unique_buys'] = df['Buy'].replace(0, np.nan).cumsum().ffill()
df['profit'] = df.groupby('unique_buys')[['Close']].apply(lambda x: x-x.iloc[0])
df['profit'] = df['profit'].mask(df['no_trading'] == 'no_trade', np.nan)
df.drop(columns=['no_trading', 'unique_buys'], inplace=True)
print(df)

OUTPUT

        date  Close  Buy  Sell  profit
0 2020-10-01     90    0     0     NaN
1 2020-10-02     91    1     0     0.0
2 2020-10-03     89    0     0    -2.0
3 2020-10-04     92    0     1     1.0
4 2020-10-05     93    0     0     NaN
5 2020-10-06     92    0     0     NaN
6 2020-10-07     94    1     0     0.0
7 2020-10-08     91    0     0    -3.0
8 2020-10-09     89    0     1    -5.0