pythonpandasdelete-row

How to delete row with max/min values


I have dataframe:

   one   N    th
0   A      5      1   
1   Z      17     0   
2   A      16     0   
3   B      9      1   
4   B      17     0   
5   B      117    1   
6   XC     35     1   
7   C      85     0    
8   Ce     965    1 

I'm looking the way to keep alternating 0101 in column three without doubling 0 or 1. So, i want to delete row with min of values in case if i have two repeating 0 in column th and max values if i have repeating 1.

My base consis of 1000000 rows.

I expect to have dataframe like this:

   one   N    th
0   A      5      1   
1   Z      17     0   
3   B      9      1   
4   B      17     0    
6   XC     35     1   
7   C      85     0    
8   Ce     965    1 

What is the fastest way to do it. I mean vectorized way. My attempts without result.


Solution

  • using a custom groupby.idxmax

    You can swap the sign if "th" is 1 (to get the max instead of min), then set up a custom grouper (with diff or shift + cumsum) and perform a groupby.idxmax to select the rows to keep:

    out = df.loc[df['N'].mul(df['th'].map({0: 1, 1: -1}))
                 .groupby(df['th'].ne(df['th'].shift()).cumsum())
                 .idxmax()]
    

    Variant with a different method to swap the sign and to compute the group:

    out = df.loc[df['N'].mask(df['th'].eq(1), -df['N'])
                 .groupby(df['th'].diff().ne(0).cumsum())
                 .idxmax()]
    

    Output:

      one    N  th
    0   A    5   1
    1   Z   17   0
    3   B    9   1
    4   B   17   0
    6  XC   35   1
    7   C   85   0
    8  Ce  965   1
    

    Intermediates:

      one    N  th  swap  group max
    0   A    5   1    -5      1   X
    1   Z   17   0    17      2   X
    2   A   16   0    16      2    
    3   B    9   1    -9      3   X
    4   B   17   0    17      4   X
    5   B  117   1  -117      5    
    6  XC   35   1   -35      5   X
    7   C   85   0    85      6   X
    8  Ce  965   1  -965      7   X
    

    using boolean masks

    The above code works for an arbitrary number of consecutive 0s or 1s. If you know that you only have up to 2 successive ones, you could also use boolean indexing, which should be significantly faster:

    # has the value higher precedence than the next?
    D = df['N'].mask(df['th'].eq(1), -df['N']).diff()
    
    # is the th different from the previous?
    G = df['th'].ne(df['th'].shift(fill_value=-1))
    
    # rule for the bottom row
    m1 = D.gt(0) | G
    
    # rule for the top row
    # same rule as above but shifted up
    # D is inverted
    # comparison is not strict in case of equality
    m2 = ( D.le(0).shift(-1, fill_value=True)
          | G.shift(-1, fill_value=True)
         )
    
    # keep rows of interest
    out = df.loc[m1&m2]
    

    Output:

      one    N  th
    0   A    5   1
    1   Z   17   0
    3   B    9   1
    4   B   17   0
    6  XC   35   1
    7   C   85   0
    8  Ce  965   1
    

    Intermediates:

      one    N  th       D      G     m1     m2  m1&m2
    0   A    5   1     NaN   True   True   True   True
    1   Z   17   0    22.0   True   True   True   True
    2   A   16   0    -1.0  False  False   True  False
    3   B    9   1   -25.0   True   True   True   True
    4   B   17   0    26.0   True   True   True   True
    5   B  117   1  -134.0   True   True  False  False
    6  XC   35   1    82.0  False   True   True   True
    7   C   85   0   120.0   True   True   True   True
    8  Ce  965   1 -1050.0   True   True   True   True
    

    More complex example with equal values:

       one    N  th       D      G     m1     m2  m1&m2
    0    A    5   1     NaN   True   True   True   True
    1    Z   17   0    22.0   True   True   True   True
    2    A   16   0    -1.0  False  False   True  False
    3    B    9   1   -25.0   True   True   True   True
    4    B   17   0    26.0   True   True   True   True
    5    B  117   1  -134.0   True   True  False  False
    6   XC   35   1    82.0  False   True   True   True
    7    C   85   0   120.0   True   True   True   True
    8   Ce  965   1 -1050.0   True   True   True   True
    9    u  123   0  1088.0   True   True   True   True # because of D.le(0)
    10   v  123   0     0.0  False  False   True  False # because or D.gt(0)
    

    NB. in case of equality, it is possible to select the first/second row or both or none, depending on the operator used (D.le(0), D.lt(0), D.gt(0), D.ge(0)).

    timings

    Although limited to maximum 2 consecutive "th", the boolean mask approach is ~4-5x faster. Timed on 1M rows:

    # groupby + idxmax
    96.4 ms ± 6.64 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
    
    # boolean masks
    22.2 ms ± 1.48 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)