Pfthb

Dataframe:

id Base field1 field2 field3
1 Y AA BB CC
1 N AA BB CC
1 N AA BB CC 
2 Y DD EE FF
2 N OO EE WT
2 N DD JQ FF
3 Y MM NN TT
3 Y MM NN TT 
3 N MM NN TT

The expected result is to group this dataframe based on the ID column, 2 validations should be performed.

1. first check if there is only one Base value 'Y' in each group. If its only true, then this row should be taken as a reference to validate step 2, else write the error as "More than one base Y found for ID" and proceed with step 1 for next ID




2. Validate if data on all the other columns that have "Base:N" match with the data on the columns where Base is 'Y', and write the names of fields that are not matching in the error column. product column is a unique field and it can be ignored for comparison of data.




3. Repeat this for all the ID int the dataframe.

The expected result is

id product Base field1 field2 field3 Error
1 A Y AA BB CC Reference value
1 B N AA BB CC Pass
1 C N AA BB CC Pass
2 D Y DD EE FF Reference value
2 E N OO EE WT field1, field3 mismatch 
2 F N DE JQ FF field1, field2 mismatch 
3 G Y MM NN TT more than 1 Y found for id:
3 H Y MM NN TT more than 1 Y found for id:
3 I N MM NN TT more than 1 Y found for id:

Any help on this?

Use custom function:

def f(x):
 #boolena mask for compare Y
 mask = x['Base'] == 'Y'
 #check multiple Y by sum of Trues
 if mask.sum() > 1:
 x['Error'] = 'more than 1 base Y found for id:'.format(x.name)
 else:
 #remove columns for not comparing with not equal
 cols = x.columns.difference(['Base','product'])
 mask1 = x[cols].ne(x.loc[mask, cols])
 #if difference get columns names by dot
 if mask1.values.any():
 vals = mask1.dot(mask1.columns + ', ').str.rstrip(', ') + ' mismatch with base' 
 x['Error'] = np.where(mask, 'Base: Y', vals) 
 else:
 x['Error'] = np.where(mask, 'Base: Y', 'Pass') 

 return x

df = df.groupby(level=0).apply(f)
print (df)
 product Base field1 field2 field3 Error
id 
1 A Y AA BB CC Base: Y
1 B N AA BB CC Pass
1 C N AA BB CC Pass
2 D Y DD EE FF Base: Y
2 E N OO EE WT field1, field3 mismatch with base
2 F N DD JQ FF field2 mismatch with base
3 G Y MM NN TT more than 1 base Y found for id:3
3 H Y MM NN TT more than 1 base Y found for id:3
3 I N MM NN TT more than 1 base Y found for id:3

Sample DataFrame:

df = pd.DataFrame('id': [1, 1, 1, 2, 2, 2, 3, 3, 3], 
 'product': ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I'], 
 'Base': ['Y', 'N', 'N', 'Y', 'N', 'N', 'Y', 'Y', 'N'], 
 'field1': ['AA', 'AA', 'AA', 'DD', 'OO', 'DD', 'MM', 'MM', 'MM'], 
 'field2': ['BB', 'BB', 'BB', 'EE', 'EE', 'JQ', 'NN', 'NN', 'NN'], 
 'field3': ['CC', 'CC', 'CC', 'FF', 'WT', 'FF', 'TT', 'TT', 'TT'])
df = df.set_index('id')
print (df)
 product Base field1 field2 field3
id 
1 A Y AA BB CC
1 B N AA BB CC
1 C N AA BB CC
2 D Y DD EE FF
2 E N OO EE WT
2 F N DD JQ FF
3 G Y MM NN TT
3 H Y MM NN TT
3 I N MM NN TT