CODEWITHSUNDEEP
pythonpandas
mannaroth
mannaroth

Reputation: 1543

Select rows between rows fulfilling certain conditions in Python

I have a large pandas dataframe and I would like to select all user actions that happen between an entry (action1 == 0) and an exit point (action1 == 1). There are multiple such user sessions in the database. It looks something like this:

User_action   Reference   Other_data  Row_index
  action2         0          foo         a
  action1         0          bar         b
  action6         0          foo         c
  action4         0          foo         d
  action1         1          bar         e
  action7         0          foo         f
  action1         0          foo         g
  action3         0          bar         h
  action1         1          foo         i
  action1         1          foo         j
  action3         0          bar         k
  action1         0          foo         l
  action9         0          foo         m
  action1         1          foo         n

The result should produce the rows with the indexes: c,d,h and m:

User_action   Reference   Other_data  Row_index
  action6         0          foo         c
  action4         0          foo         d
  action3         0          bar         h
  action9         0          foo         m

Upvotes: 1

Views: 70

Answers (1)

jezrael
jezrael

Reputation: 863451

Use:

#filter only category
df1 = df[df['User_action'] == 'action1'].copy()

#test only pairs 0, 1 and reindex for same length as original df
m1 = df1['Reference'].eq(1) & df1['Reference'].shift(1).eq(0)
m1 = m1.reindex(df.index, fill_value=False)
m2 = df1['Reference'].eq(0) & df1['Reference'].shift(-1).eq(1)
m2 = m2.reindex(df.index, fill_value=False).shift().fillna(False)

a = np.select([m1, m2], [-1,1], default=0)
m = a.cumsum() == 1
#filter by final condition
df = df[m]

print (df)
   User_action  Reference Other_data Row_index
4      action6          0        foo         c
5      action4          0        foo         d
9      action3          0        bar         h
14     action9          0        foo         m

Upvotes: 2

Related Questions