How to turn a multi conditional loop into a function in Python

Question

My Problem

I have written some code that joins up 4 independent technical signals (Buy, Sell, Short and Cover) each in their own column into one column. The signals can only be joined up in a particular order e.g. if you buy a stock in one time period then in the next time period the only possible actions are to sell it or to short it and you can't cover it.

The code works exactly how it should but the problem I have is that the loops make the code very slow, and I want to make it faster and more efficient.

Current Working Slow Code

import numpy as np
import pandas as pd

csv1 = pd.read_csv('signals.csv', delimiter = ',')
df = pd.DataFrame(csv1)

df.loc[df.index[0], 'Signal'] = "Buy"

for x in range(1,len(df.index)):
        if df["Signal"].iloc[x-1] == "Buy" and (df["Sell"].iloc[x] == "Sell" or df["Short"].iloc[x] == "Short"):
            df["Signal"].iloc[x] = df["Sell"].iloc[x] + df["Short"].iloc[x]
        elif df["Signal"].iloc[x-1] == "Short" and (df["Buy"].iloc[x] == "Buy" or df["Cover"].iloc[x] == "Cover"):
            df["Signal"].iloc[x] = df["Buy"].iloc[x] + str(df["Cover"].iloc[x])
        elif df["Signal"].iloc[x-1] == "Cover" and (df["Buy"].iloc[x] == "Buy" or df["Short"].iloc[x] == "Short"):
            df["Signal"].iloc[x] = df["Buy"].iloc[x] + df["Short"].iloc[x]
        elif df["Signal"].iloc[x-1] == "Sell" and (df["Buy"].iloc[x] == "Buy" or df["Short"].iloc[x] == "Short"):
            df["Signal"].iloc[x] = df["Buy"].iloc[x] + df["Short"].iloc[x]
        else:
            df["Signal"].iloc[x] = df["Signal"].iloc[x-1]

df

Expected Input

Short,Cover,Buy,Sell
,,,
Short,,,
,,,
,Cover,,
,,,
,,,
,,Buy,
,,,
,,,
,,,Sell
,,,
,,,
,,Buy,
,,,
,,,Sell
,,,

Expected Output

Short,Cover,Buy,Sell,Signal
,,,,Buy
Short,,,,Short
,,,,Short
,Cover,,,Cover
,,,,Cover
,,,,Cover
,,Buy,,Buy
,,,,Buy
,,,,Buy
,,,Sell,Sell
,,,,Sell
,,,,Sell
,,Buy,,Buy
,,,,Buy
,,,Sell,Sell
,,,,Sell

Any idea how i could do it?

Cheers!

What if instead of looping then if/else 'ing the different cases you'd "query" the dataframe as you want, either with boolean indexing or using query? — Cedric H., Sep 10 '19 at 08:38
Thanks for the fast reply. I'm a beginner so I am not quite sure how that would work? — pythonlearner13, Sep 10 '19 at 08:40
Look at Dataframe.query . But now I realize that the issue here is that you need to do something on row x based on a condition on row x-1 ; that's more difficult; not even actually sure what would be the best way in your case. — Cedric H., Sep 10 '19 at 08:48
Possible duplicate of [Pandas conditional creation of a series/dataframe column](https://stackoverflow.com/questions/19913659/pandas-conditional-creation-of-a-series-dataframe-column) — Erfan, Sep 10 '19 at 09:01
Look at the second part of the linked answer above where he uses `np.select`. You can apply multiple conditions with this method. — Erfan, Sep 10 '19 at 09:02
Erfan, as Cedric mentioned I need a function that can do something on row x based on a condition on row x-1 so I don't think np.select would solve that problem unfortunately — pythonlearner13, Sep 10 '19 at 09:18
It does, use `np.select` and use `shift` instead of `loc[x-1]` with a for loop. This is a trivial problem which many people faced and there are many vectorized solutions for this. You arn't the first one to run into this problem — Erfan, Sep 10 '19 at 09:31
I could be misunderstanding your post. I think your solution using shift would only work if if i am taking x-1 from another column (Buy, Sell, Short and Cover) and not from the Signal column. Basically, it is the last part of the loop where i dont think it would work... else: df["Signal"].iloc[x] = df["Signal"].iloc[x-1] — pythonlearner13, Sep 10 '19 at 09:57

score 0 · Answer 1 · answered Sep 23 '19 at 12:25

IIUC, you just need to do

df['Signal'] = df.ffill(axis=1).iloc[:,-1].ffill().fillna('Buy')

Output:

    Short  Cover  Buy  Sell Signal
0     NaN    NaN  NaN   NaN    Buy
1   Short    NaN  NaN   NaN  Short
2     NaN    NaN  NaN   NaN  Short
3     NaN  Cover  NaN   NaN  Cover
4     NaN    NaN  NaN   NaN  Cover
5     NaN    NaN  NaN   NaN  Cover
6     NaN    NaN  Buy   NaN    Buy
7     NaN    NaN  NaN   NaN    Buy
8     NaN    NaN  NaN   NaN    Buy
9     NaN    NaN  NaN  Sell   Sell
10    NaN    NaN  NaN   NaN   Sell
11    NaN    NaN  NaN   NaN   Sell
12    NaN    NaN  Buy   NaN    Buy
13    NaN    NaN  NaN   NaN    Buy
14    NaN    NaN  NaN  Sell   Sell
15    NaN    NaN  NaN   NaN   Sell

Unfortunately, the signals can only be joined up in a particular order e.g. if you buy a stock in one time period then in the next time period the only possible actions are to sell it or to short it and you can't cover it. I dont think this formula will take into account that? — pythonlearner13, Sep 25 '19 at 07:35

How to turn a multi conditional loop into a function in Python

1 Answers1

Linked