To compare the values responding the different categories for each group(using groupby)

user_8954845 0
Viewed 138

I tried to apply rules for the status of each group. The rule will be: If the values of corner A & D are both smaller than the values of corner B & C, the status for this group will be pass. Otherwise, it's gonna be failed.

Here is the dataframe, df, below.

Item    Corner   Value   
A-001     A       0.1
A-001     B       0.5
A-001     C       0.4
A-001     D       0.1
A-002     A       0.3
A-002     B       0.6
A-002     C       0.2
A-002     D       0.1

And the expected result:

Item    Corner   Value   Status
A-001     A       0.1      pass (corner A &D < corner B&C)
A-001     B       0.5      pass
A-001     C       0.4      pass
A-001     D       0.1      pass
A-002     A       0.3      fail (corner A > corner C, so the status of this group failed)
A-002     B       0.6      fail
A-002     C       0.2      fail
A-002     D       0.1      fail

So, here's my solution, but the function is still incomplete...

def rule_status(df):
    
    corner_a = df.loc[df['corner'] == 'A', 'Value']
    corner_b = df.loc[df['corner'] == 'B', 'Value']
    corner_c = df.loc[df['corner'] == 'C', 'Value']
    corner_d = df.loc[df['corner'] == 'D', 'Value']

    if max(corner_a, corner_d) < min(corner_b, corner_c):
        return 'pass' 
    else:
        return 'fail'    

df.groupby('Item').apply(lambda x :rule_status(x))

However, in the function, I just get a series of values for corner_a corner_b corner_c & corner_d, instead of the specific value for each corner.

pandas-groupby pandas dataframe python-3.x
2 Answers

I suggest you to pivot the data then compare:

pivoted = df.pivot('Item', 'Corner', 'Value')

mask = pivoted[['A','D']].max(axis=1) < pivoted[['B','C']].min(axis=1)

df['Status'] = np.where(df['Item'].map(mask), 'pass', 'fail')

Output:

    Item Corner  Value Status
0  A-001      A    0.1   pass
1  A-001      B    0.5   pass
2  A-001      C    0.4   pass
3  A-001      D    0.1   pass
4  A-002      A    0.3   fail
5  A-002      B    0.6   fail
6  A-002      C    0.2   fail
7  A-002      D    0.1   fail
user_42384080

You can use:

df['Status'] = df['Item'].map(
    df.loc[df['Corner'].isin(['A', 'D'])].groupby('Item')['Value'].max()
      .lt(df.loc[df['Corner'].isin(['B', 'C'])].groupby('Item')['Value'].min())
      .replace({True: 'pass', False: 'fail'}))
print(df)

# Output:
    Item Corner  Value Status
0  A-001      A    0.1   pass
1  A-001      B    0.5   pass
2  A-001      C    0.4   pass
3  A-001      D    0.1   pass
4  A-002      A    0.3   fail
5  A-002      B    0.6   fail
6  A-002      C    0.2   fail
7  A-002      D    0.1   fail
user_152399510
Related