In Pandas dataframe, using boolean output, how to detect crossovers of two different columns (lines)?

Question

I'm still learning python and trying out a real-time scan of the latest stock prices and in my workflow I have two different dataframes.

(1) dataframe running real-time to detect certain conditions based on the latest open / high / low / close (OHLC) prices, and LineA and LineB. Latter lines are based on the OHLC values. These are manifest in 6 columns with the same name. Index is simply time. In this example, each rows represents 1 minute, and new rows manifest in the dataframe with each passing minute. The code used here to check the crossover based on the latest rows have the following type of writing style:

def check_crossover(df):

    cond1 = any([(candles.lineA[-1] < candles.lineB[-1] and candles.lineA[-2] > candles.lineB[-2]),(candles.lineA[-2] < candles.lineB[-2] and candles.lineA[-3] > candles.lineB[-3])])

    return any([cond1,cond2])

I'm trying to detect if LineB has dipped down below LineA from above. Let's say at this moment it's 0900, and I'm trying to detect if any of this crossover happened between 0858 to 0859, or 0859 to 0900.

Translating to rows in the dataframe, I'm trying to use the above code to find crossovers within the current and previous two rows.

I've tried running in python, and it gave me a False response, so I'm thinking it's correct.

Can advise if this is correct, or if there is a better way to write this?

(2) the same exact dataframe, but this time I'm trying to populate additional columns representing the outcome of the cond1 above, per row. So if a row does not show any crossovers within the current and previous two rows, the current row under the 'Cond1' column will be 'False'.

Purpose here is for me to check if the realtime script is running correctly.

def cond1(df):
    df.loc[:, ('cond1a')] = df.LineA < df.LineB and df.LineA.shift(1) > df.LineB.shift(1)
    df.loc[:, ('cond1b')] = (df.LineA.shift(1) < df.LineB.shift(1) and df.LineA.shift(2) > df.LineB.shift(2))
    df.loc[:, ('cond1')] = df[['cond1a', 'cond1b']].all(axis=1)

    return df

This gave me the error 'The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().' I just learned that Python and Pandas have their own way of using .any() and .all(), but I'm lost here.

How I should write this, in cases where I need both conditions to be true, or just one of the conditions to be true?

Please advise!

Answer 1

The issue is the use of and. For a boolean comparison of array-like objects you want to use & (see this question). Moreover, you need to make sure to use parenthesis to separate the logical & from the tests with < and >. Finally, the function cond1 updates the dataframe df in place, so there is no need to use return at the end of the function.

In summary:

 def cond1(df):
     df.loc[:, ('cond1a')] = (df.LineA < df.LineB) & (df.LineA.shift(1) > df.LineB.shift(1))
     df.loc[:, ('cond1b')] = (df.LineA.shift(1) < df.LineB.shift(1)) & (df.LineA.shift(2) > df.LineB.shift(2))
     df.loc[:, ('cond1')] = df[['cond1a', 'cond1b']].all(axis=1)

Edit: Regarding your first question, it might be easier to do first a comparison between lineA and lineB to get a boolean index and then check when this index flips from True to False. This will give you all crossover points so far:

cond = df.lineA < df.lineB  # checks for all times whether lineB is above lineA
cond != cond.shift(1)  # returns True for all times, where lineB dips below lineA