Python conditional value for new column based on previous rows

Question

Created 5P moving average and 20P moving average for dataframe and am trying to create a new column based on the values of the 5P & 20P moving average.

If 5Pmoving average > 20Pmoving average then new column cell to = 'BUY' If 5Pmoving average < 20Pmoving average then new column cell to = 'SELL'

I've already created a column with says either BUY/SELL

Im trying to create a new column which confirmed if the row and row+1 is different then BUY/SELL depending on the order.

This is what my dataframe looks like

CBA = {5MA: [13.11,13.44,13.56,13.45,13.10,12.45],
20MA: [12.67,12.77,12.87,13.50,13.45,12.30],
test: [BUY,BUY,BUY,SELL,SELL,BUY]}

and i want the outcome

CBA = {5MA: [13.11,13.44,13.56,13.45,13.10,12.45],
20MA: [12.67,12.77,12.87,13.50,13.45,12.30],
test: [BUY,BUY,BUY,SELL,SELL,BUY],
BUY/SELL: [NaN, NaN,SELL,NaN,BUY]}

i've made this function

def buy_sell_strat(df): 
    for i, row in df:
        if df.loc[i,'test'] == 'SELL' & df.loc[i+1,'test'] == 'BUY':
          return 'BUY'
        elif df.loc[i,'test'] == 'BUY' & df.loc[i+1,'test'] == 'SELL':
         return 'SELL'
        else:
         return 'NaN'

CBA['Buy/Sell'] = CBA.apply(buy_sell_strat,axis = 1)

However im getting error

TypeError: cannot unpack non-iterable float object

If you find my answer helpful I'd appreciate it if you could accept my answer — Albo
– Albo, Commented Dec 4, 2020 at 13:24

Albo · Accepted Answer · 2020-12-04 07:40:28Z

1

loop solution

Why not just use (faster solution down below):

data = {"5MA": [13.11, 13.44, 13.56, 13.45, 13.10, 12.45],
        "20MA": [12.67, 12.77, 12.87, 13.50, 13.45, 12.30],
        "test": ["BUY", "BUY", "BUY", "SELL", "SELL", "BUY"]}


df = pd.DataFrame(data)

# takes 1.41 s ± 14.7 ms per loop (mean ± std. dev. of 7 runs, 3 loops each)
for i in range(df.index[-1]):
    if df.loc[i, 'test'] == 'SELL' and df.loc[i+1, 'test'] == 'BUY':
        df.loc[i, 'Buy/Sell'] = 'BUY'
    elif df.loc[i, 'test'] == 'BUY' and df.loc[i+1, 'test'] == 'SELL':
        df.loc[i, 'Buy/Sell'] = 'SELL'
    else:
        df.loc[i, 'Buy/Sell'] = 'NaN'

the output is:

|    |   5MA |   20MA | test   | Buy/Sell   |
|---:|------:|-------:|:-------|:-----------|
|  0 | 13.11 |  12.67 | BUY    | NaN        |
|  1 | 13.44 |  12.77 | BUY    | NaN        |
|  2 | 13.56 |  12.87 | BUY    | SELL       |
|  3 | 13.45 |  13.5  | SELL   | NaN        |
|  4 | 13.1  |  13.45 | SELL   | BUY        |
|  5 | 12.45 |  12.3  | BUY    | nan        |

fast solution

A way faster and more pandas like solution would be (~500 times faster, comparison done with a 6000 rows dataframe), thanks to @Cainã Max Couto-Silva for the np.nan hint.

df['Buy/Sell'] = np.nan

# takes 3.2 ms ± 52.1 µs per loop(mean ± std. dev. of 7 runs, 100 loops each)
df.loc[(df['test'] == "SELL") & (df['test'].shift(-1) == "BUY"), 'Buy/Sell'] = 'BUY'
df.loc[(df['test'] == "BUY") & (df['test'].shift(-1) == "SELL"), 'Buy/Sell'] = 'SELL'

which gives you the same output:

|    |   5MA |   20MA | test   | Buy/Sell   |
|---:|------:|-------:|:-------|:-----------|
|  0 | 13.11 |  12.67 | BUY    | NaN        |
|  1 | 13.44 |  12.77 | BUY    | NaN        |
|  2 | 13.56 |  12.87 | BUY    | SELL       |
|  3 | 13.45 |  13.5  | SELL   | NaN        |
|  4 | 13.1  |  13.45 | SELL   | BUY        |
|  5 | 12.45 |  12.3  | BUY    | NaN        |

edited Dec 4, 2020 at 7:40

answered Dec 4, 2020 at 6:52

Albo

1,66414 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Cainã Max Couto da Silva Over a year ago

Great answer +1! It's still possible to simplify the pandas code with df['Buy/Sell'] = np.nan.

Cainã Max Couto da Silva Over a year ago

I was thinking in df['test2'] = df.test.shift(-1) with df['Buy/Sell'] = df.filter(like='test').query('test!=test2').dropna().test.eq('BUY').replace({True:'SELL',False:'BUY'}). It works, but using .loc as you did is magnificent!

Albo Over a year ago

@CainãMaxCouto-Silva Thank you very much! You're right, df['Buy/Sell'] = np.nan would be even more beautiful!

catcit123 Over a year ago

Thanks you so much @Albo and Cainã Max Couto-Silva this works perfect. If i wanted to make this even quicker by removing the 'test' column and useing the condition: - if row(5MA > 20MA) row+1(5MA < 20MA) then BUY. could i use the same for loop or is it different as they are integers?

Collectives™ on Stack Overflow

Python conditional value for new column based on previous rows

1 Answer 1

loop solution

fast solution

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

loop solution

fast solution

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related