Select row in DataFrame based on values in multiple rows

Question

I've got a DataFrame and I'd like to select rows where in one column they have a certain value, AND the row above has a certain value in another column. How do I do this without a for loop?

For example:

df = pd.DataFrame({'one': [1,2,3,4,1,2,3,4], 'two': [1,2,3,4,5,6,7,8]})

Where I'd like to find the row where df.one on that row equals 1, and df.two on the row above equals 4, so in the example row number 4 with values [1,5].

Answer 1

You can try shift with boolean indexing:

print df
   one  two
0    1    1
1    2    2
2    3    3
3    4    4
4    1    5
5    2    6
6    3    7
7    4    8

print (df.one == 1) & (df.two.shift() == 4)
0    False
1    False
2    False
3    False
4     True
5    False
6    False
7    False
dtype: bool

print df[(df.one == 1) & (df.two.shift() == 4)]
   one  two
4    1    5