Efficiently filter windowed observations in a pandas dataframe if they contain a certain value
Question: I have a pandas dataframe that has windows/chains of string observations
indexed at the offset of their first observation. The window is of a variable
but known length. For this example we can say their chains of 4 observations.
I want to know how to most efficiently eliminate certain values if they have a
specific observation anywhere in their windows, knowing that if he 9th window
begins with the value I am looking for, I know I can get rid of it, and the
three windows before it because they will also contain the same value later in
their windows. It is possible for a window to contain multiple instances of
the value I want to filter for. Here's some sample data. Starting with a
simple series of events, ser:

    
    
    import pandas as pd
    ser = pd.Series(['a','b','c','d','e','f','g','h','i','j','k'])
    >>> ser
    0     a
    1     b
    2     c
    3     d
    4     e
    5     f
    6     g
    7     h
    8     i
    9     j
    10    k
    

Then I turn this into a dataframe where each row is a window of n
observations. Here n == 4.

    
    
    df = pd.concat([ser.shift(-x) for x in range(4)], axis=1)
    >>> df
        0    1    2    3
    0   a    b    c    d
    1   b    c    d    e
    2   c    d    e    f
    3   d    e    f    g
    4   e    f    g    h
    5   f    g    h    i
    6   g    h    i    j
    7   h    i    j    k
    8   i    j    k  NaN
    9   j    k  NaN  NaN
    10  k  NaN  NaN  NaN
    

Now I want to get rid of all the rows that include the value 'f' anywhere,
i.e.:

desired_output

    
    
        0    1    2    3
    0   a    b    c    d
    1   b    c    d    e
    6   g    h    i    j
    7   h    i    j    k
    8   i    j    k  NaN
    9   j    k  NaN  NaN
    10  k  NaN  NaN  NaN
    

I'd like to avoid searching the whole dataframe as it only contains repetition
of the first column, and my value for n can be somewhat long. In this example,
what would be the best way to drop the columns that start with 'c', 'd', 'e',
and 'f', knowing that they will all include an 'f' somewhere. Later I join all
the strings in each row into one value but it seems like it should be easier
to manipulate the data at this stage where everything is in different columns.
This is with pandas 0.16.0 and must work on python 2.76 and python 3.4. Thank
you!


Answer: You can do, without searching the whole dataframe:

    
    
    import numpy as np
    
    ind = -np.arange(0, df.shape[1])+pd.Index(ser).get_loc('f')
    df.iloc[np.setdiff1d(ser.index, ind)]
    
    #Out[48]: 
    #    0    1    2    3
    #0   a    b    c    d
    #1   b    c    d    e
    #6   g    h    i    j
    #7   h    i    j    k
    #8   i    j    k  NaN
    #9   j    k  NaN  NaN
    #10  k  NaN  NaN  NaN
    

