Select values in pandas groupby series, less than, and subsequently, greate than, a threshold

user_11170350 0
Viewed 282

I have a data frame as follow

df = pd.DataFrame({'subjects':['A','A','B','C','A','A','B','B','C','C'],
                  'value':[3,5,5,6,7,2,4,8,9,2]})

subjects    value
A            3
A            5
B            5
C            6
A            7
A            2
B            4
B            8
C            9
C            2

Now I want to select those subjects, where at some point value is less than 5 and becomes greater than 5 at some other subsequent point coming after the point where value is less than 5. In this data frame it will be A and B.

I have tried this:

df[df.groupby('subjects').value.apply(lambda x: x > 5)]

But it selects those having value greater than 5, and I want initial values to less than 5 and if there is a change in value and subsequent value is greater than 5, then return those subjects.

pandas-groupby pandas python
2 Answers

Use GroupBy.cummax for Trues for rows after match less like 5 and then filter rows in df1 if greater like 5:

thresh = 5
m1 = df['value'] < thresh

df1 = df[m1.groupby(df['subjects']).cummax()] 
print (df1)
  subjects  value
0        A      3
1        A      5
4        A      7
5        A      2
6        B      4
7        B      8
9        C      2

out = df1.loc[df1['value'] > thresh, 'subjects'].unique().tolist()
print (out)
['A', 'B']

Performance:

np.random.seed(123)
N = 10000

df = pd.DataFrame({'subjects': np.random.randint(500, size=N),
                   'value':np.random.randint(7, size=N)})
print (df)


thresh = 5
m1 = df['value'] < thresh
df1 = df[m1.groupby(df['subjects']).cummax()] 
out = df1.loc[df1['value'] > thresh, 'subjects'].unique().tolist()
print (len(out))

def lt_then_gt_threshold(v, thresh=5):
    m = (v<thresh).cummax()
    return (v[m]>thresh).any()

subjs = df.groupby('subjects')['value'].apply(lt_then_gt_threshold)

print (len(subjs[subjs].index))

In [60]: %%timeit
    ...: 
    ...: thresh = 5
    ...: m1 = df['value'] < thresh
    ...: df1 = df[m1.groupby(df['subjects']).cummax()] 
    ...: out = df1.loc[df1['value'] > thresh, 'subjects'].unique().tolist()
    ...: 
2.44 ms ± 9.43 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

In [61]: %%timeit
    ...: df.groupby('subjects')['value'].apply(lt_then_gt_threshold)
    ...: 
266 ms ± 613 µs per loop (mean ± std. dev. of 7 runs, 1 loop each)
user_29010020

Here's a solution using logical masking. Then we use .cummax() as a shortcut to logical-and that forward. Now we have a mask which identifies every element subsequent to the first value that was < 5. It's then simple to compare (v[m]>5).any():

def lt_then_gt_threshold(v, thresh=5):
    m = (v<thresh).cummax()
    return (v[m]>thresh).any()

>>> subjs = df.groupby('subjects')['value'].apply(lt_then_gt_5)

subjects
A     True
B     True
C    False

>>> subjs[subjs==True].index.tolist()

['A', 'B']
user_2022290
Related