Pandas column based on previous row multicolumn criteria

Question

I know this has been asked a few times, but I cannot get to solve it. I'm terribly sorry. I have a dataframe 'stock_df' with 1 column with 'RSI_14' values. I'm a newbie, so in order to make it easier I initialize 3 columns with == 0 for 3 states. This auxiliary columns are to explain better the question:

• More than 70 --> 'plus70' = 1
• Less than 30 --> 'minus30' = 1
• Between 70 and 30 --> 'between' = 1

The objective is to make a 'Signal' that is +1 when 'RSI_14' > 70 and -1 when 'RSI_14' < 30 (that's easy), but the tricky part is that when the state is 'between' 70 and 30 I need to put the former +1 or -1 state in 'Signal', and keep that number until the next +1 or -1 change of state and keep with that on and on... This shouldn't be that difficult with .shift(1) or .diff(1), but I don't get it.

This is the desired outcome: Outcome

I've tried np.where, but it's the last "stock_df['Signal'].shift(1)" that doesn't seem to work:

stock_df['Signal'] = np.where(stock_df['RSI_14'] > 70, 1, (np.where(stock_df['RSI_14'] < 30, -1, stock_df['Signal'].shift(1))))

I think the solution must be in "groupby" with "transform" but I've tried many different ways, but I'm quite clumsy... I really think is with groupby. I've checked A LOT of answers here, but I don't get to solve it. I'd really appreciate your help. Thanks

Answer 1

I don't think you need to create any additional columns to achieve this. Just use:

condition_values = stock_df.RSI_14.values
signal = []
last = None
for item in condition_values:
    if item < 30:
        signal.append(-1)
        last = -1
    elif item > 70:
        signal.append(1)
        last = 1
    else:
        signal.append(last)

 df['signal'] = signal

Change the none in the beginning to your liking value.

Answer 2

The following code snippet could work. Assigning signal value to be equal to the previous row signal value (when between equals 1) should get the job done.

for i in range(len(stock_df)):
    if stock_df['between'][i] == 1:
        if i == 0:
            stock_df['Signal'][i] = 1
        else:
            stock_df['Signal'][i] = stock_df['Signal'][i - 1]