Pandas column with multiple conditions

Question

I am learning to explore a trading strategies using Pandas. The "Hello World" of algorithmic trading I hear is the Moving Average Cross Over where a shorter rolling mean goes above or below a longer rolling mean to determine long and short positions, respectively.

universe = sorted(os.listdir(f'{os.getcwd()}/Data'))
for u in range(len(universe)):
    universe[u] = universe[u][:-4]

df = pd.DataFrame()
df2 = pd.DataFrame()
df3 = pd.DataFrame()
df4 = pd.DataFrame()

for s in universe:
    df[s] = pd.read_csv(f'Data/{s}.csv', index_col='Date')['Adj Close']
    df2[s] = df[s].rolling(50).mean()
    df3[s] = df[s].rolling(200).mean()
    df4[s] = np.where((df2[s] > df3[s]), 1, 0)

for df4, I'm just having a 1 in the true condition of np.where to represent Long. But how would I go about using another condition np.where((df2[s] < df3[s]), -1, 0) for having -1 represent Short? I want 0 to represent no position.

Also, I may have a condition where it only happens once in the data but hold that signal as a "1" or "-1" until another signal registers it as a 0.

So I suppose I don't really to know how to apply If-else conditions within a Pandas DF.

Your help is greatly appreciated, thank you

to obtain sample data:

ticker = ['STT', 'TROW', 'BIIB', 'ADP', 'COG', 'EMR', 'USB', 'UA', 'AXP', 'WYNN']

for t in ticker:
    try:
        pdr.DataReader(t, data_source='yahoo', start=datetime(2000,1,1), end=datetime(2020,1,1)).to_csv(f'Data/{t}.csv')
    except KeyError:
        pass

Please provide sample data in a [reproducible way](https://stackoverflow.com/questions/20109391). Otherwise people won't be able to test. — Bill Huang, Nov 21 '20 at 18:35
ok, I made an edit to my original post to obtain sample data — Shion, Nov 21 '20 at 18:43

score 0 · Answer 1 · answered Nov 21 '20 at 18:59

0

Unfortunately, I don't have enough reputation to add a comment to your question, so I will try to straight up answer it, although I think I do not 100% understand your question.

If the snippet you have is working as intended by inserting a 1 where the condition is met (as I suppose, but did not test with the exact same data as you), then you can just replace the "else" part of the method with another np.where

df4[s] = np.where((df2[s] > df3[s]), 1, np.where((df2[s] < df3[s]), -1, 0))

This would yield -1 if df3<df2, 1 if df3>df2 and 0 if df3=df2. You can in principle go on with these nested calls of np.where.

Also, I may have a condition where it only happens once in the data but hold that signal as a "1" or "-1" until another signal registers it as a 0.

I don't really understand this part of your question, maybe you can elaborate more on this and I will try to help.

answered Nov 21 '20 at 18:59

BenB

658
2
10

hi, this actually worked out perfectly, thank you. To clarify the part of the question you don't understand, say for instance I have an Asset that break past a price level where df2[s] > df3[s], I want to continue in that trade even when the price drops below that level up until I hit a StopLoss. So df4[s] should equal "1" when df[s] > df[s].shift(1) at a certain price level but continue to be "1" even if the Price falls until another signal is registered to get out of the trade. I hope this makes sense. – Shion Nov 22 '20 at 23:32
You can use more complex conditional statement inside the np.where method, e.g. `np.where((conditional1)&(conditional2), then this, else that` and use `& (and)`, `| (or)` (and `~ for NOT`, I think) to link these conditions. I guess with this it should be possible to check for more than just df2[s]>df3[s]. – BenB Nov 23 '20 at 18:56

Pandas column with multiple conditions

1 Answers1