Use columns 1 and 2 to populate column 3

Question

I’m a Python newbie and have the following Pandas dataframe - I’m trying to write code that populates the ‘signal’ column as it is below:

Days	long_entry_flag	long_exit_flag	signal
1	FALSE	TRUE
2	FALSE	FALSE
3	TRUE	FALSE	1
4	TRUE	FALSE	1
5	FALSE	FALSE	1
6	TRUE	FALSE	1
7	TRUE	FALSE	1
8	FALSE	TRUE
9	FALSE	TRUE
10	TRUE	FALSE	1
11	TRUE	FALSE	1
12	TRUE	FALSE	1
13	FALSE	FALSE	1
14	FALSE	TRUE
15	FALSE	FALSE
16	FALSE	TRUE
17	TRUE	FALSE	1
18	TRUE	FALSE	1
19	FALSE	FALSE	1
20	FALSE	FALSE	1
21	FALSE	TRUE
22	FALSE	FALSE
23	FALSE	FALSE

My pseudocode version would take the following steps

1. Look down the [‘long_entry_flag’] column until entry condition is True (day 3 initially)
2. Then we enter ‘1’ into [‘signal’] column every day until exit condition is True [‘long_exit_flag’]==True on day 8
3. Then we look back to [‘long_entry_flag’] column to wait for the next entry condition (occurs on day 10)
4. And again we enter ‘1’ into [‘signal’] column every day until exit condition is True (day 14)
5. etc.

What are some ways to populate the ‘signal’ column rapidly if possible (using vectorisation?)?

This is a subset of a large dataframe with tens of thousands of rows, and it is one of many dataframes being analysed in sequence.

Answer 1

You can do

# Assuming we're starting from the "outside"
inside = False
for ix, row in df.iterrows():
    inside = (not row['long_exit_flag']
              if inside
              else row['long_entry_flag']
                  and not row['long_exit_flag']) # [True, True] case
    df.at[ix, 'signal'] = 1 if inside else np.nan

which is going to give you exactly the output you posted.

---

Being inspired by @jezrael's answer, I created a slightly more performant version of the above while still trying to keep it as neat as I could:

# Same assumption of starting from the "outside"
df.at[0, 'signal'] = df.at[0, 'long_entry_flag']
for ix in df.index[1:]:
    df.at[ix, 'signal'] = (not df.at[ix, 'long_exit_flag']
                           if df.at[ix - 1, 'signal']
                           else df.at[ix, 'long_entry_flag']
                               and not df.at[ix, 'long_exit_flag']) # [True, True] case

# Adjust to match the requested output exactly
df['signal'] = df['signal'].replace([True, False], [1, np.nan])

Answer 2

For improving performance, use a Numba solution:

arr = df[['long_exit_flag','long_entry_flag']].values

@jit
def f(A):
    inside = False
    out = np.ones(len(A), dtype=float)
    for i in range(len(arr)):
        inside = not A[i, 0] if inside else A[i, 1]
        if not inside:
            out[i] = np.nan
    return out

df['signal'] = f(arr)

Performance:

#[21000 rows x 5 columns]
df = pd.concat([df] * 1000, ignore_index=True)

In [189]: %%timeit
     ...: inside = False
     ...: for ix, row in df.iterrows():
     ...:     inside = not row['long_exit_flag'] if inside else row['long_entry_flag']
     ...:     df.at[ix, 'signal'] = 1 if inside else np.nan
     ...:
1.58 s ± 9.45 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

In [190]: %%timeit
     ...: arr = df[['long_exit_flag','long_entry_flag']].values
     ...:
     ...: @jit
     ...: def f(A):
     ...:     inside = False
     ...:     out = np.ones(len(A), dtype=float)
     ...:     for i in range(len(arr)):
     ...:         inside = not A[i, 0] if inside else A[i, 1]
     ...:         if not inside:
     ...:             out[i] = np.nan
     ...:     return out
     ...:
     ...: df['signal'] = f(arr)
     ...:
171 ms ± 2.86 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

In [200]: %%timeit
     ...: df['d'] = np.where(~df['long_exit_flag'],df['long_entry_flag'] | df['long_exit_flag'],np.nan)
     ...:
     ...: df['new_select']= np.where(df['d']==0, np.select([df['d'].shift()==0, df['d'].shift()==1],[1,1], np.nan), df['d'])
     ...:
2.4 ms ± 561 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

You can also use NumPy for shifting, and also @Dark code is simplifying it:

In [222]: %%timeit
     ...: d = np.where(~df['long_exit_flag'].values,  df['long_entry_flag'].values | df['long_exit_flag'].values, np.nan)
     ...: shifted = np.insert(d[:-1], 0, np.nan)
     ...: m = (shifted==0) | (shifted==1)
     ...: df['signal1']= np.select([d!=0, m], [d, 1], np.nan)
     ...:
590 µs ± 35.7 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

You can also check Does iterrows have performance issues? for the general order of precedence for performance of various operations in Pandas.

Answer 3

Here's an approach with complete Boolean operations which is a vectorized approach and will be fast.

Step 1:

If long_exit_flag is True return Np.nan else apply `or` between `long_entry_flag` and `long_exit_flag`

df['d'] = np.where(df['long_exit_flag'], np.nan, df['long_entry_flag'] | df['long_exit_flag'])

Step 2: Now it's the state where both the columns are false. We need to ignore it and replace the values with the previous state. Which can be done using where and select:

df['new_signal']= np.where(df['d']==0,
                  np.select([df['d'].shift()==0, df['d'].shift()==1],[1,1], np.nan),
                  df['d'])

    Days  long_entry_flag  long_exit_flag  signal    d  new_signal
0      1            False            True     NaN  NaN         NaN
1      2            False           False     NaN  0.0         NaN
2      3             True           False     1.0  1.0         1.0
3      4             True           False     1.0  1.0         1.0
4      5            False           False     1.0  0.0         1.0
5      6             True           False     1.0  1.0         1.0
6      7             True           False     1.0  1.0         1.0
7      8            False            True     NaN  NaN         NaN
8      9            False            True     NaN  NaN         NaN
9     10             True           False     1.0  1.0         1.0
10    11             True           False     1.0  1.0         1.0
11    12             True           False     1.0  1.0         1.0
12    13            False           False     1.0  0.0         1.0
13    14            False            True     NaN  NaN         NaN
14    15            False           False     NaN  0.0         NaN
15    16            False            True     NaN  NaN         NaN
16    17             True           False     1.0  1.0         1.0
17    18             True           False     1.0  1.0         1.0
18    19            False           False     1.0  0.0         1.0
19    20            False           False     1.0  0.0         1.0
20    21            False            True     NaN  NaN         NaN

Answer 4

#let the long_exit_flag equal to 0 when the exit is TRUE
df['long_exit_flag_r'] = ~df.long_exit_flag_r
df.temp = ''

for i in range(1, len(df.index)):
    df.temp[i] = (df.signal[i-1] + df.long_entry_flag[i])*df.long_exit_flag_r

If the temp is positive then the signal should be 1, and if the temp is negative then the signal should be empty. (I kind of get stuck here.)