I have a small df with one column, z_score. I’ll then create 3 columns – sell, position, and short_exit.
The trading rule is that if z_score is greater than 2 AND there’s no an open position, sell. Which means sell value in that row will become 1. The position, with the default value of 0, also becomes 1 (this means I have an open position). It remains 1 until the position is closed. When z_score falls below 0, the position is closed, i.e., position becomes 0 again. In that case, short_exit gets the value of 1, and the position becomes 0 again because we don’t have an open position.
I believe it’s not that hard to do it. But I somehow failed.
import pandas as pd
import numpy as np
df = pd.read_excel("gate.xlsx", sheet_name="Sheet3")
# Assuming your data is in a DataFrame called df
# Initialize sell and positions columns with zeros
df['sell'] = 0
df['positions'] = 0
df['short_exit'] = 0
df['sell'] = np.where((df['z_score'] > 2) & (df['positions'].shift(1) == 0), 1, 0)
df['positions'] = np.where(((df['sell'] == 1) | (df['positions'].shift(1) == 1)) & (df['short_exit'] == 0), 1, df['positions'])
df['short_exit'] = np.where((df['z_score'] < 0) & (df['positions'] == 1), 1 ,0)
# Display the resulting DataFrame
print(df)
The expected result should be like in the attached image. In what I get position column gets value of 0 in some rows in which it shouldn’t.
Any help or hint is appreciated.
By the way is it possible to do it numpy? In one forum I was told that it requires recursion so isn’t possible to do it like I want to solve it.
user25285511 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.