Pandas groupby check if a column is strictly increasing wrt another column

user_5181947 0
Viewed 515

I have the following dataframe, df:

Subject Marks1 Marks2
English  1      10
English  1.5    20
English  1.7    30
English  3      40
Science  1      10
Science  1.5    20
Science  1.7    15
Science  3      35

I want to group by subjects and check if Marks2 is strictly increasing with increasing Marks1. If not then I want to remove that group from df and put it in another issues dataframe. So finally I will have, df:

Subject Marks1 Marks2
English  1      10
English  1.5    20
English  1.7    30
English  3      40

issues:

Subject Marks1 Marks2
Science  1      10
Science  1.5    20
Science  1.7    15
Science  3      35
pandas-groupby pandas dataframe python
2 Answers

Use DataFrameGroupBy.diff with compare for less values like 0 with DataFrame.any for all columns, then get vals for subject and filter output by Series.isin:

m = df.groupby('Subject').diff().le(0).any(axis=1)

vals = df.loc[m, 'Subject']
mask = df['Subject'].isin(vals)
df1 = df[mask]
print (df1)
   Subject  Marks1  Marks2
4  Science     1.0      10
5  Science     1.5      20
6  Science     1.7      15
7  Science     3.0      35

df2 = df[~mask]
print (df2)
   Subject  Marks1  Marks2
0  English     1.0      10
1  English     1.5      20
2  English     1.7      30
3  English     3.0      40

EDIT: Bottleneck is in difference per groups, if all groups are sorted is possible improve performance by:

#columns used for difference (passed to groupby())
cols = ['Subject','col1','col2']
#sorting by all columns (if possible and if necessary)
df = df.sort_values(cols)
m = df[['Marks1','Marks2']].diff().le(0).any(axis=1) & df.duplicated(cols)

vals = df.loc[m, 'Subject']
mask = df['Subject'].isin(vals)
df1 = df[mask]
user_29010020

.filter() using a lambda function that finds the .diff() to identify issues

 issues=df.groupby('Subject').filter(lambda x : ((x.Marks1.diff()>0)&(x.Marks2.diff()<0)).any())
    print(issues)


 Subject  Marks1  Marks2
4  Science     1.0      10
5  Science     1.5      20
6  Science     1.7      15
7  Science     3.0      35


Noissues=df[~df.index.isin(issues.index)]
print(Noissues)



  Subject  Marks1  Marks2
0  English     1.0      10
1  English     1.5      20
2  English     1.7      30
3  English     3.0      40
user_89869750
Related