How to set preference with numpy.select function

user_13928638 0
Viewed 173

Following is my sample dataset:

Price  SL    X  
14    13.8  100
14.5  13.8   0
15    13.8   0
14.7  13.8   0
13.6  13.8   0
15    13.8   0

I want to create a new column that puts in remarks based on X. I want it to have BUY when X is 100. Followed by three rows of HOLD and two rows of SELL.

I am doing this using the following code:

cond = [   
    (df['X'] == 100),
    (df['X'].shift(1) == 100),
    (df['X'].shift(2) == 100),
    (df['X'].shift(3) == 100),
    (df['X'].shift(4) == 100),
    (df['X'].shift(4) == 100),
]

choices = ['BUY', 'HOLD', 'HOLD', 'HOLD', 'SELL','SELL']
df['remarks'] = np.select(cond, choices)

Here is the outcome:

Price  SL    X    remarks
14    13.8  100    BUY
14.5  13.8   0     HOLD
15    13.8   0     HOLD
14.7  13.8   0     HOLD
13.6  13.8   0     SELL
15    13.8   0     SELL

However, I want to add another option of Stopped Out when Price < SL like in the fifth row. How do I assign Stopped Out the highest priority overall?

Desired outcome:

Price  SL    X    remarks
14    13.8  100    BUY
14.5  13.8   0     HOLD
15    13.8   0     HOLD
14.7  13.8   0     HOLD
13.6  13.8   0     Stopped Out
15    13.8   0     Stopped Out
14.5  13.8   0     
15    14.7  100    BUY
14.8  14.7   0     HOLD
13    14.7   0      Stopped Out

I would want the remarks to end once the Stopped Out shows up. And they should come up only after a BUY and not sometime before it.

pandas numpy python
3 Answers

How about using iloc?

import pandas as pd
import numpy as np

df = pd.DataFrame({'Price':[14,14.5,15,14.7,13.6,15],
                  'SL':[13.8,13.8,13.8,13.8,13.8,13.8],
                  'X':[100,0,0,0,0,0]}) 

choices = ['BUY', 'HOLD', 'HOLD', 'HOLD', 'SELL', 'SELL']
df['remarks'] = choices

df.iloc[df[df['Price'] < df['SL']].index[0]: , -1] = 'Stopped Out'

Results

user_147890100

The condition are evaluated sequentially so insert your condition with highest priority at the top of the list:

cond = [
    (df['Price'] < df['SL']),  # <- HERE
    (df['X'] == 100),
    (df['X'].shift(1) == 100),
    (df['X'].shift(2) == 100),
    (df['X'].shift(3) == 100),
    (df['X'].shift(4) == 100),
    (df['X'].shift(4) == 100),
]

choices = ['Stopped Out', 'BUY', 'HOLD', 'HOLD', 'HOLD', 'SELL','SELL']

df['remarks'] = np.select(cond, choices)
user_152399510

First add default='' to your current np.select:

cond = [
    (df['X'] == 100),
    (df['X'].shift(1) == 100),
    (df['X'].shift(2) == 100),
    (df['X'].shift(3) == 100),
    (df['X'].shift(4) == 100),
    (df['X'].shift(4) == 100),
]
choices = ['BUY', 'HOLD', 'HOLD', 'HOLD', 'SELL','SELL']
df['remarks'] = np.select(cond, choices, default='')
#                                        ^^^^^^^^^^ add this

Then groupby each BUY block and create a stop counter using a double cumsum:

buy = df['X'].eq(100).cumsum()
df['stop'] = df.groupby(buy, sort=False).apply(lambda g: g['Price'].lt(g['SL']).cumsum().cumsum()).array

#    Price    SL    X remarks  stop
# 0   14.0  13.8  100     BUY     0
# 1   14.5  13.8    0    HOLD     0
# 2   15.0  13.8    0    HOLD     0
# 3   14.7  13.8    0    HOLD     0
# 4   13.6  13.8    0    SELL     1
# 5   15.0  13.8    0             2
# 6   14.5  13.8    0             3
# 7   15.0  14.7  100     BUY     0
# 8   14.8  14.7    0    HOLD     0
# 9   13.0  14.7    0    HOLD     1

Now mask the remarks wherever stop is 1 or 2:

df['remarks'] = df['remarks'].mask(df['stop'].isin([1, 2]), 'Stopped Out')

#    Price    SL    X      remarks
# 0   14.0  13.8  100          BUY
# 1   14.5  13.8    0         HOLD
# 2   15.0  13.8    0         HOLD
# 3   14.7  13.8    0         HOLD
# 4   13.6  13.8    0  Stopped Out
# 5   15.0  13.8    0  Stopped Out
# 6   14.5  13.8    0             
# 7   15.0  14.7  100          BUY
# 8   14.8  14.7    0         HOLD
# 9   13.0  14.7    0  Stopped Out
user_131383640
Related