How to dynamically indicate the start as well as the end of a set of conditions within a DataFrame?

Refresh

February 2019

Views

13 time

0

I I seek to create an indicator column in a dataframe that looks at the values of entry and exit column and creates a window that has the following logic:

  1. Find earliest date where entry is a 1 (1/3/2019) and mark window as 1.
  2. Window remains 1 until exit is 1 (1/5/2019).
  3. Window then becomes 0 until it finds the next value of entry that equals 1 (1/8/2019).
  4. The process is repeated up to most recent date in DataFrame.

So for the Window column an exit must follow and entry or for every entry there will eventually be an exit (see example below). Also note, the date index is in reverse order.

df = pd.DataFrame({'C1':[5,3,4,5,4,2,1,4,5,4,2,3],
                    'C2':[3,3,2,3,4,1,3,2,3,5,3,3]},
                   index = pd.date_range('1/1/2019',periods = 12))
df1 = df.iloc[::-1]

# Create function to add entry and exit criteria
def window_range(df):
    crit1 = ((df['C1'] >2) & (df['C1'].shift(-1) < df['C1']))
    crit2 = (df['C2'].shift(-1)==3)
    entry = crit1&crit2
    df['Entry'] = np.where(entry,1,0)
    exit = (df['C1'] < df['C1'].shift(-1))
    df['Exit'] = np.where(exit,1,0)
    return df
df2 = window_range(df1)
df2
# The Column I'd like to add
window = list([1,0,1,1,1,0,0,1,1,1,0,0])
df2['window'] = pd.Series(window, index=df2.index)
df3 = df2
# Desired Final Output
df3

enter image description here

0 answers