title: pandas: efficient way to a filter out a few rows (outliers) from a large DataFrame
body: <p>I am looking for an efficient way to a filter out a few rows (outliers) from a large DataFrame. According to <a href="https://pandas.pydata.org/pandas-docs/stable/getting_started/comparison/comparison_with_sql.html#delete" rel="nofollow noreferrer">https://pandas.pydata.org/pandas-docs/stable/getting_started/comparison/comparison_with_sql.html#delete</a>, the advice is to select the rows that should remain. Here is an example DataFrame -</p>
  
  <pre><code>In [288]: dai                                                                                        
  Out[288]: 
                     x   y
  frame face lmark        
  1     NaN  NaN   NaN NaN
  2     NaN  NaN   NaN NaN
  3     NaN  NaN   NaN NaN
  4     NaN  NaN   NaN NaN
  5     NaN  NaN   NaN NaN
  ...               ..  ..
  5146  NaN  NaN   NaN NaN
  5147  NaN  NaN   NaN NaN
  5148  NaN  NaN   NaN NaN
  5149  NaN  NaN   NaN NaN
  5150  NaN  NaN   NaN NaN
  
  [312814 rows x 2 columns]
  </code></pre>
  
  <p>whose index is sorted -</p>
  
  <pre><code>In [295]: dai.equals(dai.sort_index())                                                               
  Out[295]: True
  </code></pre>
  
  <p>now I extract unique sorted values of <code>frame</code> index except for the last one (frame 5150) -</p>
  
  <pre><code>n [305]: frames = dai.index.get_level_values('frame').drop_duplicates().sort_values()[:-1]          
  
  In [306]: frames                                                                                     
  Out[306]: 
  Int64Index([   1,    2,    3,    4,    5,    6,    7,    8,    9,   10,
              ...
              5140, 5141, 5142, 5143, 5144, 5145, 5146, 5147, 5148, 5149],
             dtype='int64', name='frame', length=5149)
  </code></pre>
  
  <p>and then filter rows in the DataFrame using <code>.loc</code> </p>
  
  <pre><code>In [307]: dai.loc[frames]                                                                            
  Out[307]: 
                     x   y
  frame face lmark        
  1     NaN  NaN   NaN NaN
  2     NaN  NaN   NaN NaN
  3     NaN  NaN   NaN NaN
  4     NaN  NaN   NaN NaN
  5     NaN  NaN   NaN NaN
  ...               ..  ..
  5145  NaN  NaN   NaN NaN
  5146  NaN  NaN   NaN NaN
  5147  NaN  NaN   NaN NaN
  5148  NaN  NaN   NaN NaN
  5149  NaN  NaN   NaN NaN
  </code></pre>
  
  <p>The result is correct but it took longer than expected -</p>
  
  <pre><code>In [308]: timeit dai.loc[frames]                                                                     
  7.31 s ± 83.8 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
  
  In [309]: prun -l 4 dai.loc[frames]                                                                  
           1159551 function calls (1138939 primitive calls) in 7.753 seconds
  
     Ordered by: internal time
     List reduced from 253 to 4 due to restriction &lt;4&gt;
  
     ncalls  tottime  percall  cumtime  percall filename:lineno(function)
       5148    3.544    0.001    3.544    0.001 base.py:241(_outer_indexer)
      10298    1.963    0.000    1.963    0.000 {method 'searchsorted' of 'numpy.ndarray' objects}
      10298    0.811    0.000    0.900    0.000 base.py:1588(is_monotonic_increasing)
       5149    0.413    0.000    0.413    0.000 {method 'nonzero' of 'numpy.ndarray' objects}
  </code></pre>
  
  <p>Is there any way to improve the performance?</p>