How to find the maximum consecutive number for multiple columns?

Question

I need to identify the highest number of consecutive values that meet a certain criteria for multiple columns.

If my df is:

A    B    C    D    E
26   24   21   23   24
26   23   22   15   23 
24   19   17   11   15     
27   22   28   24   24 
26   27   30   23   11 
26   26   29   27   29

I want to know the maximum consecutive times that numbers over 25 occur for each column. So the output would be:

A 3
B 2
C 3
D 1
E 1

Using the following code, I can obtain the outcome for one column at a time; is there a way to create a table as above rather than repeating for each column (I have over 40 columns in total).

df.A.isnull().astype(int).groupby(df.A.notnull().astype(int).cumsum()).sum().max()

Thanks in advance.

Answer 1

Is this what you want ? pandas approach (PS: never thought I can make it one line LOL)

(df>25).apply(lambda x :x.groupby(x.diff().ne(0).cumsum()).cumcount()+1).mask(df<25).max()
Out[320]: 
A    3.0
B    2.0
C    3.0
D    1.0
E    1.0
dtype: float64

Answer 2

One option using numpy to calculate the max consecutive:

def max_consecutive(arr):
    # calculate the indices where the condition changes
    split_indices = np.flatnonzero(np.ediff1d(arr.values, to_begin=1, to_end=1))

    # calculate the chunk length of consecutive values and pick every other value based on 
    # the initial value
    try:
        max_size = np.diff(split_indices)[not arr.iat[0]::2].max()
    except ValueError:
        max_size = 0
    return max_size

df.gt(25).apply(max_consecutive)
#A    3
#B    2
#C    3
#D    1
#E    1
#dtype: int64

Timing compared with the other approach:

%timeit df.gt(25).apply(max_consecutive)
# 520 µs ± 6.92 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

%timeit (df>25).apply(lambda x :x.groupby(x.diff().ne(0).cumsum()).cumcount()+1).mask(df<25).max(0)
# 10.3 ms ± 221 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

Answer 3

Here's one with NumPy -

# mask is 2D boolean array representing islands as True values per col
def max_island_len_cols(mask):
    m,n = mask.shape
    out = np.zeros(n,dtype=int)
    b = np.zeros((m+2,n),dtype=bool)
    b[1:-1] = mask
    for i in range(mask.shape[1]):
        idx = np.flatnonzero(b[1:,i] != b[:-1,i])
        if len(idx)>0:
            out[i] = (idx[1::2] - idx[::2]).max()
    return out

output = pd.Series(max_island_len_cols(df.values>25), index=df.columns)

Sample run -

In [690]: df
Out[690]: 
    A   B   C   D   E
0  26  24  21  23  24
1  26  23  22  15  23
2  24  19  17  11  15
3  27  22  28  24  24
4  26  27  30  23  11
5  26  26  29  27  29

In [690]: 

In [691]: pd.Series(max_island_len_cols(df.values>25), index=df.columns)
Out[691]: 
A    3
B    2
C    3
D    1
E    1
dtype: int64

---

Runtime test

Inspired by the given sample that has numbers in range (24,28) and with 40 cols, let's setup a bigger input dataframe and test out all the solutions -

# Input dataframe
In [692]: df = pd.DataFrame(np.random.randint(24,28,(1000,40)))

# Proposed in this post
In [693]: %timeit pd.Series(max_island_len_cols(df.values>25), index=df.columns)
1000 loops, best of 3: 539 µs per loop

# @Psidom's solution
In [694]: %timeit df.gt(25).apply(max_consecutive)
1000 loops, best of 3: 1.81 ms per loop

# @Wen's solution
In [695]: %timeit (df>25).apply(lambda x :x.groupby(x.diff().ne(0).cumsum()).cumcount()+1).mask(df<25).max(0)
10 loops, best of 3: 95.2 ms per loop

Answer 4

An approach using pandas and scipy.ndimage.label, for fun.

import pandas as pd
from scipy.ndimage import label

struct = [[0, 1, 0],     # Structure used for segmentation
          [0, 1, 0],     # Equivalent to axis=0 in `numpy`
          [0, 1, 0]]     # Or 'columns' in `pandas`

labels, nlabels = label(df > 25, structure=struct)

>>> labels               # Labels for each column-wise block of consecutive numbers > 25
Out[]:
array([[1, 0, 0, 0, 0],
       [1, 0, 0, 0, 0],
       [0, 0, 0, 0, 0],
       [2, 0, 3, 0, 0],
       [2, 4, 3, 0, 0],
       [2, 4, 3, 5, 6]])

labels_df = pd.DataFrame(columns=df.columns, data=labels)  # Add original columns names

res = (labels_df.apply(lambda x: x.value_counts())  # Execute `value_counts` on each column
                .iloc[1:]                           # slice results for labels > 0
                .max())                             # and get max value

>>> res
Out[]:
A    3.0
B    2.0
C    3.0
D    1.0
E    1.0
dtype: float64