Pandas: Select row pairs based on specific combination of strings in one column

user_14650426 0
Viewed 135

I'm fairly new to python/pandas and have struggled to find an example specific enough for me to work with.

Say I have the following pandas dataframe, consisting of a column of event markers and a column displaying the time each marker was presented:

 df = pd.DataFrame({'Marker': ['S200', 'S4', 'S44', 'Tone', 'S200', 'S1', 'S44', 'Tone'], 
                    'Time': [0, 100, 150, 230, 300, 340, 380, 400]})

  Marker  Time
0   S200     0
1     S4   100
2    S44   150
3   Tone   230
4   S200   300
5     S1   340
6    S44   380
7   Tone   400

I would like to extract pairs of rows where S44 is followed by a Tone. The resulting output should be:

newdf = pd.DataFrame({'Marker': ['S44', 'Tone', 'S44', 'Tone'],
                   'Time': [150, 230, 380, 400]})

  Marker  Time
0    S44   150
1   Tone   230
2    S44   380
3   Tone   400

Any ideas would be appreciated!

pandas dataframe numpy python
2 Answers

One way about it is to use shift to get the indexes, add 1 and pull with loc - note that this assumes that the index is numeric and monotonic increasing:

index = df.loc[df.Marker.shift(-1).eq('Tone') & (df.Marker.eq('S44'))].index

df.loc[index.union(index +1)]
 
  Marker  Time
2    S44   150
3   Tone   230
6    S44   380
7   Tone   400
user_71757130

Another way:

s = ((df.Marker.eq('S44')) & (df.Marker.shift(-1).eq('Tone')))
df = df[s | s.shift()]

OUTPUT:


  Marker  Time
2    S44   150
3   Tone   230
6    S44   380
7   Tone   400
user_154380330
Related