CODEWITHSUNDEEP
pythonpandas
catcit123
catcit123

Reputation: 5

Python conditional value for new column based on previous rows

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

Upvotes: 0

Views: 332

Answers (1)

Albo
Albo

Reputation: 1644

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        |

Upvotes: 1

Related Questions