Filtering pandas dataframe for all rows where index is consecutive +/-1

Question

I have a big dataframe and I need to create a new dataframe only with the data where one index is consecutive to the other. For Example:

import pandas as pd
import numpy as np
indexer = [0,1,3,5,6,8,10,12,13,17,18,20,22,24,25,26]
df  = pd.DataFrame(range(50,66), index=indexer, columns = ['A'])

So the desired output in this case is:

     A
0   50
1   51
5   53
6   54
12  57
13  58
17  59
18  60
24  63
25  64
26  65

Is there a fast way of doing it in pandas? or need to do it with some kind of loop and function over each row?

Answer 1

You can't shift the index, so you first need to reset it. Then use a loc operation together with testing both up and down one shift. Remember to set your index back to the original.

df.reset_index(inplace=True)
>>> df.loc[(df['index'] == df['index'].shift(1) + 1) 
           | (df['index'] == df['index'].shift(-1) - 1), :].set_index('index')
        A
index    
0      50
1      51
5      53
6      54
12     57
13     58
17     59
18     60
24     63
25     64
26     65