Pfthb

I have a pandas dataframe as below

id name Base field1 field2 field3
1 AA Y Yes Consumer Not Applicable 
1 BB N Yes Consumer Not Applicable 
2 CC Y Yes Consumer Not Applicable 
2 DD N Yes Not Applicable Not Applicable 
2 EE N No Not Applicable Modified
3 FF Y Yes Not Applicable Applicable 
3 GG N Yes Not Applicable Not Applicable 
3 HH N Yes Not Applicable Not Applicable 

The expected result is to group this dataframe based on the ID column and check if the data on all the other columns are the same data in each group, and finally write the results.

I tried this to validate the data on each group but it always says TRUE

Code:

result_list=
for col in df.columns:
 result = df.groupby(level=0)[col].apply(lambda x: len(set(x))==1)
 result_list.append(result)

final = pd.concat(result_list,1)

The expected result is

id name field1 field2 field3 Error
1 AA Yes Consumer Not Applicable Pass 
1 BB Yes Consumer Not Applicable Pass
2 CC Yes Consumer Not Applicable field1, field2, field3 mismatch for ID: 2
2 DD Yes Not Applicable Not Applicable field1, field2, field3 mismatch for ID: 2
2 EE No Not Applicable Modified field1, field2, field3 mismatch for ID: 2
3 FF Yes Not Applicable Applicable field3 mismatch for ID: 3
3 GG Yes Not Applicable Not Applicable field3 mismatch for ID: 3
3 HH Yes Not Applicable Not Applicable field3 mismatch for ID: 3

Any help on this?

You may get what you want with the code (assuming that df has index named id):

def handler(df):
 for col in ['field1', 'field2', 'field3']:
 if df.loc[:, col].nunique() > 1:
 return 'error in for id '.format(col, df.index[0])
 else:
 return 'pass'

result = df.groupby(level=0).apply(handler)
result = df.reset_index().merge(result.to_frame().reset_index(), on='id')

result is:

 id name field1 field2 field3 0
0 1 AA Yes Consumer Not Applicable pass
1 1 BB Yes Consumer Not Applicable pass
2 2 CC Yes Consumer Not Applicable error in field1 for id 2
3 2 DD Yes Not Applicable Not Applicable error in field1 for id 2
4 2 EE No Not Applicable Modified error in field1 for id 2
5 3 FF Yes Not Applicable Applicable error in field3 for id 3
6 3 GG Yes Not Applicable Not Applicable error in field3 for id 3
7 3 HH Yes Not Applicable Not Applicable error in field3 for id 3

EDIT - minor editions in handler

def handler(df):
 cols = list()
 for col in ['field1', 'field2', 'field3']:
 if df.loc[:, col].nunique() > 1:
 cols.append(col)
 if cols:
 return 'error in for id '.format(', '.join(cols), df.index[0])
 else:
 return 'pass'

You could groupby id and then agg each column calculating the number of unique values per group and then you know there is a mistake where that number is greater than 1:

df[df.columns.drop('name')].groupby('id').agg(lambda x: len(x.unique()))>1

With this output, based on which you could construct your string.

 field1 field2 field3
id 
1 False False False
2 True True True
3 False False True