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.
CodePudding user response:
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
CodePudding user response:
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